MDEV-27132 Wrong result from query when using split optimization

This bug could affect queries with a grouping derived table containing
equalities in the where clause of its specification if the optimizer
chose to apply split optimization to access the derived table. In such
cases wrong results could be returned from the queries.
When the optimizer considers a possibility of using split optimization
to a derived table it injects equalities joining the derived table with
other tables into the where condition of the derived table. After the join
order for the execution using split optimization has been chosen as the
cheapest the injected equalities that are not used to access the derived
table are removed from the where condition of the derived table.
For this removal the optimizer looks through the conjuncts of the where
condition of the derived table, fetches the equalities and checks whether
they belong to the list of injected equalities.
As the injection of the list was performed just by the insertion of it
into the list of top level AND condition of the where condition some extra
conjuncts from the where condition could be automatically attached to the
end of the list of injected equalities. If such attached conjunct happened
to be an equality predicate it was removed from the where condition of the
derived table and thus lost for checking at the execution phase.
The bug has been fixed by injecting of a shallow copy of the list of the
pushed equalities rather than the list itself leaving the latter intact.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

3 files changed, 272 insertions, 1 deletions

diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index d2c116913f4..76313486839 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -17531,4 +17531,175 @@ id	a	a	id
 21	2	2	2
 deallocate prepare stmt;
 drop table t1,t2,t3;
+#
+# MDEV-MDEV-27132: Splittable derived with equality in WHERE
+#
+CREATE TABLE t1 (
+id int PRIMARY KEY
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(-1),(2070),(4826),(4827),(4828),(4829),(4830),(4831),(4832),(4833),(4834),
+(4835),(4836),(4837),(4838),(4839),(4840),(4841),(4842),(4843),(4844),
+(4845),(4846),(4847),(4848),(4849),(4850),(4851),(4852),(4853),(4854),
+(4855),(4856),(4857),(4858),(4859),(4860),(4861),(4862),(4863),(4864),
+(4865),(4866),(4867),(4868),(4869),(4870),(4871),(4872),(4873),(4874),
+(4875),(4876);
+CREATE TABLE t2 (
+id int PRIMARY KEY AUTO_INCREMENT,
+deleted int(1),
+t1_id int,
+email varchar(255),
+reporting_person int(1),
+KEY t1_id (t1_id)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(1,0,2064,'1test@test.ee',1),(2,1626095588,2066,'2test@test.ee',1),
+(3,0,2066,'3test@test.ee',1),(4,0,2068,'4test@test.ee',1),
+(5,0,2068,'5test@test.ee',1),(6,0,2069,'6test@test.ee',1),(7,0,2070,'',0),
+(8,0,2070,'',0),(9,0,2071,'',0),(10,0,2071,'',0),(11,0,2072,'',0),
+(12,0,2072,'',0),(13,0,2072,'13test@test.ee',1),(14,0,2073,'14test@test.ee',1),
+(15,0,2074,'15test@test.ee',1),(16,0,2075,'16test@test.ee',1),(17,0,2075,'',0),
+(18,0,2075,'',0),(19,0,2076,'19test@test.ee',1),(20,0,2077,'',0),
+(21,0,2078,'21test@test.ee',1),(22,0,2078,'22test@test.ee',1);
+INSERT INTO t2(deleted, t1_id, email, reporting_person)
+SELECT deleted, t1_id, email, reporting_person FROM t2;
+INSERT INTO t2(deleted, t1_id, email, reporting_person)
+SELECT deleted, t1_id+10000, email, reporting_person FROM t2;
+INSERT INTO t2(deleted, t1_id, email, reporting_person)
+SELECT deleted, t1_id+20000, email, reporting_person FROM t2;
+INSERT INTO t2(deleted, t1_id, email, reporting_person)
+SELECT deleted, t1_id+40000, email, reporting_person FROM t2;
+INSERT INTO t2(deleted, t1_id, email, reporting_person)
+SELECT deleted, t1_id+80000, email, reporting_person FROM t2;
+INSERT INTO t2(deleted, t1_id, email, reporting_person)
+SELECT deleted, t1_id+160000, email, reporting_person FROM t2;
+CREATE TABLE t3 (
+id int PRIMARY KEY,
+deleted int,
+t1_id int,
+YEAR int(4),
+quarter int(1),
+KEY t1_id (t1_id,year,quarter)
+) ENGINE=MyISAM;
+INSERT INTO t3 VALUES
+(1,0,3885,2020,1),(2,0,2064,2020,1),(3,1611670734,2225,2020,1),
+(4,0,2070,2020,1),(5,1611055981,2095,2020,1),(6,1610970096,2102,2020,1),
+(7,0,3974,2020,1),(153,1609851928,3892,2020,2),(154,0,3885,2020,2),
+(155,0,2064,2020,2),(156,1611670717,2225,2020,2),(157,0,2070,2020,2),
+(317,0,2257,2020,2),(318,0,3885,2020,3),(319,0,2064,2020,3),
+(320,1611670709,2225,2020,3),(321,0,2070,2020,3),(322,0,2095,2020,3),
+(323,0,2102,2020,3),(324,0,3974,2020,3),(325,0,3886,2020,3),
+(326,1609939963,2104,2020,3),(327,0,3887,2020,3),(328,0,3888,2020,3),
+(329,0,2148,2020,3),(330,0,3889,2020,3),(331,0,3890,2020,3),
+(332,0,2179,2020,3),(333,0,2115,2020,3),(334,0,2193,2020,3),
+(335,0,2213,2020,3),(336,0,3891,2020,3),(337,1609851955,3892,2020,3),
+(338,1610447706,2232,2020,3),(339,0,2235,2020,3),(340,0,2237,2020,3),
+(341,0,3972,2020,3),(342,1610449357,2242,2020,3),(343,0,3893,2020,3),
+(344,0,2257,2020,3),(345,0,3951,2020,3),(346,0,3894,2020,3),
+(347,0,3912,2020,3),(348,0,3895,2020,3),(349,0,2301,2020,3),
+(350,0,2304,2020,3),(351,0,3896,2020,3);
+ANALYZE TABLE t1,t2,t3;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+test.t3	analyze	status	OK
+set  optimizer_switch='split_materialized=on';
+SELECT t1.id
+FROM t1
+JOIN t3
+ON t3.t1_id = t1.id
+JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx
+ON tx.t1_id = t1.id
+WHERE t1.id BETWEEN 200 AND 100000;
+id
+EXPLAIN SELECT t1.id
+FROM t1
+JOIN t3
+ON t3.t1_id = t1.id
+JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx
+ON tx.t1_id = t1.id
+WHERE t1.id BETWEEN 200 AND 100000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	range	t1_id	t1_id	5	NULL	46	Using where; Using index
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.t1_id	1	Using index
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t3.t1_id	2	
+2	LATERAL DERIVED	t2	ref	t1_id	t1_id	5	test.t1.id	3	Using index condition; Using where
+EXPLAIN FORMAT=JSON SELECT t1.id
+FROM t1
+JOIN t3
+ON t3.t1_id = t1.id
+JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx
+ON tx.t1_id = t1.id
+WHERE t1.id BETWEEN 200 AND 100000;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t3",
+      "access_type": "range",
+      "possible_keys": ["t1_id"],
+      "key": "t1_id",
+      "key_length": "5",
+      "used_key_parts": ["t1_id"],
+      "rows": 46,
+      "filtered": 100,
+      "attached_condition": "t3.t1_id between 200 and 100000 and t3.t1_id is not null",
+      "using_index": true
+    },
+    "table": {
+      "table_name": "t1",
+      "access_type": "eq_ref",
+      "possible_keys": ["PRIMARY"],
+      "key": "PRIMARY",
+      "key_length": "4",
+      "used_key_parts": ["id"],
+      "ref": ["test.t3.t1_id"],
+      "rows": 1,
+      "filtered": 100,
+      "using_index": true
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "5",
+      "used_key_parts": ["t1_id"],
+      "ref": ["test.t3.t1_id"],
+      "rows": 2,
+      "filtered": 100,
+      "materialized": {
+        "lateral": 1,
+        "query_block": {
+          "select_id": 2,
+          "table": {
+            "table_name": "t2",
+            "access_type": "ref",
+            "possible_keys": ["t1_id"],
+            "key": "t1_id",
+            "key_length": "5",
+            "used_key_parts": ["t1_id"],
+            "ref": ["test.t1.id"],
+            "rows": 3,
+            "filtered": 100,
+            "index_condition": "t2.t1_id between 200 and 100000",
+            "attached_condition": "t2.reporting_person = 1"
+          }
+        }
+      }
+    }
+  }
+}
+set  optimizer_switch='split_materialized=off';
+SELECT t1.id
+FROM t1
+JOIN t3
+ON t3.t1_id = t1.id
+JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx
+ON tx.t1_id = t1.id
+WHERE t1.id BETWEEN 200 AND 100000;
+id
+set  optimizer_switch='split_materialized=default';
+DROP TABLE t1,t2,t3;
 # End of 10.3 tests
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index 9bb9d9b9bcc..4f4ffc9e6d4 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -3611,4 +3611,104 @@ deallocate prepare stmt;
 
 drop table t1,t2,t3;
 
+--echo #
+--echo # MDEV-MDEV-27132: Splittable derived with equality in WHERE
+--echo #
+
+CREATE TABLE t1 (
+  id int PRIMARY KEY
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES
+(-1),(2070),(4826),(4827),(4828),(4829),(4830),(4831),(4832),(4833),(4834),
+(4835),(4836),(4837),(4838),(4839),(4840),(4841),(4842),(4843),(4844),
+(4845),(4846),(4847),(4848),(4849),(4850),(4851),(4852),(4853),(4854),
+(4855),(4856),(4857),(4858),(4859),(4860),(4861),(4862),(4863),(4864),
+(4865),(4866),(4867),(4868),(4869),(4870),(4871),(4872),(4873),(4874),
+(4875),(4876);
+
+CREATE TABLE t2 (
+  id int PRIMARY KEY AUTO_INCREMENT,
+  deleted int(1),
+  t1_id int,
+  email varchar(255),
+  reporting_person int(1),
+  KEY t1_id (t1_id)
+) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES
+(1,0,2064,'1test@test.ee',1),(2,1626095588,2066,'2test@test.ee',1),
+(3,0,2066,'3test@test.ee',1),(4,0,2068,'4test@test.ee',1),
+(5,0,2068,'5test@test.ee',1),(6,0,2069,'6test@test.ee',1),(7,0,2070,'',0),
+(8,0,2070,'',0),(9,0,2071,'',0),(10,0,2071,'',0),(11,0,2072,'',0),
+(12,0,2072,'',0),(13,0,2072,'13test@test.ee',1),(14,0,2073,'14test@test.ee',1),
+(15,0,2074,'15test@test.ee',1),(16,0,2075,'16test@test.ee',1),(17,0,2075,'',0),
+(18,0,2075,'',0),(19,0,2076,'19test@test.ee',1),(20,0,2077,'',0),
+(21,0,2078,'21test@test.ee',1),(22,0,2078,'22test@test.ee',1);
+
+INSERT INTO t2(deleted, t1_id, email, reporting_person)
+  SELECT deleted, t1_id, email, reporting_person FROM t2;
+INSERT INTO t2(deleted, t1_id, email, reporting_person)
+  SELECT deleted, t1_id+10000, email, reporting_person FROM t2;
+INSERT INTO t2(deleted, t1_id, email, reporting_person)
+  SELECT deleted, t1_id+20000, email, reporting_person FROM t2;
+INSERT INTO t2(deleted, t1_id, email, reporting_person)
+  SELECT deleted, t1_id+40000, email, reporting_person FROM t2;
+INSERT INTO t2(deleted, t1_id, email, reporting_person)
+  SELECT deleted, t1_id+80000, email, reporting_person FROM t2;
+INSERT INTO t2(deleted, t1_id, email, reporting_person)
+  SELECT deleted, t1_id+160000, email, reporting_person FROM t2;
+
+CREATE TABLE t3 (
+  id int PRIMARY KEY,
+  deleted int,
+  t1_id int,
+  YEAR int(4),
+  quarter int(1),
+  KEY t1_id (t1_id,year,quarter)
+) ENGINE=MyISAM;
+
+INSERT INTO t3 VALUES
+(1,0,3885,2020,1),(2,0,2064,2020,1),(3,1611670734,2225,2020,1),
+(4,0,2070,2020,1),(5,1611055981,2095,2020,1),(6,1610970096,2102,2020,1),
+(7,0,3974,2020,1),(153,1609851928,3892,2020,2),(154,0,3885,2020,2),
+(155,0,2064,2020,2),(156,1611670717,2225,2020,2),(157,0,2070,2020,2),
+(317,0,2257,2020,2),(318,0,3885,2020,3),(319,0,2064,2020,3),
+(320,1611670709,2225,2020,3),(321,0,2070,2020,3),(322,0,2095,2020,3),
+(323,0,2102,2020,3),(324,0,3974,2020,3),(325,0,3886,2020,3),
+(326,1609939963,2104,2020,3),(327,0,3887,2020,3),(328,0,3888,2020,3),
+(329,0,2148,2020,3),(330,0,3889,2020,3),(331,0,3890,2020,3),
+(332,0,2179,2020,3),(333,0,2115,2020,3),(334,0,2193,2020,3),
+(335,0,2213,2020,3),(336,0,3891,2020,3),(337,1609851955,3892,2020,3),
+(338,1610447706,2232,2020,3),(339,0,2235,2020,3),(340,0,2237,2020,3),
+(341,0,3972,2020,3),(342,1610449357,2242,2020,3),(343,0,3893,2020,3),
+(344,0,2257,2020,3),(345,0,3951,2020,3),(346,0,3894,2020,3),
+(347,0,3912,2020,3),(348,0,3895,2020,3),(349,0,2301,2020,3),
+(350,0,2304,2020,3),(351,0,3896,2020,3);
+
+ANALYZE TABLE t1,t2,t3;
+
+let $q=
+SELECT t1.id
+FROM t1
+     JOIN t3
+     ON t3.t1_id = t1.id
+     JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx
+     ON tx.t1_id = t1.id
+WHERE t1.id BETWEEN 200 AND 100000;
+
+set  optimizer_switch='split_materialized=on';
+
+eval $q;
+eval EXPLAIN $q;
+eval EXPLAIN FORMAT=JSON $q;
+
+set  optimizer_switch='split_materialized=off';
+
+eval $q;
+
+set  optimizer_switch='split_materialized=default';
+
+DROP TABLE t1,t2,t3;
+
 --echo # End of 10.3 tests
diff --git a/sql/opt_split.cc b/sql/opt_split.cc
index 8a985095f3c..9dfc8ac5acb 100644
--- a/sql/opt_split.cc
+++ b/sql/opt_split.cc
@@ -1091,7 +1091,7 @@ bool JOIN::inject_best_splitting_cond(table_map remaining_tables)
   if (inj_cond)
     inj_cond->fix_fields(thd,0);
 
-  if (inject_cond_into_where(inj_cond))
+  if (inject_cond_into_where(inj_cond->copy_andor_structure(thd)))
     return true;
 
   select_lex->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED;