[ https://issues.apache.org/jira/browse/FLINK-36415?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sergey Nuyanzin updated FLINK-36415: ------------------------------------ Description: For instance if there is a table containing deeply nested array and rows like this {code:sql} `chart` row<`result` array<row<`meta` row<`symbol` string not null> not null> not null> not null> {code} then {{SELECT}} of this column like {code:sql} SELECT `chart`.`result`[1].`meta`.`symbol` FROM ... {code} will have the optimized plan like {noformat} LogicalProject(EXPR$0=[CAST(CAST(CAST(CAST(ITEM($0.result, 1).meta):RecordType:peek_no_expand(VARCHAR(2147483647) CHARACTER SET "UTF-16LE" NOT NULL symbol)):RecordType:peek_no_expand(VARCHAR(2147483647) CHARACTER SET "UTF-16LE" NOT NULL symbol).symbol):VARCHAR(2147483647) CHARACTER SET "UTF-16LE"):VARCHAR(2147483647) CHARACTER SET "UTF-16LE"]) +- LogicalTableScan(table=[[default_catalog, default_database, ItemTable, project=[chart], metadata=[]]]) {noformat} while it should contain only 2 casts (one to cast to Varchar and another for RecordType:peek_no_expand), to others are useless was: For instance if there is a table containing deeply nested array and rows like this {code:sql} `chart` row<`result` array<row<`meta` row<`symbol` string not null> not null> not null> not null> {code} the optimized plan will look like {noformat} LogicalProject(EXPR$0=[CAST(CAST(CAST(CAST(ITEM($0.result, 1).meta):RecordType:peek_no_expand(VARCHAR(2147483647) CHARACTER SET "UTF-16LE" NOT NULL symbol)):RecordType:peek_no_expand(VARCHAR(2147483647) CHARACTER SET "UTF-16LE" NOT NULL symbol).symbol):VARCHAR(2147483647) CHARACTER SET "UTF-16LE"):VARCHAR(2147483647) CHARACTER SET "UTF-16LE"]) +- LogicalTableScan(table=[[default_catalog, default_database, ItemTable, project=[chart], metadata=[]]]) {noformat} while it should contain only 2 casts (one to cast to Varchar and another for RecordType:peek_no_expand), to others are useless > ProjectWatermarkAssignerTransposeRule can generate extra casts which do not > make sense > -------------------------------------------------------------------------------------- > > Key: FLINK-36415 > URL: https://issues.apache.org/jira/browse/FLINK-36415 > Project: Flink > Issue Type: Bug > Components: Table SQL / Planner > Reporter: Sergey Nuyanzin > Assignee: Sergey Nuyanzin > Priority: Major > > For instance > if there is a table containing deeply nested array and rows like this > {code:sql} > `chart` row<`result` array<row<`meta` row<`symbol` string not null> not null> > not null> not null> > {code} > then {{SELECT}} of this column like > {code:sql} > SELECT `chart`.`result`[1].`meta`.`symbol` FROM ... > {code} > will have the optimized plan like > {noformat} > LogicalProject(EXPR$0=[CAST(CAST(CAST(CAST(ITEM($0.result, > 1).meta):RecordType:peek_no_expand(VARCHAR(2147483647) CHARACTER SET > "UTF-16LE" NOT NULL symbol)):RecordType:peek_no_expand(VARCHAR(2147483647) > CHARACTER SET "UTF-16LE" NOT NULL symbol).symbol):VARCHAR(2147483647) > CHARACTER SET "UTF-16LE"):VARCHAR(2147483647) CHARACTER SET "UTF-16LE"]) > +- LogicalTableScan(table=[[default_catalog, default_database, ItemTable, > project=[chart], metadata=[]]]) > {noformat} > while it should contain only 2 casts (one to cast to Varchar and another for > RecordType:peek_no_expand), to others are useless -- This message was sent by Atlassian Jira (v8.20.10#820010)