MDEV-31240 Crash with condition pushable into derived and containing outer referencebb-10.11-MDEV-31295-cherry-pick-pkgtest

This bug could affect queries containing a subquery over splittable derived
tables and having an outer references in its WHERE clause. If such subquery
contained an equality condition whose left part was a reference to a column
of the derived table and the right part referred only to outer columns
then the server crashed in the function st_join_table::choose_best_splitting()
The crashing code was added in the commit ce7ffe61d836fe9f0cfc1087f058bc40d66e5cfb
that made the code of the function sensitive to presence of the flag
OUTER_REF_TABLE_BIT in the KEYUSE_EXT::needed_in_prefix fields.

The field needed_in_prefix of the KEYUSE_EXT structure should not contain
table maps with OUTER_REF_TABLE_BIT or RAND_TABLE_BIT.

Note that this fix is quite conservative: for affected queries it just
returns the query plans that were used before the above mentioned commit.
In fact the equalities causing crashes should be pushed into derived tables
without any usage of split optimization.

Approved by Sergei Petrunia <sergey@mariadb.com>

3 files changed, 137 insertions, 1 deletions

diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 639e438a0a5..7c2fdb78234 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -20790,6 +20790,100 @@ a
 deallocate prepare stmt;
 drop view v1;
 drop table t1;
+#
+# MDEV-31240: condition pushed into splittable derived has reference to
+#             outer column and does not refer to any column of embedding
+#             select
+#
+create table t1 (a int);
+insert into t1 select seq from seq_1_to_1000;
+create table t2 (a int, b int, key (a));
+insert into t2 select mod(seq,100), rand(13) * mod(seq,500) from seq_1_to_1000;
+create table t3 (a int);
+insert into t3 values (3), (1);
+analyze table t1, t2, t3 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	Table is already up to date
+test.t3	analyze	status	Engine-independent statistics collected
+test.t3	analyze	status	OK
+explain select
+a,
+( select concat(t3.a,'=',dt.s)
+from
+(select a, sum(b) as s from t2 group by a) as dt,
+t3
+where dt.a=t1.a and t3.a < 3
+)
+from t1 limit 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1000	
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	<derived3>	ref	key0	key0	5	test.t1.a	2	
+3	LATERAL DERIVED	t2	ref	a	a	5	test.t1.a	10	
+select
+a,
+( select concat(t3.a,'=',dt.s)
+from
+(select a, sum(b) as s from t2 group by a) as dt,
+t3
+where dt.a=t1.a and t3.a < 3
+)
+from t1 limit 5;
+a	( select concat(t3.a,'=',dt.s)
+from
+(select a, sum(b) as s from t2 group by a) as dt,
+t3
+where dt.a=t1.a and t3.a < 3
+)
+1	1=804
+2	1=1056
+3	1=846
+4	1=947
+5	1=973
+truncate table t2;
+insert into t2 select mod(seq,10), rand(15) * mod(seq,500) from seq_1_to_1000;
+analyze table t2 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	Table is already up to date
+explain select
+a,
+( select concat(t3.a,'=',dt.s)
+from
+(select a, sum(b) as s from t2 group by a) as dt,
+t3
+where dt.a=t1.a and t3.a < 3
+)
+from t1 limit 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1000	
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	<derived3>	ref	key0	key0	5	test.t1.a	100	
+3	DERIVED	t2	ALL	a	NULL	NULL	NULL	1000	Using temporary; Using filesort
+select
+a,
+( select concat(t3.a,'=',dt.s)
+from
+(select a, sum(b) as s from t2 group by a) as dt,
+t3
+where dt.a=t1.a and t3.a < 3
+)
+from t1 limit 5;
+a	( select concat(t3.a,'=',dt.s)
+from
+(select a, sum(b) as s from t2 group by a) as dt,
+t3
+where dt.a=t1.a and t3.a < 3
+)
+1	1=11858
+2	1=11380
+3	1=11588
+4	1=11373
+5	1=11612
+drop table t1,t2,t3;
 # End of 10.4 tests
 #
 # MDEV-28958: condition pushable into view after simplification
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index 06b15f75a0b..7c87785ccc1 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -3973,6 +3973,47 @@ deallocate prepare stmt;
 drop view v1;
 drop table t1;
 
+--echo #
+--echo # MDEV-31240: condition pushed into splittable derived has reference to
+--echo #             outer column and does not refer to any column of embedding
+--echo #             select
+--echo #
+
+create table t1 (a int);
+insert into t1 select seq from seq_1_to_1000;
+
+create table t2 (a int, b int, key (a));
+insert into t2 select mod(seq,100), rand(13) * mod(seq,500) from seq_1_to_1000;
+
+create table t3 (a int);
+insert into t3 values (3), (1);
+
+analyze table t1, t2, t3 persistent for all;
+
+let $q=
+select
+  a,
+  ( select concat(t3.a,'=',dt.s)
+   from
+     (select a, sum(b) as s from t2 group by a) as dt,
+     t3
+   where dt.a=t1.a and t3.a < 3
+  )
+from t1 limit 5;
+
+eval explain $q;
+eval $q;
+
+truncate table t2;
+insert into t2 select mod(seq,10), rand(15) * mod(seq,500) from seq_1_to_1000;
+
+analyze table t2 persistent for all;
+
+eval explain $q;
+eval $q;
+
+drop table t1,t2,t3;
+
 --echo # End of 10.4 tests
 
 --echo #
diff --git a/sql/opt_split.cc b/sql/opt_split.cc
index db18e7eebfb..73127d0e053 100644
--- a/sql/opt_split.cc
+++ b/sql/opt_split.cc
@@ -664,7 +664,8 @@ add_ext_keyuse_for_splitting(Dynamic_array<KEYUSE_EXT> *ext_keyuses,
   keyuse_ext.cond_guard= added_key_field->cond_guard;
   keyuse_ext.sj_pred_no= added_key_field->sj_pred_no;
   keyuse_ext.validity_ref= 0;
-  keyuse_ext.needed_in_prefix= added_key_field->val->used_tables();
+  keyuse_ext.needed_in_prefix= added_key_field->val->used_tables() &
+			       ~(OUTER_REF_TABLE_BIT | RAND_TABLE_BIT);
   keyuse_ext.validity_var= false;
   return ext_keyuses->push(keyuse_ext);
 }