[ https://issues.apache.org/jira/browse/CALCITE-3342?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16928936#comment-16928936 ]
Julian Hyde commented on CALCITE-3342: -------------------------------------- Yes. See CALCITE-819 for why we needed to add those fields, and why we added {{RelRoot}} to project them away before the client sees them. > 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 > Priority: Major > > 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)