fix-mergejoin-redundant-pathkey-issues.patch
text/x-diff
Filename: fix-mergejoin-redundant-pathkey-issues.patch
Type: text/x-diff
Part: 0
Patch
Format: unified
| File | + | − |
|---|---|---|
| src/backend/optimizer/path/joinpath.c | 0 | 0 |
| src/backend/optimizer/path/pathkeys.c | 0 | 0 |
| src/backend/optimizer/plan/createplan.c | 0 | 0 |
| src/include/optimizer/paths.h | 0 | 0 |
| src/test/regress/expected/join.out | 0 | 0 |
| src/test/regress/sql/join.sql | 0 | 0 |
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 396ee27..a2777ea 100644
*** a/src/backend/optimizer/path/joinpath.c
--- b/src/backend/optimizer/path/joinpath.c
*************** sort_inner_and_outer(PlannerInfo *root,
*** 1009,1018 ****
outerkeys = all_pathkeys; /* no work at first one... */
/* Sort the mergeclauses into the corresponding ordering */
! cur_mergeclauses = find_mergeclauses_for_pathkeys(root,
! outerkeys,
! true,
! extra->mergeclause_list);
/* Should have used them all... */
Assert(list_length(cur_mergeclauses) == list_length(extra->mergeclause_list));
--- 1009,1017 ----
outerkeys = all_pathkeys; /* no work at first one... */
/* Sort the mergeclauses into the corresponding ordering */
! cur_mergeclauses = find_mergeclauses_for_outer_pathkeys(root,
! outerkeys,
! extra->mergeclause_list);
/* Should have used them all... */
Assert(list_length(cur_mergeclauses) == list_length(extra->mergeclause_list));
*************** generate_mergejoin_paths(PlannerInfo *ro
*** 1102,1111 ****
jointype = JOIN_INNER;
/* Look for useful mergeclauses (if any) */
! mergeclauses = find_mergeclauses_for_pathkeys(root,
! outerpath->pathkeys,
! true,
! extra->mergeclause_list);
/*
* Done with this outer path if no chance for a mergejoin.
--- 1101,1109 ----
jointype = JOIN_INNER;
/* Look for useful mergeclauses (if any) */
! mergeclauses = find_mergeclauses_for_outer_pathkeys(root,
! outerpath->pathkeys,
! extra->mergeclause_list);
/*
* Done with this outer path if no chance for a mergejoin.
*************** generate_mergejoin_paths(PlannerInfo *ro
*** 1228,1237 ****
if (sortkeycnt < num_sortkeys)
{
newclauses =
! find_mergeclauses_for_pathkeys(root,
! trialsortkeys,
! false,
! mergeclauses);
Assert(newclauses != NIL);
}
else
--- 1226,1234 ----
if (sortkeycnt < num_sortkeys)
{
newclauses =
! trim_mergeclauses_for_inner_pathkeys(root,
! mergeclauses,
! trialsortkeys);
Assert(newclauses != NIL);
}
else
*************** generate_mergejoin_paths(PlannerInfo *ro
*** 1272,1281 ****
if (sortkeycnt < num_sortkeys)
{
newclauses =
! find_mergeclauses_for_pathkeys(root,
! trialsortkeys,
! false,
! mergeclauses);
Assert(newclauses != NIL);
}
else
--- 1269,1277 ----
if (sortkeycnt < num_sortkeys)
{
newclauses =
! trim_mergeclauses_for_inner_pathkeys(root,
! mergeclauses,
! trialsortkeys);
Assert(newclauses != NIL);
}
else
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index ef58cff..689bd68 100644
*** a/src/backend/optimizer/path/pathkeys.c
--- b/src/backend/optimizer/path/pathkeys.c
*************** update_mergeclause_eclasses(PlannerInfo
*** 981,994 ****
}
/*
! * find_mergeclauses_for_pathkeys
* This routine attempts to find a set of mergeclauses that can be
! * used with a specified ordering for one of the input relations.
* If successful, it returns a list of mergeclauses.
*
! * 'pathkeys' is a pathkeys list showing the ordering of an input path.
! * 'outer_keys' is true if these keys are for the outer input path,
! * false if for inner.
* 'restrictinfos' is a list of mergejoinable restriction clauses for the
* join relation being formed.
*
--- 981,992 ----
}
/*
! * find_mergeclauses_for_outer_pathkeys
* This routine attempts to find a set of mergeclauses that can be
! * used with a specified ordering for the join's outer relation.
* If successful, it returns a list of mergeclauses.
*
! * 'pathkeys' is a pathkeys list showing the ordering of an outer-rel path.
* 'restrictinfos' is a list of mergejoinable restriction clauses for the
* join relation being formed.
*
*************** update_mergeclause_eclasses(PlannerInfo
*** 1000,1009 ****
* usable mergeclauses (represented as a list of their restrictinfo nodes).
*/
List *
! find_mergeclauses_for_pathkeys(PlannerInfo *root,
! List *pathkeys,
! bool outer_keys,
! List *restrictinfos)
{
List *mergeclauses = NIL;
ListCell *i;
--- 998,1006 ----
* usable mergeclauses (represented as a list of their restrictinfo nodes).
*/
List *
! find_mergeclauses_for_outer_pathkeys(PlannerInfo *root,
! List *pathkeys,
! List *restrictinfos)
{
List *mergeclauses = NIL;
ListCell *i;
*************** find_mergeclauses_for_pathkeys(PlannerIn
*** 1044,1062 ****
*
* It's possible that multiple matching clauses might have different
* ECs on the other side, in which case the order we put them into our
! * result makes a difference in the pathkeys required for the other
! * input path. However this routine hasn't got any info about which
* order would be best, so we don't worry about that.
*
* It's also possible that the selected mergejoin clauses produce
! * a noncanonical ordering of pathkeys for the other side, ie, we
* might select clauses that reference b.v1, b.v2, b.v1 in that
* order. This is not harmful in itself, though it suggests that
! * the clauses are partially redundant. Since it happens only with
! * redundant query conditions, we don't bother to eliminate it.
! * make_inner_pathkeys_for_merge() has to delete duplicates when
! * it constructs the canonical pathkeys list, and we also have to
! * deal with the case in create_mergejoin_plan().
*----------
*/
foreach(j, restrictinfos)
--- 1041,1060 ----
*
* It's possible that multiple matching clauses might have different
* ECs on the other side, in which case the order we put them into our
! * result makes a difference in the pathkeys required for the inner
! * input rel. However this routine hasn't got any info about which
* order would be best, so we don't worry about that.
*
* It's also possible that the selected mergejoin clauses produce
! * a noncanonical ordering of pathkeys for the inner side, ie, we
* might select clauses that reference b.v1, b.v2, b.v1 in that
* order. This is not harmful in itself, though it suggests that
! * the clauses are partially redundant. Since the alternative is
! * to omit mergejoin clauses and thereby possibly fail to generate a
! * plan altogether, we live with it. make_inner_pathkeys_for_merge()
! * has to delete duplicates when it constructs the inner pathkeys
! * list, and we also have to deal with such cases specially in
! * create_mergejoin_plan().
*----------
*/
foreach(j, restrictinfos)
*************** find_mergeclauses_for_pathkeys(PlannerIn
*** 1064,1075 ****
RestrictInfo *rinfo = (RestrictInfo *) lfirst(j);
EquivalenceClass *clause_ec;
! if (outer_keys)
! clause_ec = rinfo->outer_is_left ?
! rinfo->left_ec : rinfo->right_ec;
! else
! clause_ec = rinfo->outer_is_left ?
! rinfo->right_ec : rinfo->left_ec;
if (clause_ec == pathkey_ec)
matched_restrictinfos = lappend(matched_restrictinfos, rinfo);
}
--- 1062,1069 ----
RestrictInfo *rinfo = (RestrictInfo *) lfirst(j);
EquivalenceClass *clause_ec;
! clause_ec = rinfo->outer_is_left ?
! rinfo->left_ec : rinfo->right_ec;
if (clause_ec == pathkey_ec)
matched_restrictinfos = lappend(matched_restrictinfos, rinfo);
}
*************** make_inner_pathkeys_for_merge(PlannerInf
*** 1351,1358 ****
opathkey->pk_nulls_first);
/*
! * Don't generate redundant pathkeys (can happen if multiple
! * mergeclauses refer to same EC).
*/
if (!pathkey_is_redundant(pathkey, pathkeys))
pathkeys = lappend(pathkeys, pathkey);
--- 1345,1357 ----
opathkey->pk_nulls_first);
/*
! * Don't generate redundant pathkeys (which can happen if multiple
! * mergeclauses refer to the same EC). Because we do this, the output
! * pathkey list isn't necessarily ordered like the mergeclauses, which
! * complicates life for create_mergejoin_plan(). But if we didn't,
! * we'd have a noncanonical sort key list, which would be bad; for one
! * reason, it certainly wouldn't match any available sort order for
! * the input relation.
*/
if (!pathkey_is_redundant(pathkey, pathkeys))
pathkeys = lappend(pathkeys, pathkey);
*************** make_inner_pathkeys_for_merge(PlannerInf
*** 1361,1366 ****
--- 1360,1457 ----
return pathkeys;
}
+ /*
+ * trim_mergeclauses_for_inner_pathkeys
+ * This routine trims a list of mergeclauses to include just those that
+ * work with a specified ordering for the join's inner relation.
+ *
+ * 'mergeclauses' is a list of RestrictInfos for mergejoin clauses for the
+ * join relation being formed, in an order known to work for the
+ * currently-considered sort ordering of the join's outer rel.
+ * 'pathkeys' is a pathkeys list showing the ordering of an inner-rel path;
+ * it should be equal to, or a truncation of, the result of
+ * make_inner_pathkeys_for_merge for these mergeclauses.
+ *
+ * What we return will be a prefix of the given mergeclauses list.
+ *
+ * We need this logic because make_inner_pathkeys_for_merge's result isn't
+ * necessarily in the same order as the mergeclauses. That means that if we
+ * consider an inner-rel pathkey list that is a truncation of that result,
+ * we might need to drop mergeclauses even though they match a surviving inner
+ * pathkey. This happens when they are to the right of a mergeclause that
+ * matches a removed inner pathkey.
+ *
+ * The mergeclauses must be marked (via outer_is_left) to show which side
+ * of each clause is associated with the current outer path. (See
+ * select_mergejoin_clauses())
+ */
+ List *
+ trim_mergeclauses_for_inner_pathkeys(PlannerInfo *root,
+ List *mergeclauses,
+ List *pathkeys)
+ {
+ List *new_mergeclauses = NIL;
+ PathKey *pathkey;
+ EquivalenceClass *pathkey_ec;
+ bool matched_pathkey;
+ ListCell *lip;
+ ListCell *i;
+
+ /* No pathkeys => no mergeclauses (though we don't expect this case) */
+ if (pathkeys == NIL)
+ return NIL;
+ /* Initialize to consider first pathkey */
+ lip = list_head(pathkeys);
+ pathkey = (PathKey *) lfirst(lip);
+ pathkey_ec = pathkey->pk_eclass;
+ lip = lnext(lip);
+ matched_pathkey = false;
+
+ /* Scan mergeclauses to see how many we can use */
+ foreach(i, mergeclauses)
+ {
+ RestrictInfo *rinfo = (RestrictInfo *) lfirst(i);
+ EquivalenceClass *clause_ec;
+
+ /* Assume we needn't do update_mergeclause_eclasses again here */
+
+ /* Check clause's inner-rel EC against current pathkey */
+ clause_ec = rinfo->outer_is_left ?
+ rinfo->right_ec : rinfo->left_ec;
+
+ /* If we don't have a match, attempt to advance to next pathkey */
+ if (clause_ec != pathkey_ec)
+ {
+ /* If we had no clauses matching this inner pathkey, must stop */
+ if (!matched_pathkey)
+ break;
+
+ /* Advance to next inner pathkey, if any */
+ if (lip == NULL)
+ break;
+ pathkey = (PathKey *) lfirst(lip);
+ pathkey_ec = pathkey->pk_eclass;
+ lip = lnext(lip);
+ matched_pathkey = false;
+ }
+
+ /* If mergeclause matches current inner pathkey, we can use it */
+ if (clause_ec == pathkey_ec)
+ {
+ new_mergeclauses = lappend(new_mergeclauses, rinfo);
+ matched_pathkey = true;
+ }
+ else
+ {
+ /* Else, no hope of adding any more mergeclauses */
+ break;
+ }
+ }
+
+ return new_mergeclauses;
+ }
+
+
/****************************************************************************
* PATHKEY USEFULNESS CHECKS
*
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index da0cc7f..65ca47d 100644
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
*************** create_mergejoin_plan(PlannerInfo *root,
*** 3791,3796 ****
--- 3791,3798 ----
Oid *mergecollations;
int *mergestrategies;
bool *mergenullsfirst;
+ PathKey *opathkey;
+ EquivalenceClass *opeclass;
int i;
ListCell *lc;
ListCell *lop;
*************** create_mergejoin_plan(PlannerInfo *root,
*** 3909,3915 ****
* Compute the opfamily/collation/strategy/nullsfirst arrays needed by the
* executor. The information is in the pathkeys for the two inputs, but
* we need to be careful about the possibility of mergeclauses sharing a
! * pathkey (compare find_mergeclauses_for_pathkeys()).
*/
nClauses = list_length(mergeclauses);
Assert(nClauses == list_length(best_path->path_mergeclauses));
--- 3911,3918 ----
* Compute the opfamily/collation/strategy/nullsfirst arrays needed by the
* executor. The information is in the pathkeys for the two inputs, but
* we need to be careful about the possibility of mergeclauses sharing a
! * pathkey, as well as the possibility that the inner pathkeys are not in
! * an order matching the mergeclauses.
*/
nClauses = list_length(mergeclauses);
Assert(nClauses == list_length(best_path->path_mergeclauses));
*************** create_mergejoin_plan(PlannerInfo *root,
*** 3918,3923 ****
--- 3921,3928 ----
mergestrategies = (int *) palloc(nClauses * sizeof(int));
mergenullsfirst = (bool *) palloc(nClauses * sizeof(bool));
+ opathkey = NULL;
+ opeclass = NULL;
lop = list_head(outerpathkeys);
lip = list_head(innerpathkeys);
i = 0;
*************** create_mergejoin_plan(PlannerInfo *root,
*** 3926,3936 ****
RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
EquivalenceClass *oeclass;
EquivalenceClass *ieclass;
! PathKey *opathkey;
! PathKey *ipathkey;
! EquivalenceClass *opeclass;
! EquivalenceClass *ipeclass;
! ListCell *l2;
/* fetch outer/inner eclass from mergeclause */
if (rinfo->outer_is_left)
--- 3931,3939 ----
RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
EquivalenceClass *oeclass;
EquivalenceClass *ieclass;
! PathKey *ipathkey = NULL;
! EquivalenceClass *ipeclass = NULL;
! bool first_inner_match = false;
/* fetch outer/inner eclass from mergeclause */
if (rinfo->outer_is_left)
*************** create_mergejoin_plan(PlannerInfo *root,
*** 3947,4050 ****
Assert(ieclass != NULL);
/*
! * For debugging purposes, we check that the eclasses match the paths'
! * pathkeys. In typical cases the merge clauses are one-to-one with
! * the pathkeys, but when dealing with partially redundant query
! * conditions, we might have clauses that re-reference earlier path
! * keys. The case that we need to reject is where a pathkey is
! * entirely skipped over.
*
! * lop and lip reference the first as-yet-unused pathkey elements;
! * it's okay to match them, or any element before them. If they're
! * NULL then we have found all pathkey elements to be used.
*/
! if (lop)
{
opathkey = (PathKey *) lfirst(lop);
opeclass = opathkey->pk_eclass;
! if (oeclass == opeclass)
! {
! /* fast path for typical case */
! lop = lnext(lop);
! }
! else
! {
! /* redundant clauses ... must match something before lop */
! foreach(l2, outerpathkeys)
! {
! if (l2 == lop)
! break;
! opathkey = (PathKey *) lfirst(l2);
! opeclass = opathkey->pk_eclass;
! if (oeclass == opeclass)
! break;
! }
! if (oeclass != opeclass)
! elog(ERROR, "outer pathkeys do not match mergeclauses");
! }
! }
! else
! {
! /* redundant clauses ... must match some already-used pathkey */
! opathkey = NULL;
! opeclass = NULL;
! foreach(l2, outerpathkeys)
! {
! opathkey = (PathKey *) lfirst(l2);
! opeclass = opathkey->pk_eclass;
! if (oeclass == opeclass)
! break;
! }
! if (l2 == NULL)
elog(ERROR, "outer pathkeys do not match mergeclauses");
}
if (lip)
{
ipathkey = (PathKey *) lfirst(lip);
ipeclass = ipathkey->pk_eclass;
if (ieclass == ipeclass)
{
! /* fast path for typical case */
lip = lnext(lip);
! }
! else
! {
! /* redundant clauses ... must match something before lip */
! foreach(l2, innerpathkeys)
! {
! if (l2 == lip)
! break;
! ipathkey = (PathKey *) lfirst(l2);
! ipeclass = ipathkey->pk_eclass;
! if (ieclass == ipeclass)
! break;
! }
! if (ieclass != ipeclass)
! elog(ERROR, "inner pathkeys do not match mergeclauses");
}
}
! else
{
! /* redundant clauses ... must match some already-used pathkey */
! ipathkey = NULL;
! ipeclass = NULL;
foreach(l2, innerpathkeys)
{
ipathkey = (PathKey *) lfirst(l2);
ipeclass = ipathkey->pk_eclass;
if (ieclass == ipeclass)
break;
}
! if (l2 == NULL)
elog(ERROR, "inner pathkeys do not match mergeclauses");
}
! /* pathkeys should match each other too (more debugging) */
if (opathkey->pk_opfamily != ipathkey->pk_opfamily ||
! opathkey->pk_eclass->ec_collation != ipathkey->pk_eclass->ec_collation ||
! opathkey->pk_strategy != ipathkey->pk_strategy ||
! opathkey->pk_nulls_first != ipathkey->pk_nulls_first)
elog(ERROR, "left and right pathkeys do not match in mergejoin");
/* OK, save info for executor */
--- 3950,4043 ----
Assert(ieclass != NULL);
/*
! * We must identify the pathkey elements associated with this clause
! * by matching the eclasses (which should give a unique match, since
! * the pathkey lists should be canonical). In typical cases the merge
! * clauses are one-to-one with the pathkeys, but when dealing with
! * partially redundant query conditions, things are more complicated.
*
! * lop and lip reference the first as-yet-unmatched pathkey elements.
! * If they're NULL then we have found all pathkey elements to be
! * matched.
! *
! * The ordering of the outer pathkeys should match the mergeclauses,
! * by construction (see find_mergeclauses_for_outer_pathkeys()). There
! * could be more than one mergeclause for the same outer path key, but
! * no path key may be entirely skipped over.
*/
! if (oeclass != opeclass) /* multiple matches are not interesting */
{
+ /* doesn't match the current opathkey, so must match the next */
+ if (lop == NULL)
+ elog(ERROR, "outer pathkeys do not match mergeclauses");
opathkey = (PathKey *) lfirst(lop);
opeclass = opathkey->pk_eclass;
! lop = lnext(lop);
! if (oeclass != opeclass)
elog(ERROR, "outer pathkeys do not match mergeclauses");
}
+ /*
+ * The inner pathkeys likewise should not have skipped-over keys, but
+ * it's possible for a mergeclause to reference some earlier inner
+ * pathkey if we had redundant pathkeys. For example we might have
+ * mergeclauses like "o.a = i.x AND o.b = i.y AND o.c = i.x". The
+ * implied inner ordering is then "ORDER BY x, y, x", but the pathkey
+ * drops the second sort by x as redundant, and this code must cope.
+ *
+ * It's also possible for the implied inner-rel ordering to be like
+ * "ORDER BY x, y, x DESC". We still drop the second instance of x as
+ * redundant; but this means that the sort ordering of a redundant
+ * inner pathkey should not be considered significant.
+ */
if (lip)
{
ipathkey = (PathKey *) lfirst(lip);
ipeclass = ipathkey->pk_eclass;
if (ieclass == ipeclass)
{
! /* successful first match to this inner pathkey */
lip = lnext(lip);
! first_inner_match = true;
}
}
! if (!first_inner_match)
{
! /* redundant clause ... must match something before lip */
! ListCell *l2;
!
foreach(l2, innerpathkeys)
{
+ if (l2 == lip)
+ break;
ipathkey = (PathKey *) lfirst(l2);
ipeclass = ipathkey->pk_eclass;
if (ieclass == ipeclass)
break;
}
! if (ieclass != ipeclass)
elog(ERROR, "inner pathkeys do not match mergeclauses");
}
! /*
! * The pathkeys should always match each other as to opfamily and
! * collation (which affect equality), but if we're considering a
! * redundant inner pathkey, its sort ordering might not match. In
! * such cases we may ignore the inner pathkey's sort ordering and use
! * the outer's. (In effect, we're lying to the executor about the
! * sort direction of this inner column, but it does not matter since
! * the run-time row comparisons would only reach this column when
! * there's equality for the earlier column containing the same eclass.
! * IOW, there could be only one value in this column for the range of
! * inner rows having a given value in the earlier column, so it does
! * not matter in which direction we imagine those rows to be ordered.)
! */
if (opathkey->pk_opfamily != ipathkey->pk_opfamily ||
! opathkey->pk_eclass->ec_collation != ipathkey->pk_eclass->ec_collation)
! elog(ERROR, "left and right pathkeys do not match in mergejoin");
! if (first_inner_match &&
! (opathkey->pk_strategy != ipathkey->pk_strategy ||
! opathkey->pk_nulls_first != ipathkey->pk_nulls_first))
elog(ERROR, "left and right pathkeys do not match in mergejoin");
/* OK, save info for executor */
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index c9e4431..b2b4353 100644
*** a/src/include/optimizer/paths.h
--- b/src/include/optimizer/paths.h
*************** extern void initialize_mergeclause_eclas
*** 216,231 ****
RestrictInfo *restrictinfo);
extern void update_mergeclause_eclasses(PlannerInfo *root,
RestrictInfo *restrictinfo);
! extern List *find_mergeclauses_for_pathkeys(PlannerInfo *root,
! List *pathkeys,
! bool outer_keys,
! List *restrictinfos);
extern List *select_outer_pathkeys_for_merge(PlannerInfo *root,
List *mergeclauses,
RelOptInfo *joinrel);
extern List *make_inner_pathkeys_for_merge(PlannerInfo *root,
List *mergeclauses,
List *outer_pathkeys);
extern List *truncate_useless_pathkeys(PlannerInfo *root,
RelOptInfo *rel,
List *pathkeys);
--- 216,233 ----
RestrictInfo *restrictinfo);
extern void update_mergeclause_eclasses(PlannerInfo *root,
RestrictInfo *restrictinfo);
! extern List *find_mergeclauses_for_outer_pathkeys(PlannerInfo *root,
! List *pathkeys,
! List *restrictinfos);
extern List *select_outer_pathkeys_for_merge(PlannerInfo *root,
List *mergeclauses,
RelOptInfo *joinrel);
extern List *make_inner_pathkeys_for_merge(PlannerInfo *root,
List *mergeclauses,
List *outer_pathkeys);
+ extern List *trim_mergeclauses_for_inner_pathkeys(PlannerInfo *root,
+ List *mergeclauses,
+ List *pathkeys);
extern List *truncate_useless_pathkeys(PlannerInfo *root,
RelOptInfo *rel,
List *pathkeys);
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index c50a206..4d5931d 100644
*** a/src/test/regress/expected/join.out
--- b/src/test/regress/expected/join.out
*************** where b.f1 = t.thousand and a.f1 = b.f1
*** 2291,2296 ****
--- 2291,2376 ----
(0 rows)
--
+ -- check a case where we formerly got confused by conflicting sort orders
+ -- in redundant merge join path keys
+ --
+ explain (costs off)
+ select * from
+ j1_tbl full join
+ (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl
+ on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k;
+ QUERY PLAN
+ -----------------------------------------------------------------
+ Merge Full Join
+ Merge Cond: ((j2_tbl.i = j1_tbl.i) AND (j2_tbl.k = j1_tbl.i))
+ -> Sort
+ Sort Key: j2_tbl.i DESC, j2_tbl.k
+ -> Seq Scan on j2_tbl
+ -> Sort
+ Sort Key: j1_tbl.i DESC
+ -> Seq Scan on j1_tbl
+ (8 rows)
+
+ select * from
+ j1_tbl full join
+ (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl
+ on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k;
+ i | j | t | i | k
+ ---+---+-------+---+----
+ | | | | 0
+ | | | |
+ | 0 | zero | |
+ | | null | |
+ 8 | 8 | eight | |
+ 7 | 7 | seven | |
+ 6 | 6 | six | |
+ | | | 5 | -5
+ | | | 5 | -5
+ 5 | 0 | five | |
+ 4 | 1 | four | |
+ | | | 3 | -3
+ 3 | 2 | three | |
+ 2 | 3 | two | 2 | 2
+ | | | 2 | 4
+ | | | 1 | -1
+ | | | 0 |
+ 1 | 4 | one | |
+ 0 | | zero | |
+ (19 rows)
+
+ --
+ -- a different check for handling of redundant sort keys in merge joins
+ --
+ explain (costs off)
+ select count(*) from
+ (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x
+ left join
+ (select * from tenk1 y order by y.unique2) y
+ on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2;
+ QUERY PLAN
+ ----------------------------------------------------------------------------------
+ Aggregate
+ -> Merge Left Join
+ Merge Cond: (x.thousand = y.unique2)
+ Join Filter: ((x.twothousand = y.hundred) AND (x.fivethous = y.unique2))
+ -> Sort
+ Sort Key: x.thousand, x.twothousand, x.fivethous
+ -> Seq Scan on tenk1 x
+ -> Materialize
+ -> Index Scan using tenk1_unique2 on tenk1 y
+ (9 rows)
+
+ select count(*) from
+ (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x
+ left join
+ (select * from tenk1 y order by y.unique2) y
+ on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2;
+ count
+ -------
+ 10000
+ (1 row)
+
+ --
-- Clean up
--
DROP TABLE t1;
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index fc84237..30dfde2 100644
*** a/src/test/regress/sql/join.sql
--- b/src/test/regress/sql/join.sql
*************** select a.f1, b.f1, t.thousand, t.tenthou
*** 417,422 ****
--- 417,453 ----
(select sum(f1) as f1 from int4_tbl i4b) b
where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous;
+ --
+ -- check a case where we formerly got confused by conflicting sort orders
+ -- in redundant merge join path keys
+ --
+ explain (costs off)
+ select * from
+ j1_tbl full join
+ (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl
+ on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k;
+
+ select * from
+ j1_tbl full join
+ (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl
+ on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k;
+
+ --
+ -- a different check for handling of redundant sort keys in merge joins
+ --
+ explain (costs off)
+ select count(*) from
+ (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x
+ left join
+ (select * from tenk1 y order by y.unique2) y
+ on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2;
+
+ select count(*) from
+ (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x
+ left join
+ (select * from tenk1 y order by y.unique2) y
+ on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2;
+
--
-- Clean up