Skip site navigation (1) Skip section navigation (2)

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

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Lutischn Ferenc <yoursoft(at)freemail(dot)hu>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1409: A good and a bad news: Crazy SQL JOIN?
Date: 2005-01-20 19:54:11
Message-ID: 20050120114735.T51555@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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.


In response to

Responses

pgsql-bugs by date

Next:From: Bruno Wolff IIIDate: 2005-01-20 20:28:45
Subject: Re: BUG #1408: don't see index
Previous:From: Magnus HaganderDate: 2005-01-20 19:45:24
Subject: Re: BUG #1424: Installer bug

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group