[jira] [Created] (CALCITE-3342) sqltorelconverter adds extra fields with order by

["Jess Balint (Jira)" <ji...@apache.org>]

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)