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

Started by Maxim Bogukover 13 years ago3 messagesbugs
Jump to latest
#1Maxim Boguk
maxim.boguk@gmail.com

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 = 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)

#2Vik Fearing
vik@postgresfriends.org
In reply to: Maxim Boguk (#1)
Re: BUG #7612: Wrong result with join between two values () set

On Thu, Oct 18, 2012 at 5:40 PM, <maxim.boguk@gmail.com> wrote:

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:

This is not a bug; your test case produces correct results.

Your VALUES clauses are producing one row with two columns each called
"val" and "column2". You are joining on val and so when you switch the
values to put 2 in different columns, no results are found.

Is it possible you didn't intend to put the parentheses immediately
following the VALUES keyword? I think that is the case because your
parentheses around the numbers are superfluous.

Show quoted text

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

#3Maxim Boguk
maxim.boguk@gmail.com
In reply to: Vik Fearing (#2)
Re: BUG #7612: Wrong result with join between two values () set

On 10/19/12, Vik Reykja <vikreykja@gmail.com> wrote:

On Thu, Oct 18, 2012 at 5:40 PM, <maxim.boguk@gmail.com> wrote:

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:

This is not a bug; your test case produces correct results.

Your VALUES clauses are producing one row with two columns each called
"val" and "column2". You are joining on val and so when you switch the
values to put 2 in different columns, no results are found.

Is it possible you didn't intend to put the parentheses immediately
following the VALUES keyword? I think that is the case because your
parentheses around the numbers are superfluous.

Oops sorry for noise.
I should be more careful.