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

Allow explicit ROW value constructor in SELECT clause and elsewhere

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.17.0
    • Component/s: core
    • Labels:
      None

      Description

      Allow explicit ROW value constructor in SELECT clause and elsewhere. Now for our production, we can parse a query successfully like this :

      -- correlated IN subquery
      -- TC 01.01
      SELECT t1a,
             t1b,
             t1h
      FROM   t1
      WHERE  ( t1a, t1h ) NOT IN (SELECT t2a,
                                         t2h
                                  FROM   t2
                                  WHERE  t2a = t1a
                                  ORDER  BY t2a)
      AND t1a = 'val1a'
      

      but if we add in `Row`:

      -- correlated IN subquery
      -- TC 01.01
      SELECT t1a,
             t1b,
             t1h
      FROM   t1
      WHERE  ROW( t1a, t1h ) NOT IN (SELECT t2a,
                                         t2h
                                  FROM   t2
                                  WHERE  t2a = t1a
                                  ORDER  BY t2a)
      AND t1a = 'val1a'
      

      it will throw exception:

      Caused by: org.apache.calcite.sql.parser.SqlParseException: ROW expression
      encountered in illegal context
        at org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:351)
        at org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:133)
        at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:138)
        at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:163)
        at org.apache.flink.table.calcite.FlinkPlannerImpl.parse(FlinkPlannerImpl.scala:81)
      ... 8 more
      

      For the success query, if we exec parsed AST tree rootNode.toString(), it
      will return a query like:

      SELECT `t1a`,
             `t1b`,
             `t1h`
      FROM `t1`
      WHERE ROW(`t1a`, `t1h`) NOT IN (SELECT `t2a`, `t2h`
                                      FROM `t2`
                                      WHERE `t2a` = `t1a`
                                      ORDER BY `t2a`)
      AND `t1a` = 'val1a'
      

      This is inconsistent by Calcite itself semantic.

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              suez1224 Shuyi Chen
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: