[CALCITE-2159] Support dynamic row type in UNNEST (Chunhui Shi) Also: * Added a method RelDataTypeFactory.Builder.buildDynamic() so you can build dynamic types the same way as you build regular struct types. * Removed SqlParser.Config.allowBangEqual; it was not used.
Close apache/calcite#672 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/c3f2705f Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/c3f2705f Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/c3f2705f Branch: refs/heads/master Commit: c3f2705f65da71ecf70aa6fc1fb42ecf07114790 Parents: 7924eb0 Author: chunhui-shi <[email protected]> Authored: Wed Apr 18 16:47:36 2018 -0700 Committer: Julian Hyde <[email protected]> Committed: Tue May 1 11:16:47 2018 -0700 ---------------------------------------------------------------------- .../org/apache/calcite/rel/core/Uncollect.java | 16 +++- .../calcite/rel/type/RelDataTypeFactory.java | 9 ++ .../calcite/rel/type/RelDataTypeHolder.java | 6 +- .../apache/calcite/sql/SqlUnnestOperator.java | 18 +++- .../apache/calcite/sql/parser/SqlParser.java | 4 - .../apache/calcite/sql/type/SqlTypeUtil.java | 9 ++ .../calcite/sql/validate/UnnestNamespace.java | 31 +++---- .../calcite/test/SqlToRelConverterTest.java | 40 ++++++++- .../apache/calcite/test/SqlToRelTestBase.java | 7 +- .../calcite/test/SqlToRelConverterTest.xml | 93 +++++++++++++++++++- 10 files changed, 201 insertions(+), 32 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/c3f2705f/core/src/main/java/org/apache/calcite/rel/core/Uncollect.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/core/Uncollect.java b/core/src/main/java/org/apache/calcite/rel/core/Uncollect.java index 0f9e0b9..0c531cb 100644 --- a/core/src/main/java/org/apache/calcite/rel/core/Uncollect.java +++ b/core/src/main/java/org/apache/calcite/rel/core/Uncollect.java @@ -23,6 +23,7 @@ import org.apache.calcite.rel.RelInput; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.RelWriter; import org.apache.calcite.rel.SingleRel; +import org.apache.calcite.rel.type.DynamicRecordType; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; import org.apache.calcite.rel.type.RelDataTypeField; @@ -123,8 +124,19 @@ public class Uncollect extends SingleRel { RelDataType inputType = rel.getRowType(); assert inputType.isStruct() : inputType + " is not a struct"; final List<RelDataTypeField> fields = inputType.getFieldList(); - final RelDataTypeFactory.Builder builder = - rel.getCluster().getTypeFactory().builder(); + final RelDataTypeFactory typeFactory = rel.getCluster().getTypeFactory(); + final RelDataTypeFactory.Builder builder = typeFactory.builder(); + + if (fields.size() == 1 + && fields.get(0).getType().getSqlTypeName() == SqlTypeName.ANY) { + // Component type is unknown to Uncollect, build dynamic star record + // type. Only consider ONE field case for unknown type. + return builder + .add(DynamicRecordType.DYNAMIC_STAR_PREFIX, SqlTypeName.ANY) + .nullable(true) + .build(); + } + for (RelDataTypeField field : fields) { if (field.getType() instanceof MapSqlType) { builder.add(SqlUnnestOperator.MAP_KEY_COLUMN_NAME, field.getType().getKeyType()); http://git-wip-us.apache.org/repos/asf/calcite/blob/c3f2705f/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeFactory.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeFactory.java b/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeFactory.java index 1eeffb6..da65d87 100644 --- a/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeFactory.java +++ b/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeFactory.java @@ -549,6 +549,15 @@ public interface RelDataTypeFactory { return typeFactory.createStructType(kind, types, names); } + /** Creates a dynamic struct type with the current contents of this + * builder. */ + public RelDataType buildDynamic() { + final RelDataType dynamicType = new DynamicRecordTypeImpl(typeFactory); + final RelDataType type = build(); + dynamicType.getFieldList().addAll(type.getFieldList()); + return dynamicType; + } + /** Returns whether a field exists with the given name. */ public boolean nameExists(String name) { return names.contains(name); http://git-wip-us.apache.org/repos/asf/calcite/blob/c3f2705f/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeHolder.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeHolder.java b/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeHolder.java index 1a777ff..1473679 100644 --- a/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeHolder.java +++ b/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeHolder.java @@ -57,12 +57,16 @@ class RelDataTypeHolder { if (Util.matches(caseSensitive, f.getName(), fieldName)) { return Pair.of(f, false); } + // A dynamic star field matches any field + if (f.getType().getSqlTypeName() == SqlTypeName.DYNAMIC_STAR) { + return Pair.of(f, false); + } } final SqlTypeName typeName = DynamicRecordType.isDynamicStarColName(fieldName) ? SqlTypeName.DYNAMIC_STAR : SqlTypeName.ANY; - // This field does not exist in our field list add it + // This field does not exist in our field list; add it RelDataTypeField newField = new RelDataTypeFieldImpl( fieldName, fields.size(), http://git-wip-us.apache.org/repos/asf/calcite/blob/c3f2705f/core/src/main/java/org/apache/calcite/sql/SqlUnnestOperator.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlUnnestOperator.java b/core/src/main/java/org/apache/calcite/sql/SqlUnnestOperator.java index fb01b19..6825bc1 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlUnnestOperator.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlUnnestOperator.java @@ -16,7 +16,7 @@ */ package org.apache.calcite.sql; - +import org.apache.calcite.rel.type.DynamicRecordType; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; import org.apache.calcite.sql.type.ArraySqlType; @@ -27,7 +27,6 @@ import org.apache.calcite.sql.type.SqlOperandCountRanges; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.util.Util; - /** * The <code>UNNEST</code> operator. */ @@ -61,13 +60,24 @@ public class SqlUnnestOperator extends SqlFunctionalOperator { //~ Methods ---------------------------------------------------------------- @Override public RelDataType inferReturnType(SqlOperatorBinding opBinding) { - final RelDataTypeFactory.Builder builder = - opBinding.getTypeFactory().builder(); + final RelDataTypeFactory typeFactory = opBinding.getTypeFactory(); + final RelDataTypeFactory.Builder builder = typeFactory.builder(); for (Integer operand : Util.range(opBinding.getOperandCount())) { RelDataType type = opBinding.getOperandType(operand); + if (type.getSqlTypeName() == SqlTypeName.ANY) { + // When there is one operand with unknown type (ANY), the return type + // is dynamic star + return builder + .add(DynamicRecordType.DYNAMIC_STAR_PREFIX, + SqlTypeName.DYNAMIC_STAR) + .nullable(true) + .buildDynamic(); + } + if (type.isStruct()) { type = type.getFieldList().get(0).getType(); } + assert type instanceof ArraySqlType || type instanceof MultisetSqlType || type instanceof MapSqlType; if (type instanceof MapSqlType) { http://git-wip-us.apache.org/repos/asf/calcite/blob/c3f2705f/core/src/main/java/org/apache/calcite/sql/parser/SqlParser.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/parser/SqlParser.java b/core/src/main/java/org/apache/calcite/sql/parser/SqlParser.java index 626269a..a343a39 100644 --- a/core/src/main/java/org/apache/calcite/sql/parser/SqlParser.java +++ b/core/src/main/java/org/apache/calcite/sql/parser/SqlParser.java @@ -215,8 +215,6 @@ public class SqlParser { private int identifierMaxLength = DEFAULT_IDENTIFIER_MAX_LENGTH; private boolean caseSensitive = Lex.ORACLE.caseSensitive; private SqlConformance conformance = SqlConformanceEnum.DEFAULT; - private boolean allowBangEqual = - SqlConformanceEnum.DEFAULT.isBangEqualAllowed(); private SqlParserImplFactory parserFactory = SqlParserImpl.FACTORY; private ConfigBuilder() {} @@ -228,7 +226,6 @@ public class SqlParser { this.quoting = config.quoting(); this.identifierMaxLength = config.identifierMaxLength(); this.conformance = config.conformance(); - this.allowBangEqual = config.allowBangEqual(); this.parserFactory = config.parserFactory(); return this; } @@ -274,7 +271,6 @@ public class SqlParser { public ConfigBuilder setConformance(SqlConformance conformance) { this.conformance = conformance; - this.allowBangEqual = conformance.isBangEqualAllowed(); return this; } http://git-wip-us.apache.org/repos/asf/calcite/blob/c3f2705f/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java index 0aac278..19cdbf9 100644 --- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java +++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java @@ -962,6 +962,15 @@ public abstract class SqlTypeUtil { field.getType().getComponentType(), null), -1); + if (field.getType() instanceof ArraySqlType) { + flattenedCollectionType = + typeFactory.createArrayType( + flattenRecordType( + typeFactory, + field.getType().getComponentType(), + null), + -1); + } field = new RelDataTypeFieldImpl( field.getName(), http://git-wip-us.apache.org/repos/asf/calcite/blob/c3f2705f/core/src/main/java/org/apache/calcite/sql/validate/UnnestNamespace.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/UnnestNamespace.java b/core/src/main/java/org/apache/calcite/sql/validate/UnnestNamespace.java index 3eee0a8..2f6c918 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/UnnestNamespace.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/UnnestNamespace.java @@ -18,9 +18,10 @@ package org.apache.calcite.sql.validate; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlIdentifier; import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.SqlUnnestOperator; -import org.apache.calcite.sql.type.MultisetSqlType; + /** * Namespace for UNNEST. @@ -47,6 +48,18 @@ class UnnestNamespace extends AbstractNamespace { //~ Methods ---------------------------------------------------------------- + @Override public SqlValidatorTable getTable() { + final SqlNode toUnnest = unnest.operand(0); + if (toUnnest instanceof SqlIdentifier) { + // When operand of SqlIdentifier type does not have struct, fake a table + // for UnnestNamespace + final SqlIdentifier id = (SqlIdentifier) toUnnest; + final SqlQualified qualified = this.scope.fullyQualify(id); + return qualified.namespace.getTable(); + } + return null; + } + protected RelDataType validateImpl(RelDataType targetRowType) { // Validate the call and its arguments, and infer the return type. validator.validateCall(unnest, scope); @@ -56,22 +69,6 @@ class UnnestNamespace extends AbstractNamespace { return toStruct(type, unnest); } - /** - * Returns the type of the argument to UNNEST. - */ - private RelDataType inferReturnType() { - final SqlNode operand = unnest.operand(0); - RelDataType type = validator.getValidatedNodeType(operand); - - // If sub-query, pick out first column. - // TODO: Handle this using usual sub-select validation. - if (type.isStruct()) { - type = type.getFieldList().get(0).getType(); - } - MultisetSqlType t = (MultisetSqlType) type; - return t.getComponentType(); - } - public SqlNode getNode() { return unnest; } http://git-wip-us.apache.org/repos/asf/calcite/blob/c3f2705f/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java index a888a23..844313e 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -67,7 +67,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { /** Sets the SQL statement for a test. */ public final Sql sql(String sql) { return new Sql(sql, true, true, tester, false, - SqlToRelConverter.Config.DEFAULT, SqlConformanceEnum.DEFAULT); + SqlToRelConverter.Config.DEFAULT, tester.getConformance()); } protected final void check( @@ -1102,6 +1102,27 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql).ok(); } + @Test public void testUnnestArrayAggPlan() { + final String sql = "select d.deptno, e2.empno_avg\n" + + "from dept_nested as d outer apply\n" + + " (select avg(e.empno) as empno_avg from UNNEST(d.employees) as e) e2"; + sql(sql).conformance(SqlConformanceEnum.LENIENT).ok(); + } + + @Test public void testUnnestArrayPlan() { + final String sql = "select d.deptno, e2.empno\n" + + "from dept_nested as d,\n" + + " UNNEST(d.employees) e2"; + sql(sql).with(getExtendedTester()).ok(); + } + + @Test public void testUnnestArrayPlanAs() { + final String sql = "select d.deptno, e2.empno\n" + + "from dept_nested as d,\n" + + " UNNEST(d.employees) as e2(empno, y, z)"; + sql(sql).with(getExtendedTester()).ok(); + } + @Test public void testArrayOfRecord() { sql("select employees[1].detail.skills[2+3].desc from dept_nested").ok(); } @@ -2460,6 +2481,22 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql).with(getTesterWithDynamicTable()).ok(); } + @Test public void testDynamicNestedColumn() { + final String sql = "select t3.fake_q1['fake_col2'] as fake2\n" + + "from (\n" + + " select t2.fake_col as fake_q1\n" + + " from SALES.CUSTOMER as t2) as t3"; + sql(sql).with(getTesterWithDynamicTable()).ok(); + } + + @Test public void testDynamicSchemaUnnest() { + final String sql3 = "select t1.c_nationkey, t3.fake_col3\n" + + "from SALES.CUSTOMER as t1,\n" + + "lateral (select t2.fake_col2 as fake_col3\n" + + " from unnest(t1.fake_col) as t2) as t3"; + sql(sql3).with(getTesterWithDynamicTable()).ok(); + } + /** * Test case for Dynamic Table / Dynamic Star support * <a href="https://issues.apache.org/jira/browse/CALCITE-1150";>[CALCITE-1150]</a> @@ -2597,6 +2634,7 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { regionTable.addColumn("R_NAME", varcharType); regionTable.addColumn("R_COMMENT", varcharType); registerTable(regionTable); + return this; } // CHECKSTYLE: IGNORE 1 http://git-wip-us.apache.org/repos/asf/calcite/blob/c3f2705f/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java b/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java index 1b9581d..5d627b8 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java @@ -98,7 +98,8 @@ public abstract class SqlToRelTestBase { protected Tester createTester() { return new TesterImpl(getDiffRepos(), false, false, true, false, - null, null); + null, null, SqlToRelConverter.Config.DEFAULT, + SqlConformanceEnum.DEFAULT, Contexts.empty()); } /** @@ -636,7 +637,9 @@ public abstract class SqlToRelTestBase { } public SqlNode parseQuery(String sql) throws Exception { - SqlParser parser = SqlParser.create(sql); + final SqlParser.Config config = + SqlParser.configBuilder().setConformance(getConformance()).build(); + SqlParser parser = SqlParser.create(sql, config); return parser.parseQuery(); } http://git-wip-us.apache.org/repos/asf/calcite/blob/c3f2705f/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml ---------------------------------------------------------------------- diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml index 32da81e..c55ad57 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -723,6 +723,40 @@ LogicalProject(EXPR$0=[CHAR_LENGTH('foo')]) <![CDATA[values (character_length('foo'))]]> </Resource> </TestCase> + <TestCase name="testDynamicNestedColumn"> + <Resource name="sql"> + <![CDATA[select t3.fake_q1['fake_col2'] as fake2 +from ( + select t2.fake_col as fake_q1 + from SALES.CUSTOMER as t2) as t3]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(FAKE2=[ITEM($0, 'fake_col2')]) + LogicalProject(FAKE_Q1=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, CUSTOMER]]) +]]> + </Resource> + </TestCase> + <TestCase name="testDynamicSchemaUnnest"> + <Resource name="sql"> + <![CDATA[select t1.c_nationkey, t3.fake_col3 +from SALES.CUSTOMER as t1, +lateral (select t2.fake_col2 as fake_col3 + from unnest(t1.fake_col) as t2) as t3]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(C_NATIONKEY=[$1], FAKE_COL3=[$2]) + LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}]) + LogicalTableScan(table=[[CATALOG, SALES, CUSTOMER]]) + LogicalProject(FAKE_COL3=[ITEM($0, 'FAKE_COL2')]) + Uncollect + LogicalProject(FAKE_COL=[$cor0.FAKE_COL]) + LogicalValues(tuples=[[{ 0 }]]) +]]> + </Resource> + </TestCase> <TestCase name="testOverAvg"> <Resource name="plan"> <![CDATA[ @@ -4458,6 +4492,63 @@ LogicalProject(DEPTNO=[$0]) ]]> </Resource> </TestCase> + <TestCase name="testUnnestArrayAggPlan"> + <Resource name="sql"> + <![CDATA[select d.deptno, e2.empno_avg +from dept_nested as d outer apply + (select avg(e.empno) as empno_avg from UNNEST(d.employees) as e) e2]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(DEPTNO=[$0], EMPNO_AVG=[$7]) + LogicalProject(DEPTNO=[$0], NAME=[$1], TYPE=[$2], DESC=[$3], A=[$4], B=[$5], EMPLOYEES=[$6], EMPNO_AVG=[$7]) + LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{6}]) + LogicalProject(DEPTNO=[$0], NAME=[$1], TYPE=[$2.TYPE], DESC=[$2.DESC], A=[$2.OTHERS.A], B=[$2.OTHERS.B], EMPLOYEES=[$3]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]]) + LogicalAggregate(group=[{}], EMPNO_AVG=[AVG($0)]) + LogicalProject(EMPNO=[$0]) + Uncollect + LogicalProject(EMPLOYEES=[$cor0.EMPLOYEES_6]) + LogicalValues(tuples=[[{ 0 }]]) +]]> + </Resource> + </TestCase> + <TestCase name="testUnnestArrayPlan"> + <Resource name="sql"> + <![CDATA[select d.deptno, e2.empno +from dept_nested as d, + UNNEST(d.employees) e2]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(DEPTNO=[$0], EMPNO=[$7]) + LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{6}]) + LogicalProject(DEPTNO=[$0], NAME=[$1], TYPE=[$2.TYPE], DESC=[$2.DESC], A=[$2.OTHERS.A], B=[$2.OTHERS.B], EMPLOYEES=[$3]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]]) + Uncollect + LogicalProject(EMPLOYEES=[$cor0.EMPLOYEES_6]) + LogicalValues(tuples=[[{ 0 }]]) +]]> + </Resource> + </TestCase> + <TestCase name="testUnnestArrayPlanAs"> + <Resource name="sql"> + <![CDATA[select d.deptno, e2.empno +from dept_nested as d, + UNNEST(d.employees) as e2(empno, y, z)]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(DEPTNO=[$0], EMPNO=[$7]) + LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{6}]) + LogicalProject(DEPTNO=[$0], NAME=[$1], TYPE=[$2.TYPE], DESC=[$2.DESC], A=[$2.OTHERS.A], B=[$2.OTHERS.B], EMPLOYEES=[$3]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]]) + Uncollect + LogicalProject(EMPLOYEES=[$cor0.EMPLOYEES_6]) + LogicalValues(tuples=[[{ 0 }]]) +]]> + </Resource> + </TestCase> <TestCase name="testWithInsideWhereExistsDecorrelateRex"> <Resource name="sql"> <![CDATA[select * from emp @@ -5072,7 +5163,7 @@ LogicalProject(R_REGIONKEY=[$0], R_NAME=[$1], R_COMMENT=[$2]) <![CDATA[ LogicalProject(**=[$0]) LogicalSort(sort0=[$1], dir0=[ASC]) - LogicalProject(**=[$0], N_NATIONKEY=[$1]) + LogicalProject(**=[$0], EXPR$1=[ITEM($0, 'N_NATIONKEY')]) LogicalTableScan(table=[[CATALOG, SALES, NATION]]) ]]> </Resource>
