1 files changed, 589 insertions, 1 deletions
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index 67f6f021434..00e0c4086bb 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -3697,5 +3697,593 @@ EXECUTE stmt;
 deallocate prepare stmt;
 drop table t1,t2,t3,t4;
 
-
 --echo # End of 5.5 tests
+
+--echo #
+--echo # Start of 10.2 tests
+--echo #
+
+--echo #
+--echo # MDEV-10709 Expressions as parameters to Dynamic SQL
+--echo #
+
+--echo #
+--echo # Using a simple expressions as an EXECUTE parameter
+--echo #
+
+PREPARE stmt FROM 'SELECT ? FROM DUAL';
+EXECUTE stmt USING 10;
+DEALLOCATE PREPARE stmt;
+
+PREPARE stmt FROM 'SELECT ? FROM DUAL';
+EXECUTE stmt USING TO_BASE64('xxx');
+DEALLOCATE PREPARE stmt;
+
+PREPARE stmt FROM 'SELECT ?+? FROM DUAL';
+EXECUTE stmt USING 10, 10 + 10;
+DEALLOCATE PREPARE stmt;
+
+PREPARE stmt FROM 'SELECT CONCAT(?,?) FROM DUAL';
+EXECUTE stmt USING 'xxx', CONCAT('yyy','zzz');
+DEALLOCATE PREPARE stmt;
+
+--echo #
+--echo # Testing disallowed expressions in USING
+--echo #
+
+PREPARE stmt FROM 'SELECT ? FROM DUAL';
+--error ER_PARSE_ERROR
+EXECUTE stmt USING (SELECT 1);
+DEALLOCATE PREPARE stmt;
+
+CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test';
+PREPARE stmt FROM 'SELECT ? FROM DUAL';
+--error ER_SUBQUERIES_NOT_SUPPORTED
+EXECUTE stmt USING f1();
+DEALLOCATE PREPARE stmt;
+DROP FUNCTION f1;
+
+--echo #
+--echo # Testing erroneous expressions in USING
+--echo #
+
+PREPARE stmt FROM 'SELECT ?';
+--error ER_CANT_AGGREGATE_2COLLATIONS
+EXECUTE stmt USING _latin1'a'=_latin2'a';
+DEALLOCATE PREPARE stmt;
+
+PREPARE stmt FROM 'SELECT ?';
+--error ER_OPERAND_COLUMNS
+EXECUTE stmt USING ROW(1,2);
+DEALLOCATE PREPARE stmt;
+
+--echo #
+--echo # Creating tables from EXECUTE parameters
+--echo #
+
+PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ? AS c1 FROM DUAL';
+EXECUTE stmt USING 10;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING 10.123;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING 10.123e0;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_DATE;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIMESTAMP;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIMESTAMP(3);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIMESTAMP(6);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIME;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIME(3);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+EXECUTE stmt USING CURRENT_TIME(6);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+DEALLOCATE PREPARE stmt;
+
+
+--echo #
+--echo # Using a user variable as an EXECUTE..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;
+PREPARE stmt FROM 'CALL p1(?)';
+EXECUTE stmt USING @a;
+SELECT @a;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Using an SP variable as an EXECUTE..USING out parameter
+--echo #
+
+DELIMITER /;
+CREATE PROCEDURE p1 (OUT a INT)
+BEGIN
+  SET a=10;
+END;
+/
+CREATE PROCEDURE p2 (OUT a INT)
+BEGIN
+  PREPARE stmt FROM 'CALL p1(?)';
+  EXECUTE stmt USING a;
+END;
+/
+DELIMITER ;/
+SET @a= 1;
+CALL p2(@a);
+SELECT @a;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Testing re-prepare on a table metadata update between PREPARE and EXECUTE
+--echo #
+
+CREATE TABLE t1 (a INT);
+DELIMITER /;
+CREATE PROCEDURE p1(a INT)
+BEGIN
+  INSERT INTO t1 VALUES (a);
+END;
+/
+DELIMITER ;/
+PREPARE stmt FROM 'CALL p1(?)';
+EXECUTE stmt USING 10;
+SELECT * FROM t1;
+CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=NEW.a+1;
+EXECUTE stmt USING 20;
+SELECT * FROM t1;
+DEALLOCATE PREPARE stmt;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of MDEV-10709 Expressions as parameters to Dynamic SQL
+--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 # MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions
+--echo #
+
+--echo #
+--echo # Testing erroneous and diallowed prepare source
+--echo #
+
+--error ER_CANT_AGGREGATE_2COLLATIONS
+EXECUTE IMMEDIATE CONCAT(_latin1'SELECT 1 AS c FROM ', _latin2 'DUAL');
+--error ER_CANT_AGGREGATE_2COLLATIONS
+PREPARE stmt FROM CONCAT(_latin1'SELECT 1 AS c FROM ', _latin2 'DUAL');
+
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE (SELECT 'SELECT 1');
+--error ER_PARSE_ERROR
+PREPARE stmt FROM (SELECT 'SELECT 1');
+
+--error ER_BAD_FIELD_ERROR
+EXECUTE IMMEDIATE a;
+--error ER_BAD_FIELD_ERROR
+PREPARE stmt FROM a;
+
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE NULL;
+--error ER_PARSE_ERROR
+PREPARE stmt FROM NULL;
+
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE CONCAT(NULL);
+--error ER_PARSE_ERROR
+PREPARE stmt FROM CONCAT(NULL);
+
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE ? USING 'SELECT 1';
+
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE 10;
+
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE TIME'10:20:30';
+
+--error ER_OPERAND_COLUMNS
+EXECUTE IMMEDIATE ROW('SELECT 1','SELECT 2');
+
+--error ER_INVALID_GROUP_FUNC_USE
+EXECUTE IMMEDIATE MAX('SELECT 1 AS c');
+
+--error ER_BAD_FIELD_ERROR
+EXECUTE IMMEDIATE DEFAULT(a);
+
+--error ER_BAD_FIELD_ERROR
+EXECUTE IMMEDIATE VALUES(a);
+
+
+CREATE FUNCTION f1() RETURNS VARCHAR(64) RETURN 't1';
+--error ER_SUBQUERIES_NOT_SUPPORTED
+EXECUTE IMMEDIATE f1();
+--error ER_SUBQUERIES_NOT_SUPPORTED
+PREPARE stmt FROM f1();
+DROP FUNCTION f1;
+
+--error ER_SUBQUERIES_NOT_SUPPORTED
+EXECUTE IMMEDIATE non_existent();
+
+
+--echo #
+--echo # Testing literals in prepare source
+--echo #
+EXECUTE IMMEDIATE N'SELECT 1 AS c';
+EXECUTE IMMEDIATE _latin1'SELECT 1 AS c';
+EXECUTE IMMEDIATE 'SELECT '  '1'  ' AS c'  ' FROM '  'DUAL';
+EXECUTE IMMEDIATE 0x53454C4543542031 /*This is 'SELECT 1'*/;
+
+--echo #
+--echo # Testing user variables in prepare source
+--echo #
+
+SET @stmt='SELECT 1 AS c FROM DUAL';
+EXECUTE IMMEDIATE @stmt;
+PREPARE stmt FROM @stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @table_name='DUAL';
+EXECUTE IMMEDIATE CONCAT('SELECT 1 AS a FROM ', @table_name);
+PREPARE stmt FROM CONCAT('SELECT 1 AS a FROM ', @table_name);
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+--echo #
+--echo # Testing SP parameters and variables in prepare source
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1(table_name VARCHAR(64))
+BEGIN
+  EXECUTE IMMEDIATE CONCAT('SELECT 1 AS c FROM ', table_name);
+END;
+$$
+DELIMITER ;$$
+CALL p1('DUAL');
+DROP PROCEDURE p1;
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE table_name VARCHAR(64) DEFAULT 'DUAL';
+  EXECUTE IMMEDIATE CONCAT('SELECT 1 AS c FROM ', table_name);
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Testing complex expressions
+--echo #
+EXECUTE IMMEDIATE CONVERT('SELECT 1 AS c' USING utf8);
+EXECUTE IMMEDIATE CAST('SELECT 1 AS c' AS CHAR);
+EXECUTE IMMEDIATE _latin1'SELECT 1 AS c' COLLATE latin1_bin;
+EXECUTE IMMEDIATE (((('SELECT 1 AS c'))));
+EXECUTE IMMEDIATE CASE WHEN 1>2 THEN 'SELECT 1 AS c' ELSE 'SELECT 2 AS c' END;
+EXECUTE IMMEDIATE TRIM('SELECT 1 AS c');
+EXECUTE IMMEDIATE SUBSTRING('SELECT 1 AS c' FROM 1);
+EXECUTE IMMEDIATE COALESCE(NULL, 'SELECT 1 AS c');
+
+--echo #
+--echo # Testing SET STATEMENT and system variables
+--echo #
+CREATE TABLE t1 (a INT);
+SET STATEMENT max_sort_length=1025 FOR EXECUTE IMMEDIATE CONCAT('INSERT INTO t1 VALUES (', @@max_sort_length, ')');
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions
+--echo #
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
+
+
+--echo #
+--echo # MDEV-11360 Dynamic SQL: DEFAULT as a bind parameter
+--echo #
+
+# Correct usage
+CREATE TABLE t1 (a INT DEFAULT 10, b INT DEFAULT NULL);
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?,?)' USING DEFAULT, DEFAULT;
+SELECT * FROM t1;
+UPDATE t1 SET a=20, b=30;
+SELECT * FROM t1;
+EXECUTE IMMEDIATE 'UPDATE t1 SET a=?,b=?' USING DEFAULT, DEFAULT;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+# Incorrect usage in a expression in INSERT..VALUES
+CREATE TABLE t1 (a INT DEFAULT 10);
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?+1)' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (CONCAT(?,?))' USING DEFAULT, 'test';
+DROP TABLE t1;
+
+# Incorrect usage in UPDATE..SET
+CREATE TABLE t1 (a INT DEFAULT 10);
+INSERT INTO t1 VALUES (20);
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'UPDATE t1 SET a=?+1' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'UPDATE t1 SET a=CONCAT(?,?)' USING DEFAULT, 'test';
+DROP TABLE t1;
+
+# Incorrect usage in not an UPDATE/INSERT query at all
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CAST(? AS SIGNED)' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CAST(? AS DOUBLE)' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CAST(? AS CHAR)' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CAST(? AS DECIMAL(10,1))' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CAST(? AS TIME)' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CAST(? AS DATE)' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CAST(? AS DATETIME)' USING DEFAULT;
+
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT ?+1' USING DEFAULT;
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT CONCAT(?,?)' USING DEFAULT,'test';
+
+# Incorrect usage in the LIMIT clause
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT 1 LIMIT ?' USING DEFAULT;
+CREATE TABLE t1 (a INT DEFAULT 10);
+INSERT INTO t1 VALUES (1),(2),(3);
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'SELECT * FROM t1 LIMIT ?' USING DEFAULT;
+DROP TABLE t1;
+
+--echo # The output of this query in 'Note' is a syntactically incorrect query.
+--echo # But as it's never logged, it's ok. It should be human readable only.
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING DEFAULT;
+
+# This tests Item_param::eq() for DEFAULT as a bound value
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+--error ER_INVALID_DEFAULT_PARAM
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING DEFAULT,DEFAULT;
+DROP TABLE t1;