Skip to content

Incorrect SQL when using Contains in Select and Distinct #2054

Open
@bacobart

Description

@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

Activity

fredericDelaporte

fredericDelaporte commented on Mar 16, 2019

@fredericDelaporte
Member

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

bacobart commented on Mar 18, 2019

@bacobart
Author

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

maca88 commented on Mar 20, 2019

@maca88
Contributor

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

fredericDelaporte commented on Mar 20, 2019

@fredericDelaporte
Member

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

hazzik commented on Mar 20, 2019

@hazzik
Member

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Participants

      @hazzik@bacobart@maca88@fredericDelaporte

      Issue actions

        Incorrect SQL when using Contains in Select and Distinct · Issue #2054 · nhibernate/nhibernate-core