Re: BUG #7612: Wrong result with join between two values () set - Mailing list pgsql-bugs

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
>