MDEV-28602 Wrong result with outer join, merged derived table and viewbb-10.11-MDEV-28602-wrong-result-with-outer-join-merged-derived-table-and-view

    const item reference being mishandled in outer to inner join on
    filling in null values.

5 files changed, 131 insertions, 20 deletions

diff --git a/mysql-test/main/merge.result b/mysql-test/main/merge.result
index 230fcf48e9d..c478ee5f564 100644
--- a/mysql-test/main/merge.result
+++ b/mysql-test/main/merge.result
@@ -3929,3 +3929,52 @@ drop table tm, t;
 #
 # End of 10.8 tests
 #
+#
+# MDEV-28602 Wrong result with outer join, merged derived table and view
+#
+drop table if exists t1, t2;
+Warnings:
+Note	1051	Unknown table 'test.t1,test.t2'
+create table t1 (
+Election int(10) unsigned NOT NULL
+);
+insert into t1 (Election) values (1);
+create table t2 (
+VoteID int(10),
+ElectionID int(10),
+UserID int(10)
+);
+insert into t2 (ElectionID, UserID) values (2,  30), (3, 30);
+drop view if exists v1;
+Warnings:
+Note	4092	Unknown VIEW: 'test.v1'
+create view v1 as select * from t1
+left join ( select 'Y' AS Voted, ElectionID from t2 ) AS T 
+on T.ElectionID = t1.Election
+limit 9;
+select * from v1;
+Election	Voted	ElectionID
+1	NULL	NULL
+drop table t1, t2;
+drop view v1;
+drop view if exists v10, v20;
+Warnings:
+Note	4092	Unknown VIEW: 'test.v10,test.v20'
+drop table if exists t10, t20, t30;
+Warnings:
+Note	1051	Unknown table 'test.t10,test.t20,test.t30'
+create table t10 (a int);
+create table t20 (b int);
+insert into t10 values (1),(2);
+insert into t20 values (1),(3);
+create view v10 as select *, 'U' as u from t10 left join (select 'Y' as y, t20.b from t20) dt1 on t10.a= dt1.b limit 3;
+create table t30 (c int);
+insert into t30 values (1),(3);
+create view v20 as select * from t30 left join (select 'X' as x, v10.u, v10.y, v10.b from v10) dt2 on t30.c=dt2.b limit 6;
+select * from v20 limit 9;
+c	x	u	y	b
+1	X	U	Y	1
+3	NULL	NULL	NULL	NULL
+drop view v10, v20;
+drop table t10, t20, t30;
+# End of 10.11 tests
diff --git a/mysql-test/main/merge.test b/mysql-test/main/merge.test
index 0485f3ed1c3..05b05287e1b 100644
--- a/mysql-test/main/merge.test
+++ b/mysql-test/main/merge.test
@@ -2886,3 +2886,50 @@ drop table tm, t;
 --echo #
 --echo # End of 10.8 tests
 --echo #
+
+--echo #
+--echo # MDEV-28602 Wrong result with outer join, merged derived table and view
+--echo #
+
+drop table if exists t1, t2;
+create table t1 (
+  Election int(10) unsigned NOT NULL
+);
+
+insert into t1 (Election) values (1);
+ 
+create table t2 (
+  VoteID int(10),
+  ElectionID int(10),
+  UserID int(10)
+);
+
+insert into t2 (ElectionID, UserID) values (2,  30), (3, 30);
+#  INSERT INTO t2 (ElectionID, UserID) VALUES (1,  30);
+drop view if exists v1;
+create view v1 as select * from t1
+  left join ( select 'Y' AS Voted, ElectionID from t2 ) AS T 
+    on T.ElectionID = t1.Election
+limit 9;
+# limit X causes merge algorithm select as opposed to temp table
+select * from v1;
+drop table t1, t2;
+drop view v1;
+
+# and another contrived example showing a bit of heirarchy
+
+drop view if exists v10, v20;
+drop table if exists t10, t20, t30;
+create table t10 (a int);
+create table t20 (b int);
+insert into t10 values (1),(2);
+insert into t20 values (1),(3);
+create view v10 as select *, 'U' as u from t10 left join (select 'Y' as y, t20.b from t20) dt1 on t10.a= dt1.b limit 3;
+create table t30 (c int);
+insert into t30 values (1),(3);
+create view v20 as select * from t30 left join (select 'X' as x, v10.u, v10.y, v10.b from v10) dt2 on t30.c=dt2.b limit 6;
+select * from v20 limit 9;
+drop view v10, v20;
+drop table t10, t20, t30;
+
+--echo # End of 10.11 tests
diff --git a/sql/item.cc b/sql/item.cc
index 10a8127d06f..43aa1db13cc 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -7893,7 +7893,7 @@ Item_ref::Item_ref(THD *thd, Name_resolution_context *context_arg,
                    const LEX_CSTRING &field_name_arg,
                    bool alias_name_used_arg):
   Item_ident(thd, context_arg, null_clex_str, table_name_arg, field_name_arg),
-  ref(item), reference_trough_name(0)
+  ref(item), null_ref_table(0), reference_trough_name(0)
 {
   alias_name_used= alias_name_used_arg;
   /*
@@ -7942,7 +7942,7 @@ Item_ref::Item_ref(THD *thd, TABLE_LIST *view_arg, Item **item,
                    const LEX_CSTRING &field_name_arg,
                    bool alias_name_used_arg):
   Item_ident(thd, view_arg, field_name_arg),
-  ref(item), reference_trough_name(0)
+  ref(item), null_ref_table(0), reference_trough_name(0)
 {
   alias_name_used= alias_name_used_arg;
   /*
@@ -8588,6 +8588,12 @@ int Item_ref::save_in_field(Field *to, bool no_conversions)
     null_value= 0;
     return res;
   }
+  if (check_null_ref())
+  {
+    null_value= 1;
+    res= set_field_to_null_with_conversions(to, no_conversions);
+    return res;
+  }
   res= (*ref)->save_in_field(to, no_conversions);
   null_value= (*ref)->null_value;
   return res;
diff --git a/sql/item.h b/sql/item.h
index 2d598546b91..8684477b558 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -5531,18 +5531,42 @@ public:
 
 class Item_ref :public Item_ident
 {
+public:
+
+#define NO_NULL_TABLE (reinterpret_cast<TABLE *>(0x1))
+
+  void set_null_ref_table()
+  {
+      null_ref_table= NO_NULL_TABLE;
+  }
+
+  bool check_null_ref()
+  {
+    if( !null_ref_table )
+      return FALSE;
+    if (null_ref_table != NO_NULL_TABLE && null_ref_table->null_row)
+    {
+      null_value= 1;
+      return TRUE;
+    }
+    return FALSE;
+  }
+
+
 protected:
   void set_properties();
   bool set_properties_only; // the item doesn't need full fix_fields
 public:
   enum Ref_Type { REF, DIRECT_REF, VIEW_REF, OUTER_REF, AGGREGATE_REF };
   Item **ref;
+  TABLE *null_ref_table;
   bool reference_trough_name;
   Item_ref(THD *thd, Name_resolution_context *context_arg,
            const LEX_CSTRING &db_arg, const LEX_CSTRING &table_name_arg,
            const LEX_CSTRING &field_name_arg):
     Item_ident(thd, context_arg, db_arg, table_name_arg, field_name_arg),
-    set_properties_only(0), ref(0), reference_trough_name(1) {}
+    set_properties_only(0), ref(0), null_ref_table(NULL),
+    reference_trough_name(1) {}
   Item_ref(THD *thd, Name_resolution_context *context_arg,
            const LEX_CSTRING &field_name_arg)
    :Item_ref(thd, context_arg, null_clex_str, null_clex_str, field_name_arg)
@@ -6002,9 +6026,6 @@ class Item_direct_view_ref :public Item_direct_ref
 {
   Item_equal *item_equal;
   TABLE_LIST *view;
-  TABLE *null_ref_table;
-
-#define NO_NULL_TABLE (reinterpret_cast<TABLE *>(0x1))
 
   void set_null_ref_table()
   {
@@ -6013,17 +6034,6 @@ class Item_direct_view_ref :public Item_direct_ref
       null_ref_table= NO_NULL_TABLE;
   }
 
-  bool check_null_ref()
-  {
-    DBUG_ASSERT(null_ref_table);
-    if (null_ref_table != NO_NULL_TABLE && null_ref_table->null_row)
-    {
-      null_value= 1;
-      return TRUE;
-    }
-    return FALSE;
-  }
-
 public:
   Item_direct_view_ref(THD *thd, Name_resolution_context *context_arg,
                        Item **item,
@@ -6031,8 +6041,7 @@ public:
                        LEX_CSTRING &field_name_arg,
                        TABLE_LIST *view_arg):
     Item_direct_ref(thd, context_arg, item, table_name_arg, field_name_arg),
-    item_equal(0), view(view_arg),
-    null_ref_table(NULL)
+    item_equal(0), view(view_arg)
   {
     if (fixed())
       set_null_ref_table();
diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
index 620c52a3f40..65bedc3d337 100644
--- a/sql/sql_join_cache.cc
+++ b/sql/sql_join_cache.cc
@@ -2622,7 +2622,7 @@ enum_nested_loop_state JOIN_CACHE::join_null_complements(bool skip_last)
       get_record();
       /* The outer row is complemented by nulls for each inner table */
       restore_record(join_tab->table, s->default_values);
-      mark_as_null_row(join_tab->table);  
+      mark_as_null_row(join_tab->table);
       rc= generate_full_extensions(get_curr_rec());
       if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
         goto finish;