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
inInformix, 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