Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1892

Relbuilder .union(...) should use parentheses after .sortLimit(...) is called

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.14.0
    • Fix Version/s: None
    • Component/s: jdbc-adapter
    • Labels:
    • Environment:

      Java 7
      H2 version 1.4.195
      Calcite version 1.13.0

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

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              kevinhinterlong Kevin Hinterlong
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: