Overriding OpenConnection in adapter in v5.8

Posts   
 
    
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 13-Apr-2021 02:13:33   

We override DataAccessAdapterCore.OpenConnection in our adapters but when I've attempted to upgrade from 5.7 to 5.8 that override sometimes doesn't get called (e.g on save) since OpenConnection is now delegated to TransactionBase. Is there any workaround to enable our code to always handle OpenConnection as per 5.7?

Jeremy Thomas
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 13-Apr-2021 07:53:31   

Hi Tom,
The connection is opened when needed, not in every call. So, maybe that's why you see it to happen sometimes. Are you sure this was different on v5.7? Why are you overriding that method? What do you need to do?

David Elizondo | LLBLGen Support Team
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 13-Apr-2021 13:45:55   

daelmo wrote:

Are you sure this was different on v5.7?

Yes, I've looked at your source code. We have unit tests that fail for 5.8 but pass for 5.7

daelmo wrote:

Why are you overriding that method? What do you need to do?

We are setting DB credentials. Specifically we catch authentication exceptions and retry with generated credentials. Point is this has been working fine for years.

Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 14-Apr-2021 09:11:43   

It's CreateConnection you're after, not OpenConnection? As OpenConnection is a virtual method in DataAccessAdapterCore that's always called. (also in the case of SaveEntity for instance, and it's the only method that calls transactionadapter.openconnection). CreateConnection is delegated to TransactionAdapter but it uses a lambda which calls adapter.CreateNewPhysicalConnection (which is the virtual method you have to override if you need custom code when creating a connection).

For OpenConnection, please provide a repro case that doesn't open the connection through adapter.OpenConnection. A piece of code that performs an action that will bypass adapter.openconnection is enough.

We refactored a lot of code in the adapter class into classes which are now shared among selfservicing and adapter to simplify code. We went to great lengths to make sure the protected/public API stayed as-is, but perhaps we missed a spot.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 14-Apr-2021 09:20:32   

I think I've spotted it. If StartTransaction is required to be called (or you call it manually) it's bypassing OpenConnection as it directly calls StartTransaction on the TransactionAdapter which internally calls openconnection.

It will call OpenConnection later on again in the generic code for executing a query (but as the connection is already open it's a no-op), however for your use case that's too late, as the initial openconnection already crashed in your case).

Will see if I can create a test and will likely issue a hotfix for this as it's easy to fix and shouldn't happen indeed. Stay tuned

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 14-Apr-2021 10:43:44   

We released a hotfix build for 5.8.1, which should solve the problem. Please let us know if it doesn't.

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 14-Apr-2021 21:36:43   

Otis wrote:

We released a hotfix build for 5.8.1, which should solve the problem. Please let us know if it doesn't.

Yep that fixed that, thanks. Though now some our unit tests which rollback using System.Transactions are failing. From ORM profiler it looks like not everything in a particular unit test is using the same System.Transactions anymore

Attachments
Filename File size Added on Approval
Snapshot_20210514090957.opsnapshot 76,682 13-May-2021 23:20.03 Approved
Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 15-Apr-2021 09:27:30   

Please provide a usable repro case. Btw, we only added an openconnection prior to starting the transaction, so these tests should fail with the older 5.8 runtime as well. Was that the case?

System.Transactions.TransactionScope use an ambient transaction, I don't know what you mean with ' the same system.transactions'

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 14-May-2021 00:46:07   

Otis wrote:

Please provide a usable repro case. Btw, we only added an openconnection prior to starting the transaction, so these tests should fail with the older 5.8 runtime as well. Was that the case?

System.Transactions.TransactionScope use an ambient transaction, I don't know what you mean with ' the same system.transactions'

I got that wrong, what is happening is the connection is closing and when it opens again it's not in the TransactionScope and hence things fail. I've attached ORM profiler snapshots to this (v5.8.1) and my previous post(v5.5) to show the differences between v5.8.1 and v5.5. v5.8.1 and v5.8.0 are the same.

Attachments
Filename File size Added on Approval
Snapshot_20210514091600.opsnapshot 59,631 14-May-2021 00:46.16 Approved
Jeremy Thomas
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 14-May-2021 02:48:04   

I couldn't spot the differences between both snapshots shared. Both have the exact same connections (compared by the queries inside) enlisted in the same DTC.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 14-May-2021 09:26:54   

TomDog wrote:

Otis wrote:

Please provide a usable repro case. Btw, we only added an openconnection prior to starting the transaction, so these tests should fail with the older 5.8 runtime as well. Was that the case?

System.Transactions.TransactionScope use an ambient transaction, I don't know what you mean with ' the same system.transactions'

I got that wrong, what is happening is the connection is closing and when it opens again it's not in the TransactionScope and hence things fail. I've attached ORM profiler snapshots to this (v5.8.1) and my previous post(v5.5) to show the differences between v5.8.1 and v5.5. v5.8.1 and v5.8.0 are the same.

Like I said, please provide a usable repro case or at least code that shows what you're doing. "when it is closed and when it is reopend" can be done in a lot of different ways. Which transaction scope a connection is enlisted is up to the ado.net provider, the only thing we do (since the beginning) with transaction scope is what we should do: when an ambient transaction is present (provided by the transaction scope) we enlist the transaction with it and the adapter has code to tell the scope when a transaction rolled back/committed and postpones the final commit to the point when the transaction scope signals all participants the transaction committed (or rolled back). Our tests with these work fine.

Keep in mind that debugging tehse things could alter the behavior you might expect. For instance, with sql server a single connection won't get enlisted in a transaction scope due to an optimization but there are debugging scenarios thinkable where a second connection is opened and both then get enlisted.

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 14-May-2021 13:53:17   

Walaa wrote:

I couldn't spot the differences between both snapshots shared. Both have the exact same connections (compared by the queries inside) enlisted in the same DTC.

The 3 statements in Connection #5 in the .57 snapshot after 'INSERT INTO [AQD].[qaoc_Finding_Log]' are in the a different connection in the .00 snapshot. I.e. in the .00 snapshot 'INSERT INTO [AQD].[qaoc_Finding_Log]' is in Connection #3 but the next statement is in Connection #4.

I don't know when I'll find time to repro this but basically the code writes a record then reads it but the read fails as the transaction ended with the connection closing. In previous versions of LLBL the connection stays open.

Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 15-May-2021 09:24:22   

So you're saying: after a rollback the connection is closed while in previous versions the connection was kept open?

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 15-May-2021 18:13:42   

Otis wrote:

So you're saying: after a rollback the connection is closed while in previous versions the connection was kept open?

Pseudo code:

    [TestMethod]
    public void SomeCreateReadTest()
    {
      using (new TransactionScope())
      {
        var adapter = new DataAccessAdapter(){KeepConnectionOpen = true};
        var id = SomeCodeThatCreatesAndSavesAnEntity(adapter );
        // In v5.8 the connection is now closed while previously it stayed open
        SomeCodeThatReadsThatEntity(adapter ,id).Should().BeTrue(); // In v5.8 this is no longer in Transaction
      }
    }
Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 16-May-2021 08:56:14   

And you share an adapter? This isn't really helpful, please post the test code so we can see what's going on, we otherwise have to fill in the details which matter. For instance, in the code you show, the adapter used in the first method closes the connection, the adapter used in the second method opens a new one. This is logical and would have been done in previous versions too, so there's way more detail needed to fix this. If you share an adapter then things are different, but I can't see that from the pseudocode you posted.

It's also the question if there's something that needs fixing, I still don't know what it is that's actually wrong. That some unit test fails doesn't mean the actual API is buggy

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 16-May-2021 14:16:40   

Otis wrote:

And you share an adapter?

Yes, I've edited the Pseudo code above to be indicate that. This test against Northwind works fine in v5.8 though the KeepConnectionOpen seems to be ignored while in earlier version it's all done in 1 connection.

    [TestMethod]
    public void CreateReadEntityTest()
    {
      using (new TransactionScope())
      {
        var northwindLinqMetaData = new LinqMetaData(new DataAccessAdapter())
        {
          AdapterToUse = {KeepConnectionOpen = true}
        };
        var product = new ProductEntity {ProductName = "ProductName"};
        northwindLinqMetaData.AdapterToUse.SaveEntity(product, true).Should().BeTrue();
        var productEntity = northwindLinqMetaData.Product.FirstOrDefault(p => p.ProductId == product.ProductId);
        productEntity.Should().NotBeNull();
      }
    }
Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-May-2021 10:28:53   

thanks, we'll have a look

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-May-2021 12:32:05   

Reproduced indeed. https://imgur.com/6otcdTz

Connection 7 and 8 are in 5.8, connection 9 is same test but on 5.7. Looking into it

(edit) bug. In TrasnactionBase.Commit it calls Reset, but in Reset it always closes the connection.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-May-2021 13:18:48   

Fixed. See the hotfix build for 5.8.2

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 17-May-2021 23:42:29   

Otis wrote:

Fixed. See the hotfix build for 5.8.2

Yep sorted it thanks simple_smile but I've still got one failing test so not quite out of the woods yet. Completely unrelated to above obviously but getting this against Oracle in 5.8 - ORA-00904: "LPA_L1"."ActionPriorityLevelActionPriorityLevelSeqNo": invalid identifier.

SELECT *
FROM   (SELECT "LPA_L1"."Action",
               "LPA_L1"."ActionID",
               "LPA_L1"."ActionPriorityLevelActionPriorityLevel",
               "LPA_L1"."ActionPriorityLevelActionPriorityLevelSeqNo",
               "LPA_L1"."ActionIDSortable",
               "LPA_L1"."ActionNo",
               "LPA_L1"."ActionResponseText",
               "LPA_L1"."ActionStatus",
               "LPA_L1"."ActionStatusId",
               "LPA_L1"."ActionType",
               "LPA_L1"."AssignedTo",
               "LPA_L1"."CauseNo",
               "LPA_L1"."CompletedOn",
               "LPA_L1"."ContactPerson",
               "LPA_L1"."Department",
               "LPA_L1"."DueDate",
               "LPA_L1"."ExtActionReference",
               "LPA_L1"."ExtAuditReference",
               "LPA_L1"."WRNumber",
               "LPA_L1"."WRPeriod",
               "LPA_L1"."WRType",
               "LPA_L1"."FindingCategory",
               "LPA_L1"."FindingID",
               "LPA_L1"."FindingNo",
               "LPA_L1"."IsActionRequest",
               "LPA_L1"."Likelihood",
               "LPA_L1"."RecheckDue",
               "LPA_L1"."EnteredById",
               "LPA_L1"."RegisteredOn",
               "LPA_L1"."ReplacementAction",
               "LPA_L1"."ReplacementActionID",
               "LPA_L1"."RiskCauseNo",
               "LPA_L1"."RiskNo",
               "LPA_L1"."Severity",
               "LPA_L1"."StaffMemberName",
               "LPA_L1"."TrackedBy",
               "LPA_L1"."WRID",
               "LPA_L1"."LPFA_28",
               "LPA_L1"."SourceGroupRole",
               "LPA_L1"."SourceRole",
               "LPA_L1"."Overdue",
               "LPA_L1"."ApprovalDefinitionID",
               "LPA_L1"."RejectedApprovalDefinitionID",
               "LPA_L1"."ResponsibleDepartmentID"
        FROM   (SELECT "LPA_L3"."ACTION"                 AS "Action",
                       "LPA_L3"."ACTION_ID"              AS "ActionID",
                       "LPA_L4"."ACTION_PRIORITY_LEVEL"  AS "F__1830505937",
                       "LPA_L4"."SEQNO"                  AS "F__1143628958",
                       "LPA_L3"."ACTION_ID_SORTABLE"     AS "ActionIDSortable",
                       "LPA_L3"."ACTION_NO"              AS "ActionNo",
                       "LPA_L3"."ACTION_RESPONSE_TEXT"   AS "ActionResponseText",
                       "LPA_L3"."ACTION_STATUS_TEXT"     AS "ActionStatus",
                       "LPA_L3"."ACTION_STATUS"          AS "ActionStatusId",
                       "LPA_L5"."ACTION_TYPE"            AS "ActionType",
                       "LPA_L3"."ASSIGNED_TO"            AS "AssignedTo",
                       "LPA_L3"."CAUSE_NO"               AS "CauseNo",
                       "LPA_L3"."COMPLETED_ON"           AS "CompletedOn",
                       "LPA_L3"."CONTACT_PERSON"         AS "ContactPerson",
                       "LPA_L3"."DEPARTMENT"             AS "Department",
                       "LPA_L3"."DUE_DATE"               AS "DueDate",
                       "LPA_L3"."EXT_ACTION_REFERENCE"   AS "ExtActionReference",
                       "LPA_L6"."EXT_AUDIT_REFERENCE"    AS "ExtAuditReference",
                       "LPA_L6"."WR_NUMBER"              AS "WRNumber",
                       "LPA_L6"."WR_PERIOD"              AS "WRPeriod",
                       "LPA_L6"."WR_TYPE"                AS "WRType",
                       "LPA_L7"."FINDING_CATEGORY"       AS "FindingCategory",
                       "LPA_L3"."FINDING_ID"             AS "FindingID",
                       "LPA_L6"."FINDING_NO"             AS "FindingNo",
                       "LPA_L3"."IS_ACTION_REQUEST"      AS "IsActionRequest",
                       "LPA_L9"."GLOBAL_LIKELIHOOD"      AS "Likelihood",
                       "LPA_L3"."RECHECK_DUE"            AS "RecheckDue",
                       "LPA_L3"."REGISTERED_BY_ID"       AS "EnteredById",
                       "LPA_L3"."REGISTERED_ON"          AS "RegisteredOn",
                       "LPA_L10"."ACTION_NO"             AS "ReplacementAction",
                       "LPA_L3"."REPLACEMENT_ACTION_ID"  AS "ReplacementActionID",
                       "LPA_L3"."RISK_CAUSE_NO"          AS "RiskCauseNo",
                       "LPA_L3"."RISK_NO"                AS "RiskNo",
                       "LPA_L11"."GLOBAL_SEVERITY"       AS "Severity",
                       "LPA_L3"."REGISTERED_BY_NAME"     AS "StaffMemberName",
                       "LPA_L3"."TRACKED_BY"             AS "TrackedBy",
                       "LPA_L3"."WRID",
                       1                                 AS "LPFA_28",
                       "LPA_L2"."StaffRole"              AS "SourceGroupRole",
                       Trim((SELECT "LPA_L15"."STAFF_ROLE" AS "StaffRole"
                             FROM   ((("AQD"."QAOC_WORK_REQUEST_VIEW" "LPA_L12"
                                       RIGHT JOIN "AQD"."QAOC_FINDING" "LPA_L13"
                                           ON "LPA_L12"."WR_NUMBER" = "LPA_L13"."WR_NUMBER"
                                              AND "LPA_L12"."WR_PERIOD" = "LPA_L13"."WR_PERIOD"
                                              AND "LPA_L12"."WR_TYPE" = "LPA_L13"."WR_TYPE")
                                      INNER JOIN "AQD"."QAOC_WR_STAFF" "LPA_L14"
                                          ON "LPA_L12"."WR_NUMBER" = "LPA_L14"."WR_NUMBER"
                                             AND "LPA_L12"."WR_PERIOD" = "LPA_L14"."WR_PERIOD"
                                             AND "LPA_L12"."WR_TYPE" = "LPA_L14"."WR_TYPE")
                                     LEFT JOIN "AQD"."QAOC_WR_STAFF_ROLE" "LPA_L15"
                                         ON "LPA_L15"."STAFF_ROLE_ID" = "LPA_L14"."STAFF_ROLE_ID")
                             WHERE  (((("LPA_L13"."FINDING_ID" = "LPA_L3"."FINDING_ID")
                                       AND ("LPA_L14"."STAFF_MEMBER" = 136 /* :p3 */))))
                                AND rownum <= 1)
                            || ' ' /* :p2 */
                            || "LPA_L2"."StaffRole")     AS "SourceRole",
                       CASE
                         WHEN CASE
                                WHEN ((("LPA_L3"."ACTION_STATUS" <> 8 /* :p4 */)
                                       AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p5 */))
                                      AND ("LPA_L3"."DUE_DATE" < '20210518' /* :p6 */)) THEN 1
                                ELSE 0
                              END = 1 THEN 3 /* :p8 */
                         ELSE
                             CASE
                               WHEN CASE
                                      WHEN (((("LPA_L3"."ACTION_STATUS" <> 8 /* :p9 */)
                                              AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p10 */))
                                             AND ("LPA_L3"."DUE_DATE" < '20210519' /* :p11 */))
                                             OR ((("LPA_L3"."ACTION_STATUS" <> 8 /* :p12 */)
                                                  AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p13 */))
                                                 AND ("LPA_L3"."DUE_DATE" < '20210525' /* :p14 */))) THEN 1
                                      ELSE 0
                                    END = 1 THEN 2 /* :p16 */
                               ELSE
                                   CASE
                                     WHEN CASE
                                            WHEN (((("LPA_L3"."ACTION_STATUS" <> 8 /* :p17 */)
                                                    AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p18 */))
                                                   AND ("LPA_L3"."DUE_DATE" < '20210526' /* :p19 */))
                                                   OR ((("LPA_L3"."ACTION_STATUS" <> 8 /* :p20 */)
                                                        AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p21 */))
                                                       AND ("LPA_L3"."DUE_DATE" < '20210601' /* :p22 */))) THEN 1
                                            ELSE 0
                                          END = 1 THEN 1 /* :p24 */
                                     ELSE 0 /* :p26 */
                                   END
                             END
                       END                               AS "Overdue",
                       (SELECT "LPLA_18"."APPROVAL_DEFINITION_ID" AS "ApprovalDefinitionID"
                        FROM   "AQD"."APP_APPROVAL" "LPLA_18"
                        WHERE  (((("LPA_L3"."ACTION_ID" = "LPLA_18"."RECORD_ID")
                                  AND ((("LPLA_18"."CURRENT_FLAG" = 1 /* :p27 */)
                                        AND ("LPLA_18"."RECORD_TYPE" = 1 /* :p28 */))
                                       AND EXISTS
                                           (SELECT "LPLA_19"."STEP_STATE_ID" AS "StepStateID"
                                            FROM   "AQD"."APP_APPROVAL_STEP" "LPLA_19"
                                            WHERE  ("LPLA_18"."APPROVAL_ID" = "LPLA_19"."APPROVAL_ID"
                                                AND ("LPLA_19"."STEP_STATE_ID" = 1 /* :p29 */)))))))
                           AND rownum <= 1)              AS "ApprovalDefinitionID",
                       (SELECT "LPLA_20"."APPROVAL_DEFINITION_ID" AS "ApprovalDefinitionID"
                        FROM   "AQD"."APP_APPROVAL" "LPLA_20"
                        WHERE  (((("LPA_L3"."ACTION_ID" = "LPLA_20"."RECORD_ID")
                                  AND (("LPLA_20"."CURRENT_FLAG" = 1 /* :p30 */)
                                       AND EXISTS
                                           (SELECT "LPLA_21"."STEP_STATE_ID" AS "StepStateID"
                                            FROM   "AQD"."APP_APPROVAL_STEP" "LPLA_21"
                                            WHERE  ("LPLA_20"."APPROVAL_ID" = "LPLA_21"."APPROVAL_ID"
                                                AND ("LPLA_21"."STEP_STATE_ID" = 3 /* :p31 */)))))))
                           AND rownum <= 1)              AS "RejectedApprovalDefinitionID",
                       "LPA_L3"."RESPONSIBLE_DEPARTMENT" AS "ResponsibleDepartmentID"
                FROM   ((((((((((SELECT "LPA_L21"."STAFF_ROLE" AS "StaffRole",
                                       "LPA_L17"."FINDING_ID" AS "FindingID"
                                FROM   ((((("AQD"."QAOC_WORK_REQUEST_VIEW" "LPA_L16"
                                            RIGHT JOIN "AQD"."QAOC_FINDING" "LPA_L17"
                                                ON "LPA_L16"."WR_NUMBER" = "LPA_L17"."WR_NUMBER"
                                                   AND "LPA_L16"."WR_PERIOD" = "LPA_L17"."WR_PERIOD"
                                                   AND "LPA_L16"."WR_TYPE" = "LPA_L17"."WR_TYPE")
                                           INNER JOIN "AQD"."QAOC_WR_GROUP_ASSIGNMENT" "LPA_L18"
                                               ON "LPA_L16"."WR_NUMBER" = "LPA_L18"."WR_NUMBER"
                                                  AND "LPA_L16"."WR_PERIOD" = "LPA_L18"."WR_PERIOD"
                                                  AND "LPA_L16"."WR_TYPE" = "LPA_L18"."WR_TYPE")
                                          INNER JOIN "AQD"."QAOC_ASSIGNMENT_GROUP" "LPA_L19"
                                              ON "LPA_L19"."ASSIGNMENT_GROUP_ID" = "LPA_L18"."ASSIGNMENT_GROUP_ID")
                                         INNER JOIN "AQD"."QAOC_ASSIGNMENT_GROUP_MEMBER" "LPA_L20"
                                             ON "LPA_L19"."ASSIGNMENT_GROUP_ID" = "LPA_L20"."ASSIGNMENT_GROUP_ID")
                                        INNER JOIN "AQD"."QAOC_WR_STAFF_ROLE" "LPA_L21"
                                            ON "LPA_L21"."STAFF_ROLE_ID" = "LPA_L18"."STAFF_ROLE_ID")
                                WHERE  ((("LPA_L20"."STAFF_MEMBER_ID" = 136 /* :p32 */)))) "LPA_L2"
                                RIGHT JOIN "AQD"."QAOC_ACTN_VIEWSECURED" "LPA_L3"
                                    ON "LPA_L3"."FINDING_ID" = "LPA_L2"."FindingID")
                               LEFT JOIN "AQD"."QAOC_ACTION_PRIORITY_LEVEL" "LPA_L4"
                                   ON "LPA_L4"."ACTION_PRIORITY_LEVEL_ID" = "LPA_L3"."ACTION_PRIORITY_LEVEL_ID")
                              INNER JOIN "AQD"."QAOC_ACTION_TYPE" "LPA_L5"
                                  ON "LPA_L5"."ACTION_TYPE_ID" = "LPA_L3"."ACTION_TYPE_ID")
                             LEFT JOIN "AQD"."QAOC_FINDING" "LPA_L6"
                                 ON "LPA_L6"."FINDING_ID" = "LPA_L3"."FINDING_ID")
                            LEFT JOIN "AQD"."QAOC_FINDING_CATEGORY" "LPA_L7"
                                ON "LPA_L7"."FINDING_CATEGORY_ID" = "LPA_L6"."FINDING_CATEGORY_ID")
                           LEFT JOIN "AQD"."RM_RISK_MATRIX_CELL" "LPA_L8"
                               ON "LPA_L8"."RISK_MATRIX_CELL_ID" = "LPA_L6"."RISK_MATRIX_CELL_ID")
                          LEFT JOIN "AQD"."RM_GLOBAL_LIKELIHOOD" "LPA_L9"
                              ON "LPA_L9"."GLOBAL_LIKELIHOOD_ID" = "LPA_L8"."GLOBAL_LIKELIHOOD_ID")
                         LEFT JOIN "AQD"."QAOC_ACTION" "LPA_L10"
                             ON "LPA_L10"."ACTION_ID" = "LPA_L3"."REPLACEMENT_ACTION_ID")
                        LEFT JOIN "AQD"."RM_GLOBAL_SEVERITY" "LPA_L11"
                            ON "LPA_L11"."GLOBAL_SEVERITY_ID" = "LPA_L8"."GLOBAL_SEVERITY_ID")
                WHERE  (((("LPA_L3"."STAFF_MEMBER_ID" = 136 /* :p33 */))
                         AND (("LPA_L3"."ACTION_STATUS" IS NOT NULL)
                              AND ("LPA_L3"."ACTION_STATUS" IN (6 /* :p34 */, 1 /* :p35 */, 5 /* :p36 */, 4 /* :p37 */,
                                                                8 /* :p38 */, 7 /* :p39 */))))
                    AND ("LPA_L3"."IS_ACTION_REQUEST" = 0 /* :p40 */))) "LPA_L1"
        ORDER  BY "LPA_L1"."ActionID" ASC)
WHERE  rownum <= 10

while in 5.7

SELECT *
FROM   (SELECT "LPA_L1"."Action",
               "LPA_L1"."ActionID",
               "LPA_L1"."F__1830505937",
               "LPA_L1"."F__1143628958",
               "LPA_L1"."ActionIDSortable",
               "LPA_L1"."ActionNo",
               "LPA_L1"."ActionResponseText",
               "LPA_L1"."ActionStatus",
               "LPA_L1"."ActionStatusId",
               "LPA_L1"."ActionType",
               "LPA_L1"."AssignedTo",
               "LPA_L1"."CauseNo",
               "LPA_L1"."CompletedOn",
               "LPA_L1"."ContactPerson",
               "LPA_L1"."Department",
               "LPA_L1"."DueDate",
               "LPA_L1"."ExtActionReference",
               "LPA_L1"."ExtAuditReference",
               "LPA_L1"."WRNumber",
               "LPA_L1"."WRPeriod",
               "LPA_L1"."WRType",
               "LPA_L1"."FindingCategory",
               "LPA_L1"."FindingID",
               "LPA_L1"."FindingNo",
               "LPA_L1"."IsActionRequest",
               "LPA_L1"."Likelihood",
               "LPA_L1"."RecheckDue",
               "LPA_L1"."EnteredById",
               "LPA_L1"."RegisteredOn",
               "LPA_L1"."ReplacementAction",
               "LPA_L1"."ReplacementActionID",
               "LPA_L1"."RiskCauseNo",
               "LPA_L1"."RiskNo",
               "LPA_L1"."Severity",
               "LPA_L1"."StaffMemberName",
               "LPA_L1"."TrackedBy",
               "LPA_L1"."WRID",
               "LPA_L1"."LPFA_28",
               "LPA_L1"."SourceGroupRole",
               "LPA_L1"."SourceRole",
               "LPA_L1"."Overdue",
               "LPA_L1"."ApprovalDefinitionID",
               "LPA_L1"."RejectedApprovalDefinitionID",
               "LPA_L1"."ResponsibleDepartmentID"
        FROM   (SELECT "LPA_L3"."ACTION"                 AS "Action",
                       "LPA_L3"."ACTION_ID"              AS "ActionID",
                       "LPA_L4"."ACTION_PRIORITY_LEVEL"  AS "F__1830505937",
                       "LPA_L4"."SEQNO"                  AS "F__1143628958",
                       "LPA_L3"."ACTION_ID_SORTABLE"     AS "ActionIDSortable",
                       "LPA_L3"."ACTION_NO"              AS "ActionNo",
                       "LPA_L3"."ACTION_RESPONSE_TEXT"   AS "ActionResponseText",
                       "LPA_L3"."ACTION_STATUS_TEXT"     AS "ActionStatus",
                       "LPA_L3"."ACTION_STATUS"          AS "ActionStatusId",
                       "LPA_L5"."ACTION_TYPE"            AS "ActionType",
                       "LPA_L3"."ASSIGNED_TO"            AS "AssignedTo",
                       "LPA_L3"."CAUSE_NO"               AS "CauseNo",
                       "LPA_L3"."COMPLETED_ON"           AS "CompletedOn",
                       "LPA_L3"."CONTACT_PERSON"         AS "ContactPerson",
                       "LPA_L3"."DEPARTMENT"             AS "Department",
                       "LPA_L3"."DUE_DATE"               AS "DueDate",
                       "LPA_L3"."EXT_ACTION_REFERENCE"   AS "ExtActionReference",
                       "LPA_L6"."EXT_AUDIT_REFERENCE"    AS "ExtAuditReference",
                       "LPA_L6"."WR_NUMBER"              AS "WRNumber",
                       "LPA_L6"."WR_PERIOD"              AS "WRPeriod",
                       "LPA_L6"."WR_TYPE"                AS "WRType",
                       "LPA_L7"."FINDING_CATEGORY"       AS "FindingCategory",
                       "LPA_L3"."FINDING_ID"             AS "FindingID",
                       "LPA_L6"."FINDING_NO"             AS "FindingNo",
                       "LPA_L3"."IS_ACTION_REQUEST"      AS "IsActionRequest",
                       "LPA_L9"."GLOBAL_LIKELIHOOD"      AS "Likelihood",
                       "LPA_L3"."RECHECK_DUE"            AS "RecheckDue",
                       "LPA_L3"."REGISTERED_BY_ID"       AS "EnteredById",
                       "LPA_L3"."REGISTERED_ON"          AS "RegisteredOn",
                       "LPA_L10"."ACTION_NO"             AS "ReplacementAction",
                       "LPA_L3"."REPLACEMENT_ACTION_ID"  AS "ReplacementActionID",
                       "LPA_L3"."RISK_CAUSE_NO"          AS "RiskCauseNo",
                       "LPA_L3"."RISK_NO"                AS "RiskNo",
                       "LPA_L11"."GLOBAL_SEVERITY"       AS "Severity",
                       "LPA_L3"."REGISTERED_BY_NAME"     AS "StaffMemberName",
                       "LPA_L3"."TRACKED_BY"             AS "TrackedBy",
                       "LPA_L3"."WRID",
                       1                                 AS "LPFA_28",
                       "LPA_L2"."StaffRole"              AS "SourceGroupRole",
                       Trim((SELECT "LPA_L15"."STAFF_ROLE" AS "StaffRole"
                             FROM   ((("AQD"."QAOC_WORK_REQUEST_VIEW" "LPA_L12"
                                       RIGHT JOIN "AQD"."QAOC_FINDING" "LPA_L13"
                                           ON "LPA_L12"."WR_NUMBER" = "LPA_L13"."WR_NUMBER"
                                              AND "LPA_L12"."WR_PERIOD" = "LPA_L13"."WR_PERIOD"
                                              AND "LPA_L12"."WR_TYPE" = "LPA_L13"."WR_TYPE")
                                      INNER JOIN "AQD"."QAOC_WR_STAFF" "LPA_L14"
                                          ON "LPA_L12"."WR_NUMBER" = "LPA_L14"."WR_NUMBER"
                                             AND "LPA_L12"."WR_PERIOD" = "LPA_L14"."WR_PERIOD"
                                             AND "LPA_L12"."WR_TYPE" = "LPA_L14"."WR_TYPE")
                                     LEFT JOIN "AQD"."QAOC_WR_STAFF_ROLE" "LPA_L15"
                                         ON "LPA_L15"."STAFF_ROLE_ID" = "LPA_L14"."STAFF_ROLE_ID")
                             WHERE  (((("LPA_L13"."FINDING_ID" = "LPA_L3"."FINDING_ID")
                                       AND ("LPA_L14"."STAFF_MEMBER" = 136 /* :p3 */))))
                                AND rownum <= 1)
                            || ' ' /* :p2 */
                            || "LPA_L2"."StaffRole")     AS "SourceRole",
                       CASE
                         WHEN CASE
                                WHEN ((("LPA_L3"."ACTION_STATUS" <> 8 /* :p4 */)
                                       AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p5 */))
                                      AND ("LPA_L3"."DUE_DATE" < '20210518' /* :p6 */)) THEN 1
                                ELSE 0
                              END = 1 THEN 3 /* :p8 */
                         ELSE
                             CASE
                               WHEN CASE
                                      WHEN (((("LPA_L3"."ACTION_STATUS" <> 8 /* :p9 */)
                                              AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p10 */))
                                             AND ("LPA_L3"."DUE_DATE" < '20210519' /* :p11 */))
                                             OR ((("LPA_L3"."ACTION_STATUS" <> 8 /* :p12 */)
                                                  AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p13 */))
                                                 AND ("LPA_L3"."DUE_DATE" < '20210525' /* :p14 */))) THEN 1
                                      ELSE 0
                                    END = 1 THEN 2 /* :p16 */
                               ELSE
                                   CASE
                                     WHEN CASE
                                            WHEN (((("LPA_L3"."ACTION_STATUS" <> 8 /* :p17 */)
                                                    AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p18 */))
                                                   AND ("LPA_L3"."DUE_DATE" < '20210526' /* :p19 */))
                                                   OR ((("LPA_L3"."ACTION_STATUS" <> 8 /* :p20 */)
                                                        AND ("LPA_L3"."ACTION_STATUS" <> 4 /* :p21 */))
                                                       AND ("LPA_L3"."DUE_DATE" < '20210601' /* :p22 */))) THEN 1
                                            ELSE 0
                                          END = 1 THEN 1 /* :p24 */
                                     ELSE 0 /* :p26 */
                                   END
                             END
                       END                               AS "Overdue",
                       (SELECT "LPLA_18"."APPROVAL_DEFINITION_ID" AS "ApprovalDefinitionID"
                        FROM   "AQD"."APP_APPROVAL" "LPLA_18"
                        WHERE  (((("LPA_L3"."ACTION_ID" = "LPLA_18"."RECORD_ID")
                                  AND ((("LPLA_18"."CURRENT_FLAG" = 1 /* :p27 */)
                                        AND ("LPLA_18"."RECORD_TYPE" = 1 /* :p28 */))
                                       AND EXISTS
                                           (SELECT "LPLA_19"."STEP_STATE_ID" AS "StepStateID"
                                            FROM   "AQD"."APP_APPROVAL_STEP" "LPLA_19"
                                            WHERE  ("LPLA_18"."APPROVAL_ID" = "LPLA_19"."APPROVAL_ID"
                                                AND ("LPLA_19"."STEP_STATE_ID" = 1 /* :p29 */)))))))
                           AND rownum <= 1)              AS "ApprovalDefinitionID",
                       (SELECT "LPLA_20"."APPROVAL_DEFINITION_ID" AS "ApprovalDefinitionID"
                        FROM   "AQD"."APP_APPROVAL" "LPLA_20"
                        WHERE  (((("LPA_L3"."ACTION_ID" = "LPLA_20"."RECORD_ID")
                                  AND (("LPLA_20"."CURRENT_FLAG" = 1 /* :p30 */)
                                       AND EXISTS
                                           (SELECT "LPLA_21"."STEP_STATE_ID" AS "StepStateID"
                                            FROM   "AQD"."APP_APPROVAL_STEP" "LPLA_21"
                                            WHERE  ("LPLA_20"."APPROVAL_ID" = "LPLA_21"."APPROVAL_ID"
                                                AND ("LPLA_21"."STEP_STATE_ID" = 3 /* :p31 */)))))))
                           AND rownum <= 1)              AS "RejectedApprovalDefinitionID",
                       "LPA_L3"."RESPONSIBLE_DEPARTMENT" AS "ResponsibleDepartmentID"
                FROM   ((((((((((SELECT "LPA_L21"."STAFF_ROLE" AS "StaffRole",
                                       "LPA_L17"."FINDING_ID" AS "FindingID"
                                FROM   ((((("AQD"."QAOC_WORK_REQUEST_VIEW" "LPA_L16"
                                            RIGHT JOIN "AQD"."QAOC_FINDING" "LPA_L17"
                                                ON "LPA_L16"."WR_NUMBER" = "LPA_L17"."WR_NUMBER"
                                                   AND "LPA_L16"."WR_PERIOD" = "LPA_L17"."WR_PERIOD"
                                                   AND "LPA_L16"."WR_TYPE" = "LPA_L17"."WR_TYPE")
                                           INNER JOIN "AQD"."QAOC_WR_GROUP_ASSIGNMENT" "LPA_L18"
                                               ON "LPA_L16"."WR_NUMBER" = "LPA_L18"."WR_NUMBER"
                                                  AND "LPA_L16"."WR_PERIOD" = "LPA_L18"."WR_PERIOD"
                                                  AND "LPA_L16"."WR_TYPE" = "LPA_L18"."WR_TYPE")
                                          INNER JOIN "AQD"."QAOC_ASSIGNMENT_GROUP" "LPA_L19"
                                              ON "LPA_L19"."ASSIGNMENT_GROUP_ID" = "LPA_L18"."ASSIGNMENT_GROUP_ID")
                                         INNER JOIN "AQD"."QAOC_ASSIGNMENT_GROUP_MEMBER" "LPA_L20"
                                             ON "LPA_L19"."ASSIGNMENT_GROUP_ID" = "LPA_L20"."ASSIGNMENT_GROUP_ID")
                                        INNER JOIN "AQD"."QAOC_WR_STAFF_ROLE" "LPA_L21"
                                            ON "LPA_L21"."STAFF_ROLE_ID" = "LPA_L18"."STAFF_ROLE_ID")
                                WHERE  ((("LPA_L20"."STAFF_MEMBER_ID" = 136 /* :p32 */)))) "LPA_L2"
                                RIGHT JOIN "AQD"."QAOC_ACTN_VIEWSECURED" "LPA_L3"
                                    ON "LPA_L3"."FINDING_ID" = "LPA_L2"."FindingID")
                               LEFT JOIN "AQD"."QAOC_ACTION_PRIORITY_LEVEL" "LPA_L4"
                                   ON "LPA_L4"."ACTION_PRIORITY_LEVEL_ID" = "LPA_L3"."ACTION_PRIORITY_LEVEL_ID")
                              INNER JOIN "AQD"."QAOC_ACTION_TYPE" "LPA_L5"
                                  ON "LPA_L5"."ACTION_TYPE_ID" = "LPA_L3"."ACTION_TYPE_ID")
                             LEFT JOIN "AQD"."QAOC_FINDING" "LPA_L6"
                                 ON "LPA_L6"."FINDING_ID" = "LPA_L3"."FINDING_ID")
                            LEFT JOIN "AQD"."QAOC_FINDING_CATEGORY" "LPA_L7"
                                ON "LPA_L7"."FINDING_CATEGORY_ID" = "LPA_L6"."FINDING_CATEGORY_ID")
                           LEFT JOIN "AQD"."RM_RISK_MATRIX_CELL" "LPA_L8"
                               ON "LPA_L8"."RISK_MATRIX_CELL_ID" = "LPA_L6"."RISK_MATRIX_CELL_ID")
                          LEFT JOIN "AQD"."RM_GLOBAL_LIKELIHOOD" "LPA_L9"
                              ON "LPA_L9"."GLOBAL_LIKELIHOOD_ID" = "LPA_L8"."GLOBAL_LIKELIHOOD_ID")
                         LEFT JOIN "AQD"."QAOC_ACTION" "LPA_L10"
                             ON "LPA_L10"."ACTION_ID" = "LPA_L3"."REPLACEMENT_ACTION_ID")
                        LEFT JOIN "AQD"."RM_GLOBAL_SEVERITY" "LPA_L11"
                            ON "LPA_L11"."GLOBAL_SEVERITY_ID" = "LPA_L8"."GLOBAL_SEVERITY_ID")
                WHERE  (((("LPA_L3"."STAFF_MEMBER_ID" = 136 /* :p33 */))
                         AND (("LPA_L3"."ACTION_STATUS" IS NOT NULL)
                              AND ("LPA_L3"."ACTION_STATUS" IN (6 /* :p34 */, 1 /* :p35 */, 5 /* :p36 */, 4 /* :p37 */,
                                                                8 /* :p38 */, 7 /* :p39 */))))
                    AND ("LPA_L3"."IS_ACTION_REQUEST" = 0 /* :p40 */))) "LPA_L1"
        ORDER  BY "LPA_L1"."ActionID" ASC)
WHERE  rownum <= 10
Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 18-May-2021 09:03:15   

At first, when looking at that massive query, I was like frowning

But I think this is caused by the change we made to allow longer identifier names on Oracle, as you can see the outer projection rightfully allows ActionPriorityLevelActionPriorityLevelSeqNo and ActionPriorityLevelActionPriorityLevel, however the inner query doesn't seem to honor that and changes the identifier name to a shorthand version we used in v5.7 and earlier.

We'll look into it.

(it's related to this fix: https://www.llblgen.com/tinyforum/Thread/27269, made for 5.7.4, 5.8.1 )

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 18-May-2021 11:07:08   

Fixed. (new hotfix v5.8.2 and v5.7.4) We clamped aliases but if a field became a derived table targeting field, and thus targeting an aliased field, the target wasn't considered to be clamped, which has now been corrected. I think we cover all cases now, if not, please let me know.

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 18-May-2021 13:30:58   

Yep got it, we're good to gosmile Thanks Frans.

Jeremy Thomas