wren-engine
wren-engine copied to clipboard
ClickHouse connector: incorrect current_data function
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()