You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@kylin.apache.org by "ASF GitHub Bot (Jira)" <ji...@apache.org> on 2019/10/21 06:52:00 UTC
[jira] [Commented] (KYLIN-4166) kylin parse sql error
[ https://issues.apache.org/jira/browse/KYLIN-4166?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16955808#comment-16955808 ]
ASF GitHub Bot commented on KYLIN-4166:
---------------------------------------
zhangayqian commented on pull request #895: KYLIN-4166 Case when return null when SQL no GROUP BY
URL: https://github.com/apache/kylin/pull/895
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
> 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
> 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 :
> '''
> 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 .
> !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}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)