[KYLIN-4166] kylin parse sql error

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: v3.0.0-alpha2
• Fix Version/s: v2.6.5, v3.1.0, v3.0.1
• Component/s: Query Engine
• Flags: Important
• Assignee: Yaqian Zhang
• Reporter: phil.zhang
• Created: 12/Sep/19 15:24
• Updated: 10/Jul/20 11:33
• Resolved: 16/Dec/19 06:00

Description

 

   1.  I use tableau on kylin, tableau has generate a sql : 

SELECT "X__SQL_"."FLIGHT_TYPE" AS "FLIGHT_TYPE", SUM("X_SQL_"."SHOPPING_SUCCESS_COUNT") AS "sum_SHOPPING_SUCCESS_COUNT_ok" FROM ( select *, case when cast(DURATION_LEVEL as varchar) like '1' then '>=0s,<3s' when cast(DURATION_LEVEL as varchar) like '2' then '>=3s,<4s' when cast(DURATION_LEVEL as varchar) like '3' then '>=4s,<5s' when cast(DURATION_LEVEL as varchar) like '4' then '>=5s,<6s' when cast(DURATION_LEVEL as varchar) like '5' then '>=6s,<7s' when cast(DURATION_LEVEL as varchar) like '6' then '>=7s,<8s' when cast(DURATION_LEVEL as varchar) like '7' then '>=8s,<9s' when cast(DURATION_LEVEL as varchar) like '8' then '>=9s,<10s' when cast(DURATION_LEVEL as varchar) like '9' then '>=10s,<12s' when cast(DURATION_LEVEL as varchar) like '10' then '>=12s,<14s' when cast(DURATION_LEVEL as varchar) like '11' then '>=14s,<16s' when cast(DURATION_LEVEL as varchar) like '12' then '>=16s,<18s' when cast(DURATION_LEVEL as varchar) like '13' then '>=18s,<20s' when cast(DURATION_LEVEL as varchar) like '14' then '>=20s,<30s' when cast(DURATION_LEVEL as varchar) like '15' then '>=30s,<40s' when cast(DURATION_LEVEL as varchar) like '16' then '>=40s,<50s' when cast(DURATION_LEVEL as varchar) like '17' then '>=50s' else 'null' end as DURATION_LEVEL_TRANS from DM_AIR_API_DURATION_SPB_D ) "X_SQL_" WHERE ("X_SQL__"."DURATION_LEVEL_TRANS" = '>=5s,<6s') GROUP BY 1

 
    it seems that the sql is correct, but the result is unexpect, it returns null .

 

But actually, it should  returns some rows because there exist data fit the query, let's change the sql form to check it.

SELECT "FLIGHT_TYPE" AS "FLIGHT_TYPE",   SUM("SHOPPING_SUCCESS_COUNT") AS "sum_SHOPPING_SUCCESS_COUNT_ok" from DM_AIR_API_DURATION_SPB_D  WHERE ("DURATION_LEVEL" = 4 and "DAY_NUM"='2019-09-10') GROUP BY 1

 

 

2. and when i write sql like this 

SELECT flight_type,SUM("SHOPPING_SUCCESS_COUNT") AS "sum_SHOPPING_SUCCESS_COUNT_ok" from DM_AIR_API_DURATION_SPB_D group by flight_type

i got result like this

 

 it is same to the result of 

'''
SELECT "X__SQL_"."FLIGHT_TYPE" AS "FLIGHT_TYPE",   SUM("X_SQL_"."SHOPPING_SUCCESS_COUNT") AS "sum_SHOPPING_SUCCESS_COUNT_ok" FROM (   select ,        case         when cast(DURATION_LEVEL as varchar) like '1' then '>=0s,<3s'         when cast(DURATION_LEVEL as varchar) like '2' then '>=3s,<4s'         when cast(DURATION_LEVEL as varchar) like '3' then '>=4s,<5s'         when cast(DURATION_LEVEL as varchar) like '4' then '>=5s,<6s'         when cast(DURATION_LEVEL as varchar) like '5' then '>=6s,<7s'         when cast(DURATION_LEVEL as varchar) like '6' then '>=7s,<8s'         when cast(DURATION_LEVEL as varchar) like '7' then '>=8s,<9s'         when cast(DURATION_LEVEL as varchar) like '8' then '>=9s,<10s'         when cast(DURATION_LEVEL as varchar) like '9' then '>=10s,<12s'         when cast(DURATION_LEVEL as varchar) like '10' then '>=12s,<14s'         when cast(DURATION_LEVEL as varchar) like '11' then '>=14s,<16s'         when cast(DURATION_LEVEL as varchar) like '12' then '>=16s,<18s'         when cast(DURATION_LEVEL as varchar) like '13' then '>=18s,<20s'         when cast(DURATION_LEVEL as varchar) like '14' then '>=20s,<30s'         when cast(DURATION_LEVEL as varchar) like '15' then '>=30s,<40s'         when cast(DURATION_LEVEL as varchar) like '16' then '>=40s,<50s'         when cast(DURATION_LEVEL as varchar) like '17' then '>=50s'            else 'null'         end as DURATION_LEVEL_TRANS    from DM_AIR_API_DURATION_SPB_D ) "X_SQL_" WHERE ("X_SQL__"."DURATION_LEVEL_TRANS" = '>=0s,<3s'*) GROUP BY 1
'''

 
 

 I guess maybe there are some bugs about parsing sql like upper ? I hope someone can help me to deal this problem.

Issue Links

• links to: GitHub Pull Request #895