MDEV-10585 EXECUTE IMMEDIATE statement

1 files changed, 205 insertions, 0 deletions

diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index b8f636e35ac..89db0a91bd0 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -3841,5 +3841,210 @@ DROP TABLE t1;
 --echo #
 
 --echo #
+--echo # MDEV-10585 EXECUTE IMMEDIATE statement
+--echo #
+
+EXECUTE IMMEDIATE 'SELECT 1 AS a';
+SET @a=10;
+EXECUTE IMMEDIATE 'SELECT ? AS a' USING @a;
+EXECUTE IMMEDIATE 'SELECT ? AS a' USING 20;
+
+
+--echo #
+--echo # Erroneous queries
+--echo #
+
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE 'xxx';
+
+--error ER_WRONG_ARGUMENTS
+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
+EXECUTE IMMEDIATE 'SELECT ?' USING _latin1'a'=_latin2'a';
+
+--error ER_OPERAND_COLUMNS
+EXECUTE IMMEDIATE 'SELECT ?' USING ROW(1,2);
+
+--echo #
+--echo # Testing disallowed expressions in USING
+--echo #
+
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING (SELECT 1);
+
+CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
+--error ER_SUBQUERIES_NOT_SUPPORTED
+EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING f1();
+DROP FUNCTION f1;
+
+--echo #
+--echo # DDL
+--echo #
+
+EXECUTE IMMEDIATE 'CREATE TABLE t1 (a INT)';
+EXECUTE IMMEDIATE 'SHOW CREATE TABLE t1';
+EXECUTE IMMEDIATE 'DROP TABLE t1';
+
+SET @stmt= 'CREATE TABLE t1 (a INT)';
+EXECUTE IMMEDIATE @stmt;
+SET @stmt= 'SHOW CREATE TABLE t1';
+EXECUTE IMMEDIATE @stmt;
+SET @stmt= 'DROP TABLE t1';
+EXECUTE IMMEDIATE @stmt;
+
+
+--echo #
+--echo # DDL with parameters
+--echo #
+
+SET @a= 10, @b= 10.1, @c= 10e0, @d='str';
+EXECUTE IMMEDIATE
+  'CREATE TABLE t1 AS SELECT ? AS a,? AS b,? AS c,? AS d'
+  USING @a,@b,@c,@d;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+EXECUTE IMMEDIATE
+  'CREATE TABLE t1 AS SELECT ? AS a,? AS b,? AS c,? AS d'
+  USING 10, 10.1, 10e0, 'str';
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+EXECUTE IMMEDIATE
+  'CREATE TABLE t1 AS SELECT ? AS t1,? AS t2, ? AS d1,? AS dt1, ? AS dt2'
+  USING TIME'10:20:30',
+        TIME'10:20:30.123',
+        DATE'2001-01-01',
+        TIMESTAMP'2001-01-01 10:20:30',
+        TIMESTAMP'2001-01-01 10:20:30.123';
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Using a user variable as an EXECUTE IMMEDIATE..USING out parameter
+--echo #
+
+DELIMITER /;
+CREATE PROCEDURE p1(OUT a INT)
+BEGIN
+  SET a:= 10;
+END;
+/
+DELIMITER ;/
+SET @a=1;
+CALL p1(@a);
+SELECT @a;
+SET @a=2;
+EXECUTE IMMEDIATE 'CALL p1(?)' USING @a;
+SELECT @a;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Using an SP variable as an EXECUTE IMMEDIATE..USING out parameter
+--echo #
+
+DELIMITER /;
+CREATE PROCEDURE p1 (OUT a INT)
+BEGIN
+  SET a=10;
+END;
+/
+CREATE PROCEDURE p2 (OUT a INT)
+BEGIN
+  EXECUTE IMMEDIATE 'CALL p1(?)' USING a;
+END;
+/
+DELIMITER ;/
+SET @a= 1;
+CALL p2(@a);
+SELECT @a;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Changing user variables
+--echo #
+
+SET @a=10;
+EXECUTE IMMEDIATE 'SET @a=@a+1';
+SELECT @a;
+
+
+--echo #
+--echo # SET STATEMENT
+--echo #
+
+SET @@max_sort_length=1024;
+EXECUTE IMMEDIATE 'SET STATEMENT max_sort_length=1025 FOR SELECT @@max_sort_length';
+SELECT @@max_sort_length;
+SET @@max_sort_length=DEFAULT;
+
+
+--echo #
+--echo # Similar to prepared EXECUTE, IMMEDIATE is not allowed in stored functions
+--echo #
+DELIMITER $$;
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+  EXECUTE IMMEDIATE 'DO 1';
+  RETURN 1;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # Status variables
+--echo #
+CREATE FUNCTION get_status_var(name TEXT) RETURNS INT
+       RETURN (SELECT CAST(VARIABLE_VALUE AS INT)
+           FROM INFORMATION_SCHEMA.SESSION_STATUS
+           WHERE VARIABLE_NAME=name);
+DELIMITER $$;
+CREATE PROCEDURE test_status_var(name TEXT)
+BEGIN
+  SET @cnt0=get_status_var(name);
+  EXECUTE IMMEDIATE 'DO 1';
+  SET @cnt1=get_status_var(name);
+  SELECT @cnt1-@cnt0 AS increment;
+END;
+$$
+DELIMITER ;$$
+--echo # Note, EXECUTE IMMEDIATE does not increment COM_EXECUTE_SQL
+--echo # It increments COM_EXECUTE_IMMEDIATE instead.
+CALL test_status_var('COM_EXECUTE_SQL');
+CALL test_status_var('COM_EXECUTE_IMMEDIATE');
+CALL test_status_var('COM_STMT_PREPARE');
+CALL test_status_var('COM_STMT_EXECUTE');
+CALL test_status_var('COM_STMT_CLOSE');
+
+DROP PROCEDURE test_status_var;
+DROP FUNCTION get_status_var;
+
+--echo #
+--echo # End of MDEV-10585 EXECUTE IMMEDIATE statement
+--echo #
+
+--echo #
 --echo # End of 10.2 tests
 --echo #