[
https://issues.apache.org/jira/browse/CALCITE-1892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16088244#comment-16088244
]
Julian Hyde commented on CALCITE-1892:
--------------------------------------
Sounds valid. It looks to be a problem with the MySQL-specific SQL generation,
i.e. {{RelToSqlConverter}} and {{SqlNode.unparse}}, rather than {{RelBuilder}}.
{{RelBuilder}} is doing its job correctly, and giving the correct {{RelNode}}
tree. I saw your example, but it should be possible to reproduce this with a
fairly concise test case in {{RelToSqlConverterTest}}.
Do other SQL dialects (e.g. PostgreSQL) require parentheses too?
> 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)
