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.