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

["Kevin Hinterlong (JIRA)" <ji...@apache.org>]

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

Kevin Hinterlong updated CALCITE-1892:
--------------------------------------
    Summary: Relbuilder .union(...) should use parentheses after .sortLimit(...) is called  (was: Relbuilder union all doesn't use parentheses)

> 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 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]



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)