search

Canner / wren-engine

🤖 The semantic engine for LLMs, bringing semantic context to AI agents. 🔥
https://getwren.ai/oss
Apache License 2.0
124 stars 34 forks source link

ClickHouse connector: incorrect current_data function #651

Open onlyjackfrost opened 4 months ago

onlyjackfrost commented 4 months ago

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()

goldmedal commented 4 months ago

Thanks @onlyjackfrost report this. I tried something similar with your case but I found it work well

        SELECT COUNT("custkey") "customer_count"
        FROM
        "Customer"
        WHERE (("totalprice" > 3000) AND (EXTRACT(MONTH FROM current_date) = '7'))

Which version of the ibis server are you using? I noticed that sqlglot recently fixed many issues related to ClickHouse date functions. Perhaps upgrading will resolve this issue for you.