Thread: BUG #1409: A good and a bad news: Crazy SQL JOIN?
The following bug has been logged online:
Bug reference: 1409
Logged by: Lutischán Ferenc
Email address: yoursoft@freemail.hu
PostgreSQL version: 8.0 B5, 7.4.6
Operating system: WinXp, SLES9
Description: A good and a bad news: Crazy SQL JOIN?
Details:
Dear Developer Team!
A good news: Refering to my mail with 'Out of memory problem' (>>If you make
'create user' and 'alter group' sql command on existing users, and make it
many times, the server doesn't release the memory<<) -> this is solved in
the 7.4.6.
A bad news:
I don't understand the following situation:
Try to create these tables:
-------------------------------------------
SET client_encoding = 'UNICODE';
SET check_function_bodies = false;
SET client_min_messages = warning;
CREATE SCHEMA ifc AUTHORIZATION postgres;
SET search_path = ifc, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = true;
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
-----------------------------------------
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::char(8)
-----------------------------------------
A questions: Why different are the results?
Best Regards:
Ferenc
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.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> 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.
Yup. Looks like this bug has been there since day one (ever since we
supported outer joins, that is). I've patched it back as far as 7.2.
regards, tom lane
*** src/backend/optimizer/path/joinpath.c.orig Fri Dec 31 17:45:50 2004
--- src/backend/optimizer/path/joinpath.c Sat Jan 22 20:44:49 2005
***************
*** 271,277 ****
cur_mergeclauses,
innerrel);
/* Build pathkeys representing output sort order. */
! merge_pathkeys = build_join_pathkeys(root, joinrel, outerkeys);
/*
* And now we can make the path.
--- 271,278 ----
cur_mergeclauses,
innerrel);
/* Build pathkeys representing output sort order. */
! merge_pathkeys = build_join_pathkeys(root, joinrel, jointype,
! outerkeys);
/*
* And now we can make the path.
***************
*** 431,437 ****
* as a nestloop, and even if some of the mergeclauses are
* implemented by qpquals rather than as true mergeclauses):
*/
! merge_pathkeys = build_join_pathkeys(root, joinrel,
outerpath->pathkeys);
if (nestjoinOK)
--- 432,438 ----
* as a nestloop, and even if some of the mergeclauses are
* implemented by qpquals rather than as true mergeclauses):
*/
! merge_pathkeys = build_join_pathkeys(root, joinrel, jointype,
outerpath->pathkeys);
if (nestjoinOK)
*** src/backend/optimizer/path/pathkeys.c.orig Fri Dec 31 17:45:50 2004
--- src/backend/optimizer/path/pathkeys.c Sat Jan 22 20:44:50 2005
***************
*** 858,864 ****
--- 858,869 ----
* vars they were joined with; furthermore, it doesn't matter what kind
* of join algorithm is actually used.
*
+ * EXCEPTION: in a FULL or RIGHT join, we cannot treat the result as
+ * having the outer path's path keys, because null lefthand rows may be
+ * inserted at random points. It must be treated as unsorted.
+ *
* 'joinrel' is the join relation that paths are being formed for
+ * 'jointype' is the join type (inner, left, full, etc)
* 'outer_pathkeys' is the list of the current outer path's path keys
*
* Returns the list of new path keys.
***************
*** 866,873 ****
--- 871,882 ----
List *
build_join_pathkeys(Query *root,
RelOptInfo *joinrel,
+ JoinType jointype,
List *outer_pathkeys)
{
+ if (jointype == JOIN_FULL || jointype == JOIN_RIGHT)
+ return NIL;
+
/*
* This used to be quite a complex bit of code, but now that all
* pathkey sublists start out life canonicalized, we don't have to do
*** src/include/optimizer/paths.h.orig Fri Dec 31 17:46:56 2004
--- src/include/optimizer/paths.h Sat Jan 22 20:44:43 2005
***************
*** 114,119 ****
--- 114,120 ----
Query *subquery);
extern List *build_join_pathkeys(Query *root,
RelOptInfo *joinrel,
+ JoinType jointype,
List *outer_pathkeys);
extern List *make_pathkeys_for_sortclauses(List *sortclauses,
List *tlist);