Fix results in real ps-protocol:

Starting wth 10.6 it goes via ps protocol so the subselect removed
only once on prepare.

4 files changed, 39 insertions, 0 deletions

diff --git a/mysql-test/main/select,ps.rdiff b/mysql-test/main/select,ps.rdiff
new file mode 100644
index 00000000000..7cbcd2be98e
--- /dev/null
+++ b/mysql-test/main/select,ps.rdiff
@@ -0,0 +1,12 @@
+--- mysql-test/main/select.result	2023-01-31 09:30:58.151377805 +0100
++++ mysql-test/main/select.reject	2023-02-01 13:44:11.026958614 +0100
+@@ -5661,6 +5661,8 @@
+ Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+ Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+ Note	1249	Select 2 was reduced during optimization
++Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
++Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+ Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL  where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3)
+ PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND
+   (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))';
+
diff --git a/mysql-test/main/select.test b/mysql-test/main/select.test
index 5c18c6547e7..332b5a36aea 100644
--- a/mysql-test/main/select.test
+++ b/mysql-test/main/select.test
@@ -3,6 +3,7 @@
 #
 
 --source include/no_valgrind_without_big.inc
+--source include/protocol.inc
 
 #
 # Simple select test
@@ -4758,9 +4759,11 @@ INSERT INTO t1 VALUES (1),(2),(3);
 SELECT * FROM t1 WHERE a = 1 AND
   (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
 
+--enable_prepare_warnings
 EXPLAIN EXTENDED
 SELECT * FROM t1 WHERE a = 1 AND
   (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+--disable_prepare_warnings
 
 PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND
   (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))';
diff --git a/mysql-test/main/select_jcl6,ps.rdiff b/mysql-test/main/select_jcl6,ps.rdiff
new file mode 100644
index 00000000000..18652a0683b
--- /dev/null
+++ b/mysql-test/main/select_jcl6,ps.rdiff
@@ -0,0 +1,12 @@
+--- mysql-test/main/select_jcl6.result	2023-01-31 09:30:58.151377805 +0100
++++ mysql-test/main/select_jcl6.reject	2023-02-01 13:44:10.722958771 +0100
+@@ -5672,6 +5672,8 @@
+ Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+ Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+ Note	1249	Select 2 was reduced during optimization
++Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
++Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+ Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL  where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3)
+ PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND
+   (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))';
+
diff --git a/mysql-test/main/select_pkeycache,ps.rdiff b/mysql-test/main/select_pkeycache,ps.rdiff
new file mode 100644
index 00000000000..320a402fefc
--- /dev/null
+++ b/mysql-test/main/select_pkeycache,ps.rdiff
@@ -0,0 +1,12 @@
+--- mysql-test/main/select_pkeycache.result	2023-01-31 09:30:58.151377805 +0100
++++ mysql-test/main/select_pkeycache.reject	2023-02-01 13:43:21.742985365 +0100
+@@ -5661,6 +5661,8 @@
+ Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+ Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+ Note	1249	Select 2 was reduced during optimization
++Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
++Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+ Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL  where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3)
+ PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND
+   (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))';
+