BUG #7612: Wrong result with join between two values () set

From: maxim(dot)boguk(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #7612: Wrong result with join between two values () set
Date: 2012-10-18 15:40:00
Message-ID: E1TOsCW-0001Y0-Gd@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 7612
Logged by: Maxim Boguk
Email address: maxim(dot)boguk(at)gmail(dot)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)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2012-10-18 15:46:50 Re: BUG #6510: A simple prompt is displayed using wrong charset
Previous Message Fujii Masao 2012-10-18 15:19:30 Re: [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown