Skip to content

Failed to parse 'CASE WHEN' condition expression without brackets #723

Closed

Description

@chenyele

I had the following SQL statment:

SELECT
    CASE
        WHEN "metrics_all"."METRIC_ID" IS NULL OR "metrics_all"."SUB_METRIC" IS NULL THEN NULL
        WHEN "metrics_all"."METRIC_ID" > "metrics_all"."SUB_METRIC" THEN "metrics_all"."METRIC_ID"
        ELSE "metrics_all"."SUB_METRIC"
    END AS "MET"
FROM
    "mrs"."metrics_all" AS "metrics_all"
GROUP BY
    CASE
        WHEN "metrics_all"."METRIC_ID" IS NULL
        OR "metrics_all"."SUB_METRIC" IS NULL THEN NULL
        WHEN "metrics_all"."METRIC_ID" > "metrics_all"."SUB_METRIC" THEN "metrics_all"."METRIC_ID"
        ELSE "metrics_all"."SUB_METRIC"
    END

This SQL statment could be excuted by Command-line Client and clickhouse-jdbc-0.2.4:
image
But could not be exceuted by clickhouse-jdbc-0.3.1,because the SQL parse is mandatory ,and got the following error:
2021-09-15 19:58:44,058 | WARN | main | Parse error at line 3, column 48. Encountered: OR. If you believe the SQL is valid, please feel free to open an issue on Github with this warning and the following SQL attached.
SELECT
CASE
WHEN "metrics_all"."METRIC_ID" IS NULL OR "metrics_all"."SUB_METRIC" IS NULL THEN NULL
WHEN "metrics_all"."METRIC_ID" > "metrics_all"."SUB_METRIC" THEN "metrics_all"."METRIC_ID"
ELSE "metrics_all"."SUB_METRIC"
END AS "TEMP(Test)(2280873464)(0)"
FROM
"mrs"."metrics_all" AS "metrics_all"
GROUP BY
CASE
WHEN "metrics_all"."METRIC_ID" IS NULL
OR "metrics_all"."SUB_METRIC" IS NULL THEN NULL
WHEN "metrics_all"."METRIC_ID" > "metrics_all"."SUB_METRIC" THEN "metrics_all"."METRIC_ID"
ELSE "metrics_all"."SUB_METRIC"
END | ru.yandex.clickhouse.jdbc.parser.ClickHouseSqlParser.parse(ClickHouseSqlParser.java:71)

It seemed that the parser can not resolve 'OR' between 'When' and 'Then', so I had to add brackets like this to make it work:

SELECT
    CASE
        WHEN ("metrics_all"."METRIC_ID" IS NULL OR "metrics_all"."SUB_METRIC") IS NULL THEN NULL
        WHEN "metrics_all"."METRIC_ID" > "metrics_all"."SUB_METRIC" THEN "metrics_all"."METRIC_ID"
        ELSE "metrics_all"."SUB_METRIC"
    END AS "MET"
FROM
    "mrs"."metrics_all" AS "metrics_all"
GROUP BY
    CASE
        WHEN "metrics_all"."METRIC_ID" IS NULL
        OR "metrics_all"."SUB_METRIC" IS NULL THEN NULL
        WHEN "metrics_all"."METRIC_ID" > "metrics_all"."SUB_METRIC" THEN "metrics_all"."METRIC_ID"
        ELSE "metrics_all"."SUB_METRIC"
    END

Activity

  1. added this to the 0.3.2 Release milestone on Sep 15, 2021
  2. zhicwu commented on Sep 15, 2021

    @zhicwu
    Contributor

    Thnaks for reporting the issue. Yes, brackets are the workaround. Will need to clean up the mess of dealing with nested expressions.

    https://github.com/ClickHouse/clickhouse-jdbc/blob/5e6f74b89354e60af7d0c9437eef0520ee9ffda1/clickhouse-jdbc/src/main/javacc/ClickHouseSqlParser.jj#L612-L615

  3. linked a pull request that will close this issueEnhance new JDBC driver #765on Nov 29, 2021
  4. modified the milestones: 0.6 release, v0.5.1 on Aug 29, 2023
  5. github-actions commented on Jan 8, 2025

    @github-actions

    This issue has been automatically marked as stale because it has not had activity in the last year. It will be closed in 30 days if no further activity occurs. Please feel free to leave a comment if you believe the issue is still relevant. Thank you for your contributions!

  6. chernser commented on Jan 9, 2025

    @chernser
    Contributor

    new jdbc-v2 has no parser and no such issue.
    workaround exists.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions