[
https://issues.apache.org/jira/browse/KYLIN-4166?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xuekaiqi updated KYLIN-4166:
----------------------------
Description:
1. I use tableau on kylin, tableau has generate a sql :
{code:java}
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{code}
it seems that the sql is correct, but the result is unexpect, it returns
null .
!image-2019-09-20-15-22-15-356.png!
But actually, it should returns some rows because there exist data fit the
query, let's change the sql form to check it.
{code:java}
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{code}
!image-2019-09-20-15-23-29-881.png!
2. and when i write sql like this
{code:java}
SELECT flight_type,SUM("SHOPPING_SUCCESS_COUNT") AS
"sum_SHOPPING_SUCCESS_COUNT_ok" from DM_AIR_API_DURATION_SPB_D group by
flight_type{code}
i got result {color:#333333}like this{color}
{color:#333333}!http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=5!{color}
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
'''
!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:
1. I use tableau on kylin, tableau has generate a sql :
'''
{code:java}
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{code}
'''
it seems that the sql is correct, but the result is unexpect, it returns
null .
!image-2019-09-20-15-22-15-356.png!
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
'''
!image-2019-09-20-15-23-29-881.png!
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 {color:#333333}like this{color}
{color:#333333}!http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=5!{color}
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
'''
!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}
> 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
> Assignee: Yaqian Zhang
> Priority: Critical
> Fix For: v2.6.5, v3.1.0, v3.0.1
>
> Attachments: 1.png, 2.png, 3.png, 4.png,
> image-2019-09-20-15-22-15-356.png, image-2019-09-20-15-23-29-881.png
>
>
>
> 1. I use tableau on kylin, tableau has generate a sql :
> {code:java}
> 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{code}
>
> it seems that the sql is correct, but the result is unexpect, it returns
> null .
> !image-2019-09-20-15-22-15-356.png!
> But actually, it should returns some rows because there exist data fit the
> query, let's change the sql form to check it.
> {code:java}
> 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{code}
>
> !image-2019-09-20-15-23-29-881.png!
>
> 2. and when i write sql like this
> {code:java}
> SELECT flight_type,SUM("SHOPPING_SUCCESS_COUNT") AS
> "sum_SHOPPING_SUCCESS_COUNT_ok" from DM_AIR_API_DURATION_SPB_D group by
> flight_type{code}
> i got result {color:#333333}like this{color}
>
> {color:#333333}!http://mail.163.com/js6/s?func=mbox:getMessageData&mid=270:xtbBDhAtQVrbEIH+vQAAsh&part=5!{color}
> 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
> '''
> !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.4#803005)
