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