The following bug has been logged on the website: Bug reference: 7612 Logged by: Maxim Boguk Email address: maxim.bo...@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 = 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 (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs