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

I get:
col1 | col2
------+------
a | a
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.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com> writes:
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);

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly