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

On Tue, 18 Jan 2005, Lutischn 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=b.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=13.83..16.45 rows=131 width=28)
Merge Cond: ("outer"."?column2?" = "inner"."?column2?")
-> Sort (cost=6.92..7.24 rows=131 width=14)
Sort Key: (b.col2)::text
-> Seq Scan on test2 b (cost=0.00..2.31 rows=131 width=14)
-> Sort (cost=6.92..7.24 rows=131 width=14)
Sort Key: (a.col2)::text
-> Seq Scan on test a (cost=0.00..2.31 rows=131 width=14)
(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.