summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorOleksandr Byelkin <sanja@mariadb.com>2014-10-22 19:47:05 +0200
committerOleksandr Byelkin <sanja@mariadb.com>2014-10-22 19:58:50 +0200
commitaf93dc48cffdc9a071e3ec6e1fe51f1d7d06686b (patch)
tree938d7fa8cbefa67161d50172ff962a842904d71c
parentc3db4459561bc491582bef6bea7b83e9fe464a10 (diff)
downloadmariadb-git-bb-set-statement.tar.gz
MDEV-5231: Per query variables from Percona Server (rewritten)bb-set-statement
Diffstat
-rw-r--r--mysql-test/r/events_bugs.result10
-rw-r--r--mysql-test/r/kill.result2
-rw-r--r--mysql-test/r/set_statement.result1028
-rw-r--r--mysql-test/suite/rpl/include/rpl_set_statement.inc31
-rw-r--r--mysql-test/suite/rpl/r/rpl_set_statement.result140
-rw-r--r--mysql-test/suite/rpl/t/rpl_set_statement.test57
-rw-r--r--mysql-test/t/events_bugs.test10
-rw-r--r--mysql-test/t/kill.test2
-rw-r--r--mysql-test/t/set_statement.test960
-rw-r--r--sql/item.h11
-rw-r--r--sql/item_func.cc2
-rw-r--r--sql/lex.h1
-rw-r--r--sql/set_var.cc13
-rw-r--r--sql/set_var.h7
-rw-r--r--sql/share/errmsg-utf8.txt2
-rw-r--r--sql/sql_class.cc1
-rw-r--r--sql/sql_class.h4
-rw-r--r--sql/sql_lex.cc13
-rw-r--r--sql/sql_lex.h3
-rw-r--r--sql/sql_parse.cc103
-rw-r--r--sql/sql_plugin.h8
-rw-r--r--sql/sql_prepare.cc8
-rw-r--r--sql/sql_yacc.yy74
23 files changed, 2446 insertions, 44 deletions
diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result
index e3599218aac..7ce3c740cd4 100644
--- a/mysql-test/r/events_bugs.result
+++ b/mysql-test/r/events_bugs.result
@@ -218,13 +218,13 @@ drop event events_test.mysqltest_user1;
drop user mysqltest_user1@localhost;
drop database mysqltest_db1;
create event e_53 on schedule at (select s1 from ttx) do drop table t;
-ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
+ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions.
create event e_53 on schedule every (select s1 from ttx) second do drop table t;
-ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
+ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions.
create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t;
-ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
+ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions.
create event e_53 on schedule every 5 second ends (select s1 from ttx) do drop table t;
-ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
+ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions.
drop event if exists e_16;
drop procedure if exists p_16;
create event e_16 on schedule every 1 second do set @a=5;
@@ -265,7 +265,7 @@ begin
call p22830_wait();
select 123;
end|
-ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
+ERROR 42000: CREATE/ALTER EVENT does not support subqueries or stored functions.
create event e22830_1 on schedule every 1 hour do
begin
call p22830_wait();
diff --git a/mysql-test/r/kill.result b/mysql-test/r/kill.result
index 6afce67cc1a..6e108fda634 100644
--- a/mysql-test/r/kill.result
+++ b/mysql-test/r/kill.result
@@ -24,7 +24,7 @@ SELECT 4;
4
4
KILL (SELECT COUNT(*) FROM mysql.user);
-ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
+ERROR 42000: KILL does not support subqueries or stored functions.
SET DEBUG_SYNC= 'thread_end SIGNAL con1_end';
SET DEBUG_SYNC= 'before_do_command_net_read SIGNAL con1_read WAIT_FOR kill';
SET DEBUG_SYNC= 'now WAIT_FOR con1_read';
diff --git a/mysql-test/r/set_statement.result b/mysql-test/r/set_statement.result
new file mode 100644
index 00000000000..faa79951c55
--- /dev/null
+++ b/mysql-test/r/set_statement.result
@@ -0,0 +1,1028 @@
+'# SET STATEMENT ..... FOR .... TEST'
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS myProc;
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+DROP PROCEDURE IF EXISTS p3;
+DROP PROCEDURE IF EXISTS p4;
+DROP PROCEDURE IF EXISTS p5;
+DROP PROCEDURE IF EXISTS p6;
+DROP VIEW IF EXISTS v1;
+DROP TABLE IF EXISTS STATEMENT;
+'# Setup database'
+CREATE TABLE t1 (v1 INT, v2 INT);
+INSERT INTO t1 VALUES (1,2);
+INSERT INTO t1 VALUES (3,4);
+''
+'#------------------ STATEMENT Test 1 -----------------------#'
+'# Initialize variables to known setting'
+SET SESSION sort_buffer_size=100000;
+''
+'# Pre-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+Variable_name Value
+sort_buffer_size 100000
+SET STATEMENT sort_buffer_size=150000 FOR SELECT *,@@sort_buffer_size FROM t1;
+v1 v2 @@sort_buffer_size
+1 2 150000
+3 4 150000
+''
+'# Post-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+Variable_name Value
+sort_buffer_size 100000
+''
+'#------------------ STATEMENT Test 2 -----------------------#'
+'# Initialize variables to known setting'
+SET SESSION binlog_format=mixed;
+SET SESSION sort_buffer_size=100000;
+'# Pre-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+Variable_name Value
+sort_buffer_size 100000
+SHOW SESSION VARIABLES LIKE 'binlog_format';
+Variable_name Value
+binlog_format MIXED
+SET STATEMENT sort_buffer_size=150000, binlog_format=row
+FOR SELECT *,@@sort_buffer_size,@@binlog_format FROM t1;
+v1 v2 @@sort_buffer_size @@binlog_format
+1 2 150000 ROW
+3 4 150000 ROW
+'# Post-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+Variable_name Value
+sort_buffer_size 100000
+SHOW SESSION VARIABLES LIKE 'binlog_format';
+Variable_name Value
+binlog_format MIXED
+''
+'#------------------ STATEMENT Test 3 -----------------------#'
+'# set initial variable value, make prepared statement
+SET SESSION binlog_format=row;
+PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT *,@@binlog_format FROM t1';
+''
+'# Change variable setting'
+SET SESSION binlog_format=mixed;
+''
+'# Pre-STATEMENT variable value'
+''
+SHOW SESSION VARIABLES LIKE 'binlog_format';
+Variable_name Value
+binlog_format MIXED
+''
+EXECUTE stmt1;
+v1 v2 @@binlog_format
+1 2 ROW
+3 4 ROW
+''
+'# Post-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'binlog_format';
+Variable_name Value
+binlog_format MIXED
+''
+DEALLOCATE PREPARE stmt1;
+'#------------------ STATEMENT Test 4 -----------------------#'
+'# set initial variable value, make prepared statement
+SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1;
+''
+'# Pre-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
+Variable_name Value
+myisam_sort_buffer_size 500000
+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
+Variable_name Value
+myisam_repair_threads 1
+''
+SET STATEMENT myisam_sort_buffer_size=800000,
+myisam_repair_threads=2 FOR OPTIMIZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 optimize status OK
+''
+'# Post-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
+Variable_name Value
+myisam_sort_buffer_size 500000
+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
+Variable_name Value
+myisam_repair_threads 1
+''
+'#------------------ STATEMENT Test 5 -----------------------#'
+'# Initialize variables to known setting'
+SET SESSION sort_buffer_size=100000;
+''
+'# Pre-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+Variable_name Value
+sort_buffer_size 100000
+''
+SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2;
+ERROR 42S02: Table 'test.t2' doesn't exist
+''
+'# Post-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+Variable_name Value
+sort_buffer_size 100000
+''
+'#------------------ STATEMENT Test 6 -----------------------#'
+'# Initialize variables to known setting'
+SET SESSION keep_files_on_create=ON;
+''
+'# Pre-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
+Variable_name Value
+keep_files_on_create ON
+''
+SET STATEMENT keep_files_on_create=OFF FOR SELECT *,@@keep_files_on_create FROM t1;
+v1 v2 @@keep_files_on_create
+1 2 0
+3 4 0
+''
+'# Post-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
+Variable_name Value
+keep_files_on_create ON
+''
+'#------------------ STATEMENT Test 7 -----------------------#'
+'# Initialize variables to known setting'
+SET SESSION max_join_size=2222220000000;
+''
+'# Pre-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'max_join_size';
+Variable_name Value
+max_join_size 2222220000000
+''
+SET STATEMENT max_join_size=1000000000000 FOR SELECT *,@@max_join_size FROM t1;
+v1 v2 @@max_join_size
+1 2 1000000000000
+3 4 1000000000000
+''
+'# Post-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'max_join_size';
+Variable_name Value
+max_join_size 2222220000000
+''
+'#------------------Test 8-----------------------#'
+'# Initialize test variables'
+SET SESSION myisam_sort_buffer_size=500000,
+myisam_repair_threads=1,
+sort_buffer_size = 200000,
+max_join_size=2222220000000,
+keep_files_on_create=ON;
+''
+'# LONG '
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+Variable_name Value
+sort_buffer_size 200000
+SET STATEMENT sort_buffer_size = 100000
+FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+Variable_name Value
+sort_buffer_size 100000
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+Variable_name Value
+sort_buffer_size 200000
+''
+'# MY_BOOL '
+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
+Variable_name Value
+keep_files_on_create ON
+SET STATEMENT keep_files_on_create=OFF
+FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
+Variable_name Value
+keep_files_on_create OFF
+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
+Variable_name Value
+keep_files_on_create ON
+''
+'# INT/LONG '
+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
+Variable_name Value
+myisam_repair_threads 1
+SET STATEMENT myisam_repair_threads=2
+FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
+Variable_name Value
+myisam_repair_threads 2
+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
+Variable_name Value
+myisam_repair_threads 1
+''
+'# ULONGLONG '
+SHOW SESSION VARIABLES LIKE 'max_join_size';
+Variable_name Value
+max_join_size 2222220000000
+SET STATEMENT max_join_size=2000000000000
+FOR SHOW SESSION VARIABLES LIKE 'max_join_size';
+Variable_name Value
+max_join_size 2000000000000
+SHOW SESSION VARIABLES LIKE 'max_join_size';
+Variable_name Value
+max_join_size 2222220000000
+''
+'#------------------Test 9-----------------------#'
+'# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+myisam_repair_threads=1,
+sort_buffer_size=100000,
+binlog_format=mixed,
+keep_files_on_create=ON,
+max_join_size=2222220000000;
+''
+''
+'# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+CREATE FUNCTION myProc (cost DECIMAL(10,2))
+RETURNS DECIMAL(10,2)
+SQL SECURITY DEFINER
+tax: BEGIN
+DECLARE order_tax DECIMAL(10,2);
+SET order_tax = cost * .05 + @@sort_buffer_size;
+RETURN order_tax;
+END|
+''
+'# During Execution values
+SET STATEMENT myisam_sort_buffer_size=400000,
+myisam_repair_threads=2,
+sort_buffer_size=200000,
+binlog_format=row,
+keep_files_on_create=OFF,
+max_join_size=4444440000000 FOR
+SELECT myProc(123.45);
+myProc(123.45)
+200006.17
+SET STATEMENT myisam_sort_buffer_size=400000,
+myisam_repair_threads=2,
+sort_buffer_size=300000,
+binlog_format=row,
+keep_files_on_create=OFF,
+max_join_size=4444440000000 FOR
+SELECT myProc(123.45);
+myProc(123.45)
+300006.17
+''
+'# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2'
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+SET STATEMENT myisam_sort_buffer_size=400000,
+myisam_repair_threads=2,
+sort_buffer_size=200000,
+binlog_format=row,
+keep_files_on_create=OFF,
+max_join_size=4444440000000 FOR
+DROP FUNCTION myProc;
+''
+'# Post-STATEMENT No 2 variable value
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+'#------------------Test 11-----------------------#'
+'# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+myisam_repair_threads=1,
+sort_buffer_size=100000,
+binlog_format=mixed,
+keep_files_on_create=ON,
+max_join_size=2222220000000;
+''
+''
+'# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+SET STATEMENT myisam_sort_buffer_size=400000,
+myisam_repair_threads=2,
+sort_buffer_size=200000,
+keep_files_on_create=OFF,
+max_join_size=4444440000000 FOR
+PREPARE stmt1 FROM
+'SET STATEMENT binlog_format=row FOR SELECT *,@@binlog_format,@@sort_buffer_size FROM t1';
+''
+'Test No 1 Post Value & Test 2 Pre values'
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+SET STATEMENT myisam_sort_buffer_size=400000,
+myisam_repair_threads=2,
+sort_buffer_size=200000,
+keep_files_on_create=OFF,
+max_join_size=4444440000000 FOR
+EXECUTE stmt1;
+v1 v2 @@binlog_format @@sort_buffer_size
+1 2 ROW 200000
+3 4 ROW 200000
+''
+'# Post-STATEMENT No 2
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+'#------------------Test 12-----------------------#'
+'# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+myisam_repair_threads=1,
+sort_buffer_size=100000,
+binlog_format=mixed,
+keep_files_on_create=ON,
+max_join_size=2222220000000;
+''
+''
+'# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+SET STATEMENT myisam_sort_buffer_size=400000,
+myisam_repair_threads=2,
+sort_buffer_size=200000,
+binlog_format=row,
+keep_files_on_create=OFF,
+max_join_size=4444440000000 FOR
+CREATE PROCEDURE p1() BEGIN
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+END|
+''
+'Test No 1 Post Value & Test 2 Pre values'
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+SET STATEMENT myisam_sort_buffer_size=400001,
+myisam_repair_threads=3,
+sort_buffer_size=200001,
+binlog_format=row,
+keep_files_on_create=OFF,
+max_join_size=4444440000001 FOR
+CALL p1();
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+400001 3 200001 ROW 0 4444440000001
+''
+'# Post-STATEMENT No 2
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+'#------------------Test 13-----------------------#'
+'# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+myisam_repair_threads=1,
+sort_buffer_size=100000,
+binlog_format=mixed,
+keep_files_on_create=ON,
+max_join_size=2222220000000;
+''
+''
+CREATE PROCEDURE p2() BEGIN
+SET STATEMENT myisam_sort_buffer_size=400000,
+myisam_repair_threads=3,
+sort_buffer_size=300000,
+binlog_format=mixed,
+keep_files_on_create=OFF,
+max_join_size=3333330000000 FOR
+CALL p1();
+END|
+''
+'# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+SET STATEMENT myisam_sort_buffer_size=400000,
+myisam_repair_threads=2,
+sort_buffer_size=200000,
+binlog_format=row,
+keep_files_on_create=OFF,
+max_join_size=4444440000000 FOR
+CALL p2();
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+400000 3 300000 MIXED 0 3333330000000
+''
+'# Post-STATEMENT
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+'#------------------Test 14-----------------------#'
+'# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+myisam_repair_threads=1,
+sort_buffer_size=100000,
+binlog_format=mixed,
+keep_files_on_create=ON,
+max_join_size=2222220000000;
+''
+''
+CREATE PROCEDURE p3() BEGIN
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+SET STATEMENT myisam_sort_buffer_size=320000,
+myisam_repair_threads=2,
+sort_buffer_size=220022,
+binlog_format=row,
+keep_files_on_create=ON,
+max_join_size=2222220000000 FOR
+CALL p2();
+END|
+''
+'# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+SET STATEMENT myisam_sort_buffer_size=400000,
+myisam_repair_threads=2,
+sort_buffer_size=200000,
+binlog_format=row,
+keep_files_on_create=OFF,
+max_join_size=4444440000000 FOR
+CALL p3();
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+400000 2 200000 ROW 0 4444440000000
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+400000 3 300000 MIXED 0 3333330000000
+''
+'# Post-STATEMENT
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+''
+''
+'#------------------Test 15-----------------------#'
+'# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+myisam_repair_threads=1,
+sort_buffer_size=100000,
+binlog_format=mixed,
+keep_files_on_create=ON,
+max_join_size=2222220000000;
+''
+''
+CREATE PROCEDURE p4() BEGIN
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+SET STATEMENT myisam_sort_buffer_size=320000,
+myisam_repair_threads=2,
+sort_buffer_size=220022,
+binlog_format=row,
+keep_files_on_create=ON,
+max_join_size=2222220000000 FOR
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+SET STATEMENT myisam_sort_buffer_size=320000,
+myisam_repair_threads=2,
+sort_buffer_size=220022,
+binlog_format=row,
+keep_files_on_create=ON,
+max_join_size=2222220000000 FOR
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+SET STATEMENT myisam_sort_buffer_size=320000,
+myisam_repair_threads=2,
+sort_buffer_size=220022,
+binlog_format=row,
+keep_files_on_create=ON,
+max_join_size=2222220000000 FOR
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+END|
+''
+'# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+SET STATEMENT myisam_sort_buffer_size=400000,
+myisam_repair_threads=2,
+sort_buffer_size=200000,
+binlog_format=row,
+keep_files_on_create=OFF,
+max_join_size=4444440000000 FOR
+CALL p4();
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+400000 2 200000 ROW 0 4444440000000
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+320000 2 220022 ROW 1 2222220000000
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+320000 2 220022 ROW 1 2222220000000
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+320000 2 220022 ROW 1 2222220000000
+''
+'# Post-STATEMENT
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+'#------------------Test 16-----------------------#'
+''
+'# Pre-STATEMENT variable value
+SELECT @@sql_mode;
+@@sql_mode
+
+''
+''
+SET STATEMENT sql_mode='ansi' FOR PREPARE stmt FROM 'SELECT "t1".* FROM t1';
+execute stmt;
+v1 v2
+1 2
+3 4
+ALTER TABLE t1 ADD COLUMN v3 int;
+execute 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 '.* FROM t1' at line 1
+ALTER TABLE t1 drop COLUMN v3;
+deallocate prepare stmt;
+''
+'# Post-STATEMENT
+SELECT @@sql_mode;
+@@sql_mode
+
+check the same behaviour in normal set
+SET sql_mode='ansi';
+PREPARE stmt FROM 'SELECT "t1".* FROM t1';
+SET sql_mode=default;
+execute stmt;
+v1 v2
+1 2
+3 4
+ALTER TABLE t1 ADD COLUMN v3 int;
+execute 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 '.* FROM t1' at line 1
+ALTER TABLE t1 drop COLUMN v3;
+deallocate prepare stmt;
+SELECT @@sql_mode;
+@@sql_mode
+
+SET sql_mode='ansi';
+SELECT @@sql_mode;
+@@sql_mode
+REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
+CREATE PROCEDURE p6() BEGIN
+SELECT @@sql_mode;
+SELECT "t1".* FROM t1;
+END|
+SET sql_mode=default;
+call p6;
+@@sql_mode
+REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
+v1 v2
+1 2
+3 4
+ALTER TABLE t1 ADD COLUMN v3 int;
+create view v1 as select * from t1;
+drop view v1;
+call p6;
+@@sql_mode
+REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
+v1 v2 v3
+1 2 NULL
+3 4 NULL
+ALTER TABLE t1 drop COLUMN v3;
+drop procedure p6;
+SELECT @@sql_mode;
+@@sql_mode
+
+# SET and the statement parsed as one unit before the SET takes effect
+SET STATEMENT sql_mode='ansi' FOR
+CREATE PROCEDURE p6() BEGIN
+SELECT @@sql_mode;
+SELECT "t1".* FROM t1;
+END|
+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 '.* FROM t1;
+END' at line 4
+SELECT @@sql_mode;
+@@sql_mode
+
+SET sql_mode='ansi';
+SELECT @@sql_mode;
+@@sql_mode
+REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
+BEGIN NOT ATOMIC
+SELECT @@sql_mode;
+SELECT "t1".* FROM t1;
+END|
+@@sql_mode
+REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
+v1 v2
+1 2
+3 4
+SET sql_mode=default;
+SELECT @@sql_mode;
+@@sql_mode
+
+# SET and the statement parsed as one unit before the SET takes effect
+SET STATEMENT sql_mode='ansi' FOR
+BEGIN NOT ATOMIC
+SELECT @@sql_mode;
+SELECT "t1".* FROM t1;
+END|
+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 '.* FROM t1;
+END' at line 4
+SET STATEMENT sql_mode='ansi' FOR
+BEGIN NOT ATOMIC
+SELECT @@sql_mode;
+SELECT * FROM t1;
+SELECT @@sql_mode;
+END|
+@@sql_mode
+REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
+v1 v2
+1 2
+3 4
+@@sql_mode
+REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
+''
+''
+'#------------------Test 17-----------------------#'
+'# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+myisam_repair_threads=1,
+sort_buffer_size=100000,
+binlog_format=mixed,
+keep_files_on_create=ON,
+max_join_size=2222220000000;
+''
+'# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+SET STATEMENT myisam_sort_buffer_size=320000,
+myisam_repair_threads=2,
+sort_buffer_size=220022,
+binlog_format=row,
+keep_files_on_create=ON,
+max_join_size=2222220000000
+FOR SET SESSION
+myisam_sort_buffer_size=260000,
+myisam_repair_threads=3,
+sort_buffer_size=230013,
+binlog_format=row,
+keep_files_on_create=ON,
+max_join_size=2323230000000;
+''
+'# Post-STATEMENT
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+'#------------------Test 18-----------------------#'
+'# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+myisam_repair_threads=1,
+sort_buffer_size=100000,
+binlog_format=mixed,
+keep_files_on_create=ON,
+max_join_size=2222220000000;
+''
+'# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+CREATE PROCEDURE p5() BEGIN
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+SET SESSION
+myisam_sort_buffer_size=260000,
+myisam_repair_threads=3,
+sort_buffer_size=230013,
+binlog_format=row,
+keep_files_on_create=ON,
+max_join_size=2323230000000;
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+END|
+''
+''
+SET STATEMENT myisam_sort_buffer_size=400000,
+myisam_repair_threads=2,
+sort_buffer_size=200000,
+binlog_format=row,
+keep_files_on_create=OFF,
+max_join_size=4444440000000 FOR
+CALL p5();
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+400000 2 200000 ROW 0 4444440000000
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+260000 3 230013 ROW 1 2323230000000
+''
+'# Post-STATEMENT
+SELECT @@myisam_sort_buffer_size,
+@@myisam_repair_threads,
+@@sort_buffer_size,
+@@binlog_format,
+@@keep_files_on_create,
+@@max_join_size;
+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
+500000 1 100000 MIXED 1 2222220000000
+''
+''
+'#------------------Test 19-----------------------#'
+SET STATEMENT max_error_count=100 FOR;
+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
+SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2);
+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 'INSERT t1 VALUES (1,2)' at line 1
+SET STATEMENT FOR INSERT INTO t1 VALUES (1,2);
+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 'FOR INSERT INTO t1 VALUES (1,2)' at line 1
+SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
+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 'FOR INSERT INTO t1 VALUES (1,2)' at line 1
+SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
+ERROR HY000: Unknown system variable 'GLOBAL'
+SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
+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 '@@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2)' at line 1
+''
+''
+'#------------------Test 20-----------------------#'
+SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2);
+ERROR HY000: Variable 'connect_timeout' is a GLOBAL variable and should be set with SET GLOBAL
+''
+''
+'#------------------Test 21-----------------------#'
+SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
+@@myisam_sort_buffer_size @@sort_buffer_size
+500000 100000
+SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000
+FOR SET STATEMENT myisam_sort_buffer_size=200000
+FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
+@@myisam_sort_buffer_size @@sort_buffer_size
+200000 100000
+SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
+@@myisam_sort_buffer_size @@sort_buffer_size
+500000 100000
+''
+''
+'#------------------Test 22-----------------------#'
+CREATE TABLE STATEMENT(a INT);
+DROP TABLE STATEMENT;
+''
+'# Cleanup'
+DROP TABLE t1;
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+DROP PROCEDURE p3;
+DROP PROCEDURE p4;
+DROP PROCEDURE p5;
+CREATE TABLE t1 (v1 INT, v2 INT);
+insert into t1 values (1,1);
+CREATE FUNCTION myProc ()
+RETURNS INT
+SQL SECURITY DEFINER
+BEGIN
+DECLARE mx INT;
+SET mx = (select max(v1) from t1);
+RETURN mx;
+END|
+SET STATEMENT myisam_repair_threads=(select max(v1) from t1) FOR
+select 1;
+ERROR 42000: SET STATEMENT does not support subqueries or stored functions.
+SET STATEMENT myisam_repair_threads=myProc() FOR
+select 1;
+ERROR 42000: SET STATEMENT does not support subqueries or stored functions.
+drop function myProc;
+drop table t1;
+set session binlog_format=mixed;
+PREPARE stmt1 FROM 'SELECT @@binlog_format';
+execute stmt1;
+@@binlog_format
+MIXED
+set statement binlog_format=row for execute stmt1;
+@@binlog_format
+ROW
+execute stmt1;
+@@binlog_format
+MIXED
+deallocate prepare stmt1;
+set statement binlog_format=row for PREPARE stmt1 FROM 'SELECT @@binlog_format';
+execute stmt1;
+@@binlog_format
+MIXED
+execute stmt1;
+@@binlog_format
+MIXED
+deallocate prepare stmt1;
+PREPARE stmt1 FROM 'set statement binlog_format=row for SELECT @@binlog_format';
+execute stmt1;
+@@binlog_format
+ROW
+execute stmt1;
+@@binlog_format
+ROW
+deallocate prepare stmt1;
+set session binlog_format=default;
+set session binlog_format=mixed;
+SET STATEMENT sort_buffer_size=150000 FOR set session binlog_format=row;
+SELECT @@binlog_format;
+@@binlog_format
+ROW
+#Correctly set timestamp
+set session timestamp=4646464;
+select @@timestamp != 4646464;
+@@timestamp != 4646464
+0
+select @@timestamp != 4646464;
+@@timestamp != 4646464
+0
+#Correctly returned normal behaviour
+set session timestamp=default;
+select @@timestamp != 4646464;
+@@timestamp != 4646464
+1
+select @@timestamp != 4646464;
+@@timestamp != 4646464
+1
+#here timestamp should be set only for the statement then restored default
+set statement timestamp=4646464 for select @@timestamp;
+@@timestamp
+4646464.000000
+set @save_tm=@@timestamp;
+select @@timestamp != 4646464;
+@@timestamp != 4646464
+1
+select @@timestamp != 4646464;
+@@timestamp != 4646464
+1
+select @@timestamp != @save_tm;
+@@timestamp != @save_tm
+1
+set @save_dfs=@@default_storage_engine;
+SET @@default_storage_engine=MyISAM;
+SET STATEMENT default_storage_engine=MEMORY for CREATE TABLE t1 (a int);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) DEFAULT NULL
+) ENGINE=MEMORY DEFAULT CHARSET=latin1
+select @@default_storage_engine;
+@@default_storage_engine
+MyISAM
+drop table t1;
+SET STATEMENT default_storage_engine=MyISAM for CREATE TABLE t1 (a int);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+SET @@default_storage_engine=@save_dfs;
+SELECT @@MAX_STATEMENT_TIME;
+@@MAX_STATEMENT_TIME
+0.000000
+SET STATEMENT MAX_STATEMENT_TIME=2 FOR SELECT SLEEP(1);
+SLEEP(1)
+0
+SHOW STATUS LIKE "max_statement_time_exceeded";
+Variable_name Value
+Max_statement_time_exceeded 0
+SET STATEMENT MAX_STATEMENT_TIME=2 FOR SELECT SLEEP(3);
+SLEEP(3)
+1
+SHOW STATUS LIKE "max_statement_time_exceeded";
+Variable_name Value
+Max_statement_time_exceeded 1
+SELECT @@MAX_STATEMENT_TIME;
+@@MAX_STATEMENT_TIME
+0.000000
diff --git a/mysql-test/suite/rpl/include/rpl_set_statement.inc b/mysql-test/suite/rpl/include/rpl_set_statement.inc
new file mode 100644
index 00000000000..6ab2d4e66c6
--- /dev/null
+++ b/mysql-test/suite/rpl/include/rpl_set_statement.inc
@@ -0,0 +1,31 @@
+#Check if the variable is replicated correctly with "SET STATEMENT"
+# Usage:
+# $rpl_ssvt_var_name - the name of tested variable;
+# $rpl_ssvt_var_value - the value to set;
+# $rpl_ssvt_table - the table name to insert values.
+
+--connection master
+--echo [connection master]
+eval SELECT @@$rpl_ssvt_var_name;
+
+--connection slave
+--echo [connection slave]
+eval SELECT @@$rpl_ssvt_var_name;
+
+--connection master
+--echo [connection master]
+--disable_result_log
+eval SET STATEMENT $rpl_ssvt_var_name=$rpl_ssvt_var_value FOR
+ INSERT INTO $rpl_ssvt_table VALUES(@@$rpl_ssvt_var_name);
+--enable_result_log
+eval SELECT @@$rpl_ssvt_var_name;
+
+--sync_slave_with_master
+--echo [connection slave]
+eval SELECT * FROM $rpl_ssvt_table;
+eval SELECT @@$rpl_ssvt_var_name;
+
+--connection master
+--echo [connection master]
+eval DELETE FROM $rpl_ssvt_table;
+
diff --git a/mysql-test/suite/rpl/r/rpl_set_statement.result b/mysql-test/suite/rpl/r/rpl_set_statement.result
new file mode 100644
index 00000000000..78d34bd0853
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_set_statement.result
@@ -0,0 +1,140 @@
+include/master-slave.inc
+[connection master]
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+call mtr.add_suppression("Unsafe statement written to the binary log*");
+CREATE TABLE t1 (a bigint unsigned not null);
+CREATE TABLE t2 (a char(255) not null);
+
+There are the following types of variables:
+1) variables that are NOT replicated correctly when using STATEMENT mode;
+
+[connection master]
+SELECT @@max_join_size;
+@@max_join_size
+18446744073709551615
+[connection slave]
+SELECT @@max_join_size;
+@@max_join_size
+18446744073709551615
+[connection master]
+SET STATEMENT max_join_size=2 FOR
+INSERT INTO t1 VALUES(@@max_join_size);
+SELECT @@max_join_size;
+@@max_join_size
+18446744073709551615
+[connection slave]
+SELECT * FROM t1;
+a
+18446744073709551615
+SELECT @@max_join_size;
+@@max_join_size
+18446744073709551615
+[connection master]
+DELETE FROM t1;
+
+2) variables thar ARE replicated correctly
+They must be replicated correctly with "SET STATEMENT" too.
+
+[connection master]
+SELECT @@auto_increment_increment;
+@@auto_increment_increment
+1
+[connection slave]
+SELECT @@auto_increment_increment;
+@@auto_increment_increment
+1
+[connection master]
+SET STATEMENT auto_increment_increment=10 FOR
+INSERT INTO t1 VALUES(@@auto_increment_increment);
+SELECT @@auto_increment_increment;
+@@auto_increment_increment
+1
+[connection slave]
+SELECT * FROM t1;
+a
+10
+SELECT @@auto_increment_increment;
+@@auto_increment_increment
+1
+[connection master]
+DELETE FROM t1;
+
+3) sql_mode which is replicated correctly exept NO_DIR_IN_CREATE value;
+
+[connection master]
+SELECT @@sql_mode;
+@@sql_mode
+
+[connection slave]
+SELECT @@sql_mode;
+@@sql_mode
+
+[connection master]
+SET STATEMENT sql_mode='ERROR_FOR_DIVISION_BY_ZERO' FOR
+INSERT INTO t2 VALUES(@@sql_mode);
+SELECT @@sql_mode;
+@@sql_mode
+
+[connection slave]
+SELECT * FROM t2;
+a
+ERROR_FOR_DIVISION_BY_ZERO
+SELECT @@sql_mode;
+@@sql_mode
+
+[connection master]
+DELETE FROM t2;
+[connection master]
+SELECT @@sql_mode;
+@@sql_mode
+
+[connection slave]
+SELECT @@sql_mode;
+@@sql_mode
+
+[connection master]
+SET STATEMENT sql_mode='NO_DIR_IN_CREATE' FOR
+INSERT INTO t2 VALUES(@@sql_mode);
+SELECT @@sql_mode;
+@@sql_mode
+
+[connection slave]
+SELECT * FROM t2;
+a
+
+SELECT @@sql_mode;
+@@sql_mode
+
+[connection master]
+DELETE FROM t2;
+
+4) variables that are not replicated at all:
+default_storage_engine, storage_engine, max_heap_table_size
+
+[connection master]
+SELECT @@max_heap_table_size;
+@@max_heap_table_size
+1048576
+[connection slave]
+SELECT @@max_heap_table_size;
+@@max_heap_table_size
+1048576
+[connection master]
+SET STATEMENT max_heap_table_size=16384 FOR
+INSERT INTO t1 VALUES(@@max_heap_table_size);
+SELECT @@max_heap_table_size;
+@@max_heap_table_size
+1048576
+[connection slave]
+SELECT * FROM t1;
+a
+1048576
+SELECT @@max_heap_table_size;
+@@max_heap_table_size
+1048576
+[connection master]
+DELETE FROM t1;
+DROP TABLE t1;
+DROP TABLE t2;
+include/stop_slave.inc
diff --git a/mysql-test/suite/rpl/t/rpl_set_statement.test b/mysql-test/suite/rpl/t/rpl_set_statement.test
new file mode 100644
index 00000000000..8a3087bec6d
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_set_statement.test
@@ -0,0 +1,57 @@
+--source include/master-slave.inc
+--source include/have_binlog_format_statement.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+--enable_warnings
+
+call mtr.add_suppression("Unsafe statement written to the binary log*");
+CREATE TABLE t1 (a bigint unsigned not null);
+CREATE TABLE t2 (a char(255) not null);
+
+--echo
+--echo There are the following types of variables:
+--echo 1) variables that are NOT replicated correctly when using STATEMENT mode;
+--echo
+
+--let $rpl_ssvt_var_name=max_join_size
+--let $rpl_ssvt_var_value=2
+--let $rpl_ssvt_table=t1
+--source suite/rpl/include/rpl_set_statement.inc
+
+--echo
+--echo 2) variables thar ARE replicated correctly
+--echo They must be replicated correctly with "SET STATEMENT" too.
+--echo
+--let $rpl_ssvt_var_name=auto_increment_increment
+--let $rpl_ssvt_var_value=10
+--let $rpl_ssvt_table=t1
+--source suite/rpl/include/rpl_set_statement.inc
+
+--echo
+--echo 3) sql_mode which is replicated correctly exept NO_DIR_IN_CREATE value;
+--echo
+--let $rpl_ssvt_var_name=sql_mode
+--let $rpl_ssvt_var_value='ERROR_FOR_DIVISION_BY_ZERO'
+--let $rpl_ssvt_table=t2
+--source suite/rpl/include/rpl_set_statement.inc
+--let $rpl_ssvt_var_name=sql_mode
+--let $rpl_ssvt_var_value='NO_DIR_IN_CREATE'
+--let $rpl_ssvt_table=t2
+--source suite/rpl/include/rpl_set_statement.inc
+
+--echo
+--echo 4) variables that are not replicated at all:
+--echo default_storage_engine, storage_engine, max_heap_table_size
+--echo
+--let $rpl_ssvt_var_name=max_heap_table_size
+--let $rpl_ssvt_var_value=16384
+--let $rpl_ssvt_table=t1
+--source suite/rpl/include/rpl_set_statement.inc
+
+connection master;
+DROP TABLE t1;
+DROP TABLE t2;
+sync_slave_with_master;
+source include/stop_slave.inc;
diff --git a/mysql-test/t/events_bugs.test b/mysql-test/t/events_bugs.test
index dc31556998a..1e93917b08e 100644
--- a/mysql-test/t/events_bugs.test
+++ b/mysql-test/t/events_bugs.test
@@ -508,13 +508,13 @@ drop database mysqltest_db1;
#
# START - BUG#16394: Events: Crash if schedule contains SELECT
#
---error ER_NOT_SUPPORTED_YET
+--error ER_SUBQUERIES_NOT_SUPPORTED
create event e_53 on schedule at (select s1 from ttx) do drop table t;
---error ER_NOT_SUPPORTED_YET
+--error ER_SUBQUERIES_NOT_SUPPORTED
create event e_53 on schedule every (select s1 from ttx) second do drop table t;
---error ER_NOT_SUPPORTED_YET
+--error ER_SUBQUERIES_NOT_SUPPORTED
create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t;
---error ER_NOT_SUPPORTED_YET
+--error ER_SUBQUERIES_NOT_SUPPORTED
create event e_53 on schedule every 5 second ends (select s1 from ttx) do drop table t;
#
# END - BUG#16394: Events: Crash if schedule contains SELECT
@@ -570,7 +570,7 @@ begin
select release_lock('ee_22830');
end|
---error ER_NOT_SUPPORTED_YET
+--error ER_SUBQUERIES_NOT_SUPPORTED
create event e22830 on schedule every f22830() second do
begin
call p22830_wait();
diff --git a/mysql-test/t/kill.test b/mysql-test/t/kill.test
index 06933bcb0b9..6e97f3bbc33 100644
--- a/mysql-test/t/kill.test
+++ b/mysql-test/t/kill.test
@@ -70,7 +70,7 @@ connection con2;
SELECT 4;
connection default;
---error ER_NOT_SUPPORTED_YET
+--error ER_SUBQUERIES_NOT_SUPPORTED
KILL (SELECT COUNT(*) FROM mysql.user);
connection con1;
diff --git a/mysql-test/t/set_statement.test b/mysql-test/t/set_statement.test
new file mode 100644
index 00000000000..ed242f06995
--- /dev/null
+++ b/mysql-test/t/set_statement.test
@@ -0,0 +1,960 @@
+--echo '# SET STATEMENT ..... FOR .... TEST'
+############################ STATEMENT_SET #############################
+# #
+# Testing working functionality of SET STATEMENT #
+# #
+# #
+# There is important documentation within #
+# #
+# #
+# Author: Joe Lukas #
+# Creation: #
+# 2009-08-02 Implement this test as part of #
+# WL#681 Per query variable settings #
+# #
+########################################################################
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS myProc;
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+DROP PROCEDURE IF EXISTS p3;
+DROP PROCEDURE IF EXISTS p4;
+DROP PROCEDURE IF EXISTS p5;
+DROP PROCEDURE IF EXISTS p6;
+DROP VIEW IF EXISTS v1;
+DROP TABLE IF EXISTS STATEMENT;
+--enable_warnings
+####################################################################
+#Set up current database
+####################################################################
+--echo '# Setup database'
+CREATE TABLE t1 (v1 INT, v2 INT);
+INSERT INTO t1 VALUES (1,2);
+INSERT INTO t1 VALUES (3,4);
+--echo ''
+--echo '#------------------ STATEMENT Test 1 -----------------------#'
+####################################################################
+# Checks with variable value type ulong #
+####################################################################
+--echo '# Initialize variables to known setting'
+SET SESSION sort_buffer_size=100000;
+--echo ''
+--echo '# Pre-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+SET STATEMENT sort_buffer_size=150000 FOR SELECT *,@@sort_buffer_size FROM t1;
+--echo ''
+--echo '# Post-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+--echo ''
+--echo '#------------------ STATEMENT Test 2 -----------------------#'
+####################################################################
+# Checks for multiple set values inside STATEMENT ... FOR #
+####################################################################
+--echo '# Initialize variables to known setting'
+SET SESSION binlog_format=mixed;
+SET SESSION sort_buffer_size=100000;
+--echo '# Pre-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+SHOW SESSION VARIABLES LIKE 'binlog_format';
+SET STATEMENT sort_buffer_size=150000, binlog_format=row
+ FOR SELECT *,@@sort_buffer_size,@@binlog_format FROM t1;
+--echo '# Post-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+SHOW SESSION VARIABLES LIKE 'binlog_format';
+
+--echo ''
+--echo '#------------------ STATEMENT Test 3 -----------------------#'
+####################################################################
+# Check current variable value is stored in using stored #
+# statements. #
+####################################################################
+--echo '# set initial variable value, make prepared statement
+SET SESSION binlog_format=row;
+PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT *,@@binlog_format FROM t1';
+--echo ''
+--echo '# Change variable setting'
+SET SESSION binlog_format=mixed;
+--echo ''
+--echo '# Pre-STATEMENT variable value'
+--echo ''
+SHOW SESSION VARIABLES LIKE 'binlog_format';
+--echo ''
+EXECUTE stmt1;
+--echo ''
+--echo '# Post-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'binlog_format';
+
+--echo ''
+DEALLOCATE PREPARE stmt1;
+--echo '#------------------ STATEMENT Test 4 -----------------------#'
+####################################################################
+# Check works with OPTIMIZE TABLE command #
+# Checks works with a variable value of type INT #
+# Checks works with variable type ULONGLONG #
+####################################################################
+--echo '# set initial variable value, make prepared statement
+SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1;
+--echo ''
+--echo '# Pre-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
+--echo ''
+SET STATEMENT myisam_sort_buffer_size=800000,
+ myisam_repair_threads=2 FOR OPTIMIZE TABLE t1;
+--echo ''
+--echo '# Post-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
+
+--echo ''
+--echo '#------------------ STATEMENT Test 5 -----------------------#'
+####################################################################
+# Checks if variable reset after error in statement after FOR #
+####################################################################
+--echo '# Initialize variables to known setting'
+SET SESSION sort_buffer_size=100000;
+--echo ''
+--echo '# Pre-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+--echo ''
+--error ER_NO_SUCH_TABLE
+SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2;
+--echo ''
+--echo '# Post-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+
+--echo ''
+--echo '#------------------ STATEMENT Test 6 -----------------------#'
+####################################################################
+# Checks works with variable type MY_BOOL #
+####################################################################
+--echo '# Initialize variables to known setting'
+SET SESSION keep_files_on_create=ON;
+--echo ''
+--echo '# Pre-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
+--echo ''
+SET STATEMENT keep_files_on_create=OFF FOR SELECT *,@@keep_files_on_create FROM t1;
+--echo ''
+--echo '# Post-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
+
+--echo ''
+--echo '#------------------ STATEMENT Test 7 -----------------------#'
+####################################################################
+# Checks works with variable type HA_ROWS #
+####################################################################
+--echo '# Initialize variables to known setting'
+SET SESSION max_join_size=2222220000000;
+--echo ''
+--echo '# Pre-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'max_join_size';
+--echo ''
+SET STATEMENT max_join_size=1000000000000 FOR SELECT *,@@max_join_size FROM t1;
+--echo ''
+--echo '# Post-STATEMENT variable value'
+SHOW SESSION VARIABLES LIKE 'max_join_size';
+
+--echo ''
+--echo '#------------------Test 8-----------------------#'
+####################################################################
+# Ensure variable of each type is set to proper value during #
+# statement after FOR execution #
+####################################################################
+--echo '# Initialize test variables'
+SET SESSION myisam_sort_buffer_size=500000,
+ myisam_repair_threads=1,
+ sort_buffer_size = 200000,
+ max_join_size=2222220000000,
+ keep_files_on_create=ON;
+
+--echo ''
+--echo '# LONG '
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+SET STATEMENT sort_buffer_size = 100000
+ FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
+--echo ''
+--echo '# MY_BOOL '
+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
+SET STATEMENT keep_files_on_create=OFF
+ FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
+
+--echo ''
+--echo '# INT/LONG '
+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
+SET STATEMENT myisam_repair_threads=2
+ FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
+--echo ''
+--echo '# ULONGLONG '
+SHOW SESSION VARIABLES LIKE 'max_join_size';
+SET STATEMENT max_join_size=2000000000000
+ FOR SHOW SESSION VARIABLES LIKE 'max_join_size';
+SHOW SESSION VARIABLES LIKE 'max_join_size';
+
+--echo ''
+--echo '#------------------Test 9-----------------------#'
+####################################################################
+# No 1 - Check works with CREATE ... BEGIN ... END command #
+# Display variables during execution #
+# No 2 - Test with DROP command #
+####################################################################
+--echo '# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+ myisam_repair_threads=1,
+ sort_buffer_size=100000,
+ binlog_format=mixed,
+ keep_files_on_create=ON,
+ max_join_size=2222220000000;
+--echo ''
+--echo ''
+--echo '# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+--echo ''
+DELIMITER |;
+CREATE FUNCTION myProc (cost DECIMAL(10,2))
+ RETURNS DECIMAL(10,2)
+
+ SQL SECURITY DEFINER
+
+ tax: BEGIN
+ DECLARE order_tax DECIMAL(10,2);
+ SET order_tax = cost * .05 + @@sort_buffer_size;
+ RETURN order_tax;
+ END|
+DELIMITER ;|
+--echo ''
+--echo '# During Execution values
+SET STATEMENT myisam_sort_buffer_size=400000,
+ myisam_repair_threads=2,
+ sort_buffer_size=200000,
+ binlog_format=row,
+ keep_files_on_create=OFF,
+ max_join_size=4444440000000 FOR
+ SELECT myProc(123.45);
+SET STATEMENT myisam_sort_buffer_size=400000,
+ myisam_repair_threads=2,
+ sort_buffer_size=300000,
+ binlog_format=row,
+ keep_files_on_create=OFF,
+ max_join_size=4444440000000 FOR
+ SELECT myProc(123.45);
+--echo ''
+--echo '# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2'
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+SET STATEMENT myisam_sort_buffer_size=400000,
+ myisam_repair_threads=2,
+ sort_buffer_size=200000,
+ binlog_format=row,
+ keep_files_on_create=OFF,
+ max_join_size=4444440000000 FOR
+ DROP FUNCTION myProc;
+--echo ''
+--echo '# Post-STATEMENT No 2 variable value
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+
+--echo ''
+--echo '#------------------Test 11-----------------------#'
+####################################################################
+# No 1 - Check with PREPARE statement #
+# check with different variable on inside PREPARE #
+# No 2 - Check with EXECUTE statement #
+####################################################################
+--echo '# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+ myisam_repair_threads=1,
+ sort_buffer_size=100000,
+ binlog_format=mixed,
+ keep_files_on_create=ON,
+ max_join_size=2222220000000;
+--echo ''
+--echo ''
+--echo '# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+--echo ''
+SET STATEMENT myisam_sort_buffer_size=400000,
+ myisam_repair_threads=2,
+ sort_buffer_size=200000,
+ keep_files_on_create=OFF,
+ max_join_size=4444440000000 FOR
+ PREPARE stmt1 FROM
+ 'SET STATEMENT binlog_format=row FOR SELECT *,@@binlog_format,@@sort_buffer_size FROM t1';
+--echo ''
+--echo 'Test No 1 Post Value & Test 2 Pre values'
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+--echo ''
+SET STATEMENT myisam_sort_buffer_size=400000,
+ myisam_repair_threads=2,
+ sort_buffer_size=200000,
+ keep_files_on_create=OFF,
+ max_join_size=4444440000000 FOR
+ EXECUTE stmt1;
+--echo ''
+--echo '# Post-STATEMENT No 2
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+--echo ''
+--echo '#------------------Test 12-----------------------#'
+####################################################################
+# No 1 - Check with PROCEDURE (show variables in procedure) #
+# No 2 - Check with CALL statement show variables in PROCEDURE #
+####################################################################
+--echo '# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+ myisam_repair_threads=1,
+ sort_buffer_size=100000,
+ binlog_format=mixed,
+ keep_files_on_create=ON,
+ max_join_size=2222220000000;
+--echo ''
+--echo ''
+--echo '# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+--echo ''
+DELIMITER |;
+SET STATEMENT myisam_sort_buffer_size=400000,
+ myisam_repair_threads=2,
+ sort_buffer_size=200000,
+ binlog_format=row,
+ keep_files_on_create=OFF,
+ max_join_size=4444440000000 FOR
+ CREATE PROCEDURE p1() BEGIN
+ SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+ END|
+DELIMITER ;|
+--echo ''
+--echo 'Test No 1 Post Value & Test 2 Pre values'
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+--echo ''
+SET STATEMENT myisam_sort_buffer_size=400001,
+ myisam_repair_threads=3,
+ sort_buffer_size=200001,
+ binlog_format=row,
+ keep_files_on_create=OFF,
+ max_join_size=4444440000001 FOR
+ CALL p1();
+--echo ''
+--echo '# Post-STATEMENT No 2
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+--echo ''
+
+--echo '#------------------Test 13-----------------------#'
+####################################################################
+# Check PROCEDURE containing SET STATEMENT FOR #
+# p1() from test 12 will be used to display variables #
+####################################################################
+--echo '# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+ myisam_repair_threads=1,
+ sort_buffer_size=100000,
+ binlog_format=mixed,
+ keep_files_on_create=ON,
+ max_join_size=2222220000000;
+--echo ''
+--echo ''
+DELIMITER |;
+CREATE PROCEDURE p2() BEGIN
+ SET STATEMENT myisam_sort_buffer_size=400000,
+ myisam_repair_threads=3,
+ sort_buffer_size=300000,
+ binlog_format=mixed,
+ keep_files_on_create=OFF,
+ max_join_size=3333330000000 FOR
+ CALL p1();
+ END|
+DELIMITER ;|
+--echo ''
+--echo '# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+--echo ''
+SET STATEMENT myisam_sort_buffer_size=400000,
+ myisam_repair_threads=2,
+ sort_buffer_size=200000,
+ binlog_format=row,
+ keep_files_on_create=OFF,
+ max_join_size=4444440000000 FOR
+ CALL p2();
+--echo ''
+--echo '# Post-STATEMENT
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+--echo ''
+--echo '#------------------Test 14-----------------------#'
+####################################################################
+# Check PROCEDURE containing compound SET STATEMENT FOR #
+# p2() will be used as compounding statement from test 13 #
+####################################################################
+--echo '# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+ myisam_repair_threads=1,
+ sort_buffer_size=100000,
+ binlog_format=mixed,
+ keep_files_on_create=ON,
+ max_join_size=2222220000000;
+--echo ''
+--echo ''
+DELIMITER |;
+CREATE PROCEDURE p3() BEGIN
+ SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+ SET STATEMENT myisam_sort_buffer_size=320000,
+ myisam_repair_threads=2,
+ sort_buffer_size=220022,
+ binlog_format=row,
+ keep_files_on_create=ON,
+ max_join_size=2222220000000 FOR
+ CALL p2();
+ END|
+DELIMITER ;|
+--echo ''
+--echo '# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+--echo ''
+SET STATEMENT myisam_sort_buffer_size=400000,
+ myisam_repair_threads=2,
+ sort_buffer_size=200000,
+ binlog_format=row,
+ keep_files_on_create=OFF,
+ max_join_size=4444440000000 FOR
+ CALL p3();
+--echo ''
+--echo '# Post-STATEMENT
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+--echo ''
+
+ --echo ''
+--echo ''
+--echo '#------------------Test 15-----------------------#'
+####################################################################
+# Check PROCEDURE containing compound SET STATEMENT FOR #
+# call multiple SET STATEMENT .. FOR showing SELECT #
+####################################################################
+--echo '# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+ myisam_repair_threads=1,
+ sort_buffer_size=100000,
+ binlog_format=mixed,
+ keep_files_on_create=ON,
+ max_join_size=2222220000000;
+--echo ''
+--echo ''
+DELIMITER |;
+CREATE PROCEDURE p4() BEGIN
+ SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+ SET STATEMENT myisam_sort_buffer_size=320000,
+ myisam_repair_threads=2,
+ sort_buffer_size=220022,
+ binlog_format=row,
+ keep_files_on_create=ON,
+ max_join_size=2222220000000 FOR
+ SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+ SET STATEMENT myisam_sort_buffer_size=320000,
+ myisam_repair_threads=2,
+ sort_buffer_size=220022,
+ binlog_format=row,
+ keep_files_on_create=ON,
+ max_join_size=2222220000000 FOR
+ SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+ SET STATEMENT myisam_sort_buffer_size=320000,
+ myisam_repair_threads=2,
+ sort_buffer_size=220022,
+ binlog_format=row,
+ keep_files_on_create=ON,
+ max_join_size=2222220000000 FOR
+ SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+ END|
+DELIMITER ;|
+--echo ''
+--echo '# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+--echo ''
+SET STATEMENT myisam_sort_buffer_size=400000,
+ myisam_repair_threads=2,
+ sort_buffer_size=200000,
+ binlog_format=row,
+ keep_files_on_create=OFF,
+ max_join_size=4444440000000 FOR
+ CALL p4();
+--echo ''
+--echo '# Post-STATEMENT
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+
+--echo ''
+--echo ''
+--echo '#------------------Test 16-----------------------#'
+####################################################################
+# Test Effect on parsing #
+####################################################################
+--echo ''
+--echo '# Pre-STATEMENT variable value
+SELECT @@sql_mode;
+--echo ''
+--echo ''
+SET STATEMENT sql_mode='ansi' FOR PREPARE stmt FROM 'SELECT "t1".* FROM t1';
+execute stmt;
+ALTER TABLE t1 ADD COLUMN v3 int;
+# repreparation with other mode cause an error
+--error ER_PARSE_ERROR
+execute stmt;
+ALTER TABLE t1 drop COLUMN v3;
+deallocate prepare stmt;
+--echo ''
+--echo '# Post-STATEMENT
+SELECT @@sql_mode;
+--echo check the same behaviour in normal set
+SET sql_mode='ansi';
+PREPARE stmt FROM 'SELECT "t1".* FROM t1';
+SET sql_mode=default;
+execute stmt;
+ALTER TABLE t1 ADD COLUMN v3 int;
+# repreparation with other mode cause an error
+--error ER_PARSE_ERROR
+execute stmt;
+ALTER TABLE t1 drop COLUMN v3;
+deallocate prepare stmt;
+# the above test about SP
+SELECT @@sql_mode;
+SET sql_mode='ansi';
+SELECT @@sql_mode;
+DELIMITER |;
+ CREATE PROCEDURE p6() BEGIN
+ SELECT @@sql_mode;
+ SELECT "t1".* FROM t1;
+ END|
+DELIMITER ;|
+SET sql_mode=default;
+call p6;
+ALTER TABLE t1 ADD COLUMN v3 int;
+#force SP stack invalidation
+create view v1 as select * from t1;
+drop view v1;
+call p6;
+ALTER TABLE t1 drop COLUMN v3;
+drop procedure p6;
+
+
+SELECT @@sql_mode;
+DELIMITER |;
+--echo # SET and the statement parsed as one unit before the SET takes effect
+--error ER_PARSE_ERROR
+SET STATEMENT sql_mode='ansi' FOR
+ CREATE PROCEDURE p6() BEGIN
+ SELECT @@sql_mode;
+ SELECT "t1".* FROM t1;
+ END|
+DELIMITER ;|
+#call p1;
+#ALTER TABLE t1 ADD COLUMN v3 int;
+#--echo # no reparsing for now
+#call p1;
+#ALTER TABLE t1 drop COLUMN v3;
+#drop procedure p1;
+
+
+# the above test about compound statement
+SELECT @@sql_mode;
+SET sql_mode='ansi';
+SELECT @@sql_mode;
+DELIMITER |;
+BEGIN NOT ATOMIC
+ SELECT @@sql_mode;
+ SELECT "t1".* FROM t1;
+END|
+DELIMITER ;|
+SET sql_mode=default;
+
+
+SELECT @@sql_mode;
+DELIMITER |;
+--echo # SET and the statement parsed as one unit before the SET takes effect
+--error ER_PARSE_ERROR
+SET STATEMENT sql_mode='ansi' FOR
+BEGIN NOT ATOMIC
+ SELECT @@sql_mode;
+ SELECT "t1".* FROM t1;
+END|
+SET STATEMENT sql_mode='ansi' FOR
+BEGIN NOT ATOMIC
+ SELECT @@sql_mode;
+ SELECT * FROM t1;
+ SELECT @@sql_mode;
+END|
+DELIMITER ;|
+--echo ''
+--echo ''
+--echo '#------------------Test 17-----------------------#'
+####################################################################
+# Test effect of SET STATEMENT FOR with SET SESSION modifying #
+# the same variables as the SET STATEMENT #
+####################################################################
+--echo '# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+ myisam_repair_threads=1,
+ sort_buffer_size=100000,
+ binlog_format=mixed,
+ keep_files_on_create=ON,
+ max_join_size=2222220000000;
+--echo ''
+--echo '# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+--echo ''
+SET STATEMENT myisam_sort_buffer_size=320000,
+ myisam_repair_threads=2,
+ sort_buffer_size=220022,
+ binlog_format=row,
+ keep_files_on_create=ON,
+ max_join_size=2222220000000
+ FOR SET SESSION
+ myisam_sort_buffer_size=260000,
+ myisam_repair_threads=3,
+ sort_buffer_size=230013,
+ binlog_format=row,
+ keep_files_on_create=ON,
+ max_join_size=2323230000000;
+
+--echo ''
+--echo '# Post-STATEMENT
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+
+--echo ''
+--echo ''
+--echo '#------------------Test 18-----------------------#'
+####################################################################
+# Test effect of SET SESSION inside a stored procedure with #
+# with a SET STATEMENT on outside variables #
+####################################################################
+--echo '# set initial variable values
+SET SESSION myisam_sort_buffer_size=500000,
+ myisam_repair_threads=1,
+ sort_buffer_size=100000,
+ binlog_format=mixed,
+ keep_files_on_create=ON,
+ max_join_size=2222220000000;
+--echo ''
+--echo '# Pre-STATEMENT variable value
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+--echo ''
+--echo ''
+DELIMITER |;
+CREATE PROCEDURE p5() BEGIN
+ SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+ SET SESSION
+ myisam_sort_buffer_size=260000,
+ myisam_repair_threads=3,
+ sort_buffer_size=230013,
+ binlog_format=row,
+ keep_files_on_create=ON,
+ max_join_size=2323230000000;
+ SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+ END|
+DELIMITER ;|
+--echo ''
+--echo ''
+SET STATEMENT myisam_sort_buffer_size=400000,
+ myisam_repair_threads=2,
+ sort_buffer_size=200000,
+ binlog_format=row,
+ keep_files_on_create=OFF,
+ max_join_size=4444440000000 FOR
+ CALL p5();
+
+--echo ''
+--echo '# Post-STATEMENT
+SELECT @@myisam_sort_buffer_size,
+ @@myisam_repair_threads,
+ @@sort_buffer_size,
+ @@binlog_format,
+ @@keep_files_on_create,
+ @@max_join_size;
+
+--echo ''
+--echo ''
+--echo '#------------------Test 19-----------------------#'
+#Test for bad syntax
+--error ER_PARSE_ERROR
+SET STATEMENT max_error_count=100 FOR;
+--error ER_PARSE_ERROR
+SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2);
+--error ER_PARSE_ERROR
+SET STATEMENT FOR INSERT INTO t1 VALUES (1,2);
+--error ER_PARSE_ERROR
+SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
+--error ER_UNKNOWN_SYSTEM_VARIABLE
+SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
+--error ER_PARSE_ERROR
+SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
+
+--echo ''
+--echo ''
+--echo '#------------------Test 20-----------------------#'
+#Test for global-only variables
+--error ER_GLOBAL_VARIABLE
+SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2);
+
+--echo ''
+--echo ''
+--echo '#------------------Test 21-----------------------#'
+#Test for recursion
+SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
+SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000
+ FOR SET STATEMENT myisam_sort_buffer_size=200000
+ FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
+SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
+
+--echo ''
+--echo ''
+--echo '#------------------Test 22-----------------------#'
+#Test for STATEMENT keyword
+CREATE TABLE STATEMENT(a INT);
+DROP TABLE STATEMENT;
+
+--echo ''
+--echo '# Cleanup'
+DROP TABLE t1;
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+DROP PROCEDURE p3;
+DROP PROCEDURE p4;
+DROP PROCEDURE p5;
+
+#
+# Limitation of opening tables for set operation
+#
+CREATE TABLE t1 (v1 INT, v2 INT);
+insert into t1 values (1,1);
+DELIMITER |;
+CREATE FUNCTION myProc ()
+ RETURNS INT
+
+ SQL SECURITY DEFINER
+
+ BEGIN
+ DECLARE mx INT;
+ SET mx = (select max(v1) from t1);
+ RETURN mx;
+ END|
+DELIMITER ;|
+--error ER_SUBQUERIES_NOT_SUPPORTED
+SET STATEMENT myisam_repair_threads=(select max(v1) from t1) FOR
+ select 1;
+
+--error ER_SUBQUERIES_NOT_SUPPORTED
+SET STATEMENT myisam_repair_threads=myProc() FOR
+ select 1;
+drop function myProc;
+drop table t1;
+
+#
+# Prepared Statement
+#
+set session binlog_format=mixed;
+PREPARE stmt1 FROM 'SELECT @@binlog_format';
+execute stmt1;
+set statement binlog_format=row for execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+set statement binlog_format=row for PREPARE stmt1 FROM 'SELECT @@binlog_format';
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+PREPARE stmt1 FROM 'set statement binlog_format=row for SELECT @@binlog_format';
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+set session binlog_format=default;
+
+#
+# Percona server bug#1341438
+# SET SESSION statement combined with SET STATEMENT has no effect
+#
+set session binlog_format=mixed;
+SET STATEMENT sort_buffer_size=150000 FOR set session binlog_format=row;
+SELECT @@binlog_format;
+
+#
+# Percona sever bug#1341606
+# SET STATEMENT incorrectly restore vaues of some variables
+#
+--echo #Correctly set timestamp
+set session timestamp=4646464;
+select @@timestamp != 4646464;
+select @@timestamp != 4646464;
+--echo #Correctly returned normal behaviour
+set session timestamp=default;
+select @@timestamp != 4646464;
+select @@timestamp != 4646464;
+--echo #here timestamp should be set only for the statement then restored default
+set statement timestamp=4646464 for select @@timestamp;
+set @save_tm=@@timestamp;
+select @@timestamp != 4646464;
+select @@timestamp != 4646464;
+select @@timestamp != @save_tm;
+
+#
+# Test of temporary changing default storage engine
+#
+set @save_dfs=@@default_storage_engine;
+SET @@default_storage_engine=MyISAM;
+SET STATEMENT default_storage_engine=MEMORY for CREATE TABLE t1 (a int);
+SHOW CREATE TABLE t1;
+select @@default_storage_engine;
+drop table t1;
+SET STATEMENT default_storage_engine=MyISAM for CREATE TABLE t1 (a int);
+SHOW CREATE TABLE t1;
+drop table t1;
+SET @@default_storage_engine=@save_dfs;
+
+#
+# statement timeout
+#
+SELECT @@MAX_STATEMENT_TIME;
+SET STATEMENT MAX_STATEMENT_TIME=2 FOR SELECT SLEEP(1);
+SHOW STATUS LIKE "max_statement_time_exceeded";
+SET STATEMENT MAX_STATEMENT_TIME=2 FOR SELECT SLEEP(3);
+SHOW STATUS LIKE "max_statement_time_exceeded";
+SELECT @@MAX_STATEMENT_TIME;
diff --git a/sql/item.h b/sql/item.h
index ff0c786ab94..e006622dca2 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -2819,6 +2819,16 @@ protected:
fixed= 1;
}
public:
+ Item_string(CHARSET_INFO *csi, const char *str_arg, uint length_arg)
+ : m_cs_specified(FALSE)
+ {
+ collation.set(csi, DERIVATION_COERCIBLE);
+ set_name(NULL, 0, system_charset_info);
+ decimals= NOT_FIXED_DEC;
+ fixed= 1;
+ str_value.copy(str_arg, length_arg, csi);
+ max_length= str_value.numchars() * csi->mbmaxlen;
+ }
// Constructors with the item name set from its value
Item_string(const char *str, uint length, CHARSET_INFO *cs,
Derivation dv, uint repertoire)
@@ -2954,6 +2964,7 @@ public:
}
return MYSQL_TYPE_STRING; // Not a temporal literal
}
+
};
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 4ec0466bda8..25c0197cb9b 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -5547,7 +5547,7 @@ get_var_with_binlog(THD *thd, enum_sql_command sql_command,
tmp_var_list.push_back(new set_var_user(new Item_func_set_user_var(name,
new Item_null())));
/* Create the variable */
- if (sql_set_variables(thd, &tmp_var_list))
+ if (sql_set_variables(thd, &tmp_var_list, false))
{
thd->lex= sav_lex;
goto err;
diff --git a/sql/lex.h b/sql/lex.h
index 5ca188f99a0..51b69f00990 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -567,6 +567,7 @@ static SYMBOL symbols[] = {
{ "START", SYM(START_SYM)},
{ "STARTING", SYM(STARTING)},
{ "STARTS", SYM(STARTS_SYM)},
+ { "STATEMENT", SYM(STATEMENT_SYM)},
{ "STATS_AUTO_RECALC",SYM(STATS_AUTO_RECALC_SYM)},
{ "STATS_PERSISTENT", SYM(STATS_PERSISTENT_SYM)},
{ "STATS_SAMPLE_PAGES",SYM(STATS_SAMPLE_PAGES_SYM)},
diff --git a/sql/set_var.cc b/sql/set_var.cc
index bae65118112..1dad3603799 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -147,7 +147,7 @@ sys_var::sys_var(sys_var_chain *chain, const char *name_arg,
flags(flags_arg), show_val_type(show_val_type_arg),
guard(lock), offset(off), on_check(on_check_func), on_update(on_update_func),
deprecation_substitute(substitute),
- is_os_charset(FALSE)
+ is_os_charset(FALSE), default_val(FALSE)
{
/*
There is a limitation in handle_options() related to short options:
@@ -675,9 +675,10 @@ sys_var *intern_find_sys_var(const char *str, uint length)
-1 ERROR, message not sent
*/
-int sql_set_variables(THD *thd, List<set_var_base> *var_list)
+int sql_set_variables(THD *thd, List<set_var_base> *var_list, bool free)
{
- int error;
+ int error= 0;
+ bool was_error= thd->is_error();
List_iterator_fast<set_var_base> it(*var_list);
DBUG_ENTER("sql_set_variables");
@@ -687,7 +688,7 @@ int sql_set_variables(THD *thd, List<set_var_base> *var_list)
if ((error= var->check(thd)))
goto err;
}
- if (!(error= MY_TEST(thd->is_error())))
+ if (was_error || !(error= MY_TEST(thd->is_error())))
{
it.rewind();
while ((var= it++))
@@ -695,7 +696,8 @@ int sql_set_variables(THD *thd, List<set_var_base> *var_list)
}
err:
- free_underlaid_joins(thd, &thd->lex->select_lex);
+ if (free)
+ free_underlaid_joins(thd, &thd->lex->select_lex);
DBUG_RETURN(error);
}
@@ -788,6 +790,7 @@ int set_var::light_check(THD *thd)
*/
int set_var::update(THD *thd)
{
+ var->set_is_default(value == 0);
return value ? var->update(thd, this) : var->set_default(thd, this);
}
diff --git a/sql/set_var.h b/sql/set_var.h
index fe2a0d8e953..e4a4385f056 100644
--- a/sql/set_var.h
+++ b/sql/set_var.h
@@ -87,6 +87,7 @@ protected:
on_update_function on_update;
const char *const deprecation_substitute;
bool is_os_charset; ///< true if the value is in character_set_filesystem
+ bool default_val;
public:
sys_var(sys_var_chain *chain, const char *name_arg, const char *comment,
@@ -191,6 +192,8 @@ public:
return insert_dynamic(array, (uchar*)&option);
}
void do_deprecated_warning(THD *thd);
+ bool is_default() { return default_val; }
+ void set_is_default(bool def) { default_val= def; }
virtual uchar *default_value_ptr(THD *thd)
{ return (uchar*)&option.def_value; }
@@ -249,6 +252,7 @@ public:
virtual int check(THD *thd)=0; /* To check privileges etc. */
virtual int update(THD *thd)=0; /* To set the value */
virtual int light_check(THD *thd) { return check(thd); } /* for PS */
+ virtual bool is_system() { return FALSE; }
};
@@ -290,6 +294,7 @@ public:
else
value=value_arg;
}
+ virtual bool is_system() { return 1; }
int check(THD *thd);
int update(THD *thd);
int light_check(THD *thd);
@@ -388,7 +393,7 @@ SHOW_VAR* enumerate_sys_vars(THD *thd, bool sorted, enum enum_var_type type);
int fill_sysvars(THD *thd, TABLE_LIST *tables, COND *cond);
sys_var *find_sys_var(THD *thd, const char *str, uint length=0);
-int sql_set_variables(THD *thd, List<set_var_base> *var_list);
+int sql_set_variables(THD *thd, List<set_var_base> *var_list, bool free);
#define SYSVAR_AUTOSIZE(VAR,VAL) \
do { \
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 199a822d022..bb428079433 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7111,3 +7111,5 @@ ER_TABLE_DEFINITION_TOO_BIG
eng "The definition for table %`s is too big"
ER_STATEMENT_TIMEOUT 70100
eng "Query execution was interrupted (max_statement_time exceeded)"
+ER_SUBQUERIES_NOT_SUPPORTED 42000
+ eng "%s does not support subqueries or stored functions."
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 34fa0308f7d..90c550d6a30 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -3441,6 +3441,7 @@ void Query_arena::free_items()
{
next= free_list->next;
DBUG_ASSERT(free_list != next);
+ DBUG_PRINT("info", ("free item: 0x%lx", (ulong) free_list));
free_list->delete_self();
}
/* Postcondition: free_list is 0 */
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 4198402ae9b..7a34960e390 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -3826,6 +3826,10 @@ public:
thr_timer_end(&query_timer);
#endif
}
+ void restore_set_statement_var()
+ {
+ main_lex.restore_set_statement_var();
+ }
};
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 7eb38b1dd7a..6432e1937be 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -542,6 +542,8 @@ void lex_start(THD *thd)
lex->reset_slave_info.all= false;
lex->limit_rows_examined= 0;
lex->limit_rows_examined_cnt= ULONGLONG_MAX;
+ lex->var_list.empty();
+ lex->stmt_var_list.empty();
DBUG_VOID_RETURN;
}
@@ -4233,6 +4235,17 @@ int LEX::print_explain(select_result_sink *output, uint8 explain_flags,
return res;
}
+void LEX::restore_set_statement_var()
+{
+ DBUG_ENTER("LEX::restore_set_statement_var");
+ if (!old_var_list.is_empty())
+ {
+ DBUG_PRINT("info", ("vars: %d", old_var_list.elements));
+ sql_set_variables(thd, &old_var_list, false);
+ old_var_list.empty();
+ }
+ DBUG_VOID_RETURN;
+}
/*
Save explain structures of a UNION. The only variable member is whether the
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index b18cff131ca..6c631e20c87 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -2401,6 +2401,8 @@ struct LEX: public Query_tables_list
List<Item> *insert_list,field_list,value_list,update_list;
List<List_item> many_values;
List<set_var_base> var_list;
+ List<set_var_base> stmt_var_list; //SET_STATEMENT values
+ List<set_var_base> old_var_list; // SET STATEMENT old values
List<Item_func_set_user_var> set_var_list; // in-query assignment list
List<Item_param> param_list;
List<LEX_STRING> view_list; // view list (list of field names in view)
@@ -2775,6 +2777,7 @@ struct LEX: public Query_tables_list
int print_explain(select_result_sink *output, uint8 explain_flags,
bool is_analyze, bool *printed_anything);
+ void restore_set_statement_var();
};
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index b905d8b0bd6..20bc0431d4d 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2635,6 +2635,98 @@ mysql_execute_command(THD *thd)
thd->get_binlog_format(&orig_binlog_format,
&orig_current_stmt_binlog_format);
+ if (!lex->stmt_var_list.is_empty())
+ {
+ DBUG_PRINT("info", ("SET STATEMENT %d vars", lex->stmt_var_list.elements));
+
+ lex->old_var_list.empty();
+ List_iterator_fast<set_var_base> it(lex->stmt_var_list);
+ set_var_base *var;
+ while ((var=it++))
+ {
+ DBUG_ASSERT(var->is_system());
+ set_var *o= NULL, *v= (set_var*)var;
+ if (v->var->is_default())
+ o= new set_var(v->type, v->var, &v->base, NULL);
+ else
+ {
+ switch (v->var->option.var_type)
+ {
+ case GET_BOOL:
+ case GET_INT:
+ case GET_LONG:
+ case GET_LL:
+ {
+ bool null_value;
+ longlong val= v->var->val_int(&null_value, thd, v->type, &v->base);
+ o= new set_var(v->type, v->var, &v->base,
+ (null_value ?
+ (Item *)new Item_null() :
+ (Item *)new Item_int(val)));
+ }
+ break;
+ case GET_UINT:
+ case GET_ULONG:
+ case GET_ULL:
+ {
+ bool null_value;
+ ulonglong val= v->var->val_int(&null_value, thd, v->type, &v->base);
+ o= new set_var(v->type, v->var, &v->base,
+ (null_value ?
+ (Item *)new Item_null() :
+ (Item *)new Item_uint(val)));
+ }
+ break;
+ case GET_DOUBLE:
+ {
+ bool null_value;
+ double val= v->var->val_real(&null_value, thd, v->type, &v->base);
+ o= new set_var(v->type, v->var, &v->base,
+ (null_value ?
+ (Item *)new Item_null() :
+ (Item *)new Item_float(val, 1)));
+ }
+ break;
+ default:
+ case GET_NO_ARG:
+ case GET_DISABLED:
+ DBUG_ASSERT(0);
+ case 0:
+ case GET_FLAGSET:
+ case GET_ASK_ADDR:
+ case GET_TYPE_MASK:
+ case GET_ENUM:
+ case GET_SET:
+ case GET_STR:
+ case GET_STR_ALLOC:
+ {
+ char buff[STRING_BUFFER_USUAL_SIZE];
+ String tmp(buff, sizeof(buff), v->var->charset(thd)),*val;
+ val= v->var->val_str(&tmp, thd, v->type, &v->base);
+ if (val)
+ {
+ Item_string *str= new Item_string(v->var->charset(thd),
+ val->ptr(), val->length());
+ o= new set_var(v->type, v->var, &v->base, str);
+ }
+ else
+ o= new set_var(v->type, v->var, &v->base, new Item_null());
+ }
+ break;
+ }
+ }
+ DBUG_ASSERT(o);
+ lex->old_var_list.push_back(o);
+ }
+ if (thd->is_error() ||
+ (res= sql_set_variables(thd, &lex->stmt_var_list, false)))
+ {
+ if (!thd->is_error())
+ my_error(ER_WRONG_ARGUMENTS, MYF(0), "SET");
+ goto error;
+ }
+ }
+
/*
Force statement logging for DDL commands to allow us to update
privilege, system or statistic tables directly without the updates
@@ -4100,7 +4192,7 @@ end_with_restore_list:
if ((check_table_access(thd, SELECT_ACL, all_tables, FALSE, UINT_MAX, FALSE)
|| open_and_lock_tables(thd, all_tables, TRUE, 0)))
goto error;
- if (!(res= sql_set_variables(thd, lex_var_list)))
+ if (!(res= sql_set_variables(thd, lex_var_list, true)))
{
my_ok(thd);
}
@@ -4344,8 +4436,7 @@ end_with_restore_list:
DBUG_ASSERT(lex->event_parse_data);
if (lex->table_or_sp_used())
{
- my_error(ER_NOT_SUPPORTED_YET, MYF(0), "Usage of subqueries or stored "
- "function calls as part of this statement");
+ my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0), "CREATE/ALTER EVENT");
break;
}
@@ -4665,8 +4756,7 @@ end_with_restore_list:
{
if (lex->table_or_sp_used())
{
- my_error(ER_NOT_SUPPORTED_YET, MYF(0), "Usage of subqueries or stored "
- "function calls as part of this statement");
+ my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0), "KILL");
break;
}
@@ -5059,6 +5149,7 @@ create_sp_error:
case SQLCOM_COMPOUND:
DBUG_ASSERT(all_tables == 0);
DBUG_ASSERT(thd->in_sub_stmt == 0);
+ lex->sphead->m_sql_mode= thd->variables.sql_mode;
if (do_execute_sp(thd, lex->sphead))
goto error;
break;
@@ -5482,6 +5573,8 @@ finish:
DBUG_ASSERT(!thd->in_active_multi_stmt_transaction() ||
thd->in_multi_stmt_transaction_mode());
+
+ lex->restore_set_statement_var();
lex->unit.cleanup();
if (! thd->in_sub_stmt)
diff --git a/sql/sql_plugin.h b/sql/sql_plugin.h
index 6b310865bba..59970b61574 100644
--- a/sql/sql_plugin.h
+++ b/sql/sql_plugin.h
@@ -189,4 +189,12 @@ extern bool plugin_foreach_with_mask(THD *thd, plugin_foreach_func *func,
int type, uint state_mask, void *arg);
extern bool plugin_dl_foreach(THD *thd, const LEX_STRING *dl,
plugin_foreach_func *func, void *arg);
+
+/**
+ Create deep copy of system_variables instance.
+*/
+extern
+struct system_variables *
+copy_system_variables(const struct system_variables *src);
+extern void free_system_variables(struct system_variables *v);
#endif
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index b1765cdda04..5c67a4c45a7 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -3299,7 +3299,7 @@ void Prepared_statement::cleanup_stmt()
{
DBUG_ENTER("Prepared_statement::cleanup_stmt");
DBUG_PRINT("enter",("stmt: 0x%lx", (long) this));
-
+ thd->restore_set_statement_var();
cleanup_items(free_list);
thd->cleanup_after_query();
thd->rollback_item_tree_changes();
@@ -3619,7 +3619,9 @@ Prepared_statement::execute_loop(String *expanded_query,
Reprepare_observer reprepare_observer;
bool error;
int reprepare_attempt= 0;
-
+#ifndef DBUG_OFF
+ Item *free_list_state= thd->free_list;
+#endif
thd->select_number= select_number_after_prepare;
/* Check if we got an error when sending long data */
if (state == Query_arena::STMT_ERROR)
@@ -3646,7 +3648,7 @@ reexecute:
allocated items when cleaning up after validation of the prepared
statement.
*/
- DBUG_ASSERT(thd->free_list == NULL);
+ DBUG_ASSERT(thd->free_list == free_list_state);
/*
Install the metadata observer. If some metadata version is
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 09f7678fd83..5ebf4d29a80 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -256,7 +256,6 @@ static bool maybe_start_compound_statement(THD *thd)
Lex->sp_chistics.suid= SP_IS_NOT_SUID;
Lex->sphead->set_body_start(thd, YYLIP->get_cpp_ptr());
- Lex->sphead->m_sql_mode= thd->variables.sql_mode;
}
return 0;
}
@@ -788,8 +787,10 @@ static void sp_create_assignment_lex(THD *thd, bool no_lookahead)
lex->var_list.empty();
lex->autocommit= 0;
/* get_ptr() is only correct with no lookahead. */
- DBUG_ASSERT(no_lookahead);
- lex->sphead->m_tmp_query= lip->get_ptr();
+ if (no_lookahead)
+ lex->sphead->m_tmp_query= lip->get_ptr();
+ else
+ lex->sphead->m_tmp_query= lip->get_tok_end();
/* Inherit from outer lex. */
lex->option_type= old_lex->option_type;
}
@@ -932,10 +933,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
%parse-param { THD *thd }
%lex-param { THD *thd }
/*
- Currently there are 163 shift/reduce conflicts.
+ Currently there are 164 shift/reduce conflicts.
We should not introduce new conflicts any more.
*/
-%expect 163
+%expect 164
/*
Comments for TOKENS.
@@ -1481,6 +1482,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
%token STARTING
%token STARTS_SYM
%token START_SYM /* SQL-2003-R */
+%token STATEMENT_SYM
%token STATS_AUTO_RECALC_SYM
%token STATS_PERSISTENT_SYM
%token STATS_SAMPLE_PAGES_SYM
@@ -14317,6 +14319,7 @@ keyword_sp:
| SQL_NO_CACHE_SYM {}
| SQL_THREAD {}
| STARTS_SYM {}
+ | STATEMENT_SYM {}
| STATUS_SYM {}
| STORAGE_SYM {}
| STRING_SYM {}
@@ -14391,8 +14394,36 @@ set:
}
start_option_value_list
{}
+ | SET STATEMENT_SYM
+ {
+ LEX *lex= Lex;
+ mysql_init_select(lex);
+ lex->sql_command= SQLCOM_SET_OPTION;
+ lex->autocommit= 0;
+ }
+ set_stmt_option_value_following_option_type_list
+ {
+ LEX *lex= Lex;
+ if (lex->table_or_sp_used())
+ {
+ my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0), "SET STATEMENT");
+ MYSQL_YYABORT;
+ }
+ lex->stmt_var_list= lex->var_list;
+ lex->var_list.empty();
+ }
+ FOR_SYM verb_clause
+ {}
;
+set_stmt_option_value_following_option_type_list:
+ /*
+ Only system variables can be used here. If this condition is changed
+ please check careful code under lex->option_type == OPT_STATEMENT
+ condition on wrong type casts.
+ */
+ option_value_following_option_type
+ | set_stmt_option_value_following_option_type_list ',' option_value_following_option_type
// Start of option value list
start_option_value_list:
@@ -14497,20 +14528,29 @@ option_value_following_option_type:
{
LEX *lex= Lex;
- if ($1.var && $1.var != trg_new_row_fake_var)
+ /*
+ Ignore SET STATEMENT variables list on slaves because system
+ variables are not replicated except certain variables set the
+ values of whose are written to binlog event header and nothing
+ additional is required to set them.
+ */
+ if (!thd->slave_thread)
{
- /* It is a system variable. */
- if (set_system_variable(thd, &$1, lex->option_type, $3))
+ if ($1.var && $1.var != trg_new_row_fake_var)
+ {
+ /* It is a system variable. */
+ if (set_system_variable(thd, &$1, lex->option_type, $3))
+ MYSQL_YYABORT;
+ }
+ else
+ {
+ /*
+ Not in trigger assigning value to new row,
+ and option_type preceding local variable is illegal.
+ */
+ my_parse_error(ER(ER_SYNTAX_ERROR));
MYSQL_YYABORT;
- }
- else
- {
- /*
- Not in trigger assigning value to new row,
- and option_type preceding local variable is illegal.
- */
- my_parse_error(ER(ER_SYNTAX_ERROR));
- MYSQL_YYABORT;
+ }
}
}
;
View Lorry Controller Status