[HACKERS] group by query?

[Christopher Kings-Lynne]

In 7.4.3, my workmate came across the following situation he thought was 
odd.

Basically, we want to know why the group by and order by in the outer 
query get their variables from the inner query and not the select 
parameters in the outer query?

Chris
-- shouldn't the first SELECT query perform the GROUP BY and ORDER BY upon the date
-- in the outer SELECT as in the second query
BEGIN;
CREATE TABLE test_dates (
        date TIMESTAMP
);

INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 0);
INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 1);
INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 2);
INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 3);
INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 4);
INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 5);
INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 6);
INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 7);
INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 8);
INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 9);
INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 10);
INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 11);
INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 12);
INSERT INTO test_dates VALUES (current_timestamp::date + interval '1 day' * 13);

SELECT date - interval '1 day' * EXTRACT(dow FROM date) AS date FROM (SELECT date FROM 
test_dates) AS sub GROUP BY date ORDER BY date;
SELECT date - interval '1 day' * EXTRACT(dow FROM date) AS date FROM (SELECT date FROM 
test_dates) AS sub GROUP BY 1 ORDER BY 1;

ROLLBACK;
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend