Open
Description
I'm using LINQ with the MSSQL2012 driver, NHibernate version 5.2.4. Executing below query results in the following exception:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified
public class TestEntity
{
public virtual int Id { get; set; }
public virtual string PropertyA { get; set; }
public virtual string PropertyB { get; set; }
}
var result = session.Query<TestEntity>()
.Select(x => x.PropertyA.Contains("test") ? x.PropertyA : x.PropertyB)
.OrderBy(a => a)
.Distinct()
.ToList();
The generated SQL looks is listed below. NHibernate is adding PropertyA and PropertyB in the select, but not in the order by.
SELECT DISTINCT CASE
WHEN userquery_0_.PropertyA LIKE ('%' + @p0 + '%')
THEN 1
ELSE 0
END AS col_0_0_
,userquery_0_.PropertyA AS col_1_0_
,userquery_0_.PropertyB AS col_2_0_
FROM [TestEntity] userquery_0_
ORDER BY (
CASE
WHEN userquery_0_.PropertyA LIKE ('%' + @p0 + '%')
THEN userquery_0_.PropertyA
ELSE userquery_0_.PropertyB
END
) AS
I've made a reproduction in linqpad: download
Activity
fredericDelaporte commentedon Mar 16, 2019
If this is a regression, can you tell in which latest version it was working as expected?
The trouble comes from NHibernate choosing to evaluate part of the projection in-memory, after execution of the query, while it cannot do that for the order-by and so fully translate it to SQL.
But this causes the order-by to be based on an expression which is not part of the select, and since SQL-Server 2008, SQL-Server does no more support having a select distinct with an order-by using expressions which are not also selected.
And anyway, it also wrecks the query semantic, since the distinct will no more be applied only to the final expression but also to both of its underlying columns.
It looks like we should disable in memory evaluation of projections when a distinct is used.
bacobart commentedon Mar 18, 2019
Seems like this broke between 4.0.4.4000 and 4.1.1.4000. I thought it broke in 5.x because we are upgrading to 5.x (from 4.0.4.4000), but after some testing it seems this was already broken in 4.1.1.4000.
maca88 commentedon Mar 20, 2019
Indeed, the issue occurred with #522 because of the
SelectClauseNominator
change that modified the behavior ofConditional
expressions to be executed on the client side. With that change, queries that contain a select with a conditional subquery also stopped working. I've made PR that tries to restore the previous behavior.fredericDelaporte commentedon Mar 20, 2019
So that is a regression of 4.1.0.
Should we back-port such an old regression all the way down to 4.1.x?
I would rather handle it as just an old bug, which will be just fixed in next minor.
hazzik commentedon Mar 20, 2019
No.
Yes.