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

UNNEST(RECORDTYPE(MAP)) not supported

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

    Details

      Description

      UNNEST(RECORDTYPE(ARRAY)) and UNNEST(RECORDTYPE(MULTISET)) works well, but UNNEST(RECORDTYPE(MAP)) is not supported.

      // JdbcTest
      @Test public void testUnnestRecordType() {
          // unnest(RecordType(Array))
          CalciteAssert.that()
              .query("select * from unnest\n"
                  + "(select t.x from (values array[10, 20], array[30, 40]) as t(x))\n"
                  + " with ordinality as t(a, o)")
              .returnsUnordered("A=10; O=1", "A=20; O=2",
                  "A=30; O=1", "A=40; O=2");
      
          // unnest(RecordType(Multiset))
          CalciteAssert.that()
              .query("select * from unnest\n"
                  + "(select t.x from (values multiset[10, 20], array[30, 40]) as t(x))\n"
                  + " with ordinality as t(a, o)")
              .returnsUnordered("A=10; O=1", "A=20; O=2",
                  "A=30; O=1", "A=40; O=2");
      
          // unnest(RecordType(Map))
          CalciteAssert.that()
              .query("select * from unnest\n"
                  + "(select t.x from (values map['a', 20, 'b', 30], map['c', 40]) as t(x))\n"
                  + " with ordinality as t(a, b, o)")
              .returnsUnordered("A=a; B=20; O=1", "A=B; B=30; O=2",
                  "A=c; B=40; O=1");
        }
      

      In the case, test for unnest(RecordType(Array)) and unnest(RecordType(Multiset)) success, but got exception for unnest(RecordType(Map)).

      For the first two test of unnest(RecordType(Array)) and unnest(RecordType(Multiset)), the relnode tree with type is like this

      EnumerableUncollect(withOrdinality=[true]), type = RecordType(INTEGER EXPR$0, INTEGER ORDINALITY)
        EnumerableUnion(all=[true]), type = RecordType(INTEGER ARRAY EXPR$0)
          EnumerableCalc(expr#0=[{inputs}], expr#1=[10], expr#2=[20], expr#3=[ARRAY($t1, $t2)], EXPR$0=[$t3]), type = RecordType(INTEGER ARRAY EXPR$0)
            EnumerableValues(tuples=[[{ 0 }]]), type = RecordType(INTEGER ZERO)
          EnumerableCalc(expr#0=[{inputs}], expr#1=[30], expr#2=[40], expr#3=[ARRAY($t1, $t2)], EXPR$0=[$t3]), type = RecordType(INTEGER ARRAY EXPR$0)
            EnumerableValues(tuples=[[{ 0 }]]), type = RecordType(INTEGER ZERO)
      
      EnumerableUncollect(withOrdinality=[true]), type = RecordType(INTEGER EXPR$0, INTEGER ORDINALITY)
        EnumerableUnion(all=[true]), type = RecordType(INTEGER ARRAY EXPR$0)
          EnumerableCalc(expr#0=[{inputs}], expr#1=[$SLICE($t0)], EXPR$0=[$t1]), type = RecordType(INTEGER MULTISET EXPR$0)
            EnumerableCollect(field=[EXPR$0]), type = RecordType(RecordType(INTEGER ROW_VALUE) MULTISET EXPR$0)
              EnumerableValues(tuples=[[{ 10 }, { 20 }]]), type = RecordType(INTEGER ROW_VALUE)
          EnumerableCalc(expr#0=[{inputs}], expr#1=[30], expr#2=[40], expr#3=[ARRAY($t1, $t2)], EXPR$0=[$t3]), type = RecordType(INTEGER ARRAY EXPR$0)
            EnumerableValues(tuples=[[{ 0 }]]), type = RecordType(INTEGER ZERO)
      

      Part of the stacktrace of the exception is:

      
      Cannot apply 'UNNEST' to arguments of type 'UNNEST(<RECORDTYPE((CHAR(1), INTEGER) MAP X)>)'. Supported form(s): 'UNNEST(<MULTISET>)'
      'UNNEST(<ARRAY>)'
      'UNNEST(<MAP>)'
      UNNEST(<MULTISET>)
      	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
      	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
      	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
      	at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
      	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:839)
      	at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:824)
      	at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4905)
      	at org.apache.calcite.sql.SqlCallBinding.newValidationSignatureError(SqlCallBinding.java:280)
      	at org.apache.calcite.sql.type.CompositeOperandTypeChecker.checkOperandTypes(CompositeOperandTypeChecker.java:261)
      	at org.apache.calcite.sql.SqlOperator.checkOperandTypes(SqlOperator.java:668)
      	at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:432)
      	at org.apache.calcite.sql.validate.UnnestNamespace.validateImpl(UnnestNamespace.java:66)
      	at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
      	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1009)
      	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:969)
      	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateUnnest(SqlValidatorImpl.java:3171)
      	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3150)
      	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3135)
      	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3407)
      	at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
      	at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
      	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1009)
      	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:969)
      	at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:232)
      	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:944)
      	at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:651)
      	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:566)
      	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:265)
      	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:231)
      	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:638)
      	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:502)
      	at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:472)
      	at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:231)
      	at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:550)
      	at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
      	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
      	... 71 more
      

        Attachments

          Activity

            People

            • Assignee:
              yanlin-Lynn Wang Yanlin
              Reporter:
              yanlin-Lynn Wang Yanlin

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0h
                0h
                Logged:
                Time Spent - 40m
                40m

                  Issue deployment