[CALCITE-3567] UNNEST(RECORDTYPE(MAP)) not supported

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.21.0
• Fix Version/s: 1.22.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Wang Yanlin
• Reporter: Wang Yanlin
• Created: 04/Dec/19 09:44
• Updated: 28/May/21 10:09
• Resolved: 05/Dec/19 00:40

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

Issue Links

• links to: GitHub Pull Request #1630