MDEV-27691: make working view-protocol

Update tests for version 10.5

21 files changed, 74 insertions, 5 deletions

diff --git a/mysql-test/main/comment_database.test b/mysql-test/main/comment_database.test
index 3fe1c7b205c..bf89a57c7b2 100644
--- a/mysql-test/main/comment_database.test
+++ b/mysql-test/main/comment_database.test
@@ -52,6 +52,7 @@ SHOW CREATE DATABASE db1;
 DROP DATABASE db1;
 
 # Test the case when the database is named 'comment'
+--disable_service_connection
 CREATE DATABASE comment COMMENT 'comment' CHARACTER SET 'latin2';
 SHOW CREATE DATABASE comment;
 ALTER DATABASE comment COMMENT 'comment comment';
@@ -61,3 +62,4 @@ ALTER DATABASE COMMENT 'comment';
 SELECT * FROM information_schema.schemata
 WHERE schema_name='comment';
 DROP DATABASE comment;
+--enable_service_connection
diff --git a/mysql-test/main/func_hybrid_type.test b/mysql-test/main/func_hybrid_type.test
index 52ba4b3e1d9..1b903f78b3f 100644
--- a/mysql-test/main/func_hybrid_type.test
+++ b/mysql-test/main/func_hybrid_type.test
@@ -877,11 +877,13 @@ SELECT 0 + LEAST(TIME'-10:00:00',TIME'10:00:00') AS c;
 --echo #
 
 CREATE TABLE t1 (a INT UNSIGNED, b TIMESTAMP);
+--disable_view_protocol
 --disable_ps_protocol
 --enable_metadata
 SELECT COALESCE(a,b) FROM t1;
 --disable_metadata
 --enable_ps_protocol
+--enable_view_protocol
 DROP TABLE t1;
 
 --echo #
diff --git a/mysql-test/main/func_misc.test b/mysql-test/main/func_misc.test
index d1d54f7b00b..346e7383173 100644
--- a/mysql-test/main/func_misc.test
+++ b/mysql-test/main/func_misc.test
@@ -1299,6 +1299,7 @@ DROP TABLE t1;
 --echo #
 --echo # MDEV-10569 Add RELEASE_ALL_LOCKS SQL-function
 --echo #
+--disable_view_protocol
 
 --echo # Test function without any locks
 SELECT RELEASE_ALL_LOCKS();
@@ -1352,3 +1353,5 @@ FROM information_schema.metadata_lock_info WHERE thread_id>0 ORDER BY TABLE_SCHE
 SELECT RELEASE_ALL_LOCKS();
 SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA
 FROM information_schema.metadata_lock_info WHERE thread_id>0 ORDER BY TABLE_SCHEMA;
+
+--enable_view_protocol
diff --git a/mysql-test/main/func_regexp_pcre.test b/mysql-test/main/func_regexp_pcre.test
index 6bfae9f075c..e6e356f4a8c 100644
--- a/mysql-test/main/func_regexp_pcre.test
+++ b/mysql-test/main/func_regexp_pcre.test
@@ -393,8 +393,11 @@ SET default_regex_flags=DEFAULT;
 --error ER_REGEXP_ERROR
 SELECT 'Aq' RLIKE 'A\\q';
 
+#double warning for view protocol 
+--disable_view_protocol
 SET default_regex_flags='EXTRA';
 SELECT 'A' RLIKE 'B';
+--enable_view_protocol
 
 SET default_regex_flags=DEFAULT;
 
diff --git a/mysql-test/main/func_str.test b/mysql-test/main/func_str.test
index d45a96318bd..b8bb8a54090 100644
--- a/mysql-test/main/func_str.test
+++ b/mysql-test/main/func_str.test
@@ -995,11 +995,20 @@ select left('hello', 10);
 select left('hello', 0);
 select left('hello', -1);
 select left('hello', -4294967295);
+#enable after fix MDEV-29552
+--disable_view_protocol
 select left('hello', 4294967295);
+--enable_view_protocol
 select left('hello', -4294967296);
+#enable after fix MDEV-29552
+--disable_view_protocol
 select left('hello', 4294967296);
+--enable_view_protocol
 select left('hello', -4294967297);
+#enable after fix MDEV-29552
+--disable_view_protocol
 select left('hello', 4294967297);
+--enable_view_protocol
 #view protocol generates additional warning
 --disable_view_protocol
 select left('hello', -18446744073709551615);
@@ -1014,11 +1023,20 @@ select right('hello', 10);
 select right('hello', 0);
 select right('hello', -1);
 select right('hello', -4294967295);
+#enable after fix MDEV-29552
+--disable_view_protocol
 select right('hello', 4294967295);
+--enable_view_protocol
 select right('hello', -4294967296);
+#enable after fix MDEV-29552
+--disable_view_protocol
 select right('hello', 4294967296);
+--enable_view_protocol
 select right('hello', -4294967297);
+#enable after fix MDEV-29552
+--disable_view_protocol
 select right('hello', 4294967297);
+--enable_view_protocol
 #view protocol generates additional warning
 --disable_view_protocol
 select right('hello', -18446744073709551615);
diff --git a/mysql-test/main/gis-debug.test b/mysql-test/main/gis-debug.test
index 0e11c11e5b5..1c6bf5fcb19 100644
--- a/mysql-test/main/gis-debug.test
+++ b/mysql-test/main/gis-debug.test
@@ -161,12 +161,14 @@ SET SESSION debug_dbug="-d,cmp_item";
 --echo # MDEV-19994 Add class Function_collection
 --echo #
 
+--disable_service_connection
 SET SESSION debug_dbug="+d,make_item_func_call_native_simulate_not_found";
 --error ER_PARSE_ERROR
 SELECT CONTAINS(POINT(1,1),POINT(1,1));
 --error ER_PARSE_ERROR
 SELECT WITHIN(POINT(1,1),POINT(1,1));
 SET SESSION debug_dbug="-d,make_item_func_call_native_simulate_not_found";
+--enable_service_connection
 
 --echo #
 --echo # MDEV-20009 Add CAST(expr AS pluggable_type)
diff --git a/mysql-test/main/gis.test b/mysql-test/main/gis.test
index 64fee3390d3..f0c9a69c503 100644
--- a/mysql-test/main/gis.test
+++ b/mysql-test/main/gis.test
@@ -3247,6 +3247,7 @@ CREATE TABLE t1 (
   g    GEOMETRY
 ) CHARACTER SET utf8;
 
+--disable_view_protocol
 --enable_metadata
 SELECT * FROM t1;
 
@@ -3350,6 +3351,7 @@ SELECT
 FROM t1;
 
 --disable_metadata
+--enable_view_protocol
 DROP TABLE t1;
 
 --echo #
diff --git a/mysql-test/main/grant_kill.test b/mysql-test/main/grant_kill.test
index b29dfb32917..75a25743dc9 100644
--- a/mysql-test/main/grant_kill.test
+++ b/mysql-test/main/grant_kill.test
@@ -7,6 +7,7 @@
 --echo #
 --echo # MDEV-21743 Split up SUPER privilege to smaller privileges 
 --echo #
+--disable_service_connection
 
 --let $count_sessions=1
 
@@ -83,6 +84,8 @@ let $wait_condition=
 DROP USER foo@localhost;
 DROP USER bar@localhost;
 
+--enable_service_connection
+
 --echo #
 --echo # End of 10.5 tests
 --echo #
diff --git a/mysql-test/main/group_by.test b/mysql-test/main/group_by.test
index 318790d51b3..357959d4071 100644
--- a/mysql-test/main/group_by.test
+++ b/mysql-test/main/group_by.test
@@ -280,6 +280,7 @@ drop table t1;
 
 # Compare with hash keys
 
+--disable_view_protocol
 CREATE TABLE t1 (a char(1));
 INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL);
 flush status;
@@ -305,6 +306,7 @@ show status like 'Created%tables';
 --enable_ps_protocol
 set tmp_memory_table_size=default;
 drop table t1;
+--enable_view_protocol
 
 #
 # Test of key >= 256 bytes
diff --git a/mysql-test/main/intersect.test b/mysql-test/main/intersect.test
index 481fdee02ce..5e811f1f56d 100644
--- a/mysql-test/main/intersect.test
+++ b/mysql-test/main/intersect.test
@@ -303,11 +303,8 @@ insert into t234 values(2);
 insert into t234 values(3);
 insert into t234 values(4);
 
-#enable view protocol in 10.5
---disable_view_protocol
 --sorted_result
 select * from t13 union select * from t234 intersect select * from t12;
---enable_view_protocol
 
 drop table t12,t13,t234;
 
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index d6e5991c85c..e8ddd2ed8aa 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -687,8 +687,10 @@ set optimizer_trace='enabled=on';
 --echo # Test many rows to see output of big cost numbers
 --echo #
 
+--disable_view_protocol
 select count(*) from seq_1_to_10000000;
 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+--enable_view_protocol
 
 --echo #
 --echo # MDEV-22891: Optimizer trace: const tables are not clearly visible
@@ -708,14 +710,20 @@ insert into t3 select a,a from t0;
 explain
 select * from t1 left join (t2 join t3 on t3.pk=1000) on t2.a=t1.a and t2.pk is null;
 
+#enable after fix MDEV-27871
+--disable_view_protocol
 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const'))
 from information_schema.optimizer_trace;
+--enable_view_protocol
 
 drop table t0, t1, t2, t3;
 
 --echo #
 --echo # MDEV-23767: IN-to-subquery conversion is not visible in optimizer trace
 --echo #
+
+#enable after fix MDEV-27871
+--disable_view_protocol
 create table t0 (a int);
 INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
@@ -738,6 +746,7 @@ from information_schema.optimizer_trace;
 
 set in_predicate_conversion_threshold=@tmp;
 drop table t0;
+--enable_view_protocol
 
 --echo # End of 10.5 tests
 set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/percona_nonflushing_analyze_debug.test b/mysql-test/main/percona_nonflushing_analyze_debug.test
index 4c9c2dcb768..fdaf7aed6c0 100644
--- a/mysql-test/main/percona_nonflushing_analyze_debug.test
+++ b/mysql-test/main/percona_nonflushing_analyze_debug.test
@@ -1,5 +1,6 @@
 --source include/have_debug_sync.inc
 --source include/have_innodb.inc
+--source include/no_view_protocol.inc
 
 #set use_stat_tables='preferably_for_queries';
 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
diff --git a/mysql-test/main/set_operation_oracle.test b/mysql-test/main/set_operation_oracle.test
index 60fda4a5cb5..52152b83110 100644
--- a/mysql-test/main/set_operation_oracle.test
+++ b/mysql-test/main/set_operation_oracle.test
@@ -28,8 +28,10 @@ insert into t234 values(2);
 insert into t234 values(3);
 insert into t234 values(4);
 
-
+#enable after fix MDEV-29553
+--disable_view_protocol
 select * from t13 union select * from t234 intersect select * from t12;
+--enable_view_protocol
 set SQL_MODE=default;
 
 drop table t1,t2,t3;
@@ -45,6 +47,8 @@ insert into t3 values (1,1),(3,3);
 
 set SQL_MODE=ORACLE;
 
+#enable after fix MDEV-29553
+--disable_view_protocol
 select a,b from t1 union all select c,d from t2 intersect select e,f from t3 union all select 4,'4' from dual;
 explain extended
 select a,b from t1 union all select c,d from t2 intersect select e,f from t3 union all select 4,'4' from dual;
@@ -52,6 +56,7 @@ select a,b from t1 union all select c,d from t2 intersect select e,f from t3 uni
 select a,b from t1 union all select c,d from t2 intersect all select e,f from t3 union all select 4,'4' from dual;
 explain extended
 select a,b from t1 union all select c,d from t2 intersect all select e,f from t3 union all select 4,'4' from dual;
+--enable_view_protocol
 
 select e,f from t3 intersect select c,d from t2 union all select a,b from t1 union all select 4,'4' from dual;
 explain extended
diff --git a/mysql-test/main/thread_pool_info.test b/mysql-test/main/thread_pool_info.test
index 84dce94d778..8d4ac01bb1c 100644
--- a/mysql-test/main/thread_pool_info.test
+++ b/mysql-test/main/thread_pool_info.test
@@ -1,6 +1,8 @@
 source include/not_embedded.inc;
 source include/not_aix.inc;
 
+-- source include/no_view_protocol.inc
+
 let $have_plugin = `SELECT COUNT(*) FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_STATUS='ACTIVE' AND PLUGIN_NAME = 'THREAD_POOL_GROUPS'`;
 if(!$have_plugin)
 {
diff --git a/mysql-test/main/type_binary.test b/mysql-test/main/type_binary.test
index 9f7a0db66d5..04cdc94e6d8 100644
--- a/mysql-test/main/type_binary.test
+++ b/mysql-test/main/type_binary.test
@@ -135,6 +135,7 @@ DROP TABLE t1;
 --echo #
 
 CREATE TABLE t1 (a VARCHAR(39));
+--disable_view_protocol
 --enable_metadata
 SELECT
   CAST(a AS BINARY(0)),
@@ -153,4 +154,5 @@ SELECT
   CAST(a AS BINARY(16777216))
 FROM t1;
 --disable_metadata
+--enable_view_protocol
 DROP TABLE t1;
diff --git a/mysql-test/main/type_bit.test b/mysql-test/main/type_bit.test
index 36c4b1d064b..846fc34e2e9 100644
--- a/mysql-test/main/type_bit.test
+++ b/mysql-test/main/type_bit.test
@@ -556,11 +556,13 @@ DELIMITER ;$$
 --echo #
 
 CREATE TABLE t1 (b BIT(1));
+--disable_view_protocol
 --disable_ps_protocol
 --enable_metadata
 SELECT MIN(CASE WHEN 0 THEN b END) FROM t1;
 --disable_metadata
 --enable_ps_protocol
+--enable_view_protocol
 CREATE TABLE t2 AS SELECT MIN(CASE WHEN 0 THEN b END) FROM t1;
 SHOW CREATE TABLE t2;
 DROP TABLE t2;
diff --git a/mysql-test/main/type_float.test b/mysql-test/main/type_float.test
index f88c5c815d9..64483f718bd 100644
--- a/mysql-test/main/type_float.test
+++ b/mysql-test/main/type_float.test
@@ -337,8 +337,11 @@ select format(truncate('1.7976931348623157E+308',-12),1,'fr_BE') as foo;
 --echo # MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result
 --echo #
 
+#enable after fix MDEV-29552
+--disable_view_protocol
 SELECT LEFT('a',EXP(50));
 SELECT LEFT('a', COALESCE(1e30));
+--enable_view_protocol
 
 CREATE TABLE t1 (a FLOAT);
 INSERT INTO t1 VALUES (1e30);
diff --git a/mysql-test/main/type_json.test b/mysql-test/main/type_json.test
index bc8716161ac..38754ba6e1e 100644
--- a/mysql-test/main/type_json.test
+++ b/mysql-test/main/type_json.test
@@ -80,12 +80,14 @@ CREATE TABLE t1 (
   js2 TEXT CHECK (LENGTH(js2) > 0 AND JSON_VALID(js2)),
   js3 TEXT CHECK (LENGTH(js2) > 0 OR JSON_VALID(js2))
 ) CHARACTER SET utf8;
+--disable_view_protocol
 --disable_ps_protocol
 --enable_metadata
 SELECT * FROM t1;
 SELECT js0, JSON_COMPACT(js0), JSON_COMPACT('{}') FROM t1;
 --disable_metadata
 --enable_ps_protocol
+--enable_view_protocol
 DROP TABLE t1;
 
 
@@ -95,6 +97,7 @@ DROP TABLE t1;
 
 CREATE TABLE t1 (a JSON);
 INSERT INTO t1 VALUES ('{"a":"b"}');
+--disable_view_protocol
 --disable_ps_protocol
 --enable_metadata
 SELECT a, JSON_COMPACT(a), COALESCE(a) FROM t1;
@@ -102,6 +105,7 @@ SELECT JSON_ARRAYAGG(1), JSON_ARRAYAGG(a) FROM t1;
 SELECT JSON_OBJECTAGG('a','b'), JSON_OBJECTAGG('a',a) FROM t1;
 --disable_metadata
 --disable_ps_protocol
+--enable_view_protocol
 DROP TABLE t1;
 
 
@@ -109,12 +113,13 @@ DROP TABLE t1;
 --echo # MDEV-27018 IF and COALESCE lose "json" property
 --echo #
 
+--disable_view_protocol
 --disable_ps_protocol
 --enable_metadata
 SELECT json_object('a', (SELECT json_objectagg(b, c) FROM (SELECT 'b','c') d)) AS j FROM DUAL;
 --disable_metadata
 --disable_ps_protocol
-
+--enable_view_protocol
 
 --echo #
 --echo # End of 10.5 tests
diff --git a/mysql-test/main/type_year.test b/mysql-test/main/type_year.test
index 22c982bc7b8..08ac95793c3 100644
--- a/mysql-test/main/type_year.test
+++ b/mysql-test/main/type_year.test
@@ -367,12 +367,14 @@ DROP TABLE t2,t1;
 CREATE TABLE t1 (a YEAR);
 INSERT INTO t1 VALUES (2000),(2001);
 SELECT MAX( NULLIF( a, 1970 ) ) AS f FROM t1;
+--disable_view_protocol
 --disable_ps_protocol
 --enable_metadata
 SELECT NULLIF(a, 1970) AS f FROM t1 ORDER BY a;
 SELECT MAX(NULLIF(a, 1970)) AS f FROM t1;
 --disable_metadata
 --enable_ps_protocol
+--enable_view_protocol
 DROP TABLE t1;
 
 
diff --git a/mysql-test/main/win_percentile.test b/mysql-test/main/win_percentile.test
index 29483746f83..9626caffc54 100644
--- a/mysql-test/main/win_percentile.test
+++ b/mysql-test/main/win_percentile.test
@@ -64,7 +64,10 @@ select name from t1 a where (select  percentile_disc(0.5) within group (order by
 --error ER_WRONG_TYPE_FOR_PERCENTILE_FUNC
 select score,  percentile_cont(0.5)  within group(order by name) over (partition by score) from t1;
 
+#enable after fix MDEV-27871
+--disable_view_protocol
 select score,  percentile_disc(0.5)  within group(order by name) over (partition by score) from t1;
+--enable_view_protocol
 
 --echo #parameter value should be in the range of [0,1]
 --error ER_ARGUMENT_OUT_OF_RANGE
diff --git a/mysql-test/main/xa_prepared_binlog_off.test b/mysql-test/main/xa_prepared_binlog_off.test
index edbfa7c2825..7db25d7cc3f 100644
--- a/mysql-test/main/xa_prepared_binlog_off.test
+++ b/mysql-test/main/xa_prepared_binlog_off.test
@@ -5,6 +5,7 @@
 
 --source include/not_valgrind.inc
 --source include/not_embedded.inc
+--source include/no_view_protocol.inc
 
 # Common part with XA binlogging testing
 call mtr.add_suppression("You need to use --log-bin to make --log-slave-updates work.");