MSSQL Connector : can not handle to-many calculated field validation.

[onlyjackfrost]

The following Trino sql can not be convert to MSSQL by Ibis correctly

WITH
  "dbo_ORDERS" AS (
   SELECT
     "dbo_ORDERS"."O_CLERK" "O_CLERK"
   , "dbo_ORDERS"."O_COMMENT" "O_COMMENT"
   , "dbo_ORDERS"."O_CUSTKEY" "O_CUSTKEY"
   , "dbo_ORDERS"."O_ORDERDATE" "O_ORDERDATE"
   , "dbo_ORDERS"."O_ORDERKEY" "O_ORDERKEY"
   , "dbo_ORDERS"."O_ORDERPRIORITY" "O_ORDERPRIORITY"
   , "dbo_ORDERS"."O_ORDERSTATUS" "O_ORDERSTATUS"
   , "dbo_ORDERS"."O_SHIPPRIORITY" "O_SHIPPRIORITY"
   , "dbo_ORDERS"."O_TOTALPRICE" "O_TOTALPRICE"
   FROM
     (
      SELECT
        "dbo_ORDERS"."O_CLERK" "O_CLERK"
      , "dbo_ORDERS"."O_COMMENT" "O_COMMENT"
      , "dbo_ORDERS"."O_CUSTKEY" "O_CUSTKEY"
      , "dbo_ORDERS"."O_ORDERDATE" "O_ORDERDATE"
      , "dbo_ORDERS"."O_ORDERKEY" "O_ORDERKEY"
      , "dbo_ORDERS"."O_ORDERPRIORITY" "O_ORDERPRIORITY"
      , "dbo_ORDERS"."O_ORDERSTATUS" "O_ORDERSTATUS"
      , "dbo_ORDERS"."O_SHIPPRIORITY" "O_SHIPPRIORITY"
      , "dbo_ORDERS"."O_TOTALPRICE" "O_TOTALPRICE"
      FROM
        (
         SELECT
           "O_CLERK" "O_CLERK"
         , "O_COMMENT" "O_COMMENT"
         , "O_CUSTKEY" "O_CUSTKEY"
         , "O_ORDERDATE" "O_ORDERDATE"
         , "O_ORDERKEY" "O_ORDERKEY"
         , "O_ORDERPRIORITY" "O_ORDERPRIORITY"
         , "O_ORDERSTATUS" "O_ORDERSTATUS"
         , "O_SHIPPRIORITY" "O_SHIPPRIORITY"
         , "O_TOTALPRICE" "O_TOTALPRICE"
         FROM
           "wrenai"."dbo"."ORDERS" "dbo_ORDERS"
      )  "dbo_ORDERS"
   )  "dbo_ORDERS"
)
, "dbo_CUSTOMER" AS (
   SELECT
     "dbo_CUSTOMER"."C_ACCTBAL" "C_ACCTBAL"
   , "dbo_CUSTOMER"."C_ADDRESS" "C_ADDRESS"
   , "dbo_CUSTOMER"."C_COMMENT" "C_COMMENT"
   , "dbo_CUSTOMER"."C_CUSTKEY" "C_CUSTKEY"
   , "dbo_CUSTOMER"."C_MKTSEGMENT" "C_MKTSEGMENT"
   , "dbo_CUSTOMER"."C_NAME" "C_NAME"
   , "dbo_CUSTOMER"."C_NATIONKEY" "C_NATIONKEY"
   , "dbo_CUSTOMER"."C_PHONE" "C_PHONE"
   , "revenue"."revenue" "revenue"
   FROM
     ((
      SELECT
        "dbo_CUSTOMER"."C_ACCTBAL" "C_ACCTBAL"
      , "dbo_CUSTOMER"."C_ADDRESS" "C_ADDRESS"
      , "dbo_CUSTOMER"."C_COMMENT" "C_COMMENT"
      , "dbo_CUSTOMER"."C_CUSTKEY" "C_CUSTKEY"
      , "dbo_CUSTOMER"."C_MKTSEGMENT" "C_MKTSEGMENT"
      , "dbo_CUSTOMER"."C_NAME" "C_NAME"
      , "dbo_CUSTOMER"."C_NATIONKEY" "C_NATIONKEY"
      , "dbo_CUSTOMER"."C_PHONE" "C_PHONE"
      FROM
        (
         SELECT
           "C_ACCTBAL" "C_ACCTBAL"
         , "C_ADDRESS" "C_ADDRESS"
         , "C_COMMENT" "C_COMMENT"
         , "C_CUSTKEY" "C_CUSTKEY"
         , "C_MKTSEGMENT" "C_MKTSEGMENT"
         , "C_NAME" "C_NAME"
         , "C_NATIONKEY" "C_NATIONKEY"
         , "C_PHONE" "C_PHONE"
         FROM
           "wrenai"."dbo"."CUSTOMER" "dbo_CUSTOMER"
      )  "dbo_CUSTOMER"
   )  "dbo_CUSTOMER"
   LEFT JOIN (
      SELECT
        "dbo_CUSTOMER"."C_CUSTKEY"
      , MAX("dbo_ORDERS"."O_TOTALPRICE") "revenue"
      FROM
        ((
         SELECT
           "C_ACCTBAL" "C_ACCTBAL"
         , "C_ADDRESS" "C_ADDRESS"
         , "C_COMMENT" "C_COMMENT"
         , "C_CUSTKEY" "C_CUSTKEY"
         , "C_MKTSEGMENT" "C_MKTSEGMENT"
         , "C_NAME" "C_NAME"
         , "C_NATIONKEY" "C_NATIONKEY"
         , "C_PHONE" "C_PHONE"
         FROM
           "wrenai"."dbo"."CUSTOMER" "dbo_CUSTOMER"
      )  "dbo_CUSTOMER"
      LEFT JOIN "dbo_ORDERS" ON ("dbo_ORDERS"."O_CUSTKEY" = "dbo_CUSTOMER"."C_CUSTKEY"))
      GROUP BY 1
   )  "revenue" ON ("dbo_CUSTOMER"."C_CUSTKEY" = "revenue"."C_CUSTKEY"))
)
SELECT "revenue"
FROM
  "dbo_CUSTOMER"
LIMIT 1

I guess it is related to the "GROUP BY 1" (group by index) syntax and the "LIMIT" syntax. In MSSQL, they use TOP instead.