[jira] [Created] (CALCITE-1892) Relbuilder union all doesn't use parentheses

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

Kevin Hinterlong created CALCITE-1892:
-----------------------------------------

             Summary: Relbuilder union all doesn't use parentheses
                 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


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)