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
ORDERBY date;
SELECT date - interval '1 day' * EXTRACT(dow FROM date) AS date FROM (SELECT date FROM test_dates) AS sub GROUP BY 1
ORDERBY 1;
ROLLBACK;