MDEV-5231: Per query variables from Percona Server (rewritten)bb-set-statement

9 files changed, 2228 insertions, 12 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;