Incorrect SQL generated fetching many-to-many with subclasses

[ThomasBudy]

Hi,

I've been using NHibernate for many years and I like it very much, thank you everybody who is contributing!

When I upgraded from NHibernate.5.3.15 with net461 to NHibernate.5.4.0 with net48 two of my hundreds of mapped
entities (Property and BeWriter) could not be loaded anymore. The error was

NHibernate.Exceptions.GenericADOException : could not execute query
System.Data.SqlClient.SqlException : Incorrect syntax near ')'.
Invalid usage of the option FIRST in the FETCH statement.

The reason was that the sql query was incorrect. As you can see below the parentheses are incorrect and also
the alias sets2_1_ is not defined, it should have been sets2_.

For entity Property it looked like this:

=== SQL query ===

SELECT
        TOP (@p0) this_.id      AS id1_91_2_         ,
        this_.[type]            AS type2_91_2_       ,
        this_.[name]            AS name3_91_2_       ,
        this_.[data_type]       AS data4_91_2_       ,
        this_.[is_vector]       AS is5_91_2_         ,
        this_.[value_table]     AS value6_91_2_      ,
        sets2_.property_id      AS property2_55_4_   ,
        set3_.id                AS set1_55_4_        ,
        set3_.id                AS id1_53_0_         ,
        set3_.[set_type]        AS set2_53_0_        ,
        set3_.[name]            AS name3_53_0_       ,
        set3_.[description]     AS description4_53_0_,
        set3_.parent_set_id     AS parent5_53_0_     ,
        set3_1_.property_id     AS property2_182_0_  ,
        set3_1_.filtered_set_id AS filtered3_182_0_  ,
        set3_1_.[inclusive]     AS inclusive4_182_0_ ,
        set3_1_.[string_val]    AS string5_182_0_    ,
        set3_1_.[float_val]     AS float6_182_0_     ,
        set3_1_.[date_val]      AS date7_182_0_      ,
        set3_2_.source_id       AS source2_183_0_    ,
        source4_.id             AS id1_90_1_         ,
        source4_.[name]         AS name2_90_1_       ,
        source4_.[description]  AS description3_90_1_
FROM
        ahs2_property this_
LEFT OUTER JOIN
        (ahs2_property_set sets2_)
ON
        this_.id=sets2_1_.property_id
LEFT OUTER JOIN
        ahs2_set set3_
ON
        sets2_.set_id=set3_.id
LEFT OUTER JOIN
        ahs2_filter_set set3_1_
ON
        set3_.id=set3_1_.set_id
LEFT OUTER JOIN
        ahs2_source_series_type set3_2_
ON
        set3_.id=set3_2_.set_id
LEFT OUTER JOIN
        ahs2_source source4_
ON
        set3_2_.source_id=source4_.id

=== Database and Dialect ===

Database and dialect: I've tried

1. NHibernate.Dialect.MsSql2012Dialect with a database on a Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
2. NHibernate.Dialect.MsSql2012Dialect with a database on a Microsoft SQL Server 2016 (SP2-GDR) (KB4583460)
3. NHibernate.Dialect.MsSql2008Dialect with a database on a Microsoft SQL Server 2008 (SP3) - 10.0.5538.0 (X64)

All with the same result.

=== The code I'm running ===

/// <summary>
        /// Test all entity mappings by loading them one by one
        /// </summary>
        [Test, TestCaseSource("ClassMetaData")]
        public void EntityTest(KeyValuePair<string, IClassMetadata> entry)
        {
            using(var session = NHUtils.SessionFactory.OpenSession()) {
                var meta = entry.Value;
                var query = session.CreateCriteria(entry.Key);

                for(var i = 0; i < meta.PropertyNames.Length; ++i) {
                    if(meta.PropertyTypes[i].IsAssociationType) {
                        query.Fetch(SelectMode.Fetch, meta.PropertyNames[i]);
                    }
                }

                // If this row throws there's a mismatch between the database and the hibernate mappings
                query.SetMaxResults(1).List();
            }
        }

=== The mapping ===

I'm using Fluent NHibernate and the resulting mapping looks like this:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" auto-import="false" namespace="Admc.Data.Ahs" assembly="Admc.Data, Version=3.5.9.0, Culture=neutral, PublicKeyToken=null" xmlns="urn:nhibernate-mapping-2.2">
  <class name="Property" table="ahs2_property" dynamic-update="true" dynamic-insert="true">
    <id name="Id" column="id" type="Int32">
      <generator class="native" />
    </id>
    <property name="Type" type="Admc.Data.EnumMapper`1[[Admc.Data.Ahs.PropertyType, Admc.Data, Version=3.5.9.0, Culture=neutral, PublicKeyToken=null]], Admc.Data, Version=3.5.9.0, Culture=neutral, PublicKeyToken=null" column="`type`" not-null="true" />
    <property name="Name" type="AnsiString" column="`name`" length="50" not-null="true" />
    <property name="DataType" type="Admc.Data.EnumMapper`1[[Admc.Data.DataType, Admc.Data, Version=3.5.9.0, Culture=neutral, PublicKeyToken=null]], Admc.Data, Version=3.5.9.0, Culture=neutral, PublicKeyToken=null" column="`data_type`" not-null="true" />
    <property name="IsVector" column="`is_vector`" not-null="true" />
    <property name="ValueTable" type="AnsiString" column="`value_table`" length="50" />
    <set name="Sets" table="ahs2_property_set" cascade="save-update, persist">
      <key column="property_id" />
      <many-to-many class="Set" column="set_id" />
    </set>
  </class>
</hibernate-mapping>

=== Database schema ===
The database schema is the following:

-- data_type --

CREATE TABLE [dbo].[ahs2_data_type] (
    [name] VARCHAR (50) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT PK_ahs2_data_type PRIMARY KEY
);

-- property_type --

CREATE TABLE [dbo].[ahs2_property_type] (
    [name] VARCHAR (50) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT PK_ahs2_property_type PRIMARY KEY
);

-- set_type --

CREATE TABLE [dbo].[ahs2_set_type] (
    [name] VARCHAR (50) COLLATE Latin1_General_BIN NOT NULL CONSTRAINT PK_ahs2_set_type PRIMARY KEY
);

-- property --

CREATE TABLE [dbo].[ahs2_property] (
    [id]          INT          IDENTITY (1, 1) NOT NULL CONSTRAINT PK_ahs2_property PRIMARY KEY,
    [type]        VARCHAR (50) COLLATE Latin1_General_BIN NOT NULL,
    [name]        VARCHAR (50) NOT NULL,
    [data_type]   VARCHAR (50) COLLATE Latin1_General_BIN NOT NULL,
    [value_table] VARCHAR (50) NULL,
    [is_vector]   BIT          NOT NULL DEFAULT 0,

    CONSTRAINT UK_ahs2_property_name_type UNIQUE([name], [type])
);

ALTER TABLE [dbo].[ahs2_property]  WITH CHECK ADD  CONSTRAINT [FK_ahs2_property_ahs2_property_type] FOREIGN KEY([type])
REFERENCES [dbo].[ahs2_property_type] ([name])
GO

ALTER TABLE [dbo].[ahs2_property] CHECK CONSTRAINT [FK_ahs2_property_ahs2_property_type]
GO

ALTER TABLE [dbo].[ahs2_property]  WITH CHECK ADD  CONSTRAINT [FK_ahs2_property_ahs2_data_type] FOREIGN KEY([data_type])
REFERENCES [dbo].[ahs2_data_type] ([name])
GO

ALTER TABLE [dbo].[ahs2_property] CHECK CONSTRAINT [FK_ahs2_property_ahs2_data_type]
GO

-- set --

CREATE TABLE [dbo].[ahs2_set] (
    [id]            INT           IDENTITY (1, 1) NOT NULL CONSTRAINT PK_ahs2_set PRIMARY KEY,
    [set_type]      VARCHAR (50)  COLLATE Latin1_General_BIN NOT NULL,
    [name]          VARCHAR (50)  NOT NULL,
    [parent_set_id] INT           NULL,
    [description]   VARCHAR (255) NULL,
    [unique_name]   BIT           NOT NULL CONSTRAINT DF_ahs2_set_unique_name DEFAULT (0)

);

ALTER TABLE [dbo].[ahs2_set]  WITH CHECK ADD  CONSTRAINT [FK_ahs2_set_ahs2_set] FOREIGN KEY([parent_set_id])
REFERENCES [dbo].[ahs2_set] ([id])
GO

ALTER TABLE [dbo].[ahs2_set] CHECK CONSTRAINT [FK_ahs2_set_ahs2_set]
GO

ALTER TABLE [dbo].[ahs2_set]  WITH CHECK ADD  CONSTRAINT [FK_ahs2_set_ahs2_set_type] FOREIGN KEY([set_type])
REFERENCES [dbo].[ahs2_set_type] ([name])
GO

ALTER TABLE [dbo].[ahs2_set] CHECK CONSTRAINT [FK_ahs2_set_ahs2_set_type]
GO

-- property_set --

CREATE TABLE [dbo].[ahs2_property_set] (
    [property_id] INT NOT NULL,
    [set_id]      INT NOT NULL,

    CONSTRAINT PK_ahs2_property_set PRIMARY KEY([set_id], [property_id])
);

ALTER TABLE [dbo].[ahs2_property_set]  WITH CHECK ADD  CONSTRAINT [FK_ahs2_property_set_ahs2_property] FOREIGN KEY([property_id])
REFERENCES [dbo].[ahs2_property] ([id])
GO

ALTER TABLE [dbo].[ahs2_property_set] CHECK CONSTRAINT [FK_ahs2_property_set_ahs2_property]
GO

ALTER TABLE [dbo].[ahs2_property_set]  WITH CHECK ADD  CONSTRAINT [FK_ahs2_property_set_ahs2_set] FOREIGN KEY([set_id])
REFERENCES [dbo].[ahs2_set] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ahs2_property_set] CHECK CONSTRAINT [FK_ahs2_property_set_ahs2_set]
GO

Thanks a lot for reading! I'm very grateful for any ideas on this problem :-)

Best regards, Thomas.

[bahusoid]

    <set name="Sets" table="ahs2_property_set" cascade="save-update, persist">
      <key column="property_id" />
      <many-to-many class="Set" column="set_id" />
    </set>

Please provide mapping for Set class (with all subclasses if any). If I got it right the problem with fetching Sets property.

[ThomasBudy]

Hi Bahusoid,

Thanks for reaching out, you find the mapping of Set below. It is a little hard to provide all the subclasses because Set is very central to the code. BeWriter is not connected to Set and not as central. Also not connected to anything with subclasses. Please let me know if you need to look at BeWriter instead.

When you read below mapping, please note that it is this part that relates to the table ahs2_property_set:

<set name="SetProperties" table="ahs2_property_set" cascade="save-update, persist">
      <key column="set_id" />
      <many-to-many class="Property" column="property_id" />
</set>

Best regards, Thomas

=== Set mapping ===

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" auto-import="false" namespace="Admc.Data.Ahs" assembly="Admc.Data, Version=3.5.9.0, Culture=neutral, PublicKeyToken=null" xmlns="urn:nhibernate-mapping-2.2">
  <class name="Set" table="ahs2_set" abstract="true" dynamic-update="true" dynamic-insert="true">
    <id name="Id" column="id" type="Int32">
      <generator class="native" />
    </id>
    <discriminator>
      <column name="set_type" length="50" not-null="true" sql-type="VARCHAR" />
    </discriminator>
    <property name="SetType" type="Admc.Data.EnumMapper`1[[Admc.Data.Ahs.SetType, Admc.Data, Version=3.5.9.0, Culture=neutral, PublicKeyToken=null]], Admc.Data, Version=3.5.9.0, Culture=neutral, PublicKeyToken=null" column="`set_type`" not-null="true" update="false" insert="false" />
    <property name="Name" type="AnsiString" column="`name`" length="50" not-null="true" />
    <set name="FieldSets" table="ahs2_set_field_set" cascade="save-update, persist">
      <key column="set_id" />
      <many-to-many class="FieldSet" column="field_set_id" />
    </set>
    <property name="Description" type="AnsiString" column="`description`" length="255" />
    <set name="Properties" inverse="true" cascade="all,delete-orphan">
      <key column="set_id" on-delete="cascade" />
      <one-to-many class="SetProperty" />
    </set>
    <set name="SetProperties" table="ahs2_property_set" cascade="save-update, persist">
      <key column="set_id" />
      <many-to-many class="Property" column="property_id" />
    </set>
    <set name="GroupRoles" table="ahs2_apf_set_group_role">
      <key column="set_id" />
      <composite-element class="Admc.Data.Apf.GroupRole">
        <many-to-one name="Role" column="role_id" not-null="true" foreign-key="FK_apf_group_role_role_id" />
        <many-to-one name="Group" column="group_id" not-null="true" foreign-key="FK_apf_group_role_group_id" />
      </composite-element>
    </set>
  </class>
</hibernate-mapping>

[bahusoid]

I think I know what it's about. Though I have no time for debugging right now.
It seems TableGroupJoinHelper calls to ColumnsDependOnSubclassJoins and GenerateTableAliasForColumn need to be skipped for columns present in join.Joinable.KeyColumnNames

If it's an option, you can try renaming ahs2_property_set.property_id column to something unique not used in your Set class and subclasses. As it seems it's currently confused with column property_id from some Set class or subclass table.

[ThomasBudy]

Hi again,

I think you have come up with something important :-)

I can confirm that class SetProperty also uses property_id. (SetProperty is not a subclass to Set by the way, it's just another collection of properties related to sets). And when I looked at BeWriter it also uses the same columnname parent_id twice in its mapping. So relieved you could spot this so quickly! I could not see what BeWriter and Property had in common for several days...

[bahusoid]

Fixed by #3252

[ThomasBudy]

Great work, thanks a lot!