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

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)

On Thu, Oct 18, 2012 at 5:40 PM, <maxim(dot)boguk(at)gmail(dot)com> wrote:

> 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:
>

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.

>
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

On 10/19/12, Vik Reykja <vikreykja(at)gmail(dot)com> wrote:
> On Thu, Oct 18, 2012 at 5:40 PM, <maxim(dot)boguk(at)gmail(dot)com> wrote:
>
>> 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:
>>
>
> 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.