MDEV-18701: Wrong result from query that uses INTERSECT after UNION ALL

Recalculate distinct pointer if we cut chain of SELECTs

5 files changed, 69 insertions, 13 deletions

diff --git a/mysql-test/main/intersect.result b/mysql-test/main/intersect.result
index f2d36e1d0e9..bd88243f151 100644
--- a/mysql-test/main/intersect.result
+++ b/mysql-test/main/intersect.result
@@ -833,3 +833,36 @@ c1
 3
 drop table t12,t13,t234;
 # End of 10.3 tests
+#
+# MDEV-18701: Wrong result from query that uses INTERSECT after UNION ALL
+#
+create table t1 (a int);
+insert into t1 values (3), (1), (7), (3), (2), (7), (4);
+create table t2 (a int);
+insert into t2 values (4), (5), (9), (1), (8), (9);
+create table t3 (a int);
+insert into t3 values (8), (1), (8), (2), (3), (7), (2);
+select * from t1 where a > 4
+union all 
+select * from t2 where a < 5
+intersect
+select * from t3 where a < 5;
+a
+7
+7
+1
+explain extended
+select * from t1 where a > 4
+union all 
+select * from t2 where a < 5
+intersect
+select * from t3 where a < 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
+4	UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	6	100.00	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+3	INTERSECT	t3	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
+NULL	INTERSECT RESULT	<intersect2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 union all /* select#4 */ select `__4`.`a` AS `a` from (/* select#2 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where `test`.`t2`.`a` < 5 intersect /* select#3 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 5) `__4`
+drop table t1,t2,t3;
diff --git a/mysql-test/main/intersect.test b/mysql-test/main/intersect.test
index 0a68cab21f0..616a833ea3c 100644
--- a/mysql-test/main/intersect.test
+++ b/mysql-test/main/intersect.test
@@ -321,3 +321,30 @@ select * from t13 union select * from t234 intersect select * from t12;
 drop table t12,t13,t234;
 
 --echo # End of 10.3 tests
+
+--echo #
+--echo # MDEV-18701: Wrong result from query that uses INTERSECT after UNION ALL
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (1), (7), (3), (2), (7), (4);
+create table t2 (a int);
+insert into t2 values (4), (5), (9), (1), (8), (9);
+create table t3 (a int);
+insert into t3 values (8), (1), (8), (2), (3), (7), (2);
+
+
+select * from t1 where a > 4
+union all 
+select * from t2 where a < 5
+intersect
+select * from t3 where a < 5;
+
+explain extended
+select * from t1 where a > 4
+union all 
+select * from t2 where a < 5
+intersect
+select * from t3 where a < 5;
+
+drop table t1,t2,t3;
diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index 454356bb5fb..1d485af4a4d 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -366,6 +366,7 @@ values (1,2);
 1	2
 1	2
 3	4
+1	2
 # combination of different structures that uses VALUES structures : UNION + UNION ALL
 values (1,2),(3,4)
 union all
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index c204f96f303..89b330f263a 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -5427,6 +5427,7 @@ SELECT_LEX *LEX::wrap_select_chain_into_derived(SELECT_LEX *sel)
      DBUG_RETURN(NULL);
   Name_resolution_context *context= &dummy_select->context;
   dummy_select->automatic_brackets= FALSE;
+  sel->distinct= TRUE; // First select has not this attribute (safety)
 
   if (!(unit= dummy_select->attach_selects_chain(sel, context)))
     DBUG_RETURN(NULL);
@@ -8838,19 +8839,10 @@ void st_select_lex_unit::reset_distinct()
 }
 
 
-void st_select_lex_unit::fix_distinct(st_select_lex_unit *new_unit)
+void st_select_lex_unit::fix_distinct()
 {
-  if (union_distinct)
-  {
-    if (this != union_distinct->master_unit())
-    {
-      DBUG_ASSERT(new_unit == union_distinct->master_unit());
-      new_unit->union_distinct= union_distinct;
-      reset_distinct();
-    }
-    else
-      new_unit->reset_distinct();
-  }
+  if (union_distinct && this != union_distinct->master_unit())
+    reset_distinct();
 }
 
 
@@ -9089,6 +9081,7 @@ bool LEX::parsed_unit_in_brackets(SELECT_LEX_UNIT *unit)
     /* There is a priority jump starting from first_in_nest */
     if (create_priority_nest(first_in_nest) == NULL)
       return true;
+    unit->fix_distinct();
   }
   push_select(unit->fake_select_lex);
   return false;
@@ -9236,6 +9229,7 @@ SELECT_LEX_UNIT *LEX::parsed_select_expr_cont(SELECT_LEX_UNIT *unit,
       /* There is a priority jump starting from first_in_nest */
       if ((last= create_priority_nest(first_in_nest)) == NULL)
         return NULL;
+      unit->fix_distinct();
     }
     sel1->first_nested= last->first_nested;
   }
@@ -9273,6 +9267,7 @@ bool LEX::parsed_body_unit(SELECT_LEX_UNIT *unit)
     /* There is a priority jump starting from first_in_nest */
     if (create_priority_nest(first_in_nest) == NULL)
       return true;
+    unit->fix_distinct();
   }
   push_select(unit->fake_select_lex);
   return false;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index b78a010d4b7..61816bee803 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -995,7 +995,7 @@ public:
   unit_common_op common_op();
 
   void reset_distinct();
-  void fix_distinct(st_select_lex_unit *new_unit);
+  void fix_distinct();
 
   void register_select_chain(SELECT_LEX *first_sel);