You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@kylin.apache.org by 张飞 <fe...@163.com> on 2019/09/11 05:19:44 UTC
apache-kylin-3.0.0-alpha2-bin-hbase1x Issue
Hi~
Question 1:
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 .
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
'''
Question 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 like this
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
'''
I guess if there are some bugs about parsing sql like upper ? I hope someone can help me to deal this problem.
Re: apache-kylin-3.0.0-alpha2-bin-hbase1x Issue
Posted by Yaqian Zhang <Ya...@126.com>.
Hi 张飞：
I reproduced the problem you encountered.
In kylin, the query results of a SQL similar to "select *, case when then from table" do not meet expectations, but a SQL like "select sum (name), case when then from table group by 2" is no problem.
So I think you are right.Kylin’s parsing of CASE WHEN function does exists some bugs, you can go to Kylin's JIRA list(https://issues.apache.org/jira/projects/KYLIN/issues/) and open issue to report your problems, we will arrange time to deal with them.
> On Sep 11, 2019, at 1:19 PM, 张飞 <fe...@163.com> wrote:
>
> Hi~
> Question 1:
> 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.png>
>
> 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
> '''
> <image.png>
>
> Question 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 like this
> <image.png>
>
> 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
> '''
> <image.png>
>
>
> I guess if there are some bugs about parsing sql like upper ? I hope someone can help me to deal this problem.
>
>
>
>