NH-3616 - LINQ/HQL problems with CollectionFetchMode.Subselect with Skip and Take

[nhibernate-bot]

Dawid Ciecierski created an issue — 16th May 2014, 20:15:32:

This seems similar to NH-2296. Imagine an entity with a set of strings (think photo with a set of tags). If you set CollectionFetchMode.Subselect and then try to query using LINQ or HQL while including Skip and Take, this will result in SQL that is both illegal (references identifiers by incorrect aliases and throws an exception when executed) as well as somewhat incomplete at a higher level (does not include the TOP part as explained in NH-2296).

Show me the code

C#-side

public class Photo
{
  public int Id { get; set; }
  public string Name { get; set; }
  public ISet<string> Tags { get; set; }

  public Photo()
  {
    Tags = new HashedSet<string>();
  }
}

public class PhotoMap : ClassMapping<Photo>
{
  public PhotoMap()
  {
    Lazy(false);
    Id(p => p.Id, id => id.Generator(Generators.Identity));
    Property(p => p.Name);
    Set(
      p => p.Tags,
      collectionMapping: collectionMapping =>
      {
        collectionMapping.Fetch(CollectionFetchMode.Subselect);
        collectionMapping.Lazy(CollectionLazy.NoLazy);
        collectionMapping.Key(k => k.Column("PhotoId"));
        collectionMapping.Table("PhotoTags");
      },
      mapping: mapping => mapping.Element(e => e.Column("Tag")));
  }
}

var fetchPhoto = session.CreateCriteria<Photo>()
  .SetFirstResult(1).SetMaxResults(2).List<Photo>(); // Criteria-OK
var fetchPhoto = session.Query<Photo>()
  .Skip(1).Take(2).ToList(); // LINQ-EXCEPTION
var fetchPhoto = session.CreateQuery(
  "select p from Photo p skip 1 take 2").List<Photo>(); // HQL-EXCEPTION

Correct queries produced by CreateCriteria

SELECT TOP (2) Id0*0*,
                 Name0*0*
FROM   (SELECT this*.Id                                      as Id0_0*,
               this*.Name                                    as Name0_0*,
               ROW*NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort*row
        FROM   Photo this_) as query
WHERE  query.*_hibernate_sort*row > 1
ORDER  BY query.*_hibernate_sort*row

SELECT tags0*.PhotoId as PhotoId0*,
       tags0*.Tag     as Tag0*
FROM   PhotoTags tags0_
WHERE  tags0_.PhotoId in (SELECT TOP (2) Id
                          FROM   (select this_.Id,
                                         ROW*NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort*row
                                  FROM   Photo this_) as query
                          WHERE  query.*_hibernate_sort*row > 1
                          ORDER  BY query.*_hibernate_sort*row)

Incorrect queries produced by LINQ / HQL

SELECT TOP (2) Id0_,
                 Name0_
FROM   (select photo0*.Id                                    as Id0*,
               photo0*.Name                                  as Name0*,
               ROW*NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort*row
        from   Photo photo0_) as query
WHERE  query.*_hibernate_sort*row > 1
ORDER  BY query.*_hibernate_sort*row

SELECT tags0*.PhotoId as PhotoId0*,
       tags0*.Tag     as Tag0*
FROM   PhotoTags tags0_
WHERE  tags0*.PhotoId in (select photo0*.Id
                          FROM   (select photo0*.Id                                    as Id0*,
                                         photo0*.Name                                  as Name0*,
                                         ROW*NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort*row
                                  from   Photo photo0_) as query
                          WHERE  query.*_hibernate_sort*row > 1)

Sample project

Please find the attached sample project configured to restore NHibernate from nuget and use a local database named Playground.

In closing

Did some poking around but are not familiar enough with the way NHibernate works internally to offer a good guess regarding the reason or have a shot at making a patch. All I do know is that SubselectClauseExtractor removes the TOP(2) part from the initial select, and SubselectFetch.ToSubselectString is not able to discern the alias given to photo0_.Id, but most likely the real reason is somewhere up the execution order...

Will keep looking though, and post here in case I come across something interesing.

If you think I should offer a unit test in addition to the sample project, I could help get the ball rolling and do at least that.

---

Dawid Ciecierski added a comment — 16th May 2014, 20:17:52:

Issue also <being discussed over at Google Groups>(https://groups.google.com/forum/#!topic/nhusers/CVc0FzoJz98).

---

Dawid Ciecierski added a comment — 17th May 2014, 9:32:06:

Trying to come up with a test led me to a test named SubselectFetchWithLimit in NHibernate.Test.SubselectFetchTest.SubselectFetchFixture. The test loads two out of three parent items from the database and touches their child collections to trigger subselect loads. But then it makes sure that the third parent item's child collection also is initialized (where I believe it should not be if subselect was working well), doing so with a comment "The test for True is the test of H3.2".

With further digging it turns out the issue reported here is similar to ones that have been worked on in the past, namely NH-1123 and NH-1155. (What is different is that in my case I'm seeing exceptions too rather than just sub-optimal queries.) These issues have been closed, but fixes for them seem to have been resersed later on (e.g. (https://github.com/nhibernate/nhibernate-core/commit/4b61a6cf678fce15b37d799110f8aed6fe8c6dd7)) and it appears this did not get any further attention.

---

Dawid Ciecierski added a comment — 17th May 2014, 10:31:04:

I'm attaching three new tests covering the Skip/Take SQL errors:

• SubselectFetchWithOffsetLimit extends the existing SubselectFetchWithLimit with an additional SetFirstResult call. (/) when run against 3.3.3.SP1.
• SubselectFetchWithHqlOffsetLimit changes the above by including the offset and limit parameters in HQL rather than via the SetFirstResult / SetMaxResults calls. (x) when run against 3.3.3.SP1.
• SubselectFetchLinqWithOffsetLimit excercises the LINQ provider attempting to use it with the Skip and Take extensions. (x) when run against 3.3.3.SP1.

---

Dawid Ciecierski added a comment — 17th May 2014, 12:08:38:

(Sorry, I uploaded the wrong patch file — one utilising some custom classes instead of the included Parent/Child. v2 should be the right one.)

[teamcolombo]

any news on this bug?

[hazzik]

@teamcolombo

As far as I know, no one work on this. You are more than welcome to submit a PR. See contributing.