WHERE IN SELECT uses wrong column

[fubar-coder]

Example:

var capabilitiesQuery = session.Query<CapabilityAssignment>()
    .OfType<UserCapabilityAssignment>()
    .Where(x => x.Capability.Path == adminCapabilityPath && x.Capability.Name == adminCapabilityName)
    .Select(x => x.UserId);

var adminUsers = await session.Query<ApplicationUser>()
    .Where(x => capabilitiesQuery.Contains(x.Id))
    .ToListAsync(cancellationToken);

The executed query is:

select
  applicatio0_.Id as id1_3_, applicatio0_.ConcurrencyStamp as concurrencystamp2_3_,
  applicatio0_.UserName as username3_3_, applicatio0_.NormalizedUserName as normalizedusername4_3_,
  applicatio0_.Email as email5_3_, applicatio0_.NormalizedEmail as normalizedemail6_3_,
  applicatio0_.PasswordHash as passwordhash7_3_, applicatio0_.SecurityStamp as securitystamp8_3_,
  applicatio0_.EmailConfirmed as emailconfirmed9_3_, applicatio0_.PhoneNumber as phonenumber10_3_,
  applicatio0_.PhoneNumberConfirmed as phonenumberconfirmed11_3_,
  applicatio0_.TwoFactorEnabled as twofactorenabled12_3_, applicatio0_.LockoutEnd as lockoutend13_3_,
  applicatio0_.LockoutEnabled as lockoutenabled14_3_, applicatio0_.AccessFailedCount as accessfailedcount15_3_,
  applicatio0_.IsPasswordChangeRequired as ispasswordchangerequired16_3_, applicatio0_.IsIntern as isintern17_3_,
  applicatio0_.IsArchived as isarchived18_3_
from 
 AspNetUsers applicatio0_ 
where 
  applicatio0_.Id in (
    select capability1_.Id
    from CapabilityAssignments capability1_
      inner join Capabilities capability2_ on capability1_.CapabilityId=capability2_.Id
    where capability1_.Type=1 and capability2_.Path=@p0 and capability2_.Name=@p1);

with:

@p0 = '/ventuno/account/' [Type: String (230:0:0)]
@p1 = 'administrator' [Type: String (20:0:0)]

The problem is the select capability1_.Id, which should be a capability1_.UserId, as specified by the .Select(x => x.UserId). This seems to be a regression since - I guess - 5.2.7.

[bahusoid]

Mappings?

Does it work the same without OfType: session.Query<UserCapabilityAssignment>(). And what's use of it?

[fubar-coder]

No, it doesn't work without it. This are some of the mappings in the project, but it should be enough to get some insight into the problem:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="CapabilityAssignment" table="CapabilityAssignments" discriminator-value="null" abstract="true">
    <id name="Id" generator="guid.comb" />
    <discriminator type="int" column="Type" not-null="true" />
    <many-to-one name="Capability" column="CapabilityId" />

    <subclass name="UserCapabilityAssignment" discriminator-value="1">
      <property name="UserId" not-null="true" />
    </subclass>
    <subclass name="RoleCapabilityAssignment" discriminator-value="2">
      <property name="RoleId" not-null="true" />
    </subclass>
  </class>
</hibernate-mapping>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="ApplicationUser" table="AspNetUsers" dynamic-insert="true" dynamic-update="true">
    <id name="Id" generator="guid.comb" />
    <version name="ConcurrencyStamp" type="MyCustomStringGuidVersionType">
      <column name="ConcurrencyStamp" not-null="true" />
    </version>
    <property name="UserName" length="256" not-null="true" />
    <property name="NormalizedUserName" length="256" not-null="true" unique-key="UserNameIndex" />
    <property name="Email" length="256" />
    <property name="NormalizedEmail" length="256" index="EmailIndex" />
    <property name="PasswordHash" />
    <property name="SecurityStamp" not-null="true" />
    <property name="EmailConfirmed" />
    <property name="PhoneNumber" />
    <property name="PhoneNumberConfirmed" />
    <property name="TwoFactorEnabled" />
    <property name="LockoutEnd" />
    <property name="LockoutEnabled" />
    <property name="AccessFailedCount" />
  </class>
</hibernate-mapping>

[bahusoid]

No, it doesn't work without it.

Well it does work for me without OfType...

So:

//Doesn't work with OfType. Id column in select instead of UserId:
session.Query<CapabilityAssignment>().OfType<UserCapabilityAssignment>().Where(...).Select(x => x.UserId)

//Works properly. UserId column in select:
session.Query<UserCapabilityAssignment>().Where(...).Select(x => x.UserId)

Test case can be found here: https://github.com/bahusoid/nhibernate-core/tree/gh2626

[fubar-coder]

@bahusoid OK, it seems it was a misunderstanding. The problem is indeed the first kind of query. It did work in previous versions, but stopped working in (I guess) 5.3.x.. Thank you for the effort to produce the test cases.

[bahusoid]

Yeah it works properly in 5.2.x

[fredericDelaporte]

Fixed by #2669