wren-engine icon indicating copy to clipboard operation
wren-engine copied to clipboard

ClickHouse connector: incorrect current_data function

Open onlyjackfrost opened this issue 7 months ago • 1 comments

The rewritten sql contains incorrect current_data function usage

WITH
"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"
)
, "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"
 , "revenue"."revenue" "revenue"
 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"
 LEFT JOIN (
    SELECT
      "default_CUSTOMER"."C_CUSTKEY"
    , SUM("default_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
         "default"."CUSTOMER" "default_CUSTOMER"
    )  "default_CUSTOMER"
    LEFT JOIN "default_ORDERS" ON ("default_ORDERS"."O_CUSTKEY" = "default_CUSTOMER"."C_CUSTKEY")
    GROUP BY 1
 )  "revenue" ON ("default_CUSTOMER"."C_CUSTKEY" = "revenue"."C_CUSTKEY")
)
SELECT COUNT("C_CUSTKEY") "customer_count"
FROM
"default_CUSTOMER"
WHERE (("revenue" > 3000) AND (EXTRACT(MONTH FROM current_date) = '7'))

The current_date in the last line should be current_data()

onlyjackfrost avatar Jul 04 '24 04:07 onlyjackfrost