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