MDEV-22534 [do-not-push] a PoC that works for the example casebb-11.1-mdev-22534-poc

Test case:

--8<---------------cut here---------------start------------->8---
set @@optimizer_switch='materialization=on,firstmatch=off';
create table ten(a int primary key);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int, b int, c int);
insert into t1 select a,a,a from ten;
create table t2 select * from t1;

set optimizer_trace="enabled=on";
explain select * from t1 where a in (select a from t2 where t1.b=t2.b);
set optimizer_trace="enabled=off";
select * from information_schema.OPTIMIZER_TRACE;

set optimizer_trace="enabled=on";
explain select * from t1 where (a, b) in (select a, b from t2);
set optimizer_trace="enabled=off";
select * from information_schema.OPTIMIZER_TRACE;

drop table ten, t1, t2;
--8<---------------cut here---------------end--------------->8---

Exactly the same explain output:

--8<---------------cut here---------------start------------->8---
explain select * from t1 where a in (select a from t2 where t1.b=t2.b);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1
2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	10

explain select * from t1 where (a, b) in (select a, b from t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1
2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	10
--8<---------------cut here---------------end--------------->8---

4 files changed, 46 insertions, 0 deletions

diff --git a/sql/item.h b/sql/item.h
index 5b8b858c13c..5dc37a8fd8c 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -2204,6 +2204,7 @@ public:
   virtual bool count_sargable_conds(void *arg) { return 0; }
   virtual bool limit_index_condition_pushdown_processor(void *arg) { return 0; }
   virtual bool exists2in_processor(void *arg) { return 0; }
+  virtual bool decorrelate_in_processor(void *arg) { return 0; }
   virtual bool find_selective_predicates_list_processor(void *arg) { return 0; }
   virtual bool cleanup_is_expensive_cache_processor(void *arg)
   {
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 9e6c205ca76..a2a4afa7f50 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -3099,6 +3099,45 @@ alloc_err:
   return TRUE;
 }
 
+bool Item_in_subselect::decorrelate_in_processor(void *opt_arg)
+{
+  THD *thd= (THD *)opt_arg;
+  SELECT_LEX *first_select= unit->first_select();
+  JOIN *join= first_select->join;
+  Dynamic_array<EQ_FIELD_OUTER> eqs(PSI_INSTRUMENT_MEM, 5, 5);
+  List<Item> outer;
+  DBUG_ENTER("Item_exists_subselect::decorrelate_in_processor");
+  if (!join->conds)
+    DBUG_RETURN(FALSE);
+  /* iterate over conditions, and check whether they can be moved out. */
+  if (find_inner_outer_equalities(&join->conds, eqs))
+    DBUG_RETURN(FALSE);
+  /* move out the conditions */
+  if (left_expr->type() == Item::FIELD_ITEM)
+    outer.push_back(left_expr);
+  const uint offset= first_select->item_list.elements;
+  for (uint i= 0; i < (uint)eqs.elements(); i++)
+  {
+    Item **eq_ref= eqs.at(i).eq_ref;
+    Item_ident *local_field= eqs.at(i).local_field;
+    Item *outer_exp= eqs.at(i).outer_exp;
+    first_select->item_list.push_back(local_field, thd->mem_root);
+    first_select->ref_pointer_array[offset + i]= (Item *)local_field;
+    outer.push_back(outer_exp);
+    *eq_ref= new (thd->mem_root) Item_int(thd, 1);
+    if((*eq_ref)->fix_fields(thd, (Item **)eq_ref))
+      DBUG_RETURN(TRUE);
+  }
+  /* fixme: not always the case - partial pull out */
+  is_correlated= false;
+  left_expr= new (thd->mem_root) Item_row(thd, outer);
+  left_expr->fix_fields(thd, &left_expr);
+  OPT_TRACE_TRANSFORM(thd, trace_wrapper, trace_transform,
+                      get_select_lex()->select_number,
+                      "IN (SELECT)", "decorrelation");
+  DBUG_RETURN(FALSE);
+}
+
 /**
   Converts EXISTS subquery to IN subquery if it is possible and has sense
 
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 8d58e16bb28..de6bfd3c8d2 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -762,6 +762,8 @@ public:
     return 0;
   };
 
+  bool decorrelate_in_processor(void *opt_arg) override;
+
   bool pushdown_cond_for_in_subquery(THD *thd, Item *cond);
 
   Item_in_subselect *get_IN_subquery() override
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index e98121231a7..db1c2cb09e2 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2125,6 +2125,10 @@ JOIN::optimize_inner()
   if (select_lex->first_cond_optimization &&
       conds && conds->walk(&Item::exists2in_processor, 0, thd))
     DBUG_RETURN(1);
+
+  if (select_lex->first_cond_optimization &&
+      conds && conds->walk(&Item::decorrelate_in_processor, 0, thd))
+    DBUG_RETURN(1);
   /*
     TODO
     make view to decide if it is possible to write to WHERE directly or make Semi-Joins able to process ON condition if it is possible