Uploaded image for project: 'CarbonData'
  1. CarbonData
  2. CARBONDATA-3294

MV datamap throw error when using count(1) and case when expression

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.6.0
    • Component/s: None
    • Labels:
      None

      Description

      Query SQL
      ```
      sql(s"""SELECT MT.`3600` AS `3600`,
      MT.`2250410101` AS `2250410101`,
      count(1) over() as countNum,
      (CASE WHEN (SUM(COALESCE(seq_c, 0))) = 0 THEN NULL
      ELSE
      (CASE WHEN (CAST((SUM(COALESCE(seq_c, 0))) AS int)) = 0 THEN 0
      ELSE ((CAST((SUM(COALESCE(succ_c, 0))) AS double))
      / (CAST((SUM(COALESCE(seq_c, 0))) AS double)))
      END) * 100
      END) AS rate
      FROM (
      SELECT sum_result.*, H_REGION.`2250410101` FROM
      (SELECT cast(floor((starttime + 28800) / 3600) * 3600 - 28800 as int) AS `3600`,
      LAYER4ID,
      COALESCE(SUM(seq), 0) AS seq_c,
      COALESCE(SUM(succ), 0) AS succ_c
      FROM data_table
      WHERE STARTTIME >= 1549866600 AND STARTTIME < 1549899900
      GROUP BY cast(floor((STARTTIME + 28800) / 3600) * 3600 - 28800 as int),LAYER4ID
      )sum_result
      LEFT JOIN
      (SELECT l4id AS `225040101`,
      l4name AS `2250410101`,
      l4name AS NAME_2250410101
      FROM region
      GROUP BY l4id, l4name) H_REGION
      ON sum_result.LAYER4ID = H_REGION.`225040101`
      WHERE H_REGION.NAME_2250410101 IS NOT NULL
      ) MT
      GROUP BY MT.`3600`, MT.`2250410101`
      ORDER BY `3600` ASC LIMIT 5000""".stripMargin)
      ```

       

      ERROR:

      mismatched input 'FROM' expecting {<EOF>, 'WHERE', 'GROUP', 'ORDER', 'HAVING', 'LIMIT', 'LATERAL', 'WINDOW', 'UNION', 'EXCEPT', 'MINUS', 'INTERSECT', 'SORT', 'CLUSTER', 'DISTRIBUTE'}(line 2, pos 0)

      == SQL ==
      SELECT MT.`3600`, MT.`2250410101`, `countNum`, `rate`
      FROM
      ^^^

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                qiuchenjian Chenjian Qiu
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 1h 40m
                  1h 40m