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

Posted to dev@calcite.apache.org by Danny Chan <yu...@gmail.com> on 2020/10/20 04:41:10 UTC

[QUESTION] Does SQL standard allows project nested fields for aggregate ?

CREATE TYPE my_type AS ( a int , b VARCHAR(20));

create table t1(
  f0 my_type,
  f1 int,
  f2 varchar(20)
);

insert into t1 values((1, 'abc'), 2, ‘def’);

SELECT f0.a, max(f1) FROM t1 GROUP BY f0; — this is invalid in PostgreSQL

SELECT f0, max(f1) FROM t1 GROUP BY f0; — this is a valid query


My question is does SQL standard allows projecting nested fields for aggregate ? In current Calcite, it throws and complains that the nested field can not be seen in the scope (somehow same with the PG).

Best,
Danny Chan

Re: [QUESTION] Does SQL standard allows project nested fields for aggregate ?

Posted by Danny Chan <yu...@gmail.com>.

Thanks, it works ~ I didn’t find evidence that SQL standard does not support this, at least, the semantic is clear.

Best,
Danny Chan
在 2020年10月21日 +0800 AM7:58，Jinfeng Ni <jn...@apache.org>，写道：
> SELECT f0.a, max(f1) FROM t1 GROUP BY f0
>
> I think the problem with the 1st query is that the parser is confused
> with 'f0' being a table or column name.
>
> PostgrelSQL doc [1] suggests to use () to denote column, and the part
> after () to denote the subfield. In your case, the query should be:
>
> SELECT (f0).a, max(f1) FROM t1 GROUP BY f0;
>
> 1. https://www.postgresql.org/docs/current/rowtypes.html
>
> On Tue, Oct 20, 2020 at 12:17 PM Julian Hyde <jh...@apache.org> wrote:
> >
> > I think that your first query,
> >
> > SELECT f0.a, max(f1) FROM t1 GROUP BY f0
> >
> > should be valid. I don't know whether the SQL standard thinks it
> > should be valid, or whether Calcite can handle it. And I don't know
> > why PostgreSQL has a problem with it.
> >
> > Julian
> >
> > On Mon, Oct 19, 2020 at 9:41 PM Danny Chan <yu...@gmail.com> wrote:
> > >
> > > CREATE TYPE my_type AS ( a int , b VARCHAR(20));
> > >
> > > create table t1(
> > > f0 my_type,
> > > f1 int,
> > > f2 varchar(20)
> > > );
> > >
> > > insert into t1 values((1, 'abc'), 2, ‘def’);
> > >
> > > SELECT f0.a, max(f1) FROM t1 GROUP BY f0; — this is invalid in PostgreSQL
> > >
> > > SELECT f0, max(f1) FROM t1 GROUP BY f0; — this is a valid query
> > >
> > >
> > > My question is does SQL standard allows projecting nested fields for aggregate ? In current Calcite, it throws and complains that the nested field can not be seen in the scope (somehow same with the PG).
> > >
> > > Best,
> > > Danny Chan

Re: [QUESTION] Does SQL standard allows project nested fields for aggregate ?

Posted by Jinfeng Ni <jn...@apache.org>.

SELECT f0.a, max(f1) FROM t1 GROUP BY f0

I think the problem with the 1st query is that the parser is confused
with 'f0' being a table or column name.

PostgrelSQL doc [1] suggests to use () to denote column, and the part
after () to denote the subfield. In your case, the query should be:

SELECT (f0).a, max(f1) FROM t1 GROUP BY f0;

1. https://www.postgresql.org/docs/current/rowtypes.html

On Tue, Oct 20, 2020 at 12:17 PM Julian Hyde <jh...@apache.org> wrote:
>
> I think that your first query,
>
>   SELECT f0.a, max(f1) FROM t1 GROUP BY f0
>
> should be valid. I don't know whether the SQL standard thinks it
> should be valid, or whether Calcite can handle it. And I don't know
> why PostgreSQL has a problem with it.
>
> Julian
>
> On Mon, Oct 19, 2020 at 9:41 PM Danny Chan <yu...@gmail.com> wrote:
> >
> > CREATE TYPE my_type AS ( a int , b VARCHAR(20));
> >
> > create table t1(
> >   f0 my_type,
> >   f1 int,
> >   f2 varchar(20)
> > );
> >
> > insert into t1 values((1, 'abc'), 2, ‘def’);
> >
> > SELECT f0.a, max(f1) FROM t1 GROUP BY f0; — this is invalid in PostgreSQL
> >
> > SELECT f0, max(f1) FROM t1 GROUP BY f0; — this is a valid query
> >
> >
> > My question is does SQL standard allows projecting nested fields for aggregate ? In current Calcite, it throws and complains that the nested field can not be seen in the scope (somehow same with the PG).
> >
> > Best,
> > Danny Chan

Re: [QUESTION] Does SQL standard allows project nested fields for aggregate ?

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

I think that your first query,

  SELECT f0.a, max(f1) FROM t1 GROUP BY f0

should be valid. I don't know whether the SQL standard thinks it
should be valid, or whether Calcite can handle it. And I don't know
why PostgreSQL has a problem with it.

Julian

On Mon, Oct 19, 2020 at 9:41 PM Danny Chan <yu...@gmail.com> wrote:
>
> CREATE TYPE my_type AS ( a int , b VARCHAR(20));
>
> create table t1(
>   f0 my_type,
>   f1 int,
>   f2 varchar(20)
> );
>
> insert into t1 values((1, 'abc'), 2, ‘def’);
>
> SELECT f0.a, max(f1) FROM t1 GROUP BY f0; — this is invalid in PostgreSQL
>
> SELECT f0, max(f1) FROM t1 GROUP BY f0; — this is a valid query
>
>
> My question is does SQL standard allows projecting nested fields for aggregate ? In current Calcite, it throws and complains that the nested field can not be seen in the scope (somehow same with the PG).
>
> Best,
> Danny Chan