You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Hrudaya Reddy <hr...@csod.com.INVALID> on 2020/08/04 04:51:54 UTC
Implementation of DISTINCT and ORDER BY
Hi all,
I am trying to generate the following SQL query but I am facing some difficulties in finding the right syntax to implement 'DISTINCT' and 'ORDER BY'
SELECT DISTINCT name_first, name_last FROM users ORDER BY name_last DESC
I would really appreciate it if you could guide me with the correct usage.
Thanks in advance.
Regards,
Hrudaya
This message, together with any attachments, is intended only for the use of the individual or entity to which it is addressed and may contain confidential and/or privileged information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this message, or any attachment, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
RE: Re: Re: Implementation of DISTINCT and ORDER BY
Posted by Hrudaya Reddy <hr...@csod.com.INVALID>.
Thanks for your input Julian and Rui.
Regards,
Hrudaya
-----Original Message-----
From: Julian Hyde <jh...@apache.org>
Sent: Thursday, August 6, 2020 10:03 AM
To: dev@calcite.apache.org
Subject: [EXTERNAL] Re: Re: Implementation of DISTINCT and ORDER BY
The CASE is to make null values collate first or last. I guess the target DB does not support NULLS LAST (or whatever) syntax. I think the behavior would be the same whether or not you have DISTINCT.
On Tue, Aug 4, 2020 at 4:42 PM Rui Wang <am...@apache.org> wrote:
>
> I think the RelNode you build by RelBuilder is equivalent to SELECT
> DISTINCT FROM ORDER BY, except that the generated ORDER BY condition
> is different.
>
> -Rui
>
> On Tue, Aug 4, 2020 at 3:40 PM Hrudaya Reddy <hr...@csod.com.invalid>
> wrote:
>
> > Thanks a lot for your input Rui.
> >
> > This is how I am implementing it.
> >
> > RelNode relNode = builder
> > .scan("users")
> >
> > .project(builder.field("name_first"),
> > builder.field("name_last"))
> > .distinct()
> >
> > .sort(builder.desc(builder.field("name_last")))
> > .build(); and the query generated is
> >
> > SELECT [name_first], [name_last]
> > FROM [users]
> > GROUP BY [name_first], [name_last]
> > ORDER BY CASE WHEN [name_last] IS NULL THEN 0 ELSE 1 END,
> > [name_last] DESC
> >
> > So, just wanted to make sure that this is the right approach.
> >
> > Regards,
> > Hrudaya
> >
> > -----Original Message-----
> > From: Rui Wang <am...@apache.org>
> > Sent: Monday, August 3, 2020 10:08 PM
> > To: dev@calcite.apache.org
> > Subject: [EXTERNAL] Re: Implementation of DISTINCT and ORDER BY
> >
> > I did a test locally and I found your example syntax is correct.
> >
> > I used "select distinct deptno, empno from emp order by empno desc"
> > and Calcite generates this plan:
> >
> > LogicalSort(sort0=[$1], dir0=[DESC])
> > LogicalAggregate(group=[{0, 1}])
> > LogicalProject(DEPTNO=[$7], EMPNO=[$0])
> > LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> >
> >
> > What error message did you get?
> >
> > -Rui
> >
> >
> > On Mon, Aug 3, 2020 at 9:52 PM Hrudaya Reddy
> > <hr...@csod.com.invalid>
> > wrote:
> > >
> > > Hi all,
> > >
> > > I am trying to generate the following SQL query but I am facing
> > > some
> > difficulties in finding the right syntax to implement 'DISTINCT' and
> > 'ORDER BY'
> > >
> > > SELECT DISTINCT name_first, name_last FROM users ORDER BY
> > > name_last DESC
> > >
> > >
> > > I would really appreciate it if you could guide me with the
> > > correct
> > usage.
> > >
> > >
> > >
> > > Thanks in advance.
> > >
> > > Regards,
> > > Hrudaya
> > >
> > > This message, together with any attachments, is intended only for
> > > the
> > use of the individual or entity to which it is addressed and may
> > contain confidential and/or privileged information. If you are not
> > the intended recipient(s), or the employee or agent responsible for
> > delivery of this message to the intended recipient(s), you are
> > hereby notified that any dissemination, distribution or copying of
> > this message, or any attachment, is strictly prohibited. If you have
> > received this message in error, please immediately notify the sender
> > and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
> >
> > --------------------------------------------------------------------
> > -- *EXTERNAL MESSAGE WARNING: This email originated from outside of
> > Cornerstone. Do not click links or open attachments unless you
> > recognize the sender and know the content is safe. Please see this
> > wiki for more information on email safety:
> > https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
> >
----------------------------------------------------------------------
*EXTERNAL MESSAGE WARNING: This email originated from outside of Cornerstone. Do not click links or open attachments unless you recognize the sender and know the content is safe. Please see this wiki for more information on email safety: https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
This message, together with any attachments, is intended only for the use of the individual or entity to which it is addressed and may contain confidential and/or privileged information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this message, or any attachment, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
Re: Re: Implementation of DISTINCT and ORDER BY
Posted by Julian Hyde <jh...@apache.org>.
The CASE is to make null values collate first or last. I guess the
target DB does not support NULLS LAST (or whatever) syntax. I think
the behavior would be the same whether or not you have DISTINCT.
On Tue, Aug 4, 2020 at 4:42 PM Rui Wang <am...@apache.org> wrote:
>
> I think the RelNode you build by RelBuilder is equivalent to SELECT
> DISTINCT FROM ORDER BY, except that the generated ORDER BY condition is
> different.
>
> -Rui
>
> On Tue, Aug 4, 2020 at 3:40 PM Hrudaya Reddy <hr...@csod.com.invalid>
> wrote:
>
> > Thanks a lot for your input Rui.
> >
> > This is how I am implementing it.
> >
> > RelNode relNode = builder
> > .scan("users")
> > .project(builder.field("name_first"),
> > builder.field("name_last"))
> > .distinct()
> >
> > .sort(builder.desc(builder.field("name_last")))
> > .build();
> > and the query generated is
> >
> > SELECT [name_first], [name_last]
> > FROM [users]
> > GROUP BY [name_first], [name_last]
> > ORDER BY CASE WHEN [name_last] IS NULL THEN 0 ELSE 1 END, [name_last] DESC
> >
> > So, just wanted to make sure that this is the right approach.
> >
> > Regards,
> > Hrudaya
> >
> > -----Original Message-----
> > From: Rui Wang <am...@apache.org>
> > Sent: Monday, August 3, 2020 10:08 PM
> > To: dev@calcite.apache.org
> > Subject: [EXTERNAL] Re: Implementation of DISTINCT and ORDER BY
> >
> > I did a test locally and I found your example syntax is correct.
> >
> > I used "select distinct deptno, empno from emp order by empno desc"
> > and Calcite generates this plan:
> >
> > LogicalSort(sort0=[$1], dir0=[DESC])
> > LogicalAggregate(group=[{0, 1}])
> > LogicalProject(DEPTNO=[$7], EMPNO=[$0])
> > LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> >
> >
> > What error message did you get?
> >
> > -Rui
> >
> >
> > On Mon, Aug 3, 2020 at 9:52 PM Hrudaya Reddy <hr...@csod.com.invalid>
> > wrote:
> > >
> > > Hi all,
> > >
> > > I am trying to generate the following SQL query but I am facing some
> > difficulties in finding the right syntax to implement 'DISTINCT' and 'ORDER
> > BY'
> > >
> > > SELECT DISTINCT name_first, name_last FROM users ORDER BY name_last DESC
> > >
> > >
> > > I would really appreciate it if you could guide me with the correct
> > usage.
> > >
> > >
> > >
> > > Thanks in advance.
> > >
> > > Regards,
> > > Hrudaya
> > >
> > > This message, together with any attachments, is intended only for the
> > use of the individual or entity to which it is addressed and may contain
> > confidential and/or privileged information. If you are not the intended
> > recipient(s), or the employee or agent responsible for delivery of this
> > message to the intended recipient(s), you are hereby notified that any
> > dissemination, distribution or copying of this message, or any attachment,
> > is strictly prohibited. If you have received this message in error, please
> > immediately notify the sender and delete the message, together with any
> > attachments, from your computer. Thank you for your cooperation.
> >
> > ----------------------------------------------------------------------
> > *EXTERNAL MESSAGE WARNING: This email originated from outside of
> > Cornerstone. Do not click links or open attachments unless you recognize
> > the sender and know the content is safe. Please see this wiki for more
> > information on email safety:
> > https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
> >
Re: Re: Implementation of DISTINCT and ORDER BY
Posted by Rui Wang <am...@apache.org>.
I think the RelNode you build by RelBuilder is equivalent to SELECT
DISTINCT FROM ORDER BY, except that the generated ORDER BY condition is
different.
-Rui
On Tue, Aug 4, 2020 at 3:40 PM Hrudaya Reddy <hr...@csod.com.invalid>
wrote:
> Thanks a lot for your input Rui.
>
> This is how I am implementing it.
>
> RelNode relNode = builder
> .scan("users")
> .project(builder.field("name_first"),
> builder.field("name_last"))
> .distinct()
>
> .sort(builder.desc(builder.field("name_last")))
> .build();
> and the query generated is
>
> SELECT [name_first], [name_last]
> FROM [users]
> GROUP BY [name_first], [name_last]
> ORDER BY CASE WHEN [name_last] IS NULL THEN 0 ELSE 1 END, [name_last] DESC
>
> So, just wanted to make sure that this is the right approach.
>
> Regards,
> Hrudaya
>
> -----Original Message-----
> From: Rui Wang <am...@apache.org>
> Sent: Monday, August 3, 2020 10:08 PM
> To: dev@calcite.apache.org
> Subject: [EXTERNAL] Re: Implementation of DISTINCT and ORDER BY
>
> I did a test locally and I found your example syntax is correct.
>
> I used "select distinct deptno, empno from emp order by empno desc"
> and Calcite generates this plan:
>
> LogicalSort(sort0=[$1], dir0=[DESC])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$7], EMPNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>
>
> What error message did you get?
>
> -Rui
>
>
> On Mon, Aug 3, 2020 at 9:52 PM Hrudaya Reddy <hr...@csod.com.invalid>
> wrote:
> >
> > Hi all,
> >
> > I am trying to generate the following SQL query but I am facing some
> difficulties in finding the right syntax to implement 'DISTINCT' and 'ORDER
> BY'
> >
> > SELECT DISTINCT name_first, name_last FROM users ORDER BY name_last DESC
> >
> >
> > I would really appreciate it if you could guide me with the correct
> usage.
> >
> >
> >
> > Thanks in advance.
> >
> > Regards,
> > Hrudaya
> >
> > This message, together with any attachments, is intended only for the
> use of the individual or entity to which it is addressed and may contain
> confidential and/or privileged information. If you are not the intended
> recipient(s), or the employee or agent responsible for delivery of this
> message to the intended recipient(s), you are hereby notified that any
> dissemination, distribution or copying of this message, or any attachment,
> is strictly prohibited. If you have received this message in error, please
> immediately notify the sender and delete the message, together with any
> attachments, from your computer. Thank you for your cooperation.
>
> ----------------------------------------------------------------------
> *EXTERNAL MESSAGE WARNING: This email originated from outside of
> Cornerstone. Do not click links or open attachments unless you recognize
> the sender and know the content is safe. Please see this wiki for more
> information on email safety:
> https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
>
RE: Re: Implementation of DISTINCT and ORDER BY
Posted by Hrudaya Reddy <hr...@csod.com.INVALID>.
Thanks a lot for your input Rui.
This is how I am implementing it.
RelNode relNode = builder
.scan("users")
.project(builder.field("name_first"), builder.field("name_last"))
.distinct()
.sort(builder.desc(builder.field("name_last")))
.build();
and the query generated is
SELECT [name_first], [name_last]
FROM [users]
GROUP BY [name_first], [name_last]
ORDER BY CASE WHEN [name_last] IS NULL THEN 0 ELSE 1 END, [name_last] DESC
So, just wanted to make sure that this is the right approach.
Regards,
Hrudaya
-----Original Message-----
From: Rui Wang <am...@apache.org>
Sent: Monday, August 3, 2020 10:08 PM
To: dev@calcite.apache.org
Subject: [EXTERNAL] Re: Implementation of DISTINCT and ORDER BY
I did a test locally and I found your example syntax is correct.
I used "select distinct deptno, empno from emp order by empno desc"
and Calcite generates this plan:
LogicalSort(sort0=[$1], dir0=[DESC])
LogicalAggregate(group=[{0, 1}])
LogicalProject(DEPTNO=[$7], EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
What error message did you get?
-Rui
On Mon, Aug 3, 2020 at 9:52 PM Hrudaya Reddy <hr...@csod.com.invalid> wrote:
>
> Hi all,
>
> I am trying to generate the following SQL query but I am facing some difficulties in finding the right syntax to implement 'DISTINCT' and 'ORDER BY'
>
> SELECT DISTINCT name_first, name_last FROM users ORDER BY name_last DESC
>
>
> I would really appreciate it if you could guide me with the correct usage.
>
>
>
> Thanks in advance.
>
> Regards,
> Hrudaya
>
> This message, together with any attachments, is intended only for the use of the individual or entity to which it is addressed and may contain confidential and/or privileged information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this message, or any attachment, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
----------------------------------------------------------------------
*EXTERNAL MESSAGE WARNING: This email originated from outside of Cornerstone. Do not click links or open attachments unless you recognize the sender and know the content is safe. Please see this wiki for more information on email safety: https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
Re: Implementation of DISTINCT and ORDER BY
Posted by Rui Wang <am...@apache.org>.
I did a test locally and I found your example syntax is correct.
I used "select distinct deptno, empno from emp order by empno desc"
and Calcite generates this plan:
LogicalSort(sort0=[$1], dir0=[DESC])
LogicalAggregate(group=[{0, 1}])
LogicalProject(DEPTNO=[$7], EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
What error message did you get?
-Rui
On Mon, Aug 3, 2020 at 9:52 PM Hrudaya Reddy <hr...@csod.com.invalid> wrote:
>
> Hi all,
>
> I am trying to generate the following SQL query but I am facing some difficulties in finding the right syntax to implement 'DISTINCT' and 'ORDER BY'
>
> SELECT DISTINCT name_first, name_last FROM users ORDER BY name_last DESC
>
>
> I would really appreciate it if you could guide me with the correct usage.
>
>
>
> Thanks in advance.
>
> Regards,
> Hrudaya
>
> This message, together with any attachments, is intended only for the use of the individual or entity to which it is addressed and may contain confidential and/or privileged information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this message, or any attachment, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.