[
https://issues.apache.org/jira/browse/CALCITE-1892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16090428#comment-16090428
]
Julian Hyde commented on CALCITE-1892:
--------------------------------------
I don't agree with your interpretation of that Stack Overflow post. Parentheses
around SELECT expressions are very useful to override the usual precedence of
UNION, INTERSECT and EXCEPT, support for them is required by the SQL standard
(even if SQLLite doesn't support them), and many databases do support them.
Calcite's parser also supports them. See SqlParserTest.testOrderInternal, for
instance.
So, I think the JDBC adapter should generate SQL with parentheses if by
default. If there are particular dialects that don't support them, let's cross
that bridge when we get to it.
> 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)
