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

[Julian Hyde]

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