Open
Description
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.
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_