[CALCITE-863] AssertionError while translating query with UNNEST and having two correlation conditions

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Labels: pull-request-available sub-query
• Assignee: Unassigned
• Reporter: Wei Xue
• Created: 31/Aug/15 16:23
• Updated: 18/Jun/21 08:31

Description

DDL:

CREATE TABLE score_table(
    student_id INTEGER NOT NULL, 
    subject_id INTEGER NOT NULL, 
    scores INTEGER[] 
    CONSTRAINT pk PRIMARY KEY (student_id, subject_id));

Running the UNNEST correlated query with two correlation conditions would cause an AssertionError:

SELECT s.student_id, s.subject_id, t.score
FROM 
    score_table s, 
    UNNEST((
        SELECT scores 
        FROM score_table s2 
        WHERE s.student_id = s2.student_id 
            AND s.subject_id = s2.subject_id)) 
    AS t(score)

java.lang.AssertionError: Internal error: Type 'RecordType(INTEGER STUDENT_ID, INTEGER SUBJECT_ID, INTEGER ARRAY SCORES)' has no field 'EXPR$0'
	at org.apache.calcite.util.Util.newInternal(Util.java:777)
	at org.apache.calcite.rex.RexBuilder.makeFieldAccess(RexBuilder.java:167)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:3187)
	at org.apache.calcite.sql2rel.SqlToRelConverter.access$5(SqlToRelConverter.java:3161)
	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4126)
	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:1)
	at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:271)
	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4009)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectList(SqlToRelConverter.java:3396)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:625)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:581)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:2768)
	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:535)
	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:222)
	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:188)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:671)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:572)
	at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:541)
	at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:173)
	at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:561)
	at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:477)
	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:109)
	at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:130)
	at org.apache.phoenix.calcite.CalciteIT$Sql.getResult(CalciteIT.java:157)
	at org.apache.phoenix.calcite.CalciteIT$Sql.explainIs(CalciteIT.java:133)
	at org.apache.phoenix.calcite.CalciteIT.testUnnest(CalciteIT.java:1057)

But if there's only one correlation condition, the query runs fine:

SELECT s.student_id, s.subject_id, t.score
FROM 
    score_table s, 
    UNNEST((
        SELECT scores 
        FROM score_table s2 
        WHERE s.student_id = s2.student_id)) 
    AS t(score)

Issue Links

• depends upon: CALCITE-1045 Decorrelate sub-queries in Project and Join
• is related to: CALCITE-816 Represent sub-query as a RexNode
• links to: GitHub Pull Request #1108