You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@calcite.apache.org by JiaTao Tao <ta...@apache.org> on 2020/03/04 11:43:55 UTC

SQL has order by, there will be one more column(the sort column) in result plan

SQL;
```
SELECT u.ID, u.NAME
FROM USERS u
ORDER BY u.AGE
```

PLAN:
```
LogicalSort(sort0=[$2], dir0=[ASC])
  LogicalProject(ID=[$0], NAME=[$1], AGE=[$2])
    EnumerableTableScan(table=[[RECR, USERS]])
```
Plan's row type: RecordType(INTEGER ID, VARCHAR NAME, INTEGER AGE)

But the SQL only project ID and NAME, there should be a LogicalProject
(ID, NAME) on LogicalSort. Is this by design? And what about this extra
column?



Regards!

Aron Tao

Re: SQL has order by, there will be one more column(the sort column) in result plan

Posted by JiaTao Tao <ta...@apache.org>.

Seems `root.project()` works, thanks a lot!


Regards!

Aron Tao


JiaTao Tao <ta...@apache.org> 于2020年3月5日周四 上午11:05写道：

> Thanks, Julian
>
> I can see the fields in RelRoot, and I'm a little confused, I want to
> use RelVisitor to visit the RelNode, and here comes the RelRoot, any advice
> to deal with RelRoot when using RelVisitor?
>
>
> Regards!
>
> Aron Tao
>
>
> Julian Hyde <jh...@apache.org> 于2020年3月5日周四 上午2:03写道：
>
>> There is a class RelRoot that wraps the top-most RelNode, and projects
>> away columns that are only used for sorting. Read through
>> https://issues.apache.org/jira/browse/CALCITE-2999 <
>> https://issues.apache.org/jira/browse/CALCITE-2999>, where someone had
>> the same misunderstanding that you have.
>>
>>
>> > On Mar 4, 2020, at 3:43 AM, JiaTao Tao <ta...@apache.org> wrote:
>> >
>> > SQL;
>> > ```
>> > SELECT u.ID, u.NAME
>> > FROM USERS u
>> > ORDER BY u.AGE
>> > ```
>> >
>> > PLAN:
>> > ```
>> > LogicalSort(sort0=[$2], dir0=[ASC])
>> >  LogicalProject(ID=[$0], NAME=[$1], AGE=[$2])
>> >    EnumerableTableScan(table=[[RECR, USERS]])
>> > ```
>> > Plan's row type: RecordType(INTEGER ID, VARCHAR NAME, INTEGER AGE)
>> >
>> > But the SQL only project ID and NAME, there should be a LogicalProject
>> > (ID, NAME) on LogicalSort. Is this by design? And what about this extra
>> > column?
>> >
>> >
>> >
>> > Regards!
>> >
>> > Aron Tao
>>
>>

Re: SQL has order by, there will be one more column(the sort column) in result plan

Posted by JiaTao Tao <ta...@apache.org>.

Thanks, Julian

I can see the fields in RelRoot, and I'm a little confused, I want to
use RelVisitor to visit the RelNode, and here comes the RelRoot, any advice
to deal with RelRoot when using RelVisitor?


Regards!

Aron Tao


Julian Hyde <jh...@apache.org> 于2020年3月5日周四 上午2:03写道：

> There is a class RelRoot that wraps the top-most RelNode, and projects
> away columns that are only used for sorting. Read through
> https://issues.apache.org/jira/browse/CALCITE-2999 <
> https://issues.apache.org/jira/browse/CALCITE-2999>, where someone had
> the same misunderstanding that you have.
>
>
> > On Mar 4, 2020, at 3:43 AM, JiaTao Tao <ta...@apache.org> wrote:
> >
> > SQL;
> > ```
> > SELECT u.ID, u.NAME
> > FROM USERS u
> > ORDER BY u.AGE
> > ```
> >
> > PLAN:
> > ```
> > LogicalSort(sort0=[$2], dir0=[ASC])
> >  LogicalProject(ID=[$0], NAME=[$1], AGE=[$2])
> >    EnumerableTableScan(table=[[RECR, USERS]])
> > ```
> > Plan's row type: RecordType(INTEGER ID, VARCHAR NAME, INTEGER AGE)
> >
> > But the SQL only project ID and NAME, there should be a LogicalProject
> > (ID, NAME) on LogicalSort. Is this by design? And what about this extra
> > column?
> >
> >
> >
> > Regards!
> >
> > Aron Tao
>
>

Re: SQL has order by, there will be one more column(the sort column) in result plan

Posted by Julian Hyde <jh...@apache.org>.

There is a class RelRoot that wraps the top-most RelNode, and projects away columns that are only used for sorting. Read through https://issues.apache.org/jira/browse/CALCITE-2999 <https://issues.apache.org/jira/browse/CALCITE-2999>, where someone had the same misunderstanding that you have.

> On Mar 4, 2020, at 3:43 AM, JiaTao Tao <ta...@apache.org> wrote:
> 
> SQL;
> ```
> SELECT u.ID, u.NAME
> FROM USERS u
> ORDER BY u.AGE
> ```
> 
> PLAN:
> ```
> LogicalSort(sort0=[$2], dir0=[ASC])
>  LogicalProject(ID=[$0], NAME=[$1], AGE=[$2])
>    EnumerableTableScan(table=[[RECR, USERS]])
> ```
> Plan's row type: RecordType(INTEGER ID, VARCHAR NAME, INTEGER AGE)
> 
> But the SQL only project ID and NAME, there should be a LogicalProject
> (ID, NAME) on LogicalSort. Is this by design? And what about this extra
> column?
> 
> 
> 
> Regards!
> 
> Aron Tao