MDEV-27624 Wrong result for left join, 'not exists' and join buffersbb-10.4-MDEV-27624

Cause:
When linked join buffers are employed to process nested outer joins
there is a problem with applying the 'not exists' optimization:
the match flag used for outer joins is not propagated among linked
join buffers. This leads to incorrect results when the 'not exists'
optimization is applied to a nested outer join.

Solution:
Disable applying the 'not exists' optimization for linked join buffers

1 files changed, 16 insertions, 0 deletions

diff --git a/sql/sql_join_cache.h b/sql/sql_join_cache.h
index d0bf4761f65..eb943b28be2 100644
--- a/sql/sql_join_cache.h
+++ b/sql/sql_join_cache.h
@@ -531,6 +531,22 @@ protected:
   /* Check matching to a partial join record from the join buffer */
   bool check_match(uchar *rec_ptr);
 
+  /*
+    Determine whether it is enough to check only the first match for
+    the table and skip other possible matches. This is used for semi-joins
+    and 'not exists' optimization.
+
+    NOTE: 'not exists' optimization is not yet implemented for linked
+    join buffers so is not applicable if prev_cache is not NULL
+  */
+  bool check_only_first_match(JOIN_TAB *tab)
+  {
+    return tab->is_inner_table_of_semi_join_with_first_match() ||
+           (tab->is_inner_table_of_outer_join() &&
+            tab->table->reginfo.not_exists_optimize &&
+            !prev_cache);
+  }
+
   /* 
     This constructor creates an unlinked join cache. The cache is to be
     used to join table 'tab' to the result of joining the previous tables