On Sat, Sep 03, 2005 at 04:29:25PM -0400, Allan Wang wrote:
Here's a simplified, complete test case:

CREATE TABLE foo (
x integer,
y integer
);

INSERT INTO foo (x, y) VALUES (1, 2);

SELECT * FROM (SELECT sum(x), (SELECT y) AS yy FROM foo GROUP BY y) AS s LIMIT 1;
sum | yy |
-----+----+---
1 | 2 | 2
(1 row)

SELECT * FROM (SELECT sum(x), (SELECT y) AS yy FROM foo GROUP BY yy) AS s LIMIT 1;
sum | yy
-----+----
1 | 2
(1 row)

SELECT * FROM (SELECT sum(x), (SELECT y) AS yy FROM foo GROUP BY y) AS s;
sum | yy
-----+----
1 | 2
(1 row)

SELECT * FROM (SELECT sum(x), y AS yy FROM foo GROUP BY y) AS s LIMIT 1;
sum | yy
-----+----
1 | 2
(1 row)

SELECT * FROM (SELECT x, (SELECT y) AS yy FROM foo) AS s LIMIT 1;
x | yy
---+----
1 | 2
(1 row)

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
Thanks for the test case. The bug seems to be introduced by the code
I added a couple months ago to eliminate unnecessary SubqueryScan plan
nodes: the Limit node ends up with a targetlist different from its
immediate input node, which is wrong because Limit doesn't do any
projection, only pass on its input tuples (or not). There are probably
related cases involving Sort nodes (ORDER BY on a sub-select) and other
plan nodes that don't do projection. Haven't decided on an appropriate
fix yet --- seems we have to either prevent the SubqueryScan from being
removed in this context, or fix the tlist of the parent node. Don't
know which will be less messy.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
Patch applied. Thanks for the test case.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match