The following bug has been logged on the website:
Bug reference: 7612
Logged by: Maxim Boguk
Email address: maxim.boguk@gmail.com
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 =3D 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 =3D b.val;
val | val
-----+-----
(0 rows)
explain (analyze, verbose) results of the both queries:
mboguk=3D# 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 =3D b.val;
QUERY PLAN
---------------------------------------------------------------------------=
----------------------------------
Nested Loop (cost=3D0.00..0.04 rows=3D1 width=3D8) (actual time=3D0.070..=
0.118
rows=3D1 loops=3D1)
Output: "*VALUES*".column1, "*VALUES*".column1
Join Filter: ("*VALUES*".column1 =3D "*VALUES*".column1)
-> Values Scan on "*VALUES*" (cost=3D0.00..0.01 rows=3D1 width=3D4) (a=
ctual
time=3D0.016..0.027 rows=3D1 loops=3D1)
Output: "*VALUES*".column1, "*VALUES*".column2
-> Values Scan on "*VALUES*" (cost=3D0.00..0.01 rows=3D1 width=3D4) (a=
ctual
time=3D0.013..0.024 rows=3D1 loops=3D1)
Output: "*VALUES*".column1, "*VALUES*".column2
Total runtime: 0.209 ms
(8 rows)
mboguk=3D# 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 =3D b.val;
QUERY PLAN
---------------------------------------------------------------------------=
----------------------------------
Nested Loop (cost=3D0.00..0.04 rows=3D1 width=3D8) (actual time=3D0.056..=
0.056
rows=3D0 loops=3D1)
Output: "*VALUES*".column1, "*VALUES*".column1
Join Filter: ("*VALUES*".column1 =3D "*VALUES*".column1)
Rows Removed by Join Filter: 1
-> Values Scan on "*VALUES*" (cost=3D0.00..0.01 rows=3D1 width=3D4) (a=
ctual
time=3D0.008..0.013 rows=3D1 loops=3D1)
Output: "*VALUES*".column1, "*VALUES*".column2
-> Values Scan on "*VALUES*" (cost=3D0.00..0.01 rows=3D1 width=3D4) (a=
ctual
time=3D0.007..0.013 rows=3D1 loops=3D1)
Output: "*VALUES*".column1, "*VALUES*".column2
Total runtime: 0.100 ms
(9 rows)
