MDEV-16708: Unsupported commands for prepared statements

Disable running of the statements
PREPARE FROM/EXECUTE/EXECUTE IMMEDIATE/DEALLOCATE
in PS mode. Adjust tests main.ps, main.ps_1general

6 files changed, 44 insertions, 157 deletions

diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result
index 70ea6870368..eb17def9a4b 100644
--- a/mysql-test/main/ps.result
+++ b/mysql-test/main/ps.result
@@ -28,9 +28,11 @@ ERROR HY000: Unknown prepared statement handler (no_such_statement) given to DEA
 execute stmt1;
 ERROR HY000: Incorrect arguments to EXECUTE
 prepare stmt2 from 'prepare nested_stmt from "select 1"';
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt2 from 'execute stmt1';
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt2 from 'deallocate prepare z';
-deallocate prepare stmt2;
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt3 from 'insert into t1 values (?,?)';
 set @arg1=5, @arg2='five';
 execute stmt3 using @arg1, @arg2;
@@ -4719,13 +4721,13 @@ ERROR HY000: Incorrect arguments to EXECUTE
 EXECUTE IMMEDIATE 'SELECT ?';
 ERROR HY000: Incorrect arguments to EXECUTE
 EXECUTE IMMEDIATE 'EXECUTE IMMEDIATE "SELECT 1"';
-1
-1
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 EXECUTE IMMEDIATE 'PREPARE stmt FROM "SELECT 1"';
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 EXECUTE IMMEDIATE 'EXECUTE stmt';
-1
-1
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 EXECUTE IMMEDIATE 'DEALLOCATE PREPARE stmt';
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 EXECUTE IMMEDIATE 'SELECT ?' USING _latin1'a'=_latin2'a';
 ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin2_general_ci,COERCIBLE) for operation '='
 EXECUTE IMMEDIATE 'SELECT ?' USING ROW(1,2);
diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test
index 5933aea4a03..e5285022efd 100644
--- a/mysql-test/main/ps.test
+++ b/mysql-test/main/ps.test
@@ -38,10 +38,12 @@ deallocate prepare no_such_statement;
 execute stmt1;
 
 # Nesting ps commands is now allowed:
+--error ER_UNSUPPORTED_PS
 prepare stmt2 from 'prepare nested_stmt from "select 1"';
+--error ER_UNSUPPORTED_PS
 prepare stmt2 from 'execute stmt1';
+--error ER_UNSUPPORTED_PS
 prepare stmt2 from 'deallocate prepare z';
-deallocate prepare stmt2;
 
 # PS insert 
 prepare stmt3 from 'insert into t1 values (?,?)';
@@ -4193,12 +4195,16 @@ EXECUTE IMMEDIATE 'SELECT 1' USING @a;
 --error ER_WRONG_ARGUMENTS
 EXECUTE IMMEDIATE 'SELECT ?';
 
+--error ER_UNSUPPORTED_PS
 EXECUTE IMMEDIATE 'EXECUTE IMMEDIATE "SELECT 1"';
 
+--error ER_UNSUPPORTED_PS
 EXECUTE IMMEDIATE 'PREPARE stmt FROM "SELECT 1"';
 
+--error ER_UNSUPPORTED_PS
 EXECUTE IMMEDIATE 'EXECUTE stmt';
 
+--error ER_UNSUPPORTED_PS
 EXECUTE IMMEDIATE 'DEALLOCATE PREPARE stmt';
 
 --error ER_CANT_AGGREGATE_2COLLATIONS
diff --git a/mysql-test/main/ps_1general.result b/mysql-test/main/ps_1general.result
index c42b3d07bbc..05a40f54a52 100644
--- a/mysql-test/main/ps_1general.result
+++ b/mysql-test/main/ps_1general.result
@@ -381,8 +381,11 @@ drop table t5 ;
 deallocate prepare stmt_do ;
 deallocate prepare stmt_set ;
 prepare stmt1 from ' prepare stmt2 from '' select 1 ''  ' ;
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt1 from ' execute stmt2 ' ;
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt1 from ' deallocate prepare never_prepared ' ;
+ERROR HY000: This command is not supported in the prepared statement protocol yet
 prepare stmt1 from 'alter view v1 as select 2';
 prepare stmt4 from ' use test ' ;
 prepare stmt3 from ' create database mysqltest ';
diff --git a/mysql-test/main/ps_1general.test b/mysql-test/main/ps_1general.test
index f98c05a69fb..b0f408e3bcf 100644
--- a/mysql-test/main/ps_1general.test
+++ b/mysql-test/main/ps_1general.test
@@ -406,8 +406,11 @@ deallocate prepare stmt_do ;
 deallocate prepare stmt_set ;
 
 ## nonsense like prepare of prepare,execute or deallocate
+--error ER_UNSUPPORTED_PS
 prepare stmt1 from ' prepare stmt2 from '' select 1 ''  ' ;
+--error ER_UNSUPPORTED_PS
 prepare stmt1 from ' execute stmt2 ' ;
+--error ER_UNSUPPORTED_PS
 prepare stmt1 from ' deallocate prepare never_prepared ' ;
 
 prepare stmt1 from 'alter view v1 as select 2';
diff --git a/mysql-test/main/ps_missed_cmds.result b/mysql-test/main/ps_missed_cmds.result
index f16cae445fb..e016226bb17 100644
--- a/mysql-test/main/ps_missed_cmds.result
+++ b/mysql-test/main/ps_missed_cmds.result
@@ -546,59 +546,7 @@ EXECUTE stmt_1;
 # Clean up
 DEALLOCATE PREPARE stmt_1;
 DROP PROCEDURE p1;
-# Test case 15: Check that the 'PREPARE FROM' statement can be executed
-# as a prepared statement.
-PREPARE stmt_1 FROM 'PREPARE stmt_2 FROM "SELECT 1"';
-EXECUTE stmt_1;
-# Execute the same prepared statement the second time to check that
-# no internal structures used for handling the 'PREPARE' statement
-# were damaged.
-EXECUTE stmt_1;
-# Now execute the prepared statement with the name stmt_2
-# It is expected that output contains the single row '1'
-EXECUTE stmt_2;
-1
-1
-# Clean up
-DEALLOCATE PREPARE stmt_1;
-DEALLOCATE PREPARE stmt_2;
-# Test case 16: Check that the 'EXECUTE' statement can be executed
-# as a prepared statement.
-PREPARE stmt_1 FROM 'SELECT 1';
-PREPARE stmt_2 FROM 'EXECUTE stmt_1';
-# Execute the statement stmt_2. Expected result is output of one row '1'
-EXECUTE stmt_2;
-1
-1
-# Execute the same prepared statement the second time to check that
-# no internal structures used for handling the 'EXECUTE' statement
-# were damaged.
-EXECUTE stmt_2;
-1
-1
-# Clean up
-DEALLOCATE PREPARE stmt_1;
-DEALLOCATE PREPARE stmt_2;
-# Test case 17: Check that the statement 'DEALLOCATE PREPARE'
-# can be executed as a prepared statement.
-PREPARE stmt_1 FROM 'SELECT 1';
-PREPARE stmt_2 FROM 'DEALLOCATE PREPARE stmt_1';
-# After the prepared statement 'stmt_2' be executed
-# the prepared statement stmt_1 will be deallocated.
-EXECUTE stmt_2;
-# Execute the same prepared statement the second time to check that
-# no internal structures used for handling the 'DEALLOCATE PREPARE'
-# statement were damaged. This time invocation results in the error
-# ER_UNKNOWN_STMT_HANDLER since the prepared statement stmt_1
-# has just been released. So, just ignore this error.
-EXECUTE stmt_2;
-ERROR HY000: Unknown prepared statement handler (stmt_1) given to DEALLOCATE PREPARE
-# Check that the stmt_1 doesn't no longer exist
-EXECUTE stmt_1;
-ERROR HY000: Unknown prepared statement handler (stmt_1) given to EXECUTE
-# Clean up
-DEALLOCATE PREPARE stmt_2;
-# Test case 18: Check that the 'CREATE VIEW' statement can be executed
+# Test case 15: Check that the 'CREATE VIEW' statement can be executed
 # as a prepared statement.
 # Create environment for the test case
 CREATE TABLE t1 (a INT);
@@ -617,7 +565,7 @@ ERROR 42S01: Table 'v1' already exists
 DEALLOCATE PREPARE stmt_1;
 DROP VIEW v1;
 DROP TABLE t1;
-# Test case 19: Check that the 'CREATE TRIGGER' statement can be executed
+# Test case 16: Check that the 'CREATE TRIGGER' statement can be executed
 # as a prepared statement.
 CREATE TABLE t1 (a INT);
 PREPARE stmt_1 FROM 'CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a=1';
@@ -629,10 +577,10 @@ EXECUTE stmt_1;
 # already exist. It is expected behaviour.
 EXECUTE stmt_1;
 ERROR HY000: Trigger 'test.trg1' already exists
-# Test case 20: Check that the 'DROP TRIGGER' statement can be executed
+# Test case 17: Check that the 'DROP TRIGGER' statement can be executed
 # as a prepared statement.
 # This test relies on presence of the trigger trg1 created by
-# the test case 19.
+# the test case 16.
 PREPARE stmt_1 FROM 'DROP TRIGGER trg1';
 EXECUTE stmt_1;
 # Execute the same prepared statement the second time to check that
@@ -644,7 +592,7 @@ ERROR HY000: Trigger does not exist
 # Clean up
 DEALLOCATE PREPARE stmt_1;
 DROP TABLE t1;
-# Test case 21: Check that XA related SQL statements can be executed
+# Test case 18: Check that XA related SQL statements can be executed
 # as prepared statements.
 # Create the table t1 used by XA transaction.
 CREATE TABLE t1 (a INT);
@@ -734,7 +682,7 @@ DEALLOCATE PREPARE stmt_3;
 DEALLOCATE PREPARE stmt_4;
 DEALLOCATE PREPARE stmt_5;
 DEALLOCATE PREPARE stmt_6;
-# Test case 22: Check that the  CREATE SERVER/ALTER SERVER/DROP SERVER
+# Test case 19: Check that the  CREATE SERVER/ALTER SERVER/DROP SERVER
 # statements can be executed as prepared statements.
 PREPARE stmt_1 FROM "CREATE SERVER s FOREIGN DATA WRAPPER mysql OPTIONS (USER 'u1', HOST '127.0.0.1')";
 PREPARE stmt_2 FROM "ALTER SERVER s OPTIONS (USER 'u2')";
@@ -764,7 +712,7 @@ ERROR HY000: The foreign server name you are trying to reference does not exist.
 DEALLOCATE PREPARE stmt_1;
 DEALLOCATE PREPARE stmt_2;
 DEALLOCATE PREPARE stmt_3;
-# Test case 23: Check that the CREATE EVENT/ALTER EVENT/DROP EVENT
+# Test case 20: Check that the CREATE EVENT/ALTER EVENT/DROP EVENT
 # statements can be executed as a prepared statement
 PREPARE stmt_1 FROM "CREATE EVENT e1 ON SCHEDULE AT CURRENT_TIMESTAMP - INTERVAL 1 DAY ON COMPLETION PRESERVE DO SET @a=1";
 PREPARE stmt_2 FROM "ALTER EVENT e1 COMMENT 'New comment'";
@@ -802,7 +750,7 @@ ERROR HY000: Unknown event 'e1'
 DEALLOCATE PREPARE stmt_1;
 DEALLOCATE PREPARE stmt_2;
 DEALLOCATE PREPARE stmt_3;
-# Test case 24: Check that the SIGNAL and RESIGNAL statements
+# Test case 21: Check that the SIGNAL and RESIGNAL statements
 # can be executed as a prepared statement
 PREPARE stmt_1 FROM "SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT='Hello, world!'";
 EXECUTE stmt_1;
@@ -822,7 +770,7 @@ EXECUTE stmt_1;
 ERROR 0K000: RESIGNAL when handler not active
 # Clean up
 DEALLOCATE PREPARE stmt_1;
-# Test case 25: Check that the 'SHOW RELAYLOG EVENTS' statement can be
+# Test case 22: Check that the 'SHOW RELAYLOG EVENTS' statement can be
 # executed as a prepared statement.
 PREPARE stmt_1 FROM 'SHOW RELAYLOG EVENTS';
 EXECUTE stmt_1;
@@ -834,7 +782,7 @@ EXECUTE stmt_1;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 # Clean up
 DEALLOCATE PREPARE stmt_1;
-# Test case 26: Check the 'GET DIAGNOSTICS' statement
+# Test case 23: Check the 'GET DIAGNOSTICS' statement
 # can be executed as a prepared statement
 PREPARE stmt_1 FROM 'GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT';
 # Query from non existent table to fill the diagnostics area
@@ -854,18 +802,7 @@ ERROR 42S02: Table 'test.non_existent_table_1' doesn't exist
 EXECUTE stmt_1;
 # Clean up
 DEALLOCATE PREPARE stmt_1;
-# Test case 27: Check the the statement 'EXECUTE IMMEDIATE'
-# can be executed as a prepared statement
-PREPARE stmt_1 FROM "EXECUTE IMMEDIATE 'SELECT 1'";
-EXECUTE stmt_1;
-1
-1
-# Execute the same prepared statement the second time to check that
-# no internal structures used for handling the 'EXECUTE IMMEDIATE'
-# statement were damaged.
-# Clean up
-DEALLOCATE PREPARE stmt_1;
-# Test Test case 28: Check the statements 'BACKUP'/'BACKUP UNLOCK'
+# Test case 24: Check the statements 'BACKUP'/'BACKUP UNLOCK'
 # can be executed as a prepared statement
 CREATE TABLE t1 (a INT);
 PREPARE stmt_1 FROM 'BACKUP LOCK t1';
@@ -884,7 +821,7 @@ EXECUTE stmt_2;
 DROP TABLE t1;
 DEALLOCATE PREPARE stmt_1;
 DEALLOCATE PREPARE stmt_2;
-# Test Test case 29: Check the statements 'CREATE/ALTER/DROP TABLEPSPACE'
+# Test Test case 25: Check the statements 'CREATE/ALTER/DROP TABLEPSPACE'
 # can be executed as a prepared statement
 PREPARE stmt_1 FROM "CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB";
 PREPARE stmt_2 FROM "ALTER TABLESPACE ts1 ADD DATAFILE 'ts1_1.ibd' ENGINE=InnoDB";
diff --git a/mysql-test/main/ps_missed_cmds.test b/mysql-test/main/ps_missed_cmds.test
index b621ef6d611..3aa14d5cce1 100644
--- a/mysql-test/main/ps_missed_cmds.test
+++ b/mysql-test/main/ps_missed_cmds.test
@@ -327,59 +327,7 @@ EXECUTE stmt_1;
 DEALLOCATE PREPARE stmt_1;
 DROP PROCEDURE p1;
 
---echo # Test case 15: Check that the 'PREPARE FROM' statement can be executed
---echo # as a prepared statement.
-PREPARE stmt_1 FROM 'PREPARE stmt_2 FROM "SELECT 1"';
-EXECUTE stmt_1;
---echo # Execute the same prepared statement the second time to check that
---echo # no internal structures used for handling the 'PREPARE' statement
---echo # were damaged.
-EXECUTE stmt_1;
---echo # Now execute the prepared statement with the name stmt_2
---echo # It is expected that output contains the single row '1'
-EXECUTE stmt_2;
---echo # Clean up
-DEALLOCATE PREPARE stmt_1;
-DEALLOCATE PREPARE stmt_2;
-
---echo # Test case 16: Check that the 'EXECUTE' statement can be executed
---echo # as a prepared statement.
-PREPARE stmt_1 FROM 'SELECT 1';
-PREPARE stmt_2 FROM 'EXECUTE stmt_1';
---echo # Execute the statement stmt_2. Expected result is output of one row '1'
-EXECUTE stmt_2;
---echo # Execute the same prepared statement the second time to check that
---echo # no internal structures used for handling the 'EXECUTE' statement
---echo # were damaged.
-EXECUTE stmt_2;
-
---echo # Clean up
-DEALLOCATE PREPARE stmt_1;
-DEALLOCATE PREPARE stmt_2;
-
---echo # Test case 17: Check that the statement 'DEALLOCATE PREPARE'
---echo # can be executed as a prepared statement.
-PREPARE stmt_1 FROM 'SELECT 1';
-PREPARE stmt_2 FROM 'DEALLOCATE PREPARE stmt_1';
---echo # After the prepared statement 'stmt_2' be executed
---echo # the prepared statement stmt_1 will be deallocated.
-EXECUTE stmt_2;
---echo # Execute the same prepared statement the second time to check that
---echo # no internal structures used for handling the 'DEALLOCATE PREPARE'
---echo # statement were damaged. This time invocation results in the error
---echo # ER_UNKNOWN_STMT_HANDLER since the prepared statement stmt_1
---echo # has just been released. So, just ignore this error.
---error ER_UNKNOWN_STMT_HANDLER
-EXECUTE stmt_2;
-
---echo # Check that the stmt_1 doesn't no longer exist
---error ER_UNKNOWN_STMT_HANDLER
-EXECUTE stmt_1;
-
---echo # Clean up
-DEALLOCATE PREPARE stmt_2;
-
---echo # Test case 18: Check that the 'CREATE VIEW' statement can be executed
+--echo # Test case 15: Check that the 'CREATE VIEW' statement can be executed
 --echo # as a prepared statement.
 --echo # Create environment for the test case
 CREATE TABLE t1 (a INT);
@@ -403,7 +351,7 @@ DEALLOCATE PREPARE stmt_1;
 DROP VIEW v1;
 DROP TABLE t1;
 
---echo # Test case 19: Check that the 'CREATE TRIGGER' statement can be executed
+--echo # Test case 16: Check that the 'CREATE TRIGGER' statement can be executed
 --echo # as a prepared statement.
 CREATE TABLE t1 (a INT);
 PREPARE stmt_1 FROM 'CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @a=1';
@@ -416,10 +364,10 @@ EXECUTE stmt_1;
 --error ER_TRG_ALREADY_EXISTS
 EXECUTE stmt_1;
 
---echo # Test case 20: Check that the 'DROP TRIGGER' statement can be executed
+--echo # Test case 17: Check that the 'DROP TRIGGER' statement can be executed
 --echo # as a prepared statement.
 --echo # This test relies on presence of the trigger trg1 created by
---echo # the test case 19.
+--echo # the test case 16.
 PREPARE stmt_1 FROM 'DROP TRIGGER trg1';
 EXECUTE stmt_1;
 --echo # Execute the same prepared statement the second time to check that
@@ -433,7 +381,7 @@ EXECUTE stmt_1;
 DEALLOCATE PREPARE stmt_1;
 DROP TABLE t1;
 
---echo # Test case 21: Check that XA related SQL statements can be executed
+--echo # Test case 18: Check that XA related SQL statements can be executed
 --echo # as prepared statements.
 --echo # Create the table t1 used by XA transaction.
 CREATE TABLE t1 (a INT);
@@ -533,7 +481,7 @@ DEALLOCATE PREPARE stmt_4;
 DEALLOCATE PREPARE stmt_5;
 DEALLOCATE PREPARE stmt_6;
 
---echo # Test case 22: Check that the  CREATE SERVER/ALTER SERVER/DROP SERVER
+--echo # Test case 19: Check that the  CREATE SERVER/ALTER SERVER/DROP SERVER
 --echo # statements can be executed as prepared statements.
 
 PREPARE stmt_1 FROM "CREATE SERVER s FOREIGN DATA WRAPPER mysql OPTIONS (USER 'u1', HOST '127.0.0.1')";
@@ -569,7 +517,7 @@ DEALLOCATE PREPARE stmt_1;
 DEALLOCATE PREPARE stmt_2;
 DEALLOCATE PREPARE stmt_3;
 
---echo # Test case 23: Check that the CREATE EVENT/ALTER EVENT/DROP EVENT
+--echo # Test case 20: Check that the CREATE EVENT/ALTER EVENT/DROP EVENT
 --echo # statements can be executed as a prepared statement
 
 PREPARE stmt_1 FROM "CREATE EVENT e1 ON SCHEDULE AT CURRENT_TIMESTAMP - INTERVAL 1 DAY ON COMPLETION PRESERVE DO SET @a=1";
@@ -618,7 +566,7 @@ DEALLOCATE PREPARE stmt_1;
 DEALLOCATE PREPARE stmt_2;
 DEALLOCATE PREPARE stmt_3;
 
---echo # Test case 24: Check that the SIGNAL and RESIGNAL statements
+--echo # Test case 21: Check that the SIGNAL and RESIGNAL statements
 --echo # can be executed as a prepared statement
 PREPARE stmt_1 FROM "SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=30001, MESSAGE_TEXT='Hello, world!'";
 --error 30001
@@ -641,7 +589,7 @@ EXECUTE stmt_1;
 --echo # Clean up
 DEALLOCATE PREPARE stmt_1;
 
---echo # Test case 25: Check that the 'SHOW RELAYLOG EVENTS' statement can be
+--echo # Test case 22: Check that the 'SHOW RELAYLOG EVENTS' statement can be
 --echo # executed as a prepared statement.
 PREPARE stmt_1 FROM 'SHOW RELAYLOG EVENTS';
 EXECUTE stmt_1;
@@ -652,7 +600,7 @@ EXECUTE stmt_1;
 --echo # Clean up
 DEALLOCATE PREPARE stmt_1;
 
---echo # Test case 26: Check the 'GET DIAGNOSTICS' statement
+--echo # Test case 23: Check the 'GET DIAGNOSTICS' statement
 --echo # can be executed as a prepared statement
 PREPARE stmt_1 FROM 'GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT';
 
@@ -674,19 +622,7 @@ EXECUTE stmt_1;
 --echo # Clean up
 DEALLOCATE PREPARE stmt_1;
 
---echo # Test case 27: Check the the statement 'EXECUTE IMMEDIATE'
---echo # can be executed as a prepared statement
-
-PREPARE stmt_1 FROM "EXECUTE IMMEDIATE 'SELECT 1'";
-EXECUTE stmt_1;
---echo # Execute the same prepared statement the second time to check that
---echo # no internal structures used for handling the 'EXECUTE IMMEDIATE'
---echo # statement were damaged.
-
---echo # Clean up
-DEALLOCATE PREPARE stmt_1;
-
---echo # Test Test case 28: Check the statements 'BACKUP'/'BACKUP UNLOCK'
+--echo # Test case 24: Check the statements 'BACKUP'/'BACKUP UNLOCK'
 --echo # can be executed as a prepared statement
 CREATE TABLE t1 (a INT);
 PREPARE stmt_1 FROM 'BACKUP LOCK t1';
@@ -709,7 +645,7 @@ DROP TABLE t1;
 DEALLOCATE PREPARE stmt_1;
 DEALLOCATE PREPARE stmt_2;
 
---echo # Test Test case 29: Check the statements 'CREATE/ALTER/DROP TABLEPSPACE'
+--echo # Test Test case 25: Check the statements 'CREATE/ALTER/DROP TABLEPSPACE'
 --echo # can be executed as a prepared statement
 
 PREPARE stmt_1 FROM "CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB";