Incorrect SQL when using Contains in Select and Distinct

[bacobart]

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

[fredericDelaporte]

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]

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]

Indeed, the issue occurred with #522 because of the SelectClauseNominator change that modified the behavior of Conditional 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]

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]

Should we back-port such an old regression all the way down to 4.1.x?

No.

handle it as just an old bug, which will be just fixed in next minor.

Yes.