Re: BUG #1409: A good and a bad news: Crazy SQL JOIN?

On Tue, 18 Jan 2005, Lutisch=C3=A1n Ferenc wrote:

> CREATE TABLE test (
>     col1 character varying(10),
>     col2 character varying(10)
> );
>
>
> ALTER TABLE ifc.test OWNER TO postgres;
>
> CREATE TABLE test2 (
>     col1 character varying(10),
>     col2 character varying(10)
> );
>
>
> ALTER TABLE ifc.test2 OWNER TO postgres;
>
> COPY test (col1, col2) FROM stdin;
> b    ac
> ba    a
> \N    aac
> \N    aab
> \.
>
> COPY test2 (col1, col2) FROM stdin;
> b    ac
> \N    aac
> ba    a
> \N    aaa
> \.
> -----------------------------------------------
>
> And try to make the following selects:
> -----------------------------------------
> select a.col2 as col1, b.col2 from
> ifc.test a full outer join ifc.test2 b on a.col2=3Db.col2
> order by b.col2

I get:
 col1 | col2
------+------
 a    | a
      | aaa
 aab  |
 aac  | aac
 ac   | ac
(5 rows)

Is this what you see as well?  I think the result is wrong.

The explain output looks for me like:

                              QUERY PLAN
----------------------------------------------------------------------
 Merge Full Join  (cost=3D13.83..16.45 rows=3D131 width=3D28)
   Merge Cond: ("outer"."?column2?" =3D "inner"."?column2?")
   ->  Sort  (cost=3D6.92..7.24 rows=3D131 width=3D14)
         Sort Key: (b.col2)::text
         ->  Seq Scan on test2 b  (cost=3D0.00..2.31 rows=3D131 width=3D14)
   ->  Sort  (cost=3D6.92..7.24 rows=3D131 width=3D14)
         Sort Key: (a.col2)::text
         ->  Seq Scan on test a  (cost=3D0.00..2.31 rows=3D131 width=3D14)
(8 rows)

It looks like it thinks that the output is already sorted by b.col2 which
would appear to be untrue if rows are being extended from a so I think
this is a bug optimizing the query.  The ::char(8) case forces a sort step
which appears to make it return the correct results.