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
