v1-0001-Fix-right-anti-joins-when-the-inner-relation-is-proven-unique.patch

From fa4b30cad222e474013d6fd03996d0e7c8c8520d Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 25 Jun 2024 23:21:10 +0900
Subject: [PATCH v1] Fix right-anti-joins when the inner relation is proven
 unique

For an inner_unique join, we always assume that the executor will stop
scanning for matches after the first match.  Therefore, for a mergejoin
that is inner_unique and whose mergeclauses are sufficient to identify a
match, we set the skip_mark_restore flag to true, indicating that the
executor need not do mark/restore calls.  However, merge-right-anti-join
does not get this memo and continues scanning the inner side for matches
after the first match.  If there are duplicates in the outer scan, we
may incorrectly skip matching some inner tuples, which can lead to wrong
results.

Here we fix this issue by ensuring that merge-right-anti-join also
advances to next outer tuple after the first match in inner_unique
cases.  This also saves cycles by avoiding unnecessary scanning of inner
tuples after the first match.

Although hash-right-anti-join does not suffer from this wrong results
issue, we apply the same change to it as well, to help save cycles for
the same reason.
---
 src/backend/executor/nodeHashjoin.c  | 21 +++++++++++----------
 src/backend/executor/nodeMergejoin.c | 21 +++++++++++----------
 2 files changed, 22 insertions(+), 20 deletions(-)

diff --git a/src/backend/executor/nodeHashjoin.c b/src/backend/executor/nodeHashjoin.c
index dbf114cd5e..f7de96a368 100644
--- a/src/backend/executor/nodeHashjoin.c
+++ b/src/backend/executor/nodeHashjoin.c
@@ -565,20 +565,21 @@ ExecHashJoinImpl(PlanState *pstate, bool parallel)
 					}
 
 					/*
-					 * In a right-antijoin, we never return a matched tuple.
-					 * And we need to stay on the current outer tuple to
-					 * continue scanning the inner side for matches.
+					 * If we only need to consider the first matching inner
+					 * tuple, then advance to next outer tuple after we've
+					 * processed this one.
 					 */
-					if (node->js.jointype == JOIN_RIGHT_ANTI)
-						continue;
+					if (node->js.single_match)
+						node->hj_JoinState = HJ_NEED_NEW_OUTER;
 
 					/*
-					 * If we only need to join to the first matching inner
-					 * tuple, then consider returning this one, but after that
-					 * continue with next outer tuple.
+					 * In a right-antijoin, we never return a matched tuple.
+					 * If it's not an inner_unique join, we need to stay on the
+					 * current outer tuple to continue scanning the inner side
+					 * for matches.
 					 */
-					if (node->js.single_match)
-						node->hj_JoinState = HJ_NEED_NEW_OUTER;
+					if (node->js.jointype == JOIN_RIGHT_ANTI)
+						continue;
 
 					if (otherqual == NULL || ExecQual(otherqual, econtext))
 						return ExecProject(node->js.ps.ps_ProjInfo);
diff --git a/src/backend/executor/nodeMergejoin.c b/src/backend/executor/nodeMergejoin.c
index 4fb34e3537..1ea962704e 100644
--- a/src/backend/executor/nodeMergejoin.c
+++ b/src/backend/executor/nodeMergejoin.c
@@ -805,20 +805,21 @@ ExecMergeJoin(PlanState *pstate)
 					}
 
 					/*
-					 * In a right-antijoin, we never return a matched tuple.
-					 * And we need to stay on the current outer tuple to
-					 * continue scanning the inner side for matches.
+					 * If we only need to consider the first matching inner
+					 * tuple, then advance to next outer tuple after we've
+					 * processed this one.
 					 */
-					if (node->js.jointype == JOIN_RIGHT_ANTI)
-						break;
+					if (node->js.single_match)
+						node->mj_JoinState = EXEC_MJ_NEXTOUTER;
 
 					/*
-					 * If we only need to join to the first matching inner
-					 * tuple, then consider returning this one, but after that
-					 * continue with next outer tuple.
+					 * In a right-antijoin, we never return a matched tuple.
+					 * If it's not an inner_unique join, we need to stay on the
+					 * current outer tuple to continue scanning the inner side
+					 * for matches.
 					 */
-					if (node->js.single_match)
-						node->mj_JoinState = EXEC_MJ_NEXTOUTER;
+					if (node->js.jointype == JOIN_RIGHT_ANTI)
+						break;
 
 					qualResult = (otherqual == NULL ||
 								  ExecQual(otherqual, econtext));
-- 
2.43.0