[(CASE ... END) * EXPRESSION AS FIELD] gives error

Hello!
We have a complex query to analyse, and we're having some problems with it...
Since the query is very complex, I've extracted the part that have the problem:

SELECT
(CASE WHEN FIELD_A=0 THEN FIELD_B
WHEN FIELD_C >FIELD_D  THEN (CASE WHEN FIELD_A>0 THEN
(FIELD_B)/(FIELD_A/(DATEDIFF(DAY,:started,:end)+1))
ELSE 0 END)-FIELD_D ELSE 0 END)*FIELD_A/(DATEDIFF(DAY,:started,:end)+1)  AS UNNECESSARY_COMPLEX_EXPRESSION
FROM TEST

When trying to parse this, we get this error:

Exception in thread "main" net.sf.jsqlparser.JSQLParserException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "*" "*"
    at line 5, column 32.

Was expecting one of:

    ";"
    "CONNECT"
    "EMIT"
    "GROUP"
    "HAVING"
    "INTO"
    "START"
    "WINDOW"
    <EOF>

	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:263)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:81)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:47)
	at org.example.Main.main(Main.java:323)
Caused by: java.util.concurrent.ExecutionException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "*" "*"
    at line 5, column 32.

Was expecting one of:

    ";"
    "CONNECT"
    "EMIT"
    "GROUP"
    "HAVING"
    "INTO"
    "START"
    "WINDOW"
    <EOF>

	at java.base/java.util.concurrent.FutureTask.report(FutureTask.java:122)
	at java.base/java.util.concurrent.FutureTask.get(FutureTask.java:205)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:258)
	... 3 more
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "*" "*"
    at line 5, column 32.

Was expecting one of:

    ";"
    "CONNECT"
    "EMIT"
    "GROUP"
    "HAVING"
    "INTO"
    "START"
    "WINDOW"
    <EOF>

	at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:33398)
	at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:33231)
	at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:167)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil$1.call(CCJSqlParserUtil.java:253)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil$1.call(CCJSqlParserUtil.java:250)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:833)

The issue is that query runs normally on SqlServer.
I can make it work with JSQLParser by removing the enclosing '(' from the CASE statement.

Good Morning! Thank you for sending this, because its an odd example.

I indeed could not parse the query as you sent it
however, I have been able to parse and format the query as soon as I add whitespace to it:

SELECT CASE
                        WHEN field_a = 0
                            THEN field_b
                        WHEN field_c > field_d
                            THEN ( CASE
                                    WHEN field_a > 0
                                        THEN ( field_b ) / ( field_a / ( Datediff( day, :started, :end ) + 1 ) )
                                    ELSE 0
                                END ) - field_d
                        ELSE 0
                    END  * field_a / ( Datediff( day, :dataInicio, :dataFim ) + 1 ) AS unnecessary_complex_expression
FROM test
;

You can try it online here.

Turns out that the Extra Brackets ( ) around the CASE statement cause the problems. Without it, it will parse fine.
I will eventually try to make this more robust, but it won't be quick because some tedious testing will be necessary.

fix: SelectItem tests for Condition() and ConcatExpression() before X…

fix: Case within brackets

I think I got that fixed, although I come to the conclusion that our Grammar is pretty broken regarding bracket ( ) management and expressions.

In my opinion, the useBrackets properties of some expressions was a big mistake and should be refactored into a generic ExpressionList as ( Expression ) (where the list has brackets and can have Zero, One or more elements).

This would eliminate Parenthesis, SubQuery and usingBrackets and maybe some more.

I think I got that fixed

Neat!

In my opinion, the useBrackets properties of some expressions was a big mistake and should be refactored into a generic ExpressionList as ( Expression ) (where the list has brackets and can have Zero, One or more elements)

Yeah, that would make the parsing more generic right? But I imagine that you would have a lot of side-effects on the parsed tree.

A huge problem with those brackets and even with the generic parsing of those is performance. In combination with lookaheads in the grammar, this could be a disaster. However, much of the bracket handling was already externalized using the Parenthesis production, when I began the project. One could make indeed an experiment to do so. Maybe the more recent versions of JavaCC are better at dealing with this.
One thing to keep in mind here is, that one would need to introduce multiple versions of this Parenthesis object to differ between special occurrences, to not open the grammar for every item at every position if only there are brackets around it.

Theoretical this bracketing could be endless deep and then you have the problem in finding the right end and therefore a performance problem:

(((((( A ) B ) C ) D ) E ) F )

And therefore to determine the type of the first parenthesis you have to parse all the way to F.

Issue1673 case within brackets (#1675)

Navigation Menu

Repository navigation

[(CASE ... END) * EXPRESSION AS FIELD] gives error #1673

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

[(CASE ... END) * EXPRESSION AS FIELD] gives error #1673

Description

Activity

manticore-projects commented on Nov 22, 2022

manticore-projects commented on Nov 22, 2022

manticore-projects commented on Nov 28, 2022

Ryaryu commented on Nov 29, 2022

wumpz commented on Dec 10, 2022

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions