Linq HasMany Property FirstOrDefaultQuery Fails with oracle

[gokhanabatay]

Hi,
Below linq expression works on postgresql with successfully but does not work on Oracle Db.
The main problem is there is a misplaced rownum with second inner sql for Oracle.

LinqQueryFirstOrDefaultTest

var customers =
    session.Query<Customer>()
           .Select(x => new
           {
               x.Guid,
               x.Name,
               City = x.Addresses.Where(y => y.City == "NewYork").Select(y => y.City).FirstOrDefault()
           }).ToList();

Postgresql:

2019-03-18 18:09:01,553 [NonParallelWorker] DEBUG SQL - select customer0_.GUID as col_0_0_, customer0_.NAME as col_1_0_, (select addresses1_.CITY from ADDRESS addresses1_ where customer0_.GUID=addresses1_.CUSTOMER_GUID and addresses1_.CITY=:p0 limit 1) as col_2_0_ from CUSTOMER customer0_;:p0 = 'NewYork' [Type: String (0:0:0)]

Oracle:

ERROR AbstractBatcher - Could not execute query: select customer0_.GUID as col_0_0_, customer0_.NAME as col_1_0_, (select CITY from ( select addresses1_.CITY from ADDRESS addresses1_ where customer0_.GUID=addresses1_.CUSTOMER_GUID and addresses1_.CITY=:p0 ) where rownum <=1) as col_2_0_ from CUSTOMER customer0_
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "CUSTOMER0_"."GUID": invalid identifier
   at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(DbCommand cmd) in C:\Sources\nhibernate-core\src\NHibernate\AdoNet\AbstractBatcher.cs:line 235
2019-03-18 18:01:56,332 [14  ] WARN  ADOExceptionReporter - ORA-00904: "CUSTOMER0_"."GUID": invalid identifier
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "CUSTOMER0_"."GUID": invalid identifier
   at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(DbCommand cmd) in C:\Sources\nhibernate-core\src\NHibernate\AdoNet\AbstractBatcher.cs:line 235
   at NHibernate.Loader.Loader.GetResultSet(DbCommand st, QueryParameters queryParameters, ISessionImplementor session, IResultTransformer forcedResultTransformer) in C:\Sources\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 1512

Generated SQL:

select
    customer0_.GUID as col_0_0_,
    customer0_.NAME as col_1_0_,
    (
        select
            CITY
        from
            (
                select
                    addresses1_.CITY
                from
                    ADDRESS addresses1_
                where
                    customer0_.GUID = addresses1_.CUSTOMER_GUID
                    and addresses1_.CITY = :p0
            )
        where
            rownum <= 1
    ) as col_2_0_
from
    CUSTOMER customer0_

[gokhanabatay]

@hazzik @fredericDelaporte @maca88

[hazzik]

First of all what version of Oracle do you use?

[hazzik]

This is a known problem for oracle that it cannot access the outside expression. There can be 2 root causes:

• 11g and before has a restriction on how deep you can go into the subquery, eg you can only reference outer expression up to 1 level deep.
• The inner expression is executed first and therefore the outer table is not visible.

[bahusoid]

there is a misplaced rownum with second inner sql for Oracle.

Not sure what it means. Can you provide expected SQL that works?

[gokhanabatay]

Oracle Version: 11g R2

You are right @hazzik but is it a known issue of NHibernate?
I know Oracle behaves different but is this a kind of difference needed to be supported by NHibernate?
If sql is generated like below it will be ok.

select
    customer0_.GUID as col_0_0_,
    customer0_.NAME as col_1_0_,
    (
        select
            addresses1_.CITY
        from
            ADDRESS addresses1_
        where
            customer0_.GUID = addresses1_.CUSTOMER_GUID
            and addresses1_.CITY = :p0
            and rownum <= 1
    ) as col_2_0_
from
    CUSTOMER customer0_

[gokhanabatay]

Now as a workaround we will use Max() in linq, but I think that it's a mandotory difference that needed to be supported by NHibernate.

session.Query<Customer>()
       .Select(x => new
       {
           x.Guid,
           x.Name,
           City = x.Addresses.Where(y => y.City == "NewYork").Select(y => y.City).Max()
       }).ToList();

select
    customer0_.GUID as col_0_0_,
    customer0_.NAME as col_1_0_,
    (
        select
            max(addresses1_.CITY)
        from
            ADDRESS addresses1_
        where
            customer0_.GUID = addresses1_.CUSTOMER_GUID
            and addresses1_.CITY = :p0
    ) as col_2_0_
from
    CUSTOMER customer0_;
:p0 = 'NewYork'

[hazzik]

is it a known issue of NHibernate?

Somehow know. There are several similar reports here, but not exact this issue.

[hazzik]

Ok, there is an interesting read for whomever wants to implement the improvement for the paging solving this issue.

[bahusoid]

Suggested article actually doesn't provide any new insights on this matter. But it shows that suggested fix by @gokhanabatay (adding rownum directly to query) won't work with ordered/grouped queries:

The FROM/WHERE clause goes first.
ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
SELECT is applied.
GROUP BY is applied.
HAVING is applied.
ORDER BY is applied.

[hazzik]

Yes, this is what I had in mind.