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

kylin parse sql error

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: v3.0.0-alpha2
    • Fix Version/s: v2.6.5, v3.1.0, v3.0.1
    • 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. image-2019-09-20-15-23-29-881.png
          67 kB
          phil.zhang
        2. image-2019-09-20-15-22-15-356.png
          95 kB
          phil.zhang
        3. 4.png
          112 kB
          phil.zhang
        4. 3.png
          47 kB
          phil.zhang
        5. 2.png
          35 kB
          phil.zhang
        6. 1.png
          59 kB
          phil.zhang

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: