OnAfterGetRecord trigger ignores IsolationLevel specified onOpenPage trigger

[tyurm]

1. Describe the bug OnAfterGetRecord trigger ignores IsolationLevel specified onOpenPage trigger. The only way to get "COMMITED" data on the page is to run CalcFields onAfterGetRecord, but this kills performance.

2. To Reproduce Steps to reproduce the behavior:

2.1. Use page below for debugging

page 50100 "ALIssue Customer List"
{
    ApplicationArea = All;
    PageType = List;
    SourceTable = Customer;
    UsageCategory = Lists;

    layout
    {
        area(Content)
        {
            repeater(GroupName)
            {
                field("No."; Rec."No.")
                {
                    ApplicationArea = All;

                }
                field(Comment; Rec.Comment)
                {
                    ApplicationArea = All;
                }
            }
        }
    }
    trigger OnOpenPage()
    begin
        Rec.ReadIsolation := IsolationLevel::ReadCommitted;
    end;

    trigger OnAfterGetRecord()
    var
        IsolationLvl: IsolationLevel;
    begin
        //add breakpoint and check last executed sql statement
        //sql statement shows "Select FROM "Comment Line" with(READUNCOMMITED)
        IsolationLvl := Rec.ReadIsolation(); // IsolationLevel = READCOMMITED
        Rec.CalcFields(Comment);
        //add breakpoint after CalcFields and check last executed sql statement
        //sql statement shows "Select FROM "Comment Line" with(READCOMMITED)    
    end;
}

2.2. Add two breakpoints OnAfterGetRecord trigger - before Rec.CalcFields and after. 2.3. Compare last executed SQL statement.

3. Expected behavior BC gets data from "Comment Line" table according to IsolationLevel specified onOpenPage trigger, in our example - READCOMMITED.

4. Actual behavior statement before CalcFields shows that BC "READUNCOMMITED" data from "Comment Line" table:

SELECT 
  TOP (50) "Customer"."timestamp" AS "timestamp", 
  "Customer"."No_" AS "No_", 
  "Customer"."Name" AS "Name", 
  "Customer"."Contact" AS "Contact", 
  "Customer"."Shipping Agent Code" AS "Shipping Agent Code", 
  "Customer"."Country_Region Code" AS "Country_Region Code", 
  "Customer"."Image" AS "Image", 
  "Customer"."$systemId" AS "$systemId", 
  "Customer"."$systemCreatedAt" AS "SystemCreatedAt", 
  "Customer"."$systemCreatedBy" AS "SystemCreatedBy", 
  "Customer"."$systemModifiedAt" AS "SystemModifiedAt", 
  "Customer"."$systemModifiedBy" AS "SystemModifiedBy", 
  ISNULL(
    "SUB$Comment"."Comment$Comment Line$EXISTS", 
    0
  ) AS "Comment" 
FROM 
  "db_bcprodweu_t92808517_20240526_03145495_a8c2".dbo."CRONUS CH$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972" AS "Customer" WITH(READCOMMITTED) OUTER APPLY (
    SELECT 
      TOP (1) CAST (1 AS TINYINT) AS "Comment$Comment Line$EXISTS" 
    FROM 
      "db_bcprodweu_t92808517_20240526_03145495_a8c2".dbo."CRONUS CH$Comment Line$437dbf0e-84ff-417a-965d-ed2bb9650972" AS "Comment$Comment Line" WITH(READUNCOMMITTED) 
    WHERE 
      (
        "Comment$Comment Line"."Table Name" = @0 
        AND "Comment$Comment Line"."No_" = "Customer"."No_"
      )
  ) AS "SUB$Comment" 
ORDER BY 
  "No_" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

statement after CalcFields shows that BC "READCOMMITED" data from "Comment Line" table:

SELECT 
  TOP 1 NULL 
FROM 
  "db_bcprodweu_t92808517_20240526_03145495_a8c2".dbo."CRONUS CH$Comment Line$437dbf0e-84ff-417a-965d-ed2bb9650972" "97" WITH(READCOMMITTED) 
WHERE 
  (
    "97"."Table Name" = @0 
    AND "97"."No_" = @1
  ) OPTION(OPTIMIZE FOR UNKNOWN)

5. Versions:

• AL Language: 13.0.1027618
• Visual Studio Code: 1.89.1
• Business Central: 24.1.18927.19282
• List of Visual Studio Code extensions that you have installed: AL Language, AL CodeActions, AL Object ID Ninja, AL ToolBox, AZ AL Dev Tools

[thloke]

Hi, this issue is out of scope of for this repository. You should do one of the following:

• Open a support request to CSS through the PartnerSource portal
• Open a bug in Collaborate
• Contact your Service Account Manager (SAM) in your local subsidiary to understand what is included in your contract as of support incident and PAH (Partner Advisory Hours). Your SAM might also direct you "step by step" how to open a support request or how to get credentials, if this is the first time for you or your company

As a reminder, this repository only handles issues to do with the compiler or VSCode development experience for AL. This is to do with the execution of AL code on the server, so we can't handle it here and it needs to go to the server team.