ClickHouse connector: can not use () after from closure

[onlyjackfrost]

the following sql statement can not be executed by ClickHouse

WITH
  "default_CUSTOMER" AS (
   SELECT
     "default_CUSTOMER"."C_ACCTBAL" "C_ACCTBAL"
   , "default_CUSTOMER"."C_ADDRESS" "C_ADDRESS"
   , "default_CUSTOMER"."C_COMMENT" "C_COMMENT"
   , "default_CUSTOMER"."C_CUSTKEY" "C_CUSTKEY"
   , "default_CUSTOMER"."C_MKTSEGMENT" "C_MKTSEGMENT"
   , "default_CUSTOMER"."C_NAME" "C_NAME"
   , "default_CUSTOMER"."C_NATIONKEY" "C_NATIONKEY"
   , "default_CUSTOMER"."C_PHONE" "C_PHONE"
   FROM
     (
      SELECT
        "default_CUSTOMER"."C_ACCTBAL" "C_ACCTBAL"
      , "default_CUSTOMER"."C_ADDRESS" "C_ADDRESS"
      , "default_CUSTOMER"."C_COMMENT" "C_COMMENT"
      , "default_CUSTOMER"."C_CUSTKEY" "C_CUSTKEY"
      , "default_CUSTOMER"."C_MKTSEGMENT" "C_MKTSEGMENT"
      , "default_CUSTOMER"."C_NAME" "C_NAME"
      , "default_CUSTOMER"."C_NATIONKEY" "C_NATIONKEY"
      , "default_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
           "default"."CUSTOMER" "default_CUSTOMER"
      )  "default_CUSTOMER"
   )  "default_CUSTOMER"
)
, "default_ORDERS" AS (
   SELECT
     "default_ORDERS"."O_CLERK" "O_CLERK"
   , "default_ORDERS"."O_COMMENT" "O_COMMENT"
   , "default_ORDERS"."O_CUSTKEY" "O_CUSTKEY"
   , "default_ORDERS"."O_ORDERDATE" "O_ORDERDATE"
   , "default_ORDERS"."O_ORDERKEY" "O_ORDERKEY"
   , "default_ORDERS"."O_ORDERPRIORITY" "O_ORDERPRIORITY"
   , "default_ORDERS"."O_ORDERSTATUS" "O_ORDERSTATUS"
   , "default_ORDERS"."O_SHIPPRIORITY" "O_SHIPPRIORITY"
   , "default_ORDERS"."O_TOTALPRICE" "O_TOTALPRICE"
   FROM
     (
      SELECT
        "default_ORDERS"."O_CLERK" "O_CLERK"
      , "default_ORDERS"."O_COMMENT" "O_COMMENT"
      , "default_ORDERS"."O_CUSTKEY" "O_CUSTKEY"
      , "default_ORDERS"."O_ORDERDATE" "O_ORDERDATE"
      , "default_ORDERS"."O_ORDERKEY" "O_ORDERKEY"
      , "default_ORDERS"."O_ORDERPRIORITY" "O_ORDERPRIORITY"
      , "default_ORDERS"."O_ORDERSTATUS" "O_ORDERSTATUS"
      , "default_ORDERS"."O_SHIPPRIORITY" "O_SHIPPRIORITY"
      , "default_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
           "default"."ORDERS" "default_ORDERS"
      )  "default_ORDERS"
   )  "default_ORDERS"
)
SELECT tmp.*
FROM
  (
   SELECT
     "C_NAME"
   , SUM("O_TOTALPRICE") "total_revenue"
   FROM
     ("default_CUSTOMER" "C"
   INNER JOIN "default_ORDERS" "O" ON ("C"."C_CUSTKEY" = "O"."O_CUSTKEY"))
   GROUP BY "C_NAME"
   ORDER BY "total_revenue" DESC
)  tmp
LIMIT 1

after removing the () after FROM, the SQL can be execute

WITH
... 
SELECT tmp.*
FROM
  (
   SELECT
     "C_NAME"
   , SUM("O_TOTALPRICE") "total_revenue"
   FROM
     "default_CUSTOMER" "C"
   INNER JOIN "default_ORDERS" "O" ON ("C"."C_CUSTKEY" = "O"."O_CUSTKEY")
   GROUP BY "C_NAME"
   ORDER BY "total_revenue" DESC
)  tmp
LIMIT 1

[onlyjackfrost]

Note: All the TO-MANY calculated fields can not be supported by the ClickHouse connector for now.

[goldmedal]

I have filed an issue in tobymao/sqlglot#3735 but they don't have plan to fix it currently. So, we're going to close this issue after #649 .