Re: [HACKERS] Some progress on INSERT/SELECT/GROUP BY bugs

> >
> > > I still am unclear which of these are valid SQL:
> > >
> > > select a as b from test order by a
> > > select a as b from test order by b
> > >
> > Both are valid, and don't forget the third variant:
> >
> > select a as b from test order by 1
> >
> > Andreas
> >
>
> I wonder why this should be valid. Consider the following
> test case:
>
> CREATE TABLE t1 (a int4, b int4);
> SELECT a AS b, b AS a FROM t1 GROUP BY a, b;
>
> Is that now GROUP BY 1,2 or BY 2,1? Without the grouping, it
>
The order of the columns in a group by don't affect the result.
It will affect the sort order, but without an order by, the order is
implementation depentent and not guaranteed by ANSI.

> is a totally valid statement because the column DISPLAY-names
> given with AS don't affect the rest of it.
>
Resumee:
group by and where ignores alias completely (in Oracle and Informix)
order by uses alias
(only if unambiguous in Informix, alias precedes column name
in Oracle)

So I guess our group by code does it different, than all others :-(

At last what about this, even if it is how the others do it, it is not
consistent with
our group by:
regression=> select a as b, b as c from a where c=3;
b|c
-+-
3|1
(1 row)

Does anyone know what standard says ?

Andreas