Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
maxim.boguk@gmail.com
Date:
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)

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

From
Vik Reykja
Date:
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.



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

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

From
Maxim Boguk
Date:
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.