Incorrect association of LIMIT and OFFSET in a union query

[sivaraam]

Note: Similar to #899

Describe the bug

The LIMIT and OFFSET present at the end of a UNION query should be associated with the UNION query as a whole. Currently, it is being associated with the last SELECT query in the UNION.

To Reproduce

Steps to reproduce the behavior:

1. Example SQL

SELECT * FROM table1 UNION SELECT * FROM table2 ORDER BY col LIMIT 4 OFFSET 5

2. Parsing this SQL using JSqlParser with this statements

Select unionQuery = (Select) CCJSqlParserUtil.parse("SELECT * FROM table1 UNION SELECT * FROM table2 ORDER BY col LIMIT 4 OFFSET 5");
SetOperationList unionQueries = (SetOperationList) unionQuery.getSelectBody();
System.out.println("Limit: " + unionQueries.getLimit() + "; Offset: " + unionQueries.getOffset());
unionQueries.getSelects().forEach(query -> {
    PlainSelect plainQuery = (PlainSelect) query;
    System.out.println("Limit: " + plainQuery.getLimit() + "; Offset: " + plainQuery.getOffset());
});

Actual behavior

Limit: null; Offset: null
Limit: null; Offset: null
Limit:  LIMIT 4; Offset:  OFFSET 5

Expected behavior

Limit:  LIMIT 4; Offset:  OFFSET 5
Limit: null; Offset: null
Limit: null; Offset: null

Reference

• sql - Combining UNION and LIMIT operations in MySQL query - Stack Overflow
• MySQL :: MySQL 8.0 Reference Manual :: 13.2.10.3 UNION Clause

System

• Database you are using: PostgreSQL, MySQL
• Java Version: 11.0.3
• JSqlParser version: 3.1

[manticore-projects]

Works with JSQLParser 4.6, closed.