[CALCITE-1892] Relbuilder .union(...) should use parentheses after .sortLimit(...) is called

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 1.14.0
• Fix Version/s: 1.32.0
• Component/s: jdbc-adapter
• Labels: newbie

Environment

Java 7
H2 version 1.4.195
Calcite version 1.13.0

• Assignee: Unassigned
• Reporter: Kevin Hinterlong
• Created: 14/Jul/17 21:27
• Updated: 10/Sep/22 08:42
• Resolved: 25/Mar/22 21:07

Description

I'm trying to build up multiple queries separately as RelNode , then call

builder.pushAll(unionParts).union(true);

to union the results in SQL, but I end up with
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement

It turns out to be an issue with lack of parentheses between calls to UNION ALL

I've done some digging and according to the mysql docs the union call needs to have these RelNode's (or their equivalent sql) wrapped in parentheses when using ORDER BY and LIMIT which is
.sortLimit(offset, fetch, builder.field("TEST_VALUE"))
with the RelBuilder

I've been able to resolve this by converting each RelNode to SQL and manually wrapping it in parentheses between calls to UNION ALL before executing the statement against H2
Like so: (<insert query>) UNION ALL (<insert query>)

Here's an example I made to show exactly what's happening kevinhinterlong/CalciteUnionError

Issue Links

• duplicates: CALCITE-5013 JDBC adapter should retain parentheses if a query in a UNION has a LIMIT clause