search

oracle-samples / oracle-db-examples

Examples of applications and tool usage for Oracle Database
https://www.oracle.com/database/technologies/
Other
1.31k stars 829 forks source link

EF Core invalid generated query #349

Open tomasfil opened 5 months ago

tomasfil commented 5 months ago

Hello, I have come across bug with EF Core Linq to entities. This query:

Query
    .Select(s=> new ValueSet<JobLine, List<ValueSet<Transaction, List<string>>>>
    {
        Value1 = s,
        Value2 =s.Transactions!.Select(x=>
        new ValueSet<Transaction, List<string>>
        {
            Value1 = x,
            Value2 = x.PairStatuses!
                                .Where(ps => (x.CreatedDate > ps.StartDate && x.CreatedDate < ps.EndDate) || (x.UpdatedDate > ps.StartDate && x.UpdatedDate < ps.EndDate))
                                .Select(s => s.User!.FullName)
                                .ToList()
        }).ToList()
    })
    .Where(jl => jl.CaseNo == caseNo)
    .Include(x => x.JobLineItems!)
    .ThenInclude(x => x.EbsItem!)
    .Include(x => x.JobHeader!)
    .ThenInclude(x => x.RequestType!)
    .Include(x=>x.Transactions!)
    .ThenInclude(x => x.TransactionItems)
    .AsNoTrackingWithIdentityResolution();

Results into this sql 

SELECT "t"."ID", "t"."ATTRIBUTE1", "t"."ATTRIBUTE2", "t"."ATTRIBUTE3", "t"."ATTRIBUTE4", "t"."ATTRIBUTE5", "t"."ATTRIBUTE6", "t"."CASE_NO", "t"."CREATED_BY", "t"."CREATED_DATE", "t"."FROM_INVENTORY", "t"."FROM_LOCATOR", "t"."JOB_HEADER_ID", "t"."MACHINE_ID", "t"."PICKING_ORDER", "t"."PRINTED", "t"."ROUTING_ID", "t"."SOURCE_LINE_ID", "t"."STATUS", "t"."TO_INVENTORY", "t"."TO_LOCATOR", "t"."UPDATED_BY", "t"."UPDATED_DATE", "t"."VALID", "t"."WINDOWS_USER", "t0"."ID", "v"."ID", "t1"."ID", "t1"."APP_ID", "t1"."APPL_REV", "t1"."ATTRIBUTE1", "t1"."ATTRIBUTE2", "t1"."ATTRIBUTE3", "t1"."ATTRIBUTE4", "t1"."ATTRIBUTE5", "t1"."ATTRIBUTE6", "t1"."CONTENT_ID", "t1"."CREATED_BY", "t1"."CREATED_DATE", "t1"."DEVICE_IP", "t1"."INVENTORY_ITEM_ID", "t1"."ITEM", "t1"."JOB_LINE_ID", "t1"."LOT_NO", "t1"."QUANTITY", "t1"."SHIPPING_QUANTITY", "t1"."SOURCE_LINE_ITEM_ID", "t1"."UOM", "t1"."UPDATED_BY", "t1"."UPDATED_DATE", "t1"."VALID", "t1"."VENDOR_LOT_NO", "t1"."WINDOWS_USER", "t1"."ID0", "t1"."BUILD_IN_WIP_FLAG", "t1"."CLASS", "t1"."DESCRIPTION", "t1"."EXPIRATION_DAYS", "t1"."FIXED_LOT_MULTIPLIER", "t1"."INITIAL_GOKI", "t1"."INVENTORY_ITEM_STATUS_CODE", "t1"."ITEM0", "t1"."ITEM_NO", "t1"."ITM_CREATED_BY", "t1"."ITM_CREATION_DATE", "t1"."ITM_UPDATE_DATE", "t1"."ITM_UPDATED_BY", "t1"."MTL_TRANSACTIONS_ENABLED_FLAG", "t1"."PRD_CD", "t1"."RECEIVING_ROUTING_ID", "t1"."RECEIVING_ROUTING_NM", "t1"."SHIPPING", "t1"."STOCK_ENABLED_FLAG", "t1"."UOM_CD", "t1"."WEIGHT", "t0"."ATTRIBUTE1", "t0"."ATTRIBUTE2", "t0"."ATTRIBUTE3", "t0"."ATTRIBUTE4", "t0"."ATTRIBUTE5", "t0"."ATTRIBUTE6", "t0"."CREATED_BY", "t0"."CREATED_DATE", "t0"."DIRECTION_FLG", "t0"."DOC_NAME", "t0"."IMPORTED_BY", "t0"."IMPORTED_DATE", "t0"."JOB_TYPE", "t0"."MARKED", "t0"."NEED_TO_DATE", "t0"."NOTE", "t0"."PL_LAST_PRINT_DATE", "t0"."PL_PRINT_COUNT", "t0"."PRIORITY", "t0"."REQUEST_TYPE_ID", "t0"."SOURCE", "t0"."SOURCE_HEADER_ID", "t0"."SOURCE_NAME", "t0"."STATUS", "t0"."UPDATED_BY", "t0"."UPDATED_DATE", "t0"."VALID", "v"."ATTRIBUTE1", "v"."ATTRIBUTE2", "v"."ATTRIBUTE3", "v"."ATTRIBUTE4", "v"."ATTRIBUTE5", "v"."ATTRIBUTE6", "v"."COL_ORDER", "v"."CREATED_BY", "v"."CREATED_DATE", "v"."DESTINATION_PARTY_ID", "v"."PAIR_TRX", "v"."REC_NUM", "v"."REQUEST_DESC", "v"."REQUEST_TYPE_FLOW", "v"."SOURCE_PARTY_ID", "v"."TRX_MATERIAL_TYPE", "v"."UPDATED_BY", "v"."UPDATED_DATE", "v"."VALID", "t3"."ID", "t3"."APP_ID", "t3"."APPL_REV", "t3"."ATTRIBUTE1", "t3"."ATTRIBUTE2", "t3"."ATTRIBUTE3", "t3"."ATTRIBUTE4", "t3"."ATTRIBUTE5", "t3"."ATTRIBUTE6", "t3"."CASE_NO", "t3"."CODE_TEXT", "t3"."CREATED_BY", "t3"."CREATED_DATE", "t3"."DEVICE_IP", "t3"."ERP_INTERFACED", "t3"."FROM_ACCOUNT", "t3"."FROM_ACCOUNT_ID", "t3"."FROM_INVENTORY", "t3"."FROM_LOCATOR", "t3"."FROM_LOCATOR_ID", "t3"."JOB_LINE_ID", "t3"."LABEL_ID", "t3"."MACHINE_ID", "t3"."TO_ACCOUNT", "t3"."TO_ACCOUNT_ID", "t3"."TO_INVENTORY", "t3"."TO_LOCATOR", "t3"."TO_LOCATOR_ID", "t3"."TRX_TYPE_ID", "t3"."UPDATED_BY", "t3"."UPDATED_DATE", "t3"."VALID", "t3"."WINDOWS_USER", "t3"."ID0", "t3"."APP_ID0", "t3"."APPL_REV0", "t3"."ATTRIBUTE10", "t3"."ATTRIBUTE20", "t3"."ATTRIBUTE30", "t3"."ATTRIBUTE40", "t3"."ATTRIBUTE50", "t3"."ATTRIBUTE60", "t3"."CONTENT_ID", "t3"."CREATED_BY0", "t3"."CREATED_DATE0", "t3"."DEVICE_IP0", "t3"."ERP_INTERFACE_ID", "t3"."INVENTORY_ITEM_ID", "t3"."ITEM", "t3"."LOT_NO", "t3"."QUANTITY", "t3"."SOURCE_TRX_ITEM_ID", "t3"."TRX_ID", "t3"."UOM", "t3"."UPDATED_BY0", "t3"."UPDATED_DATE0", "t3"."VALID0", "t3"."VENDOR_LOT_NO", "t3"."WINDOWS_USER0", "t6"."ID", "t6"."APP_ID", "t6"."APPL_REV", "t6"."ATTRIBUTE1", "t6"."ATTRIBUTE2", "t6"."ATTRIBUTE3", "t6"."ATTRIBUTE4", "t6"."ATTRIBUTE5", "t6"."ATTRIBUTE6", "t6"."CASE_NO", "t6"."CODE_TEXT", "t6"."CREATED_BY", "t6"."CREATED_DATE", "t6"."DEVICE_IP", "t6"."ERP_INTERFACED", "t6"."FROM_ACCOUNT", "t6"."FROM_ACCOUNT_ID", "t6"."FROM_INVENTORY", "t6"."FROM_LOCATOR", "t6"."FROM_LOCATOR_ID", "t6"."JOB_LINE_ID", "t6"."LABEL_ID", "t6"."MACHINE_ID", "t6"."TO_ACCOUNT", "t6"."TO_ACCOUNT_ID", "t6"."TO_INVENTORY", "t6"."TO_LOCATOR", "t6"."TO_LOCATOR_ID", "t6"."TRX_TYPE_ID", "t6"."UPDATED_BY", "t6"."UPDATED_DATE", "t6"."VALID", "t6"."WINDOWS_USER", "t6"."ID0", "t6"."APP_ID0", "t6"."APPL_REV0", "t6"."ATTRIBUTE10", "t6"."ATTRIBUTE20", "t6"."ATTRIBUTE30", "t6"."ATTRIBUTE40", "t6"."ATTRIBUTE50", "t6"."ATTRIBUTE60", "t6"."CONTENT_ID", "t6"."CREATED_BY0", "t6"."CREATED_DATE0", "t6"."DEVICE_IP0", "t6"."ERP_INTERFACE_ID", "t6"."INVENTORY_ITEM_ID", "t6"."ITEM", "t6"."LOT_NO", "t6"."QUANTITY", "t6"."SOURCE_TRX_ITEM_ID", "t6"."TRX_ID", "t6"."UOM", "t6"."UPDATED_BY0", "t6"."UPDATED_DATE0", "t6"."VALID0", "t6"."VENDOR_LOT_NO", "t6"."WINDOWS_USER0", "t6"."FULL_NAME", "t6"."ID1", "t6"."ID00"
      FROM "WMS"."T_JOB_LINES" "t"
      LEFT JOIN "WMS"."T_JOB_HEADERS" "t0" ON ("t"."JOB_HEADER_ID" = "t0"."ID")
      LEFT JOIN "WMS"."V_REQUEST_TYPE" "v" ON ("t0"."REQUEST_TYPE_ID" = "v"."ID")
      LEFT JOIN (
          SELECT "t2"."ID", "t2"."APP_ID", "t2"."APPL_REV", "t2"."ATTRIBUTE1", "t2"."ATTRIBUTE2", "t2"."ATTRIBUTE3", "t2"."ATTRIBUTE4", "t2"."ATTRIBUTE5", "t2"."ATTRIBUTE6", "t2"."CONTENT_ID", "t2"."CREATED_BY", "t2"."CREATED_DATE", "t2"."DEVICE_IP", "t2"."INVENTORY_ITEM_ID", "t2"."ITEM", "t2"."JOB_LINE_ID", "t2"."LOT_NO", "t2"."QUANTITY", "t2"."SHIPPING_QUANTITY", "t2"."SOURCE_LINE_ITEM_ID", "t2"."UOM", "t2"."UPDATED_BY", "t2"."UPDATED_DATE", "t2"."VALID", "t2"."VENDOR_LOT_NO", "t2"."WINDOWS_USER", "v0"."ID" "ID0", "v0"."BUILD_IN_WIP_FLAG", "v0"."CLASS", "v0"."DESCRIPTION", "v0"."EXPIRATION_DAYS", "v0"."FIXED_LOT_MULTIPLIER", "v0"."INITIAL_GOKI", "v0"."INVENTORY_ITEM_STATUS_CODE", "v0"."ITEM" "ITEM0", "v0"."ITEM_NO", "v0"."ITM_CREATED_BY", "v0"."ITM_CREATION_DATE", "v0"."ITM_UPDATE_DATE", "v0"."ITM_UPDATED_BY", "v0"."MTL_TRANSACTIONS_ENABLED_FLAG", "v0"."PRD_CD", "v0"."RECEIVING_ROUTING_ID", "v0"."RECEIVING_ROUTING_NM", "v0"."SHIPPING", "v0"."STOCK_ENABLED_FLAG", "v0"."UOM_CD", "v0"."WEIGHT"
          FROM "WMS"."T_JOB_LINES_ITEM" "t2"
          LEFT JOIN "WMS"."V_EBS_ITEM" "v0" ON ("t2"."INVENTORY_ITEM_ID" = "v0"."ID")
      ) "t1" ON ("t"."ID" = "t1"."JOB_LINE_ID")
      LEFT JOIN (
          SELECT "t4"."ID", "t4"."APP_ID", "t4"."APPL_REV", "t4"."ATTRIBUTE1", "t4"."ATTRIBUTE2", "t4"."ATTRIBUTE3", "t4"."ATTRIBUTE4", "t4"."ATTRIBUTE5", "t4"."ATTRIBUTE6", "t4"."CASE_NO", "t4"."CODE_TEXT", "t4"."CREATED_BY", "t4"."CREATED_DATE", "t4"."DEVICE_IP", "t4"."ERP_INTERFACED", "t4"."FROM_ACCOUNT", "t4"."FROM_ACCOUNT_ID", "t4"."FROM_INVENTORY", "t4"."FROM_LOCATOR", "t4"."FROM_LOCATOR_ID", "t4"."JOB_LINE_ID", "t4"."LABEL_ID", "t4"."MACHINE_ID", "t4"."TO_ACCOUNT", "t4"."TO_ACCOUNT_ID", "t4"."TO_INVENTORY", "t4"."TO_LOCATOR", "t4"."TO_LOCATOR_ID", "t4"."TRX_TYPE_ID", "t4"."UPDATED_BY", "t4"."UPDATED_DATE", "t4"."VALID", "t4"."WINDOWS_USER", "t5"."ID" "ID0", "t5"."APP_ID" "APP_ID0", "t5"."APPL_REV" "APPL_REV0", "t5"."ATTRIBUTE1" "ATTRIBUTE10", "t5"."ATTRIBUTE2" "ATTRIBUTE20", "t5"."ATTRIBUTE3" "ATTRIBUTE30", "t5"."ATTRIBUTE4" "ATTRIBUTE40", "t5"."ATTRIBUTE5" "ATTRIBUTE50", "t5"."ATTRIBUTE6" "ATTRIBUTE60", "t5"."CONTENT_ID", "t5"."CREATED_BY" "CREATED_BY0", "t5"."CREATED_DATE" "CREATED_DATE0", "t5"."DEVICE_IP" "DEVICE_IP0", "t5"."ERP_INTERFACE_ID", "t5"."INVENTORY_ITEM_ID", "t5"."ITEM", "t5"."LOT_NO", "t5"."QUANTITY", "t5"."SOURCE_TRX_ITEM_ID", "t5"."TRX_ID", "t5"."UOM", "t5"."UPDATED_BY" "UPDATED_BY0", "t5"."UPDATED_DATE" "UPDATED_DATE0", "t5"."VALID" "VALID0", "t5"."VENDOR_LOT_NO", "t5"."WINDOWS_USER" "WINDOWS_USER0"
          FROM "WMS"."T_TRX" "t4"
          LEFT JOIN "WMS"."T_TRX_ITEM" "t5" ON ("t4"."ID" = "t5"."TRX_ID")
      ) "t3" ON ("t"."ID" = "t3"."JOB_LINE_ID")
      LEFT JOIN (
          SELECT "t7"."ID", "t7"."APP_ID", "t7"."APPL_REV", "t7"."ATTRIBUTE1", "t7"."ATTRIBUTE2", "t7"."ATTRIBUTE3", "t7"."ATTRIBUTE4", "t7"."ATTRIBUTE5", "t7"."ATTRIBUTE6", "t7"."CASE_NO", "t7"."CODE_TEXT", "t7"."CREATED_BY", "t7"."CREATED_DATE", "t7"."DEVICE_IP", "t7"."ERP_INTERFACED", "t7"."FROM_ACCOUNT", "t7"."FROM_ACCOUNT_ID", "t7"."FROM_INVENTORY", "t7"."FROM_LOCATOR", "t7"."FROM_LOCATOR_ID", "t7"."JOB_LINE_ID", "t7"."LABEL_ID", "t7"."MACHINE_ID", "t7"."TO_ACCOUNT", "t7"."TO_ACCOUNT_ID", "t7"."TO_INVENTORY", "t7"."TO_LOCATOR", "t7"."TO_LOCATOR_ID", "t7"."TRX_TYPE_ID", "t7"."UPDATED_BY", "t7"."UPDATED_DATE", "t7"."VALID", "t7"."WINDOWS_USER", "t8"."ID" "ID0", "t8"."APP_ID" "APP_ID0", "t8"."APPL_REV" "APPL_REV0", "t8"."ATTRIBUTE1" "ATTRIBUTE10", "t8"."ATTRIBUTE2" "ATTRIBUTE20", "t8"."ATTRIBUTE3" "ATTRIBUTE30", "t8"."ATTRIBUTE4" "ATTRIBUTE40", "t8"."ATTRIBUTE5" "ATTRIBUTE50", "t8"."ATTRIBUTE6" "ATTRIBUTE60", "t8"."CONTENT_ID", "t8"."CREATED_BY" "CREATED_BY0", "t8"."CREATED_DATE" "CREATED_DATE0", "t8"."DEVICE_IP" "DEVICE_IP0", "t8"."ERP_INTERFACE_ID", "t8"."INVENTORY_ITEM_ID", "t8"."ITEM", "t8"."LOT_NO", "t8"."QUANTITY", "t8"."SOURCE_TRX_ITEM_ID", "t8"."TRX_ID", "t8"."UOM", "t8"."UPDATED_BY" "UPDATED_BY0", "t8"."UPDATED_DATE" "UPDATED_DATE0", "t8"."VALID" "VALID0", "t8"."VENDOR_LOT_NO", "t8"."WINDOWS_USER" "WINDOWS_USER0", "t9"."FULL_NAME", "t9"."ID" "ID1", "t9"."ID0" "ID00"
          FROM "WMS"."T_TRX" "t7"
          LEFT JOIN "WMS"."T_TRX_ITEM" "t8" ON ("t7"."ID" = "t8"."TRX_ID")
          OUTER APPLY (
              SELECT "t11"."FULL_NAME", "t10"."ID", "t11"."ID" "ID0"
              FROM "WMS"."T_PAIR_STATUS" "t10"
              LEFT JOIN "WMS"."T_USERS" "t11" ON ("t10"."USER_ID" = "t11"."ID")
              WHERE (((("t7"."MACHINE_ID" = "t10"."MACHINE_ID")) AND (((((((("t7"."CREATED_DATE" > "t10"."START_DATE")) AND (("t7"."CREATED_DATE" < "t10"."END_DATE"))))) OR ((((("t7"."UPDATED_DATE" > "t10"."START_DATE")) AND (("t7"."UPDATED_DATE" < "t10"."END_DATE"))))))))))
          ) "t9"
      ) "t6" ON ("t"."ID" = "t6"."JOB_LINE_ID")
      WHERE ("t"."CASE_NO" = :caseNo_0)

      ORDER BY "t"."ID", "t0"."ID", "v"."ID", "t1"."ID", "t1"."ID0", "t3"."ID", "t3"."ID0", "t6"."ID", "t6"."ID0", "t6"."ID1"

And that fails on:

OracleExecutionStrategy.ExecuteAsync() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00907: missing right parenthesis
         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, Int64 internalInitialJSONFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, 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.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
      --- End of stack trace from previous location ---
         at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
         at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)

System info: Oracle 11.2g .NET 7 Oracle.EntityFrameworkCore 7.21.13

This is not the first time that this parenthesis bug arrived. I have simulated the same complex query with MSSQL and Postgre and both work. You should add some kind of unit test to test the generated queries especially for the parenthesis.

tomasfil commented 5 months ago

The issues is definetly the select. Same reproduces directly on table only with select without includes:

 Query.Select(s =>
 new ValueSet<decimal, IEnumerable<string>>
 {
     Value1 = s.Id,
     Value2 = s.PairStatuses!.Where(ps =>
         (s.CreatedDate > ps.StartDate && s.CreatedDate < ps.EndDate) || (s.UpdatedDate > ps.StartDate && s.UpdatedDate < ps.EndDate))
     .Select(s => s.User!.FullName)
 })
     .Where(x=> transactionIds.Contains(x.Id))
alexkeh commented 5 months ago

@tomasfil Does this problem occur with DB 19c or higher? Oracle DB 11.2 is no longer supported. ODP.NET no longer tests nor fixes bugs against DB 11.2 except under rare circumstances.

If so, can you provide a complete EF Core test case? We'll try to reproduce the issue in in house and fix it if we identify an ODP.NET code problem.

Of note, MS ends EF Core 7 support next week. Oracle plans to desupport EF Core 7 at the same time.

tomasfil commented 4 months ago

I will try and provide simple test case soon.

alexkeh commented 4 months ago

Providing a test case may be moot if this bug doesn't occur with Oracle DB 19c or higher. The ODP.NET team doesn't plan to implement bug fixes for out of support DB versions.

alexkeh commented 4 months ago

@gvenzl You can close this issue as this appears to be problem specific to an out of support DB version.