21 Oct
2014
21 Oct
'14
1:27 p.m.
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