You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Chathura Widanage <ch...@voltrondata.com> on 2021/12/21 10:52:20 UTC

Behavior of SINGLE_VALUE aggregate

Hi,

Could you please explain the behaviour of SINGLE_VALUE aggregate with the
equivalent SQL query?

SqlSingleValueAggFunction (Apache Calcite calcite API)
<https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/fun/SqlSingleValueAggFunction.html>

It's not clear whether the "value" in the description refers to an entire
table or a cell. Also, there are no samples publicly available for this
aggregate function.

Regards,
Chathura

Re: Behavior of SINGLE_VALUE aggregate

Posted by Chathura Widanage <ch...@voltrondata.com>.
Hi Vladimir,

Thanks for the response. Okay, I get it now. So it would be equivalent to

SELECT X.x FROM X where X.y = (SELECT Z.x FROM Z).

And this requires validating that the subquery returns just one row.
Totally make sense now. Thanks a lot, Vladimir.

Regards,
Chathura

On Tue, Dec 21, 2021 at 4:41 PM Vladimir Ozerov <pp...@gmail.com> wrote:

> Hi Chathura,
>
> While the SINGLE_VALUE function could be used explicitly, usually this is a
> result of a scalar subquery unnesting, and the goal of the function is to
> ensure that the underlying operator returns no more than one row
> (otherwise, this is not a scalar).
>
> Regards,
> Vladimir.
>
> вт, 21 дек. 2021 г. в 13:52, Chathura Widanage <ch...@voltrondata.com>:
>
> > Hi,
> >
> > Could you please explain the behaviour of SINGLE_VALUE aggregate with the
> > equivalent SQL query?
> >
> > SqlSingleValueAggFunction (Apache Calcite calcite API)
> > <
> >
> https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/fun/SqlSingleValueAggFunction.html
> > >
> >
> > It's not clear whether the "value" in the description refers to an entire
> > table or a cell. Also, there are no samples publicly available for this
> > aggregate function.
> >
> > Regards,
> > Chathura
> >
>

Re: Behavior of SINGLE_VALUE aggregate

Posted by Vladimir Ozerov <pp...@gmail.com>.
Hi Chathura,

While the SINGLE_VALUE function could be used explicitly, usually this is a
result of a scalar subquery unnesting, and the goal of the function is to
ensure that the underlying operator returns no more than one row
(otherwise, this is not a scalar).

Regards,
Vladimir.

вт, 21 дек. 2021 г. в 13:52, Chathura Widanage <ch...@voltrondata.com>:

> Hi,
>
> Could you please explain the behaviour of SINGLE_VALUE aggregate with the
> equivalent SQL query?
>
> SqlSingleValueAggFunction (Apache Calcite calcite API)
> <
> https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/fun/SqlSingleValueAggFunction.html
> >
>
> It's not clear whether the "value" in the description refers to an entire
> table or a cell. Also, there are no samples publicly available for this
> aggregate function.
>
> Regards,
> Chathura
>