[
https://issues.apache.org/jira/browse/CALCITE-1892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16089821#comment-16089821
]
Kevin Hinterlong commented on CALCITE-1892:
-------------------------------------------
Ok so it looks like this is something that is supported by MySQL (and
PostgreSQL, not sure what else), but Standard SQL doesn’t allow this. So for
example SQLite doesn’t support it.
Here’s a [relevant
stackoverflow|https://stackoverflow.com/questions/4653124/what-does-the-sql-standard-say-about-parentheses-in-sql-union-except-intersect-s]
So it looks like the best portable way to accomplish this would be to make
multiple queries.
> 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)
