[KYLIN-3460] {fn CURRENT_DATE()} parse error

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: v2.3.0, v2.3.1, v2.4.0
• Fix Version/s: v2.4.1
• Component/s: Driver - ODBC, Query Engine
• Assignee: Temple Zhou
• Reporter: Temple Zhou
• Created: 19/Jul/18 09:58
• Updated: 09/Sep/18 05:53
• Resolved: 29/Aug/18 06:06

Description

Dear all,

I'm facing a very critical problem. When I use Tableau 10.5 with Kylin 2.1.0, the Tableau Server will send many SQL with "

{fn CURRENT_DATE()}" via ODBC Driver(2.1.0), and the Kylin Server 2.1.0 will parse the CURRENT_DATE() to "2018-07-20 00:00:00"(the real current date). the logs are as below

Query Id: 14e7b152-6be0-4539-8fd0-c28bbffa6b25
SQL: SELECT "APP_DAILY_ACTIVE_USER_PROPERTY_DI"."AV" AS "AV",
       COUNT(DISTINCT "APP_DAILY_ACTIVE_USER_PROPERTY_DI"."MC") AS "usr_Calculation_267682710052360192_ok"
FROM "ADL"."APP_DAILY_ACTIVE_USER_PROPERTY_DI" "APP_DAILY_ACTIVE_USER_PROPERTY_DI"
LEFT JOIN "DIM"."APP" "APP" ON ("APP_DAILY_ACTIVE_USER_PROPERTY_DI"."AC" = "APP"."ID")
INNER JOIN "DIM"."CALENDAR" "CALENDAR" ON ("APP_DAILY_ACTIVE_USER_PROPERTY_DI"."ACTION_DT" = "CALENDAR"."CAL_DT")
WHERE ((CAST("APP_DAILY_ACTIVE_USER_PROPERTY_DI"."ACTION_DT" AS TIMESTAMP) >= {ts '2018-06-20 00:00:00'})
       AND (CAST("APP_DAILY_ACTIVE_USER_PROPERTY_DI"."ACTION_DT" AS TIMESTAMP) < {ts '2018-07-20 00:00:00'})
       AND ("APP"."NAME" = 'xxx'))
GROUP BY "APP_DAILY_ACTIVE_USER_PROPERTY_DI"."AV"
User: ADMIN
Success: true
Duration: 0.06
Project: app

However, recently, I upgrade my Kylin Server to 2.3.1 even 2.4.0, then there are many Tableau reports get the same error due to the SQL contain {fn CURRENT_DATE()}

Tableau Error Image

Besides, I found that the logs(2.3.1&2.4.0) are different from the logs(2.1.0), the logs(2.4.0) are as below

Query Id: b160f20c-84e4-4b81-bfe9-54ed22a723d6
SQL: SELECT "APP_DAILY_ACTIVE_USER_PROPERTY_DI"."AV" AS "AV",
       COUNT(DISTINCT "APP_DAILY_ACTIVE_USER_PROPERTY_DI"."MC") AS "usr_Calculation_267682710052360192_ok"
FROM "ADL"."APP_DAILY_ACTIVE_USER_PROPERTY_DI" "APP_DAILY_ACTIVE_USER_PROPERTY_DI"
LEFT JOIN "DIM"."APP" "APP" ON ("APP_DAILY_ACTIVE_USER_PROPERTY_DI"."AC" = "APP"."ID")
INNER JOIN "DIM"."CALENDAR" "CALENDAR" ON ("APP_DAILY_ACTIVE_USER_PROPERTY_DI"."ACTION_DT" = "CALENDAR"."CAL_DT")
WHERE ((CAST("APP_DAILY_ACTIVE_USER_PROPERTY_DI"."ACTION_DT" AS TIMESTAMP) >= {fn TIMESTAMPADD(SQL_TSI_DAY,-29,{fn CURRENT_DATE()})})
       AND (CAST("APP_DAILY_ACTIVE_USER_PROPERTY_DI"."ACTION_DT" AS TIMESTAMP) < {fn TIMESTAMPADD(SQL_TSI_DAY,1,{fn CURRENT_DATE()})})
       AND ("APP"."NAME" = 'xxx')
       AND ("APP"."PLATFORM" = 'Android'))
GROUP BY "APP_DAILY_ACTIVE_USER_PROPERTY_DI"."AV"
User: ADMIN
Success: false
Duration: 0.029
Project: app

The problem that bothers me the most is that why the older version can parse the "fn CURRENT_DATE()" but the newer can't.

Issue Links

• duplicates: KYLIN-3025 kylin odbc error : {fn CONVERT} for bigint type in tableau 10.4