[BUG] JSQLParser Version 4.6 : MySQL : ParseException from using ORDER BY with UNION query

[kmantzelSNow]

Hello, 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

[manticore-projects]

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]

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]

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]

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]

Duplicate #903

[manticore-projects]

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.