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

[Jinfeng Ni]

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 <yuzhao....@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