diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/ctype_ucs.result | 10 | ||||
| -rw-r--r-- | mysql-test/r/keywords.result | 5 | ||||
| -rw-r--r-- | mysql-test/r/mysqld--help.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/ps.result | 213 | ||||
| -rw-r--r-- | mysql-test/suite/binlog/r/binlog_stm_ps.result | 26 | ||||
| -rw-r--r-- | mysql-test/suite/binlog/t/binlog_stm_ps.test | 16 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result | 4 | ||||
| -rw-r--r-- | mysql-test/t/ctype_ucs.test | 12 | ||||
| -rw-r--r-- | mysql-test/t/keywords.test | 7 | ||||
| -rw-r--r-- | mysql-test/t/ps.test | 205 |
10 files changed, 497 insertions, 3 deletions
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 0a75b11e064..91860cde1db 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -6222,5 +6222,15 @@ DROP TABLE t1; # SET STORAGE_ENGINE=Default; # +# MDEV-10585 EXECUTE IMMEDIATE statement +# +SET character_set_connection=ucs2; +EXECUTE IMMEDIATE 'SELECT COLLATION("a")'; +COLLATION("a") +ucs2_general_ci +SET @stmt='SELECT COLLATION("a")'; +EXECUTE IMMEDIATE @stmt; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 +# # End of 10.2 tests # diff --git a/mysql-test/r/keywords.result b/mysql-test/r/keywords.result index a3588017e97..f5bf600dc40 100644 --- a/mysql-test/r/keywords.result +++ b/mysql-test/r/keywords.result @@ -275,3 +275,8 @@ set option=1; ERROR HY000: Unknown system variable 'option' set option option=1; ERROR HY000: Unknown system variable 'option' +# +# MDEV-10585 EXECUTE IMMEDIATE statement +# +CREATE TABLE immediate (immediate int); +DROP TABLE immediate; diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index e498caebb4d..862113c224a 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -1374,7 +1374,7 @@ performance-schema-max-rwlock-instances -1 performance-schema-max-socket-classes 10 performance-schema-max-socket-instances -1 performance-schema-max-stage-classes 150 -performance-schema-max-statement-classes 184 +performance-schema-max-statement-classes 185 performance-schema-max-table-handles -1 performance-schema-max-table-instances -1 performance-schema-max-thread-classes 50 diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 8e1c5056b88..cf2cbce8f93 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -4289,5 +4289,218 @@ DROP TABLE t1; # End of MDEV-10709 Expressions as parameters to Dynamic SQL # # +# MDEV-10585 EXECUTE IMMEDIATE statement +# +EXECUTE IMMEDIATE 'SELECT 1 AS a'; +a +1 +SET @a=10; +EXECUTE IMMEDIATE 'SELECT ? AS a' USING @a; +a +10 +EXECUTE IMMEDIATE 'SELECT ? AS a' USING 20; +a +20 +# +# Erroneous queries +# +EXECUTE IMMEDIATE 'xxx'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'xxx' at line 1 +EXECUTE IMMEDIATE 'SELECT 1' USING @a; +ERROR HY000: Incorrect arguments to EXECUTE +EXECUTE IMMEDIATE 'SELECT ?'; +ERROR HY000: Incorrect arguments to EXECUTE +EXECUTE IMMEDIATE 'EXECUTE IMMEDIATE "SELECT 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'; +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); +ERROR 21000: Operand should contain 1 column(s) +# +# Testing disallowed expressions in USING +# +EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING (SELECT 1); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 1)' at line 1 +CREATE FUNCTION f1() RETURNS VARCHAR(10) RETURN 'test'; +EXECUTE IMMEDIATE 'SELECT ? FROM DUAL' USING f1(); +ERROR 42000: EXECUTE..USING does not support subqueries or stored functions +DROP FUNCTION f1; +# +# DDL +# +EXECUTE IMMEDIATE 'CREATE TABLE t1 (a INT)'; +EXECUTE IMMEDIATE 'SHOW CREATE TABLE t1'; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +EXECUTE IMMEDIATE 'DROP TABLE t1'; +SET @stmt= 'CREATE TABLE t1 (a INT)'; +EXECUTE IMMEDIATE @stmt; +SET @stmt= 'SHOW CREATE TABLE t1'; +EXECUTE IMMEDIATE @stmt; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SET @stmt= 'DROP TABLE t1'; +EXECUTE IMMEDIATE @stmt; +# +# DDL with parameters +# +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(21) NOT NULL, + `b` decimal(3,1) DEFAULT NULL, + `c` double NOT NULL, + `d` varchar(3) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(21) NOT NULL, + `b` decimal(3,1) DEFAULT NULL, + `c` double NOT NULL, + `d` varchar(3) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `t1` time DEFAULT NULL, + `t2` time(3) DEFAULT NULL, + `d1` date DEFAULT NULL, + `dt1` datetime DEFAULT NULL, + `dt2` datetime(3) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# Using a user variable as an EXECUTE IMMEDIATE..USING out parameter +# +CREATE PROCEDURE p1(OUT a INT) +BEGIN +SET a:= 10; +END; +/ +SET @a=1; +CALL p1(@a); +SELECT @a; +@a +10 +SET @a=2; +EXECUTE IMMEDIATE 'CALL p1(?)' USING @a; +SELECT @a; +@a +10 +DROP PROCEDURE p1; +# +# Using an SP variable as an EXECUTE IMMEDIATE..USING out parameter +# +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; +/ +SET @a= 1; +CALL p2(@a); +SELECT @a; +@a +10 +DROP PROCEDURE p2; +DROP PROCEDURE p1; +# +# Changing user variables +# +SET @a=10; +EXECUTE IMMEDIATE 'SET @a=@a+1'; +SELECT @a; +@a +11 +# +# SET STATEMENT +# +SET @@max_sort_length=1024; +EXECUTE IMMEDIATE 'SET STATEMENT max_sort_length=1025 FOR SELECT @@max_sort_length'; +@@max_sort_length +1025 +SELECT @@max_sort_length; +@@max_sort_length +1024 +SET @@max_sort_length=DEFAULT; +# +# Similar to prepared EXECUTE, IMMEDIATE is not allowed in stored functions +# +CREATE FUNCTION f1() RETURNS INT +BEGIN +EXECUTE IMMEDIATE 'DO 1'; +RETURN 1; +END; +$$ +ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger +# +# Status variables +# +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); +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; +$$ +# Note, EXECUTE IMMEDIATE does not increment COM_EXECUTE_SQL +# It increments COM_EXECUTE_IMMEDIATE instead. +CALL test_status_var('COM_EXECUTE_SQL'); +increment +0 +CALL test_status_var('COM_EXECUTE_IMMEDIATE'); +increment +1 +CALL test_status_var('COM_STMT_PREPARE'); +increment +1 +CALL test_status_var('COM_STMT_EXECUTE'); +increment +1 +CALL test_status_var('COM_STMT_CLOSE'); +increment +1 +DROP PROCEDURE test_status_var; +DROP FUNCTION get_status_var; +# +# End of MDEV-10585 EXECUTE IMMEDIATE statement +# +# # End of 10.2 tests # diff --git a/mysql-test/suite/binlog/r/binlog_stm_ps.result b/mysql-test/suite/binlog/r/binlog_stm_ps.result index a64871ad679..bca298d029d 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_ps.result +++ b/mysql-test/suite/binlog/r/binlog_stm_ps.result @@ -103,3 +103,29 @@ master-bin.000002 # Query # # use `test`; INSERT INTO t1 VALUES (TIMESTAMP'2001- master-bin.000002 # Query # # COMMIT DROP TABLE t1; SET TIMESTAMP=DEFAULT; +# +# MDEV-10585 EXECUTE IMMEDIATE statement +# +FLUSH LOGS; +CREATE TABLE t1 (a INT); +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (101)'; +SET @a=102; +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING @a; +SET @a=103; +SET @stmt='INSERT INTO t1 VALUES (?)'; +EXECUTE IMMEDIATE @stmt USING @a; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000003 # Binlog_checkpoint # # master-bin.000003 +master-bin.000003 # Gtid # # GTID #-#-# +master-bin.000003 # Query # # use `test`; CREATE TABLE t1 (a INT) +master-bin.000003 # Gtid # # BEGIN GTID #-#-# +master-bin.000003 # Query # # use `test`; INSERT INTO t1 VALUES (101) +master-bin.000003 # Query # # COMMIT +master-bin.000003 # Gtid # # BEGIN GTID #-#-# +master-bin.000003 # Query # # use `test`; INSERT INTO t1 VALUES (102) +master-bin.000003 # Query # # COMMIT +master-bin.000003 # Gtid # # BEGIN GTID #-#-# +master-bin.000003 # Query # # use `test`; INSERT INTO t1 VALUES (103) +master-bin.000003 # Query # # COMMIT +DROP TABLE t1; diff --git a/mysql-test/suite/binlog/t/binlog_stm_ps.test b/mysql-test/suite/binlog/t/binlog_stm_ps.test index 8e52b71d3f0..38f13507cf1 100644 --- a/mysql-test/suite/binlog/t/binlog_stm_ps.test +++ b/mysql-test/suite/binlog/t/binlog_stm_ps.test @@ -54,3 +54,19 @@ SELECT * FROM t1; source include/show_binlog_events.inc; DROP TABLE t1; SET TIMESTAMP=DEFAULT; + +--echo # +--echo # MDEV-10585 EXECUTE IMMEDIATE statement +--echo # + +FLUSH LOGS; +CREATE TABLE t1 (a INT); +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (101)'; +SET @a=102; +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING @a; +SET @a=103; +SET @stmt='INSERT INTO t1 VALUES (?)'; +EXECUTE IMMEDIATE @stmt USING @a; +--let $binlog_file = LAST +source include/show_binlog_events.inc; +DROP TABLE t1; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 9b31ae23613..2a6c24653e1 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -3021,9 +3021,9 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STATEMENT_CLASSES SESSION_VALUE NULL -GLOBAL_VALUE 184 +GLOBAL_VALUE 185 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 184 +DEFAULT_VALUE 185 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Maximum number of statement instruments. diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index aa5838e7c6d..2090f35fa70 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -975,5 +975,17 @@ let $coll_pad='ucs2_bin'; --source include/ctype_pad_all_engines.inc --echo # +--echo # MDEV-10585 EXECUTE IMMEDIATE statement +--echo # +SET character_set_connection=ucs2; +EXECUTE IMMEDIATE 'SELECT COLLATION("a")'; + +# We don't support character sets with mbminlen>1 in the parser yet +# Returning "syntax error" is fine +SET @stmt='SELECT COLLATION("a")'; +--error ER_PARSE_ERROR +EXECUTE IMMEDIATE @stmt; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/t/keywords.test b/mysql-test/t/keywords.test index 54052e65014..40beee9e3c1 100644 --- a/mysql-test/t/keywords.test +++ b/mysql-test/t/keywords.test @@ -173,3 +173,10 @@ drop table option; set option=1; --error 1193 set option option=1; + +--echo # +--echo # MDEV-10585 EXECUTE IMMEDIATE statement +--echo # + +CREATE TABLE immediate (immediate int); +DROP TABLE immediate; 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 # |