In article <28855.1260486487@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Harald Fuchs <hari.fuchs@gmail.com> writes:
>> That being said, I still think that PostgreSQL could do better - how
>> about naming expression columns so that they are distinct from column
>> names?
> Even though the rules we use are pretty arbitrary, I'm hesitant to make
> changes in them; it seems more likely to break existing applications
> than to do anyone any good.
Well, it would be far less confusing. Here's a self-contained example:
CREATE TABLE t1 ( id serial NOT NULL, adr text NOT NULL, usr text NOT NULL, PRIMARY KEY (id) );
COPY t1 (adr, usr) FROM stdin DELIMITER '|'; a1|u1_1 a2|u2_1 a2|u2_2 a2|u2_2 a3|u3_1 a3|u3_2 a3|u3_2 a3|u3_3 a3|u3_3
a3|u3_3\.
SELECT CASE lag(adr) OVER (ORDER BY adr, usr) WHEN adr THEN NULL ELSE adr END, usr,
count(*)FROM t1 GROUP BY adr, usr ORDER BY adr, usr;
The result set is:
adr | usr | count -----+------+------- a1 | u1_1 | 1 a2 | u2_1 | 1 a3 | u3_1 | 1 | u2_2 |
2 | u3_2 | 2 | u3_3 | 3
This means IMHO that "GROUP BY adr, usr" groups by the column named
"adr", whereas "ORDER BY adr, usr" orders by the unnamed CASE
expression which happens to "hide" the column name without warning.