[ 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: us...@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)