Actually, the cause for my previous findings is: for the first case,
SqlToRelConverterTest introduce another LogicalProject (RelRoot.project)
after applying the SqlToRelConverter to remove fields that are not needed.
But this function does not work with Record type and flattened fields. It
simply projects the first several fields from input index-wise, and does
not take into account the flattening behavior. The second case does not
trigger the extra project because it's trivial.
For your case, I tried below:
MockTable deptNestedTable =
MockTable.create(this, salesSchema, "DEPT_NESTED", false, 4);
deptNestedTable.addColumn("DEPTNO", f.intType, true);
deptNestedTable.addColumn("NAME", f.varchar10Type);
deptNestedTable.addColumn("SKILLRECORD", f.skillRecordType);
deptNestedTable.addColumn("EMPLOYEES", f.empListType);
registerTable(deptNestedTable);
Run the following test:
@Test public void testArrayOfRecord() {
sql("select employees[1] from dept_nested").ok();
}
I am actually getting the following error when run:
java.lang.AssertionError: type mismatch:
ref:
RecordType(INTEGER NOT NULL EMPNO, VARCHAR(10) CHARACTER SET "ISO-8859-1"
COLLATE "ISO-8859-1$en_US$primary" NOT NULL ENAME, RecordType(VARCHAR(10)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL
TYPE, VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary" NOT NULL DESC) NOT NULL ARRAY NOT NULL SKILLS)
NOT NULL ARRAY NOT NULL
input:
VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"
NOT NULL
at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31)
at org.apache.calcite.plan.RelOptUtil.eq(RelOptUtil.java:1838)
at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:125)
at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:57)
at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:112)
at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:140)
at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:57)
at org.apache.calcite.rex.RexCall.accept(RexCall.java:107)
at
org.apache.calcite.rex.RexVisitorImpl.visitFieldAccess(RexVisitorImpl.java:98)
at org.apache.calcite.rex.RexChecker.visitFieldAccess(RexChecker.java:149)
at org.apache.calcite.rex.RexChecker.visitFieldAccess(RexChecker.java:57)
at org.apache.calcite.rex.RexFieldAccess.accept(RexFieldAccess.java:81)
at org.apache.calcite.rel.core.Project.isValid(Project.java:187)
at org.apache.calcite.rel.core.Project.<init>(Project.java:84)
at
org.apache.calcite.rel.logical.LogicalProject.<init>(LogicalProject.java:65)
at
org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:120)
at
org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:103)
at
org.apache.calcite.rel.core.RelFactories$ProjectFactoryImpl.createProject(RelFactories.java:127)
at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1064)
at org.apache.calcite.plan.RelOptUtil.createProject(RelOptUtil.java:2956)
at org.apache.calcite.plan.RelOptUtil.createProject(RelOptUtil.java:2873)
at
org.apache.calcite.sql2rel.RelStructuredTypeFlattener.rewriteRel(RelStructuredTypeFlattener.java:477)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.apache.calcite.util.ReflectUtil.invokeVisitorInternal(ReflectUtil.java:257)
at org.apache.calcite.util.ReflectUtil.invokeVisitor(ReflectUtil.java:214)
at org.apache.calcite.util.ReflectUtil$1.invokeVisitor(ReflectUtil.java:464)
at
org.apache.calcite.sql2rel.RelStructuredTypeFlattener$RewriteRelVisitor.visit(RelStructuredTypeFlattener.java:721)
at
org.apache.calcite.sql2rel.RelStructuredTypeFlattener.rewrite(RelStructuredTypeFlattener.java:177)
at
org.apache.calcite.sql2rel.SqlToRelConverter.flattenTypes(SqlToRelConverter.java:462)
at
org.apache.calcite.test.SqlToRelTestBase$TesterImpl.convertSqlToRel(SqlToRelTestBase.java:585)
at
org.apache.calcite.test.SqlToRelTestBase$TesterImpl.assertConvertsTo(SqlToRelTestBase.java:690)
at
org.apache.calcite.test.SqlToRelConverterTest$Sql.convertsTo(SqlToRelConverterTest.java:2784)
at
org.apache.calcite.test.SqlToRelConverterTest$Sql.ok(SqlToRelConverterTest.java:2776)
at
org.apache.calcite.test.SqlToRelConverterTest.testArrayOfRecord(SqlToRelConverterTest.java:1059)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
at
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
at
com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
Shuyi
On Wed, Mar 21, 2018 at 6:09 PM, Shuyi Chen <[email protected]> wrote:
> Thanks a lot, Anton. This seems to be a bug in Calcite. When the statement
> involving record types, sql validation seems to work, but the rel plan
> generated might be wrong. I can also reproduce your case:
>
> MockTable deptNestedTable =
> MockTable.create(this, salesSchema, "DEPT_NESTED", false, 4);
> deptNestedTable.addColumn("DEPTNO", f.intType, true);
> deptNestedTable.addColumn("NAME", f.varchar10Type);
> deptNestedTable.addColumn("SKILLRECORD", f.skillRecordType);
> deptNestedTable.addColumn("EMPLOYEES", f.empListType);
> registerTable(deptNestedTable);
>
> Run the following test:
>
> @Test public void testArrayOfRecord() {
> sql("select skillrecord, employees from dept_nested").ok();
> }
>
> yield:
> LogicalProject(SKILLRECORD=[$0], EMPLOYEES=[$1])
> LogicalProject(SKILLRECORD=[$2], SKILLRECORD1=[$3], EMPLOYEES=[$4])
> LogicalProject(DEPTNO=[$0], NAME=[$1], TYPE=[$2.TYPE], DESC=[$2.DESC],
> EMPLOYEES=[$3])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
>
> Sometimes, it works:
>
> @Test public void testArrayOfRecord() {
> sql("select name, employees from dept_nested").ok();
> }
>
> yield:
>
> LogicalProject(NAME=[$1], EMPLOYEES=[$4])
> LogicalProject(DEPTNO=[$0], NAME=[$1], TYPE=[$2.TYPE], DESC=[$2.DESC],
> EMPLOYEES=[$3])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
>
> I can take a deeper look.
>
> Shuyi
>
> On Wed, Mar 21, 2018 at 11:06 AM, Anton Kedin <[email protected]>
> wrote:
>
>> Hi,
>>
>> I have an issue I am not sure how to handle, would appreciate any
>> pointers.
>>
>> I have a table with row type:
>> RecordType(
>> INTEGER orderId,
>> RecordType(VARCHAR name, INTEGER personId)
>> person,
>> RecordType(VARCHAR sku, INTEGER price, VARCHAR currency, VARCHAR ARRAY
>> tags)
>> ARRAY items
>> )
>>
>> With this row type I am trying to model a JSON object which looks like
>> this:
>> { "orderId" : 1,
>> "person" : { "name" : "john", "personId" : 12, },
>> "items": [
>> { "sku" : "aaa01", "price" : 12, "currency" : "USD", "tags" : ["blue",
>> "book"] }
>> ]}
>>
>> When selecting the whole items array I get the following plan:
>> SELECT items FROM PCOLLECTION
>>
>> LogicalProject(items=[$3])
>> LogicalProject(orderId=[$0], name=[$1.name], personId=[$1.personId],
>> items
>> =[$2])
>> LogicalTableScan(table=[[PCOLLECTION]])
>>
>> Which looks correct and it works. One thing to note here is that Calcite
>> flattens the person row, and makes the input ref for the items field as
>> $3,
>> as expected.
>>
>> But when I want to get a specific element from that array I get the
>> following:
>> SELECT items[0] FROM PCOLLECTION
>>
>> LogicalProject(EXPR$0$0=[ITEM($2, 0).sku], EXPR$0$1=[ITEM($2, 0).price],
>> EXPR$0$2=[ITEM($2, 0).currency], EXPR$0$3=[ITEM($2, 0).tags])
>> LogicalProject(orderId=[$0], name=[$1.name], personId=[$1.personId],
>> items
>> =[$2])
>> LogicalTableScan(table=[[PCOLLECTION]])
>>
>> The first project looks the same. Flattened person row, items array, all
>> looks similar to the above.
>> But the outer project calls ITEM($2, i). I would expect it to be
>> ITEM($3, i) instead,
>> to adjust for the flattened person row, but it keeps the index as $2,
>> which
>> would have been the correct index if the row was not flattened, but it
>> should be $3 for flattened row, similar to the previous example.
>>
>> Is there something I am missing or is it a bug and Calcite should adjust
>> the input ref index to account for flattened rows in this case as well?
>>
>> Thank you,
>> Anton
>>
>
>
>
> --
> "So you have to trust that the dots will somehow connect in your future."
>
--
"So you have to trust that the dots will somehow connect in your future."
