[ https://issues.apache.org/jira/browse/CALCITE-1892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16091574#comment-16091574 ]
Kevin Hinterlong commented on CALCITE-1892: ------------------------------------------- I think that would work for most cases. The only one I know which wouldn't work is SQLite which needs something like (taken from one of the answers in the Stack Overflow post, I don't use or need it). {code:sql} SELECT * FROM (SELECT * FROM "PUBLIC"."TEST" ORDER BY "TEST_VALUE" LIMIT 1 OFFSET 0) UNION ALL SELECT * FROM (SELECT * FROM "PUBLIC"."TEST" ORDER BY "TEST_VALUE" LIMIT 2 OFFSET 1) {code} > Relbuilder .union(...) should use parentheses after .sortLimit(...) is called > ----------------------------------------------------------------------------- > > Key: CALCITE-1892 > URL: https://issues.apache.org/jira/browse/CALCITE-1892 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter > Affects Versions: 1.13.0 > Environment: Java 7 > H2 version 1.4.195 > Calcite version 1.13.0 > Reporter: Kevin Hinterlong > Assignee: Julian Hyde > Labels: newbie > > 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|https://dev.mysql.com/doc/refman/5.7/en/union.html] 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|https://github.com/kevinhinterlong/CalciteUnionError] -- This message was sent by Atlassian JIRA (v6.4.14#64029)