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

kylin parse sql error

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: v3.0.0-alpha2
    • Fix Version/s: v3.1.0
    • Component/s: Query Engine
    • Labels:
      None
    • Flags:
      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

          Issue Links

            Activity

              People

              • Assignee:
                zhangyaqian Yaqian Zhang
                Reporter:
                fei8665688 phil.zhang
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: