You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@kylin.apache.org by "phil.zhang (Jira)" <ji...@apache.org> on 2019/09/16 09:57:00 UTC
[jira] [Updated] (KYLIN-4166) kylin parse sql error
[ https://issues.apache.org/jira/browse/KYLIN-4166?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
phil.zhang updated KYLIN-4166:
------------------------------
Description:
{quote} 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 .{quote}
!http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=4!
{quote} 2. 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
'''{quote}
!http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=4!
{quote}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 {color:#333333}like this{color}{quote}
{color:#333333}!http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=5!{color}
{quote}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
'''{quote}
!http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=6!
{quote} *I guess maybe there are some bugs about parsing sql like upper ? I hope someone can help me to deal this problem.*{quote}
was:
我在kylin上使用tableau，tableau生成了一个sql：
"""
选择"X ___ SQL ___"。"FLIGHT_TYPE"AS"FLIGHT_TYPE"，SUM（"X ___ SQL ___"。"SHOPPING_SUCCESS_COUNT"）AS"sum_SHOPPING_SUCCESS_COUNT_ok"FROM（选择*，转换时的情况（DURATION_LEVEL as varchar），如'1'，然后'> = 0s，当施放时（3）'（'D'为'var'），然后'> = 3s，<4s'，当施放时（DURATION_LEVEL作为varchar）像'3'然后'> = 4s，<5s'时施放（DURATION_LEVEL作为varchar）比如'4'然后 *'> = 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
"""
似乎sql是正确的，但结果是意外的，它返回null。
!http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=4!
2.实际上，它应该返回一些行，因为存在适合查询的数据，让我们更改sql表单来检查它。
"""
选择"FLIGHT_TYPE"AS"FLIGHT_TYPE"，SUM（"SHOPPING_SUCCESS_COUNT"）AS"sum_SHOPPING_SUCCESS_COUNT_ok"来自DM_AIR_API_DURATION_SPB_D WHERE（"DURATION_LEVEL"= 4和"DAY_NUM"='2019-09-10'）GROUP BY 1
"""
!http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=4!
当我写这样的SQL
"""
通过flight_type从DM_AIR_API_DURATION_SPB_D组中选择flight_type，SUM（"SHOPPING_SUCCESS_COUNT"）AS"sum_SHOPPING_SUCCESS_COUNT_ok"
"""
我有导致 {color:#333333}这样的{color}
{color:#333333}!http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=5!{color}
它的结果是一样的
"""
选择"X ___ SQL ___"。"FLIGHT_TYPE"AS"FLIGHT_TYPE"，SUM（"X ___ SQL ___"。"SHOPPING_SUCCESS_COUNT"）AS"sum_SHOPPING_SUCCESS_COUNT_ok"FROM（选择*，投射时的情况（DURATION_LEVEL as varchar），如'1'，然后 *'> = 0s， <3S'* 当施放（DURATION_LEVEL作为varchar）像'2'然后'> = 3s，<4s'时施放（DURATION_LEVEL作为varchar）像'3'然后'> = 4s，<5s'当施放时（DURATION_LEVEL作为varchar）像'4 'then'> = 5s，<6s'，当施法时（DURATION_LEVEL作为varchar），如'5'，然后'> = 6s，<7s'，当施法时（DURATION_LEVEL作为varchar），如'6'，然后'> = 7s，<8s'当施放（DURATION_LEVEL作为varchar）像'7'然后'> = 8s，<9s'时施放（DURATION_LEVEL作为varchar）像'8'然后'> = 9s，<10s'当施放时（DURATION_LEVEL作为varchar）像'9 'then'> = 10s，<12s'时施放（DURATION_LEVEL as varchar），如'10'然后'> = 12s，<14s'当施放（DURATION_LEVEL作为varchar）像'11'然后'> = 14s，<16s'时施放（DURATION_LEVEL作为varchar）像'12'然后'> = 16s，<18s'当施放时（DURATION_LEVEL作为varchar）像'13 'then'> = 18s，<20s'，当施放时（DURATION_LEVEL作为varchar）像'14'然后'> = 20s，<30s'当施放时（DURATION_LEVEL作为varchar）像'15'然后'> = 30s，<40s'当施放（DURATION_LEVEL作为varchar）像'16'然后'> = 40s，<50s'时施放（DURATION_LEVEL作为varchar）像'17'然后'> = 50s'否则'null'结束为DURATION_LEVEL_TRANS来自DM_AIR_API_DURATION_SPB_D）"X___SQL___" WHERE（"X ___ SQL ___"。"DURATION_LEVEL_TRANS"===然后'> = 14s，<16s'，当施放时（DURATION_LEVEL作为varchar）像'12'然后'> = 16s，<18s'当施放时（DURATION_LEVEL作为varchar）像'13'然后'> = 18s，<20s'时施放（DURATION_LEVEL作为varchar），如'14'，然后'> = 20s，<30s'，当施放时（DURATION_LEVEL作为varchar）像'15'然后'> = 30s，<40s'，当施放（DURATION_LEVEL作为varchar）像'16'然后'> = 40s，<50s'，当投出（DURATION_LEVEL as varchar）时像'17'然后'> = 50s'否则'null'结束DURATION_LEVEL_TRANS来自DM_AIR_API_DURATION_SPB_D）"X___SQL___"WHERE（"X ___ SQL ___"。"DURATION_LEVEL_TRANS"=然后'> = 14s，<16s'，当施放时（DURATION_LEVEL作为varchar）像'12'然后'> = 16s，<18s'当施放时（DURATION_LEVEL作为varchar）像'13'然后'> = 18s，<20s'时施放（DURATION_LEVEL作为varchar），如'14'，然后'> = 20s，<30s'，当施放时（DURATION_LEVEL作为varchar）像'15'然后'> = 30s，<40s'，当施放（DURATION_LEVEL作为varchar）像'16'然后'> = 40s，<50s'，当投出（DURATION_LEVEL as varchar）时像'17'然后'> = 50s'否则'null'结束DURATION_LEVEL_TRANS来自DM_AIR_API_DURATION_SPB_D）"X___SQL___"WHERE（"X ___ SQL ___"。"DURATION_LEVEL_TRANS"=当施放（DURATION_LEVEL as varchar）像'12'然后'> = 16s，<18s'时施放（DURATION_LEVEL作为varchar）像'13'然后'> = 18s，<20s'当施放（DURATION_LEVEL as varchar）像'14 'then'> = 20s，<30s'时施放（DURATION_LEVEL as varchar），如'15'，然后'> = 30s，<40s'，当施放时（DURATION_LEVEL作为varchar），如'16'，然后'> = 40s，<50s'当施放（DURATION_LEVEL作为varchar）像'17'然后'> = 50s'否则'null'结束为DURATION_LEVEL_TRANS来自DM_AIR_API_DURATION_SPB_D）"X___SQL___"WHERE（"X ___ SQL ___"。"DURATION_LEVEL_TRANS"=当施放（DURATION_LEVEL as varchar）像'12'然后'> = 16s，<18s'时施放（DURATION_LEVEL作为varchar）像'13'然后'> = 18s，<20s'当施放（DURATION_LEVEL as varchar）像'14 'then'> = 20s，<30s'时施放（DURATION_LEVEL as varchar），如'15'，然后'> = 30s，<40s'，当施放时（DURATION_LEVEL作为varchar），如'16'，然后'> = 40s，<50s'当施放（DURATION_LEVEL作为varchar）像'17'然后'> = 50s'否则'null'结束为DURATION_LEVEL_TRANS来自DM_AIR_API_DURATION_SPB_D）"X___SQL___"WHERE（"X ___ SQL ___"。"DURATION_LEVEL_TRANS"=然后'> = 18s，<20s'，当施放时（DURATION_LEVEL作为varchar）像'14'然后'> = 20s，<30s'当施放（DURATION_LEVEL作为varchar）时像'15'然后'> = 30s，<40s'时施放（DURATION_LEVEL作为varchar），如'16'，然后'> = 40s，<50s'投射时（DURATION_LEVEL作为varchar）像'17'然后'> = 50s'其他'null'结束DURATION_LEVEL_TRANS来自DM_AIR_API_DURATION_SPB_D）"X___SQL___"WHERE （"X ___ SQL ___"。"DURATION_LEVEL_TRANS"=然后'> = 18s，<20s'，当施放时（DURATION_LEVEL作为varchar）像'14'然后'> = 20s，<30s'当施放（DURATION_LEVEL作为varchar）时像'15'然后'> = 30s，<40s'时施放（DURATION_LEVEL作为varchar），如'16'，然后'> = 40s，<50s'投射时（DURATION_LEVEL作为varchar）像'17'然后'> = 50s'其他'null'结束DURATION_LEVEL_TRANS来自DM_AIR_API_DURATION_SPB_D）"X___SQL___"WHERE （"X ___ SQL ___"。"DURATION_LEVEL_TRANS"=当施放（DURATION_LEVEL作为varchar）像'16'然后'> = 40s，<50s'时施放（DURATION_LEVEL作为varchar）像'17'然后'> = 50s'否则'null'结束为DURATION_LEVEL_TRANS来自DM_AIR_API_DURATION_SPB_D）"X___SQL___" WHERE（"X ___ SQL ___"。"DURATION_LEVEL_TRANS"=当施放（DURATION_LEVEL作为varchar）像'16'然后'> = 40s，<50s'时施放（DURATION_LEVEL作为varchar）像'17'然后'> = 50s'否则'null'结束为DURATION_LEVEL_TRANS来自DM_AIR_API_DURATION_SPB_D）"X___SQL___" WHERE（"X ___ SQL ___"。"DURATION_LEVEL_TRANS"= *'> = 0s，<3s'*）GROUP BY 1
"""
!http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=6!
我想也许在解析sql时会出现一些错误，比如'case when'，我希望有人可以修复它。
> kylin parse sql error
> ---------------------
>
> Key: KYLIN-4166
> URL: https://issues.apache.org/jira/browse/KYLIN-4166
> Project: Kylin
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: v3.0.0-alpha2
> Reporter: phil.zhang
> Priority: Critical
> Attachments: 1.png, 2.png, 3.png, 4.png
>
>
> {quote} 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 .{quote}
> !http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=4!
> {quote} 2. 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
> '''{quote}
> !http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=4!
>
> {quote}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 {color:#333333}like this{color}{quote}
> {color:#333333}!http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=5!{color}
>
> {quote}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
> '''{quote}
> !http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=6!
>
>
> {quote} *I guess maybe there are some bugs about parsing sql like upper ? I hope someone can help me to deal this problem.*{quote}
--
This message was sent by Atlassian Jira
(v8.3.2#803003)