1 2 Previous Next 18 Replies Latest reply: Oct 30, 2013 12:59 PM by 1051566 RSS

    Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)

    896182
      Just downloaded the Beta 3 version in order to successfully implement a "paging" strategy (orderby().skip().take()) that in previous version (1120240) does not work.

      Having done this, now I'm having various problems of bad generated queries that lead to EntityCommandExecutionException. I'm pasting portions of the generated queries only to illustrate this issues because the whole queries are too big, nevertheless I think this should be of help.

      Both problems occur in queries with "paging".


      ISSUE #1) ORDER BY uses incorrect table prefix
      (first part of query)
      ....
      LEFT OUTER JOIN "EPG"."Banks" "Extent9" ON "Extent1"."BankId" = "Extent9"."Id"     
      ORDER BY "Join8"."PaymentDateTime" ASC )  "Join8"   
      WHERE ("Join8"."row_number" > 10) AND (ROWNUM <= (10) )
      ...
      (query continues)

      Please note that the ORDER BY uses alias "Join8" that is the name given to that sub-query. It should be ORDER BY "Extent1"."PaymentDateTime" ASC, instead of "Join8"; if you manually change that, the query runs just fine.


      ISSUE #2) missing table name in FROM clause
      (first part of query)
      ....
      "RefundsAllowed", "Descripcion" FROM          ORDER BY "Id" DESC ) "EPG"."Payments"  ORDER BY "Id" DESC
      ...
      (query continues)
      Please note the missing table alias name between "FROM" and "ORDER BY".

      If more data is needed (e.g. db structure, linq query, generated query, etc.), I can provide them.

      Now I'll go back to the previous version that seems more stable than the new one.

      Thanks and regards,

      Ernesto Fuentes.

      PS: Technical data.
      Visual Studio .Net 2010 SP1
      .Net Framework 4
      ODAC1120250 32 bits (October 2011)
      Windows 7 32 bits
      Oracle Database 11g Express Edition
        • 1. Re: Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)
          15208
          Do the generated queries have APPLY keyword? Such as OUTER APPLY.
          The "LEFT OUTER JOIN" seems to indicates so.

          Would you please provide complete LINQ queries that reproduce the error?
          Scripts to create test objects are welcome.
          • 2. Re: Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)
            896182
            Hi, thanks for the quick response. The query does not use APPLY clause, but it uses various UNION ALL.

            The linq query is this one:
                            var query = context.Payments.
                                Include("Country").
                                Include("Currency").
                                Include("CustomerDocumentType").
                                Include("MerchantPaymentType").
                                Include("Merchant").
                                Include("PaymentCustomDatas").
                                Include("PaymentBreakdowns").
                                Include("PaymentProcessor").
                                Include("PaymentStatus").
                                Include("Bank").
                                Include("PaymentLocks").
                                Select(p => p);

                            query = query.OrderBy(p => p.PaymentDateTime);
                            query = query.Skip(10);
                            query = query.Take(10);

                            var result = query.ToList();
            Almost all includes are foreign keys, except for PaymentBreakdowns, PaymentLocks and PaymentCustomDatas that are one to many relationships (child entity collection).

            I'll post the full query in parts because it exceeds 3000 chars limit.

            I'll also provide the scripts.

            Thanks again,

            Ernesto Fuentes.
            • 3. Re: Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)
              896182
              SCRIPTS (part 1)

              -- DDL for Table Banks
              CREATE TABLE "EPG"."Banks" ("Id" NUMBER(10,0), "Name" NVARCHAR2(100), "Code" VARCHAR2(11 BYTE), "PaymentProcessorId" NUMBER(10,0)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ;
              -- DDL for Table Countries
              CREATE TABLE "EPG"."Countries" ("Id" NUMBER(5,0), "Name" NVARCHAR2(100), "Code" CHAR(2 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ;
              -- DDL for Table Currencies
              CREATE TABLE "EPG"."Currencies" ("Id" NUMBER(5,0), "Name" NVARCHAR2(100), "Code" CHAR(3 BYTE), "CountryId" NUMBER(5,0)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ;
              -- DDL for Table DocumentTypes
              CREATE TABLE "EPG"."DocumentTypes" ("Id" NUMBER(5,0), "Code" VARCHAR2(10 BYTE), "Description" NVARCHAR2(100), "CountryId" NUMBER(5,0), "IsLegalEntity" CHAR(1 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ;
              -- DDL for Table MerchantPaymentTypes
              CREATE TABLE "EPG"."MerchantPaymentTypes" ("Id" NUMBER(10,0), "Description" NVARCHAR2(100), "MerchantId" NUMBER(10,0), "Code" VARCHAR2(36 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ;
              -- DDL for Table Merchants
              CREATE TABLE "EPG"."Merchants" ("Id" NUMBER(10,0), "Name" NVARCHAR2(100), "CountryId" NUMBER(5,0), "Code" VARCHAR2(10 BYTE), "RefundsAllowed" CHAR(1 BYTE) DEFAULT 'F') SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ;
              • 4. Re: Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)
                896182
                SCRIPTS (part 2)


                -- DDL for Table PaymentBreakdown
                CREATE TABLE "EPG"."PaymentBreakdown" ("Id" NUMBER(19,0), "PaymentId" NUMBER(19,0), "ItemCode" VARCHAR2(20 BYTE), "Description" VARCHAR2(2000 BYTE), "Amount" NUMBER(18,5), "Balance" NUMBER(18,5)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ;
                -- DDL for Table PaymentCustomData
                CREATE TABLE "EPG"."PaymentCustomData" ("Id" NUMBER(19,0), "PaymentId" NUMBER(19,0), "Key" NVARCHAR2(24), "Value" NVARCHAR2(2000)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ;
                -- DDL for Table PaymentLocks
                CREATE TABLE "EPG"."PaymentLocks" ("Id" NUMBER(19,0), "PaymentId" NUMBER(19,0), "OperationLogId" VARCHAR2(36 BYTE), "LockFrom" TIMESTAMP (3), "LockTo" TIMESTAMP (3), "LockTypeId" NUMBER(3,0), "PaymentProcessorId" NUMBER(10,0), "IsActive" CHAR(1 BYTE), "Token" VARCHAR2(36 BYTE), "BankId" NUMBER(10,0)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ;
                -- DDL for Table PaymentProcessors
                CREATE TABLE "EPG"."PaymentProcessors" ("Id" NUMBER(10,0), "Name" NVARCHAR2(100), "Code" VARCHAR2(10 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ;
                -- DDL for Table PaymentStatuses
                CREATE TABLE "EPG"."PaymentStatuses" ("Id" NUMBER(3,0), "Code" CHAR(3 BYTE), "Descripcion" NVARCHAR2(100)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ;
                • 5. Re: Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)
                  896182
                  SCRIPTS (part 3)

                  -- DDL for Table Payments
                  CREATE TABLE "EPG"."Payments" ("Id" NUMBER(19,0), "TransactionId" NVARCHAR2(36), "MerchantId" NUMBER(10,0), "CountryId" NUMBER(5,0), "MerchantPaymentTypeId" NUMBER(10,0), "MerchantPaymentId" NVARCHAR2(36), "IsDirectDebit" CHAR(1 BYTE), "CustomerDocumentTypeId" NUMBER(5,0), "CustomerDocumentNumber" NVARCHAR2(36), "PaymentDateTime" TIMESTAMP (3), "ExpirationDateTime" TIMESTAMP (3), "TotalAmount" NUMBER(18,5), "CurrencyId" NUMBER(5,0), "BankId" NUMBER(10,0), "Description" NVARCHAR2(2000), "OperationLogIdCreation" VARCHAR2(36 BYTE), "OperationLogIdLastUpdate" VARCHAR2(36 BYTE), "PaymentStatusId" NUMBER(3,0), "PaymentProcessorId" NUMBER(10,0), "CreationDateTime" DATE, "ConfirmRejectCancelDateTime" TIMESTAMP (6), "ProcessDateTime" DATE, "DestinationAccount" VARCHAR2(24 BYTE), "OnSuccessUrl" VARCHAR2(200 BYTE), "OnErrorUrl" VARCHAR2(200 BYTE), "PaymentProcessorPaymentId" VARCHAR2(36 BYTE), "ConfirmationSentToMerchant" CHAR(1 BYTE) DEFAULT 'F', "RegistrationSentToProcessor" CHAR(1 BYTE) DEFAULT 'F', "Balance" NUMBER(18,5), "IsRefund" CHAR(1 BYTE) DEFAULT 'F') SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ;
                  • 6. Re: Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)
                    896182
                    Sorry, the scripts create only the tables without PKs or FKs. The 3000 char limit does not allow me to send the entire DB structure, nor the full problematic queries. Is there a way for me to send this to you?

                    Regards,

                    Ernesto Fuentes.
                    • 7. Re: Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)
                      15208
                      Thank you so much for providing the setup scripts and test case.
                      Please send email to Alex Keh (alex.keh@oracle.com) with your attachements.
                      He is the Program Manager of this product and he will handle it.
                      Thanks again.
                      • 8. Re: Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)
                        15208
                        Please send your email to Christian Shay (christian.shay@oracle.com), who is a PM too, as Alex is not available now.
                        • 9. Re: Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)
                          896182
                          Ok, I've already sent a database export and the incorrectly generated queries.

                          Regards,

                          Ernesto.
                          • 10. Re: Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)
                            15208
                            I have got your test case and reproduced the issue. Thank you very much.
                            • 11. Re: Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)
                              896182
                              Hi, I'm glad the info that we sent has been useful.

                              Let me ask: when do you think a Beta 4 (or some fix) would be available for downloading and testing?

                              I'm asking this because although Beta 3 has some useful improvements, in our tests it proved to be far more unstable than the previous one. We had to uninstall it and re-install the Beta2.

                              Thanks and regards,

                              Ernesto.
                              • 12. Re: Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)
                                899367
                                BUMP! Beta 3 == BAD. You should really pull this release as we fell into the same trap. I can't imagine anyone is using release successfully. We need this fixed ASAP.
                                • 13. Re: Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)
                                  Alex.Keh .Product.Manager-Oracle
                                  There will not be a Beta 4. The next release will be the production release very close to the end of this year.
                                  • 14. Re: Problem with generated query using ROWNUM and ORDERBY (ODAC1120250)
                                    Wallace Kelly
                                    If I am following correctly, there was a release 11.2.0.3.0 on December 28, 2011. Can anyone confirm that the bug described in this thread was fixed?
                                    1 2 Previous Next