Given an HQL query, NH generated invalid SQL for SQL Server 2000 in the where clause for an inner-join, adding multiple "and" keywords at the end. I believe this was working before so it's a regression.
Hibernate version:
1.2.0GA and SVN
Mapping documents:
Code:
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" xmlns:hbm="urn:nhibernate-mapping-2.2"
xmlns:dm="http://www.megabyte.net/ns/dotnet/2.0/dm">
<!--
ArticleIndex:
Non-persistent class to retrieve an index of articles
-->
<import class="Megabyte.Data.CMS.ArticleIndex, Megabyte" rename="ArticleIndex"/>
<!--
Application:
Applications installed in this database.
-->
<class name="Megabyte.Data.Application, Megabyte" table="tb_application_mbcom">
<id name="ID" column="appl_pk">
<generator class="native"/>
</id>
<version name="Common_LastModified" type="Timestamp" column="aud_timestamp"/>
<property name="Name" column="appl_name" length="40"/>
<bag name="Parameters" cascade="all" inverse="true" generic="true">
<key column="parm_appl_fk"/>
<one-to-many class="Megabyte.Data.Parameter, Megabyte"/>
</bag>
<bag name="Roles" cascade="all" inverse="true" generic="true">
<key column="rol_appl_fk"/>
<one-to-many class="Megabyte.Data.Security.Role, Megabyte"/>
</bag>
</class>
<!--
Article:
Articles in the CMS. All text in an article is in the content table.
-->
<class name="Megabyte.Data.CMS.Article, Megabyte" table="tb_pagearticle_mbcom">
<id name="ID" column="art_pk">
<generator class="native"/>
</id>
<version name="Common_LastModified" type="Timestamp" column="aud_timestamp"/>
<property name="Path" column="art_code" length="50"/>
<property name="StartDate" column="art_startdate"/>
<property name="EndDate" column="art_enddate"/>
<many-to-one name="Application" column="art_appl_fk"/>
<property name="Common_RowStatus" type="string" length="1" column="aud_status"/>
<property name="Common_Session" type="long" column="aud_session"/>
</class>
<!--
Content:
Article contents. Each record is a version of the text in one language.
-->
<class name="Megabyte.Data.CMS.Content, Megabyte" table="tb_pagecontent_mbcom">
<id name="ID" column="pgc_pk">
<generator class="native"/>
</id>
<version name="Common_LastModified" type="Timestamp" column="aud_timestamp"/>
<property name="Language" column="pgc_langcode" length="2"/>
<property name="Version" column="pgc_version"/>
<property name="Status" column="pgc_status"/>
<property name="Title" column="pgc_title" length="80"/>
<property name="Summary" column="pgc_summary" length="4000"/>
<property name="Keywords" column="pgc_keywords" length="50"/>
<property name="BodyContent" column="pgc_bodycontent" length="102400"/>
<property name="CreatedOn" column="pgc_createdon"/>
<property name="ModifiedOn" column="pgc_modifiedon"/>
<property name="ApprovedOn" column="pgc_approvedon"/>
<many-to-one name="Article" column="pgc_art_fk"/>
<many-to-one name="CreatedBy" column="pgc_createdby"/>
<many-to-one name="ModifiedBy" column="pgc_modifiedby"/>
<many-to-one name="ApprovedBy" column="pgc_approvedby"/>
<property name="Common_RowStatus" type="string" length="1" column="aud_status"/>
<property name="Common_Session" type="long" column="aud_session"/>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():Code:
IQuery iq = NHSession.CreateQuery(
"SELECT new ArticleIndex(cnt.Article.ID,cnt.Article.Path,cnt.Title,cnt.Summary,cnt.Status) " +
"from Content cnt " +
"where cnt.Article.Application=:app and cnt.Language=:lang");
IList<Megabyte.Data.CMS.ArticleIndex> ret =
iq.SetParameter("app", app)
.SetString("lang", lang)
.List<Megabyte.Data.CMS.ArticleIndex>();
In the above, the parameter 'app' is an object of type Megabyte.Data.Application, 'lang' is a string.
Full stack trace of any exception that occurs:Code:
[SqlException (0x80131904): Line 1: Incorrect syntax near ')'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857322
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734934
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
System.Data.SqlClient.SqlDataReader.get_MetaData() +62
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() +9
NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Impl\BatcherImpl.cs:181
NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Loader\Loader.cs:1395
NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Loader\Loader.cs:415
NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Loader\Loader.cs:181
NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Loader\Loader.cs:1783
[ADOException: could not execute query
[ select article1_.art_pk as x0_0_, article1_.art_code as x1_0_, content0_.pgc_title as x2_0_, content0_.pgc_summary as x3_0_, content0_.pgc_status as x4_0_ from tb_pagecontent_mbcom content0_, tb_pagearticle_mbcom article1_ where (article1_.art_appl_fk=? and content0_.pgc_art_fk=article1_.art_pk and )and(content0_.pgc_langcode=? ) ]
Name: app - Value: Megabyte.Data.Application
Name: lang - Value: mt
[SQL: select article1_.art_pk as x0_0_, article1_.art_code as x1_0_, content0_.pgc_title as x2_0_, content0_.pgc_summary as x3_0_, content0_.pgc_status as x4_0_ from tb_pagecontent_mbcom content0_, tb_pagearticle_mbcom article1_ where (article1_.art_appl_fk=? and content0_.pgc_art_fk=article1_.art_pk and )and(content0_.pgc_langcode=? )]]
NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Loader\Loader.cs:1793
NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Loader\Loader.cs:1735
NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Loader\Loader.cs:1729
NHibernate.Hql.Classic.QueryTranslator.List(ISessionImplementor session, QueryParameters queryParameters) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Hql\Classic\QueryTranslator.cs:1008
NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters, IList results) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Impl\SessionImpl.cs:1773
NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Impl\SessionImpl.cs:1739
NHibernate.Impl.QueryImpl.List() in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Impl\QueryImpl.cs:87
Index.All(Application app, String lang) in x:\Systems\MBNETAPI2\CMS\App_Code\DB.cs:246
Index.All(Application app) in x:\Systems\MBNETAPI2\CMS\App_Code\DB.cs:278
CMSHierarchyArticleRoot..ctor(CMSHierarchyApplicationItem parent, Application app) in x:\Systems\MBNETAPI2\CMS\App_Code\CMSHierarchyData.cs:306
CMSHierarchyApplicationItem..ctor(CMSHierarchyRootItem parent, Application app) in x:\Systems\MBNETAPI2\CMS\App_Code\CMSHierarchyData.cs:256
CMSHierarchyRootItem..ctor() in x:\Systems\MBNETAPI2\CMS\App_Code\CMSHierarchyData.cs:163
CMSTreeDataSourceView.Select() in x:\Systems\MBNETAPI2\CMS\App_Code\CMSTreeDataSourceView.cs:29
System.Web.UI.WebControls.TreeView.DataBindNode(TreeNode node) +126
System.Web.UI.WebControls.TreeView.PerformDataBinding() +305
System.Web.UI.WebControls.HierarchicalDataBoundControl.PerformSelect() +82
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.TreeView.DataBind() +4
Template.Template_Load(Object sender, EventArgs e) in x:\Systems\MBNETAPI2\CMS\Template.master.cs:44
System.EventHandler.Invoke(Object sender, EventArgs e) +0
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Control.LoadRecursive() +131
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061
Name and version of the database you are using:The generated SQL (show_sql=true):select article1_.art_pk as x0_0_, article1_.art_code as x1_0_, content0_.pgc_title as x2_0_, content0_.pgc_summary as x3_0_, content0_.pgc_status as x4_0_ from tb_pagecontent_mbcom content0_, tb_pagearticle_mbcom article1_ where (article1_.art_appl_fk=? and content0_.pgc_art_fk=article1_.art_pk
and and )and(content0_.pgc_langcode=? ) ]
Workaround / hackIn NHibernate\SqlCommand\QueryJoinFragment.cs, in AddCondition(string condition), add a line at the start:
Code:
if (condition.Trim().Equals("")) return false;
in AddCondition(SqlString condition), add:
Code:
if (condition.ToString().Trim().Equals("")) return false;