[
https://issues.apache.org/jira/browse/CALCITE-1892?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16091574#comment-16091574
]
Kevin Hinterlong edited comment on CALCITE-1892 at 7/18/17 1:57 PM:
--------------------------------------------------------------------
I think that would work for most cases. The only one I know which wouldn't work
is SQLite which needs something like below (taken from one of the answers in
the Stack Overflow post, I don't use or need support for SQLite)
{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}
was (Author: kevinhinterlong):
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)
