New subject: [Maria-developers] [Commits] 28fcccc: MDEV-5231: Per query variables from Percona Server (rewritten)

17 Oct 2014

On 17.10.14 21:00, Sergei Golubchik wrote:
> Hi, Sanja!
>
> On Oct 13, sanja@mariadb.com wrote:
>> revision-id: 28fccccad32fa8a47dc572b968613e18ff51b634
>> parent(s): fec5ab5a56cb9a45c621207620cc85079cddf537
>> committer: Oleksandr Byelkin
>> branch nick: work-maria-10.1-sysvar2
>> timestamp: 2014-10-13 22:35:40 +0200
>> message:
>>
>> MDEV-5231: Per query variables from Percona Server (rewritten)
> This looks a lot better that the previous patch. Thanks!
> See my comments below.
>
> Some parts of the patch are not used (left-overs from the old
> implementation?). Some others, I think, are not needed.
Yes, I found that I made cleanup in wrong tree :(
>
> And some of the old tests don't make much sense, your new tests made
> them redundant and useless.
>
> A couple of comments after reading my previous review:
>
> * please add a test for any sysvar with non-trivial implementations of
>    sys_var::session_update(), that is, for a sysvar where old approach
>    doesn't work. Unless, of course, your timestamp tests are exactly that :)
yes, timestamp is that test.
>
> * add tests with @@default_engine. For example:
>
>    SET @@default_engine=MyISAM;
>    SET STATEMENT @@default_engine=MEMORY CREATE TABLE t1 (a int);
>    SHOW CREATE TABLE t1; -- verify the engine
>    SET STATEMENT @@default_engine=MyISAM CREATE TABLE t2 (a int);
Done. I think that to test other & current engine is enough because we 
test variable not engines and using 2 engine which always present makes 
the test simpler.
>
> etc.
>
> See other comments below:
>
>> diff --git a/mysql-test/r/set_statement.result b/mysql-test/r/set_statement.result
>> new file mode 100644
>> index 0000000..fed937e
>> --- /dev/null
>> +++ b/mysql-test/r/set_statement.result
>> @@ -0,0 +1,941 @@
>> +'# 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 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;
> 1. this is not "make prepared statement" as the comment says
Yes, probably mistake of test creator (cut'n'paste?)
> 2. what does it actually test? you don't verify whether
>     new values had any effect.
I have no idea how to check the effect, but maybe it is testing crash...
>> +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	150000
> Looks wrong. The value is not restored in case of an error.
fixed. It was problem that sql_set_variables was not working correctly 
is you enter it with already happened error.
>
>> +''
>> +'#------------------ 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
> How is this test different from other tests above?
>
>> +''
>> +'# 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
> Same question - how is this test different from other tests above?
Probably they check different variables?
>
>> +''
>> +'# 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;
>> +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)
>> +6.17
> How does this verify that SET STATEMENT had any effect?
fixed
>
>> +''
>> +'# 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;
> How does this verify that SET STATEMENT had any effect?
I have no idea what is verified here (crash, syntax, or just test suite 
was strange from the beginning?)
>
>> +''
>> +'# 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 10-----------------------#'
>> +'# 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
>> +PREPARE stmt2
>> +FROM 'SELECT * 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,
>> +binlog_format=row,
>> +keep_files_on_create=OFF,
>> +max_join_size=4444440000000 FOR
>> +EXECUTE stmt2;
>> +v1	v2
>> +1	2
>> +3	4
> How does this verify that SET STATEMENT had any effect?
>
> Add @@binlog_format and @@sort_buffer_size to the SELECT
> and use different values for @@sort_buffer_size in
> SET STATEMENT for PREPARE and EXECUTE.
>
> Ok, I see that you do it below. Then how is this test useful?
removed
>
>> +''
>> +'# 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
>> +''
>> +DEALLOCATE PREPARE stmt2;
>> +''
>> +'#------------------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 FROM t1';
> please also add @@sort_buffer_size to this SELECT
ok
>
>> +''
>> +'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
>> +1	2	ROW
>> +3	4	ROW
>> +''
>> +'# 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=400000,
>> +myisam_repair_threads=2,
>> +sort_buffer_size=200000,
>> +binlog_format=row,
>> +keep_files_on_create=OFF,
>> +max_join_size=4444440000000 FOR
> Use different values here, not same as in the previous SET STATEMENT
ok
>
>> +CALL p1();
>> +@@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
>> +''
>> +'# 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
> Ok, that's getting interesting. Now before deallocating, try to trigger
> automatic reprepare. e.g. ALTER TABLE t1 ADD COLUMN v3 int;
> and then execute it again.
>
> also do the same with SP. I mean, set statement sql_mode=ansi create procedure
> this procedure uses ansi quotes. verify that the correct sql mode (ansi)
> is recorded in mysql.proc table.

All checks added and fixed what could be fixed.
>
>> +deallocate prepare stmt;
>> +''
>> +'# Post-STATEMENT
>> +SELECT @@sql_mode;
>> +@@sql_mode
>> +
>> +''
>> +''
>> +'#------------------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;
> Add a couple of other variables to SET SESSION. The point - have some
> variables in SET SESSION that are *not* present in SET STATEMENT.
> Verify that their values were correctly set.
>
> Ok, I see that you do it below. Then how is this test useful?
>
>> +''
>> +'# 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|
> How is this test different from other tests above?
>
>> +''
>> +''
>> +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 max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
> Same erroneous statement tested twice?
removed
>
>> +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);
>> +PREPARE stmt
>> +FROM 'SELECT * FROM t1';
>> +SET STATEMENT myisam_sort_buffer_size=400000,
>> +myisam_repair_threads=(select max(v1) from t1),
>> +sort_buffer_size=200000,
>> +binlog_format=row,
>> +keep_files_on_create=OFF,
>> +max_join_size=4444440000000 FOR
>> +EXECUTE stmt;
>> +ERROR HY000: SET STATEMENT does not support using tables in the assignment variables expressions
> Why do you need a prepared statement to test that error?
Fixed
>
>> +deallocate prepare stmt;
>> +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
> those are good tests, and they make many of the tests above unnecessary
I only can guess what some test means, so I tried keep all test just in 
case they are testing something I am not think of.
>
>> +#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
>> diff --git a/sql/set_var.cc b/sql/set_var.cc
>> index bae6511..20e3040 100644
>> --- a/sql/set_var.cc
>> +++ b/sql/set_var.cc
>> @@ -147,7 +147,7 @@ void sys_var_end()
>>     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(TRUE)
> I've asked below why is that needed.
For some variables (like timestamp) default value is not a certain value 
but a special state (timestamp in such state returns current time 
instead of fixed value).
>
>>   {
>>     /*
>>       There is a limitation in handle_options() related to short options:
>> @@ -695,7 +695,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);
> Ok, so in normal SET you want to free_underlaid_joins,
> and in SET STATEMENT you don't. Right?
yes
>
>>     DBUG_RETURN(error);
>>   }
>>   
>> diff --git a/sql/item_func.cc b/sql/item_func.cc
>> index 2b89aa0..651e0b1 100644
>> --- a/sql/item_func.cc
>> +++ b/sql/item_func.cc
>> @@ -5556,7 +5556,7 @@ longlong Item_func_get_user_var::val_int()
>>       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, true))
> Make if 'false', not 'true' here. It doesn't make any practical difference
> (there are no joins, the variable is set to Item_null, explicitly),
> but makes the logic a bit clearer (standalone SET statement = free joins,
> a SET that is part of another statement = don't free joins).
I went this way as i means less changes
>
> Alternatively (and better) approach would be to remove sql_set_variables()
> here and use the same code that Item_func_set_user_var does.
>
> And then sql_set_variables() will be only used in SET variants, where
> a sysvar can be used. Here sysvars aren't possible, so sql_set_variables() is
> misleading.
>
>>       {
>>         thd->lex= sav_lex;
>>         goto err;
>> diff --git a/sql/set_var.h b/sql/set_var.h
>> index e48f394..9ee914b 100644
>> --- a/sql/set_var.h
>> +++ b/sql/set_var.h
>> @@ -118,6 +119,9 @@ class sys_var
>>     */
>>     bool set_default(THD *thd, set_var *var);
>>     bool update(THD *thd, set_var *var);
>> +  void stmt_update(THD *thd) {
>> +    on_update && on_update(this, thd, OPT_SESSION);
>> +  }
> Doesn't seem to be used anywhere in the patch
removed
>
>>   
>>     String *val_str_nolock(String *str, THD *thd, const uchar *value);
>>     longlong val_int(bool *is_null, THD *thd, enum_var_type type, const LEX_STRING *base);
>> diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
>> index 233bb83..4072af4 100644
>> --- a/sql/share/errmsg-utf8.txt
>> +++ b/sql/share/errmsg-utf8.txt
>> @@ -7110,3 +7110,5 @@ ER_SLAVE_SKIP_NOT_IN_GTID
>>   	eng "When using GTID, @@sql_slave_skip_counter can not be used. Instead, setting @@gtid_slave_pos explicitly can be used to skip to after a given GTID position."
>>   ER_STATEMENT_TIMEOUT 70100
>>           eng "Query execution was interrupted (max_statement_time exceeded)"
>> +ER_SET_STATEMENT_TABLES_IS_NOT_SUPPORTED
>> +        eng "SET STATEMENT does not support using tables in the assignment variables expressions"
> Better write it as
>
> ER_SUBQUERIES_NOT_SUPPORTED
>      eng "%s does not support subqueries or stored functions."
>
> and use it for SET STATEMENT:
>
>    my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0), "SET STATEMENT");
>
> and also use it for KILL:
>
> - 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");
fixed
>
>> diff --git a/sql/sql_class.h b/sql/sql_class.h
>> index d7bbfc3..0f6b39e 100644
>> --- a/sql/sql_class.h
>> +++ b/sql/sql_class.h
>> @@ -3711,6 +3711,10 @@ class THD :public Statement,
>>     void rgi_unlock_temporary_tables();
>>     bool rgi_have_temporary_tables();
>>   public:
>> +  inline MEM_ROOT *get_execution_mem_root()
>> +  {
>> +    return &main_mem_root;
>> +  }
> Doesn't seem to be used anywhere in this patch
removed
>
>>     /*
>>       Flag, mutex and condition for a thread to wait for a signal from another
>>       thread.
>> diff --git a/sql/sql_lex.h b/sql/sql_lex.h
>> index 5e9c7b9..03861d1 100644
>> --- a/sql/sql_lex.h
>> +++ b/sql/sql_lex.h
>> @@ -2379,6 +2381,8 @@ struct LEX: public Query_tables_list
>>       required a local context, the parser pops the top-most context.
>>     */
>>     List<Name_resolution_context> context_stack;
>> +  /* true if SET STATEMENT ... FOR ... statement is use, false otherwise */
>> +  bool set_statement;
> This doesn't seem to be needed, I've checked all places where
> set_statement is used below.
removed
>
>>   
>>     SQL_I_List<ORDER> proc_list;
>>     SQL_I_List<TABLE_LIST> auxiliary_table_list, save_list;
>> diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
>> index d8906b2..eff0e08 100644
>> --- a/sql/sql_parse.cc
>> +++ b/sql/sql_parse.cc
>> @@ -2637,6 +2637,98 @@ static bool do_execute_sp(THD *thd, sp_head *sp)
>>     thd->get_binlog_format(&orig_binlog_format,
>>                            &orig_current_stmt_binlog_format);
>>   
>> +  if (lex->set_statement && !lex->stmt_var_list.is_empty())
> Why do you check lex->set_statement? I'd think that
>
>    if (!lex->stmt_var_list.is_empty())
>
> is quite enough.
yes
>
>> +  {
>> +    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);
> Why do you treat default values differently?
because default value is not just a value for some variables, but 
special state.
>
>> +      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));
>> +              str->set_str_with_copy(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
>> @@ -2812,6 +2904,12 @@ static bool do_execute_sp(THD *thd, sp_head *sp)
>>     }
>>     case SQLCOM_EXECUTE:
>>     {
>> +    /*
>> +      Clean up free_list here from Items used in setting statement variables
>> +      to present clear item list to Prepared_statement::execute_loop which
>> +      need it empty because switch arena (and so item list) on statement arena.
>> +    */
>> +    //free_items(thd->free_list); thd->free_list= NULL;
> Eh? Forgot to remove it?
removed
>
>>       mysql_sql_stmt_execute(thd);
>>       break;
>>     }
>> diff --git a/sql/sql_plugin.cc b/sql/sql_plugin.cc
>> index 9ae3d79..4b54989 100644
>> --- a/sql/sql_plugin.cc
>> +++ b/sql/sql_plugin.cc
>> @@ -4078,3 +4078,48 @@ static void restore_ptr_backup(uint n, st_ptr_backup *backup)
>>       (backup++)->restore();
>>   }
>>   
>> +
>> +/**
>> +  Create deep copy of system_variables instance.
>> +*/
>> +struct system_variables *
>> +copy_system_variables(const struct system_variables *src)
> Doesn't seem to be used anywhere in the patch
removed
>
>> +{
>> +  struct system_variables *dst;
>> +
>> +  DBUG_ASSERT(src);
>> +
>> +  dst= (struct system_variables *)
>> +    sql_alloc(sizeof(struct system_variables));
>> +  if (!dst)
>> +    return NULL;
>> +  *dst= *src;
>> +
>> +  if (dst->dynamic_variables_ptr)
>> +  {
>> +    if (!(dst->dynamic_variables_ptr=
>> +          (char *)my_malloc(dst->dynamic_variables_size, MYF(MY_WME | MY_FAE))))
>> +      return NULL;
>> +    memcpy(dst->dynamic_variables_ptr,
>> +           src->dynamic_variables_ptr,
>> +           src->dynamic_variables_size);
>> +  }
>> +
>> +  mysql_mutex_lock(&LOCK_plugin);
>> +  dst->table_plugin=
>> +    intern_plugin_lock(NULL, src->table_plugin);
>> +  mysql_mutex_unlock(&LOCK_plugin);
>> +
>> +  return dst;
>> +}
>> +
>> +void free_system_variables(struct system_variables *v)
> Doesn't seem to be used anywhere in the patch
removed
>> +{
>> +  DBUG_ASSERT(v);
>> +
>> +  mysql_mutex_lock(&LOCK_plugin);
>> +  intern_plugin_unlock(NULL, v->table_plugin);
>> +  mysql_mutex_unlock(&LOCK_plugin);
>> +
>> +  my_free(v->dynamic_variables_ptr);
>> +}
>> diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
>> index e7fcdfb..d140597 100644
>> --- a/sql/sql_yacc.yy
>> +++ b/sql/sql_yacc.yy
>> @@ -14416,8 +14420,52 @@ set:
>>             }
>>             start_option_value_list
>>             {}
>> +        | SET STATEMENT_SYM
>> +          {
>> +            LEX *lex= Lex;
>> +            mysql_init_select(lex);
>> +            lex->sql_command= SQLCOM_SET_OPTION;
>> +	    /* Don't clear var_list in the case of recursive statement */
> What's "recursive set statement"? SET STATEMENT a=1 FOR SET STATEMENT ?
> I'd simply return a syntax error here. Like
>
>     MYSQL_YYABORT_UNLESS(lex->stmt_var_list.is_empty());
now with separate lists they will not conflicts.
>
>> +	    if (!lex->set_statement)
>> +            {
>> +              lex->var_list.empty();
>> +              lex->stmt_var_list.empty();
> Why here, and not in lex_start() ?
more work on every command, but I've moved
>> +            }
>> +            lex->autocommit= 0;
>> +            lex->set_statement= true;
>> +            sp_head *sp= lex->sphead;
>> +            if (sp && !sp->is_invoked())
>> +            {
>> +              sp->m_param_begin= ((yychar == YYEMPTY) ?  YYLIP->get_ptr() : YYLIP->get_tok_start());
>> +              sp->m_param_end= ((yychar == YYEMPTY) ?  YYLIP->get_ptr() : YYLIP->get_tok_end());
> what's that?
Looks like black magic but it has no effect on execution. Removed.
>
>> +            }
>> +          }
>> +          set_stmt_option_value_following_option_type_list
>> +          {
>> +            LEX *lex= Lex;
>> +            if (lex->query_tables)
>> +            {
>> +              my_error(ER_SET_STATEMENT_TABLES_IS_NOT_SUPPORTED, MYF(0));
> why? what about stored functions that use tables internally?
I added check later (where we iterate the list to store old values).
>
>> +              MYSQL_YYABORT;
>> +            }
>> +            //if (lex->set_statement && lex->stmt_var_list.is_empty())
>> +            {
>> +              lex->stmt_var_list= lex->var_list;
>> +              lex->var_list.empty();
>> +            }
>> +          }
>> +          FOR_SYM statement
>> +	  {}
>>           ;
>>   
>> +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:
>> @@ -14522,6 +14570,14 @@ option_value_following_option_type:
>>             {
>>               LEX *lex= Lex;
>>   
>> +            /*
>> +              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 && lex->set_statement))
>> +            {
> Hmm, okay. Alternatively, you can simply make sure that set statement
> is not written into binlog. Or don't execute assignments in the slave thread.
Having SET STATEMENT in a separate list we do not need this check.
>
>>               if ($1.var && $1.var != trg_new_row_fake_var)
>>               {
>>                 /* It is a system variable. */
> Regards,
> Sergei

Re: [Maria-developers] [Commits] 28fcccc: MDEV-5231: Per query variables from Percona Server (rewritten)

Sergei Golubchik

Oleksandr Byelkin

Sergei Golubchik

Oleksandr Byelkin

tags

participants (2)