GeezerVison is showing github.com — [BUG] JSQLParser Version 4.6 : MySQL : ParseException from using ORDER BY with UNION query · Issue #1737 · JSQLParser/JSqlParser
[BUG] JSQLParser Version 4.6 : MySQL : ParseException from using ORDER BY with UNION query #1737DescriptionHello, as per the error tracker guidelines I tested the sql statement in the interactive demo page here and was not able to format the query. However, I can execute it in MySql.
Failing SQL Feature:
- A
JSQLParserException is being thrown from calling CCJSqlParserUtil.parse(sql) with the sql statement provided below.
net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "ORDER" "ORDER"
SQL Example:
SELECT `topic`, `order_0`
FROM (
(SELECT `topic` as `topic`, `priority` AS `order_0` FROM table1 GROUP BY `topic`)
UNION ALL
(SELECT `topic` as `topic`, `priority` AS `order_0` FROM table2 GROUP BY `topic`)
ORDER BY `order_0`
)
GROUP BY `topic`
ORDER BY `order_0`;Note that if you do one of the following:
- remove the inner
ORDER BY
- remove the parentheses around the inner
SELECTs that get UNION
then this statement can be parsed.
Stack trace:
Exception in thread "main" net.sf.jsqlparser.JSQLParserException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "ORDER" "ORDER"
at line 6, column 10.
Was expecting:
")"
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 tooling.TestResultDiffer.main(TestResultDiffer.java:67)
Caused by: java.util.concurrent.ExecutionException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "ORDER" "ORDER"
at line 6, column 10.
Was expecting:
")"
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: "ORDER" "ORDER"
at line 6, column 10.
Was expecting:
")"
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:38866)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:38696)
at net.sf.jsqlparser.parser.CCJSqlParser.FromItem(CCJSqlParser.java:10637)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:7851)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:8107)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:7718)
at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:7713)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:377)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:285)
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:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Software Information:
- JSqlParser version: 4.6 (this parse exception happens since version 4.1)
- Database: MySQL (MariaDB)
- jdk 11
Activitymanticore-projects commented on Mar 1, 2023 Greetings.
Thank you for reporting. The extra brackets around the SELECT ... statements are not supported and without it, everything will work:
SELECT `topic`
, `order_0`
FROM ( SELECT `topic` AS `topic`
, `priority` AS `order_0`
FROM table1
GROUP BY `topic`
UNION ALL
SELECT `topic` AS `topic`
, `priority` AS `order_0`
FROM table2
GROUP BY `topic`
ORDER BY `order_0` )
GROUP BY `topic`
ORDER BY `order_0`
;Test online.
In general, the Parser is not very good with Extra Brackets yet and I recommend to avoid such Extra Brackets where possible. manticore-projects commented on Mar 1, 2023 The main problem is, that the Parser expected the ORDER BY to be part of any of those SELECT ... statements.
It is a clearly a bug but also unlikely to get this fixed promptly since it will take some major refactoring. kmantzelSNow commented on Mar 1, 2023 Thanks for the very quick response!
I see. For now I will go forward with your suggestion of removing the parentheses since that can be parsed manticore-projects commented on Mar 2, 2023 SELECT `topic`
, `order_0`
FROM ( SELECT `topic` AS `topic`
, `priority` AS `order_0`
FROM table1
GROUP BY `topic`
UNION ALL (
SELECT `topic` AS `topic`
, `priority` AS `order_0`
FROM table2
GROUP BY `topic`
ORDER BY `order_0` ) )
GROUP BY `topic`
ORDER BY `order_0`
;Illustration where the problem is: ORDER BY ... does not belong to the UNION ..., but is expected for the SELECT .... This part needs to be rewritten. The sample shows the "current logic" of the parser. manticore-projects commented on Mar 2, 2023 manticore-projects commented on Mar 20, 2023 I am working on this one. I got this statement parsed already, although at the cost of breaking other statements due to the flawed Bracket implementation.
MetadataMetadataDevelopmentNo branches or pull requestsIssue actions
|