Jess Balint created CALCITE-3342: ------------------------------------ Summary: sqltorelconverter adds extra fields with order by Key: CALCITE-3342 URL: https://issues.apache.org/jira/browse/CALCITE-3342 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.20.0 Reporter: Jess Balint
This is the input query: {code:sql} select concat(i.CONSTRAINT_SCHEMA, '_', i.CONSTRAINT_NAME) as INDEX_NAME, ii.COLUMN_NAME, ii.ORDINAL_POSITION, case when i.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'Y' else 'N' end as PRIMARY_KEY from INFORMATION_SCHEMA.table_constraints i inner join INFORMATION_SCHEMA.key_column_usage ii on i.CONSTRAINT_SCHEMA = ii.CONSTRAINT_SCHEMA and i.CONSTRAINT_NAME = ii.CONSTRAINT_NAME and i.TABLE_SCHEMA = ii.TABLE_SCHEMA and i.TABLE_NAME = ii.TABLE_NAME where i.TABLE_SCHEMA = 'serf' and i.TABLE_NAME = 'aml_large_transfers' and i.CONSTRAINT_TYPE in ('PRIMARY KEY', 'UNIQUE') and ii.POSITION_IN_UNIQUE_CONSTRAINT is null order by concat(i.CONSTRAINT_SCHEMA, '_', i.CONSTRAINT_NAME), ii.TABLE_SCHEMA, ii.TABLE_NAME, ii.ORDINAL_POSITION {code} and SqlToRelConverter adds fields for the sorting but doesnt project them out: {noformat} LogicalSort(sort0=[$0], sort1=[$4], sort2=[$5], sort3=[$2], dir0=[ASC], dir1=[ASC], dir2=[ASC], dir3=[ASC]) LogicalProject(INDEX_NAME=[||(||($1, '_'), $2)], COLUMN_NAME=[$12], ORDINAL_POSITION=[$13], PRIMARY_KEY=[CASE(=($5, 'PRIMARY KEY'), 'Y', 'N')], TABLE_SCHEMA=[$10], TABLE_NAME=[$11]) LogicalFilter(condition=[AND(=($3, 'serf'), =($4, 'aml_large_transfers'), OR(=($5, 'PRIMARY KEY'), =($5, 'UNIQUE')), IS NULL($14))]) LogicalJoin(condition=[AND(=($1, $7), =($2, $8), =($3, $10), =($4, $11))], joinType=[inner]) LogicalTableScan(table=[[INFORMATION_SCHEMA, TABLE_CONSTRAINTS]]) LogicalTableScan(table=[[INFORMATION_SCHEMA, KEY_COLUMN_USAGE]]) {noformat} -- This message was sent by Atlassian Jira (v8.3.2#803003)