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

6 files changed, 95 insertions, 23 deletions

diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result
index b7fcb55e4fe..dba65b490a0 100644
--- a/mysql-test/main/join_outer.result
+++ b/mysql-test/main/join_outer.result
@@ -2825,5 +2825,25 @@ WHERE t3.pk IN (2);
 1
 drop view v4;
 drop table t1,t2,t3,t4;
+#
+#  MDEV-27624 Wrong result for nested left join,
+#    'not exists' optimization and join buffers
+#
+CREATE TABLE t1 (a INT NOT NULL, b INT, c INT);
+INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1);
+CREATE TABLE t2(a INT NOT NULL);
+INSERT INTO t2 VALUES (1), (2);
+CREATE TABLE t3(a INT not null, b INT);
+INSERT INTO t3 VALUES (1, 1), (2, 1), (3, 1);
+SELECT *
+FROM (t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a = t3.b)
+ON (t2.a = 1 AND (
+t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL)
+)
+)
+WHERE t1.c = 1 AND t3.a is NULL;
+a	b	c	a	a	b
+1	3	1	NULL	NULL	NULL
+DROP TABLE t1, t2, t3;
 # end of 10.3 tests
 SET optimizer_switch=@org_optimizer_switch;
diff --git a/mysql-test/main/join_outer.test b/mysql-test/main/join_outer.test
index 82c7b265b56..304a49097c7 100644
--- a/mysql-test/main/join_outer.test
+++ b/mysql-test/main/join_outer.test
@@ -2336,6 +2336,30 @@ WHERE t3.pk IN (2);
 drop view v4;
 drop table t1,t2,t3,t4;
 
+--echo #
+--echo #  MDEV-27624 Wrong result for nested left join,
+--echo #    'not exists' optimization and join buffers
+--echo #
+
+CREATE TABLE t1 (a INT NOT NULL, b INT, c INT);
+INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1);
+
+CREATE TABLE t2(a INT NOT NULL);
+INSERT INTO t2 VALUES (1), (2);
+
+CREATE TABLE t3(a INT not null, b INT);
+INSERT INTO t3 VALUES (1, 1), (2, 1), (3, 1);
+
+SELECT *
+  FROM (t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a = t3.b)
+             ON (t2.a = 1 AND (
+                 t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL)
+         )
+      )
+WHERE t1.c = 1 AND t3.a is NULL;
+
+DROP TABLE t1, t2, t3;
+
 --echo # end of 10.3 tests
 
 SET optimizer_switch=@org_optimizer_switch;
diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result
index 3cb846426fe..ddf247b024d 100644
--- a/mysql-test/main/join_outer_jcl6.result
+++ b/mysql-test/main/join_outer_jcl6.result
@@ -2832,5 +2832,25 @@ WHERE t3.pk IN (2);
 1
 drop view v4;
 drop table t1,t2,t3,t4;
+#
+#  MDEV-27624 Wrong result for nested left join,
+#    'not exists' optimization and join buffers
+#
+CREATE TABLE t1 (a INT NOT NULL, b INT, c INT);
+INSERT INTO t1 VALUES (1,1,1), (1,2,1), (1,3,1);
+CREATE TABLE t2(a INT NOT NULL);
+INSERT INTO t2 VALUES (1), (2);
+CREATE TABLE t3(a INT not null, b INT);
+INSERT INTO t3 VALUES (1, 1), (2, 1), (3, 1);
+SELECT *
+FROM (t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a = t3.b)
+ON (t2.a = 1 AND (
+t3.b = t1.a AND t3.a > t1.b OR t3.a is NULL)
+)
+)
+WHERE t1.c = 1 AND t3.a is NULL;
+a	b	c	a	a	b
+1	3	1	NULL	NULL	NULL
+DROP TABLE t1, t2, t3;
 # end of 10.3 tests
 SET optimizer_switch=@org_optimizer_switch;
diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
index f072a675e31..fbe1bfed9bf 100644
--- a/sql/sql_join_cache.cc
+++ b/sql/sql_join_cache.cc
@@ -2055,7 +2055,7 @@ bool JOIN_CACHE::skip_if_not_needed_match()
   if (prev_cache)
     offset+= prev_cache->get_size_of_rec_offset();
 
-  if (!join_tab->check_only_first_match())
+  if (!check_only_first_match(join_tab))
     return FALSE;
 
   match_fl= get_match_flag_by_pos(pos+offset);
@@ -2287,8 +2287,8 @@ enum_nested_loop_state JOIN_CACHE::join_matching_records(bool skip_last)
   int error;
   enum_nested_loop_state rc= NESTED_LOOP_OK;
   join_tab->table->null_row= 0;
-  bool check_only_first_match=
-    join_tab->check_only_first_match() &&
+  bool check_only_first_match_actually=
+    check_only_first_match(join_tab) &&
     (!join_tab->first_inner ||                            // semi-join case
      join_tab->first_inner == join_tab->first_unmatched); // outer join case
   bool outer_join_first_inner= join_tab->is_first_inner_for_outer_join();
@@ -2357,7 +2357,7 @@ enum_nested_loop_state JOIN_CACHE::join_matching_records(bool skip_last)
         Also those records that must be null complemented are not considered
         as candidates for matches.
       */
-      if ((!check_only_first_match && !outer_join_first_inner) ||
+      if ((!check_only_first_match_actually && !outer_join_first_inner) ||
           !skip_next_candidate_for_match(rec_ptr))
       {
 	read_next_candidate_for_match(rec_ptr);
@@ -2535,7 +2535,7 @@ inline bool JOIN_CACHE::check_match(uchar *rec_ptr)
   do
   {
     set_match_flag_if_none(first_inner, rec_ptr);
-    if (first_inner->check_only_first_match() &&
+    if (check_only_first_match(first_inner) &&
         !join_tab->first_inner)
       DBUG_RETURN(TRUE);
     /* 
@@ -3821,7 +3821,7 @@ uchar *JOIN_CACHE_BNLH::get_next_candidate_for_match()
 
 bool JOIN_CACHE_BNLH::skip_next_candidate_for_match(uchar *rec_ptr)
 {
- return  join_tab->check_only_first_match() &&
+  return check_only_first_match(join_tab) &&
           (get_match_flag_by_pos(rec_ptr) == MATCH_FOUND);
 }
 
@@ -4241,7 +4241,7 @@ RETURN VALUE
 
 bool JOIN_CACHE_BKA::skip_next_candidate_for_match(uchar *rec_ptr)
 {
-  return join_tab->check_only_first_match() &&
+  return check_only_first_match(join_tab) &&
     (get_match_flag_by_pos(rec_ptr) == MATCH_FOUND);
 }
 
@@ -4297,12 +4297,11 @@ RETURN VALUE
 int JOIN_CACHE_BKA::init(bool for_explain)
 {
   int res;
-  bool check_only_first_match= join_tab->check_only_first_match();
 
   RANGE_SEQ_IF rs_funcs= { bka_range_seq_key_info,
     bka_range_seq_init,
     bka_range_seq_next,
-    check_only_first_match ?  bka_range_seq_skip_record : 0,
+    check_only_first_match(join_tab) ? bka_range_seq_skip_record : 0,
     bka_skip_index_tuple };
 
   DBUG_ENTER("JOIN_CACHE_BKA::init");
@@ -4689,16 +4688,15 @@ bool JOIN_CACHE_BKAH::prepare_look_for_matches(bool skip_last)
 
 int JOIN_CACHE_BKAH::init(bool for_explain)
 {
-  bool check_only_first_match= join_tab->check_only_first_match();
-
   no_association= MY_TEST(mrr_mode & HA_MRR_NO_ASSOCIATION);
 
-  RANGE_SEQ_IF rs_funcs= { bka_range_seq_key_info,
-                           bkah_range_seq_init,
-                           bkah_range_seq_next,
-                           check_only_first_match && !no_association ?
-                             bkah_range_seq_skip_record : 0,
-                           bkah_skip_index_tuple };
+  RANGE_SEQ_IF rs_funcs= {
+    bka_range_seq_key_info,
+    bkah_range_seq_init,
+    bkah_range_seq_next,
+    check_only_first_match(join_tab) && !no_association ?
+      bkah_range_seq_skip_record : 0,
+    bkah_skip_index_tuple };
 
   DBUG_ENTER("JOIN_CACHE_BKAH::init");
 
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 
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 9a6237f00a6..4afb845afe4 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -572,12 +572,6 @@ typedef struct st_join_table {
   {
     return is_first_inner_for_outer_join() || first_sj_inner_tab == this ; 
   }
-  bool check_only_first_match()
-  {
-    return is_inner_table_of_semi_join_with_first_match() ||
-           (is_inner_table_of_outer_join() &&
-            table->reginfo.not_exists_optimize);
-  }
   bool is_last_inner_table()
   {
     return (first_inner && first_inner->last_inner == this) ||