[
https://issues.apache.org/jira/browse/CALCITE-1892?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kevin Hinterlong updated CALCITE-1892:
--------------------------------------
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|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 as a
result of calling {{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 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]
was:
I'm trying to build multiple queries separately as {{RelNode}}s, 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 as a
result of calling {{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 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]
> 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
> 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: beginner
>
> 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 as a
> result of calling {{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 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)
