MDEV-29988: Major performance regression with 10.6.11

The idea is to put Item_direct_ref_to_item as a transparent and
permanent wrapper before a string which require conversion.
So that Item_direct_ref_to_item would be the only place where
the pointer to the string item is stored, this pointer can be changed
and restored during PS execution as needed. And if any permanent
(subquery) optimization would need a pointer to the item,
it'll use a pointer to the Item_direct_ref_to_item - which is
a permanent item and won't go away.

2 files changed, 50 insertions, 0 deletions

diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result
index 26c41526389..16700fd83d3 100644
--- a/mysql-test/main/ps.result
+++ b/mysql-test/main/ps.result
@@ -5700,3 +5700,18 @@ EXECUTE stmt USING 'd';
 EXECUTE stmt USING 'd';
 300
 DROP TABLE t1, t2, t3;
+set @@max_session_mem_used=default;
+create table t (a varchar(10)) character set utf8;
+insert into t values ('');
+prepare stmt from "select 1 from t where a = ?";
+set @@max_session_mem_used=(select memory_used*2 from information_schema.processlist where id=connection_id());
+deallocate prepare stmt;
+drop table t;
+set @@max_session_mem_used=default;
+create table t (a varchar(10)) character set utf8;
+insert into t values ('');
+prepare stmt from "select 1 from t where a = 'a'";
+set @@max_session_mem_used=(select memory_used*2 from information_schema.processlist where id=connection_id());
+deallocate prepare stmt;
+drop table t;
+set @@max_session_mem_used=default;
diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test
index 2ccfac3c119..87cae7211a4 100644
--- a/mysql-test/main/ps.test
+++ b/mysql-test/main/ps.test
@@ -5116,3 +5116,38 @@ EXECUTE stmt USING 'b';
 EXECUTE stmt USING 'd';
 EXECUTE stmt USING 'd';
 DROP TABLE t1, t2, t3;
+
+set @@max_session_mem_used=default;
+create table t (a varchar(10)) character set utf8;
+insert into t values ('');
+prepare stmt from "select 1 from t where a = ?";
+set @@max_session_mem_used=(select memory_used*2 from information_schema.processlist where id=connection_id());
+let $run= 1000;
+disable_result_log;
+disable_query_log;
+while ($run) {
+  execute stmt using repeat('x',10000);
+  dec $run;
+}
+enable_result_log;
+enable_query_log;
+deallocate prepare stmt;
+drop table t;
+set @@max_session_mem_used=default;
+
+create table t (a varchar(10)) character set utf8;
+insert into t values ('');
+prepare stmt from "select 1 from t where a = 'a'";
+set @@max_session_mem_used=(select memory_used*2 from information_schema.processlist where id=connection_id());
+let $run= 1000;
+disable_result_log;
+disable_query_log;
+while ($run) {
+  execute stmt;
+  dec $run;
+}
+enable_result_log;
+enable_query_log;
+deallocate prepare stmt;
+drop table t;
+set @@max_session_mem_used=default;