MDEV-29382: Query returns wrong number of recordsbb-10.9-mdev29382

The issue occurred when DuplicateWeedout optimization was applied
together with Join Buffer, and also there was an SJ-Materialization-lookup
table in the join prefix.

DuplicateWeedout optimization includes the rowid of SJ-Materialization
temptable into its Duplicate checks.
However, Join Buffering code did not save/restore the rowids of
SJ-Materialization temptables. This meant the check could miss some
duplicates.

The fix makes the Join Buffering code to save/restore rowids for
SJ-Materialization temp.tables.

4 files changed, 182 insertions, 15 deletions

diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result
index 242b01f8955..6a67ab634bf 100644
--- a/mysql-test/main/subselect_innodb.result
+++ b/mysql-test/main/subselect_innodb.result
@@ -728,4 +728,72 @@ drop table t1;
 #
 # End of 10.4 tests
 #
+#
+# MDEV-29382: Query returns wrong number of records
+#
+create table t1     (parentid int, value1 int) engine=innodb;
+create table t2      (parentid int, childid int) engine=innodb;
+create index ix_childindex on t2 (parentid);
+insert into t1 (parentid, value1)
+values(1, 1),
+(2, null),
+(3, 3),
+(4, null),
+(5, 5),
+(6, 6),
+(7, 1);
+insert into t2 (parentid, childid)
+values(1, 11),
+(2, 21),
+(2, 22),
+(3, 31),
+(3, 32),
+(3, 33),
+(4, 41),
+(4, 42),
+(4, 43),
+(4, 44),
+(6, 61),
+(6, 62),
+(6, 63),
+(6, 64),
+(6, 65),
+(6, 66),
+(7, 77);
+select
+count(p_1.parentid)
+from
+t1 p_2
+inner join t2 c_1 on p_2.parentid = c_1.parentid
+inner join t1 p   on p_2.parentid = p.parentid
+inner join t1 p_1 on p_2.parentid = p_1.parentid
+inner join t2 c4   on c4.parentid = p_1.parentid
+where
+exists ( select *
+from t1 p_3
+where
+exists ( select *
+from t2 c_2
+where
+c_2.parentid > 1
+and c_2.parentid = p_3.parentid
+)
+and p_3.parentid = p_1.parentid )
+and c4.parentid % 2 = 0
+and exists ( select *
+from t2 c_3
+where
+c_3.parentid > 1
+and c_3.parentid = p.parentid
+)
+and c_1.parentid > 1
+and exists ( select *
+from t2 c_4
+where
+c_4.parentid > 1
+and c_4.parentid = p_2.parentid
+);
+count(p_1.parentid)
+56
+drop table t1,t2;
 SET GLOBAL innodb_stats_persistent = @saved_stats_persistent;
diff --git a/mysql-test/main/subselect_innodb.test b/mysql-test/main/subselect_innodb.test
index 493a7425ea8..3fb04d1c694 100644
--- a/mysql-test/main/subselect_innodb.test
+++ b/mysql-test/main/subselect_innodb.test
@@ -715,4 +715,76 @@ drop table t1;
 --echo # End of 10.4 tests
 --echo #
 
+--echo #
+--echo # MDEV-29382: Query returns wrong number of records
+--echo #
+
+create table t1     (parentid int, value1 int) engine=innodb;
+
+create table t2      (parentid int, childid int) engine=innodb;
+create index ix_childindex on t2 (parentid);
+
+insert into t1 (parentid, value1)
+values(1, 1),
+(2, null),
+(3, 3),
+(4, null),
+(5, 5),
+(6, 6),
+(7, 1);
+
+insert into t2 (parentid, childid)
+values(1, 11),
+(2, 21),
+(2, 22),
+(3, 31),
+(3, 32),
+(3, 33),
+(4, 41),
+(4, 42),
+(4, 43),
+(4, 44),
+(6, 61),
+(6, 62),
+(6, 63),
+(6, 64),
+(6, 65),
+(6, 66),
+(7, 77);
+
+select
+  count(p_1.parentid)
+from
+  t1 p_2
+  inner join t2 c_1 on p_2.parentid = c_1.parentid
+  inner join t1 p   on p_2.parentid = p.parentid
+  inner join t1 p_1 on p_2.parentid = p_1.parentid
+  inner join t2 c4   on c4.parentid = p_1.parentid
+where
+  exists ( select *
+           from t1 p_3
+           where
+             exists ( select *
+                      from t2 c_2
+                      where
+                        c_2.parentid > 1
+                        and c_2.parentid = p_3.parentid
+                    )
+             and p_3.parentid = p_1.parentid )
+  and c4.parentid % 2 = 0
+  and exists ( select *
+               from t2 c_3
+               where
+                 c_3.parentid > 1
+                and c_3.parentid = p.parentid
+              )
+  and c_1.parentid > 1
+  and exists ( select *
+               from t2 c_4
+               where
+                 c_4.parentid > 1
+                 and c_4.parentid = p_2.parentid
+              );
+drop table t1,t2;
+
 SET GLOBAL innodb_stats_persistent = @saved_stats_persistent;
diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
index 620c52a3f40..0188cc9ac8f 100644
--- a/sql/sql_join_cache.cc
+++ b/sql/sql_join_cache.cc
@@ -219,8 +219,10 @@ void JOIN_CACHE::calc_record_fields()
   /*
     The following loop will get inside SJM nests, because data may be unpacked
     to sjm-inner tables.
+    We also cover SJM roots (aka bush roots), as their rowids may need to be
+    saved and restored.
   */
-  for (; tab != join_tab ; tab= next_linear_tab(join, tab, WITHOUT_BUSH_ROOTS))
+  for (; tab != join_tab ; tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS))
   {	    
     tab->calc_used_field_length(FALSE);
     flag_fields+= MY_TEST(tab->used_null_fields || tab->used_uneven_bit_fields);
@@ -585,7 +587,7 @@ void JOIN_CACHE::create_remaining_fields()
   CACHE_FIELD **copy_ptr= blob_ptr+data_field_ptr_count;
 
   for (tab= start_tab; tab != join_tab; 
-       tab= next_linear_tab(join, tab, WITHOUT_BUSH_ROOTS))
+       tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS))
   {
     MY_BITMAP *rem_field_set;
     TABLE *table= tab->table;
@@ -599,10 +601,21 @@ void JOIN_CACHE::create_remaining_fields()
       rem_field_set= &table->tmp_set;
     }  
 
-    length+= add_table_data_fields_to_join_cache(tab, rem_field_set,
-                                                 &data_field_count, &copy,
-                                                 &data_field_ptr_count,
-                                                 &copy_ptr);
+    /*
+      Do not add columns for SJM nests:
+       - for SJ-Materialization-Scan, the columns are unpacked to the
+         source tables.
+       - for SJ-Materialization-Lookup, equality substitution should
+         make sure all references are made to the source of data for
+         making the lookup.
+    */
+    if (!tab->bush_children)
+    {
+      length+= add_table_data_fields_to_join_cache(tab, rem_field_set,
+                                                   &data_field_count, &copy,
+                                                   &data_field_ptr_count,
+                                                   &copy_ptr);
+    }
   
     /* SemiJoinDuplicateElimination: allocate space for rowid if needed */
     if (tab->keep_current_rowid)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 3b0061fcf8e..1cb57e17a83 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -10263,6 +10263,27 @@ void JOIN_TAB::calc_used_field_length(bool max_fl)
   MY_BITMAP *read_set= table->read_set;
 
   uneven_bit_fields= null_fields= blobs= fields= rec_length=0;
+
+  /* Take into account that DuplicateElimination may need to store rowid */
+  uint rowid_add_size= 0;
+
+  if (keep_current_rowid)
+  {
+    rowid_add_size= table->file->ref_length;
+    rec_length += rowid_add_size;
+    fields++;
+  }
+
+  if (bush_children)
+  {
+    /*
+      This JOIN_TAB represents an SJ-Materialization nest. The table is
+      the materialized temptable. For such tables, we may need their rowid
+      (taken care of above). We don't need any columns.
+    */
+    goto save_and_exit;
+  }
+
   for (f_ptr=table->field ; (field= *f_ptr) ; f_ptr++)
   {
     if (bitmap_is_set(read_set, field->field_index))
@@ -10284,14 +10305,6 @@ void JOIN_TAB::calc_used_field_length(bool max_fl)
   if (table->maybe_null)
     rec_length+=sizeof(my_bool);
 
-  /* Take into account that DuplicateElimination may need to store rowid */
-  uint rowid_add_size= 0;
-  if (keep_current_rowid)
-  {
-    rowid_add_size= table->file->ref_length; 
-    rec_length += rowid_add_size;
-    fields++;
-  }
 
   if (max_fl)
   {
@@ -10308,7 +10321,8 @@ void JOIN_TAB::calc_used_field_length(bool max_fl)
   } 
   else if (table->file->stats.mean_rec_length)
     set_if_smaller(rec_length, table->file->stats.mean_rec_length + rowid_add_size);
-      
+
+save_and_exit:
   used_fields=fields;
   used_fieldlength=rec_length;
   used_blobs=blobs;