[jira] [Updated] (CALCITE-3342) SqlToRelConverter adds extra fields with order by

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

     [ https://issues.apache.org/jira/browse/CALCITE-3342?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jess Balint updated CALCITE-3342:
---------------------------------
    Summary: SqlToRelConverter adds extra fields with order by  (was: sqltorelconverter adds extra fields with order by)

> 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)