The following bug has been logged on the website:
Bug reference: 7612
Logged by: Maxim Boguk
Email address: [email protected]
PostgreSQL version: 9.2.1
Operating system: Linux
Description:
Join between two values() set could produce wrong results:
Test case:
Correct answer:
SELECT a.val, b.val FROM (VALUES( (2), (1) )) AS a (val) JOIN (VALUES( (2),
(42) )) AS b (val) ON a.val = b.val;
val | val
-----+-----
2 | 2
(1 row)
now just change position of (2) and (1) in a(val):
Wrong answer:
SELECT a.val, b.val FROM (VALUES( (1), (2) )) AS a (val) JOIN (VALUES(
(2), (42) )) AS b (val) ON a.val = b.val;
val | val
-----+-----
(0 rows)
explain (analyze, verbose) results of the both queries:
mboguk=# explain (analyze, verbose) SELECT a.val, b.val FROM (VALUES( (2),
(1) )) AS a (val) JOIN (VALUES( (2), (42) )) AS b (val) ON a.val = b.val;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..0.04 rows=1 width=8) (actual time=0.070..0.118
rows=1 loops=1)
Output: "*VALUES*".column1, "*VALUES*".column1
Join Filter: ("*VALUES*".column1 = "*VALUES*".column1)
-> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) (actual
time=0.016..0.027 rows=1 loops=1)
Output: "*VALUES*".column1, "*VALUES*".column2
-> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) (actual
time=0.013..0.024 rows=1 loops=1)
Output: "*VALUES*".column1, "*VALUES*".column2
Total runtime: 0.209 ms
(8 rows)
mboguk=# explain (analyze, verbose) SELECT a.val, b.val FROM (VALUES( (1),
(2) )) AS a (val) JOIN (VALUES( (2), (42) )) AS b (val) ON a.val = b.val;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..0.04 rows=1 width=8) (actual time=0.056..0.056
rows=0 loops=1)
Output: "*VALUES*".column1, "*VALUES*".column1
Join Filter: ("*VALUES*".column1 = "*VALUES*".column1)
Rows Removed by Join Filter: 1
-> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) (actual
time=0.008..0.013 rows=1 loops=1)
Output: "*VALUES*".column1, "*VALUES*".column2
-> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) (actual
time=0.007..0.013 rows=1 loops=1)
Output: "*VALUES*".column1, "*VALUES*".column2
Total runtime: 0.100 ms
(9 rows)
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
