[HACKERS] group by query?
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
Re: [HACKERS] group by query?
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? Ah, don't worry - I get it now :) Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
