GeezerVison is showing github.com — [(CASE ... END) * EXPRESSION AS FIELD] gives error · Issue #1673 · JSQLParser/JSqlParser
[(CASE ... END) * EXPRESSION AS FIELD] gives error #1673DescriptionHello!
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. Activitymanticore-projects commented on Nov 22, 2022 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. manticore-projects commented on Nov 22, 2022 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. added 2 commits that reference this issue on Nov 27, 2022This issue will close once commit 2cf887b is merged into the 'master' branch.2cf887b This issue will close once commit e0471b5 is merged into the 'master' branch.e0471b5 manticore-projects commented on Nov 28, 2022 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. Ryaryu commented on Nov 29, 2022
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. wumpz commented on Dec 10, 2022 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. added a commit that references this issue on Jan 31, 2023This issue will close once commit 2ced7de is merged into the 'master' branch.2ced7de
MetadataMetadataDevelopmentNo branches or pull requestsIssue actions
|