[jira] [Resolved] (CALCITE-1892) Relbuilder .union(...) should use parentheses after .sortLimit(...) is called

["Julian Hyde (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-1892?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde resolved CALCITE-1892.
----------------------------------
    Resolution: Duplicate

I'm marking this as a duplicate of CALCITE-5013. (Not to diminish it in any way. It was logged first, and contains a lot of good investigation. But the new bug has a more useful subject, and a PR attached.)

> 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.14.0
>         Environment: Java 7
> H2 version 1.4.195
> Calcite version 1.13.0
>            Reporter: Kevin Hinterlong
>            Priority: Major
>              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
(v8.20.1#820001)