Uploaded image for project: 'Kylin'
  1. Kylin
  2. KYLIN-4166

kylin parse sql error

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • v3.0.0-alpha2
    • v2.6.5, v3.1.0, v3.0.1
    • Query Engine
    • None
    • Important

    Description

       

         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 .

       

      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

       


       

      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 maybe there are some bugs about parsing sql like upper ? I hope someone can help me to deal this problem.

      Attachments

        1. 1.png
          59 kB
          phil.zhang
        2. 2.png
          35 kB
          phil.zhang
        3. 3.png
          47 kB
          phil.zhang
        4. 4.png
          112 kB
          phil.zhang
        5. image-2019-09-20-15-22-15-356.png
          95 kB
          phil.zhang
        6. image-2019-09-20-15-23-29-881.png
          67 kB
          phil.zhang

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            zhangyaqian Yaqian Zhang
            fei8665688 phil.zhang
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Issue deployment