diff options
Diffstat (limited to 'mysql-test/r/set_statement.result')
| -rw-r--r-- | mysql-test/r/set_statement.result | 1028 |
1 files changed, 1028 insertions, 0 deletions
diff --git a/mysql-test/r/set_statement.result b/mysql-test/r/set_statement.result new file mode 100644 index 00000000000..faa79951c55 --- /dev/null +++ b/mysql-test/r/set_statement.result @@ -0,0 +1,1028 @@ +'# SET STATEMENT ..... FOR .... TEST' +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS myProc; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; +DROP PROCEDURE IF EXISTS p4; +DROP PROCEDURE IF EXISTS p5; +DROP PROCEDURE IF EXISTS p6; +DROP VIEW IF EXISTS v1; +DROP TABLE IF EXISTS STATEMENT; +'# Setup database' +CREATE TABLE t1 (v1 INT, v2 INT); +INSERT INTO t1 VALUES (1,2); +INSERT INTO t1 VALUES (3,4); +'' +'#------------------ STATEMENT Test 1 -----------------------#' +'# Initialize variables to known setting' +SET SESSION sort_buffer_size=100000; +'' +'# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 100000 +SET STATEMENT sort_buffer_size=150000 FOR SELECT *,@@sort_buffer_size FROM t1; +v1 v2 @@sort_buffer_size +1 2 150000 +3 4 150000 +'' +'# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 100000 +'' +'#------------------ STATEMENT Test 2 -----------------------#' +'# Initialize variables to known setting' +SET SESSION binlog_format=mixed; +SET SESSION sort_buffer_size=100000; +'# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 100000 +SHOW SESSION VARIABLES LIKE 'binlog_format'; +Variable_name Value +binlog_format MIXED +SET STATEMENT sort_buffer_size=150000, binlog_format=row +FOR SELECT *,@@sort_buffer_size,@@binlog_format FROM t1; +v1 v2 @@sort_buffer_size @@binlog_format +1 2 150000 ROW +3 4 150000 ROW +'# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 100000 +SHOW SESSION VARIABLES LIKE 'binlog_format'; +Variable_name Value +binlog_format MIXED +'' +'#------------------ STATEMENT Test 3 -----------------------#' +'# set initial variable value, make prepared statement +SET SESSION binlog_format=row; +PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT *,@@binlog_format FROM t1'; +'' +'# Change variable setting' +SET SESSION binlog_format=mixed; +'' +'# Pre-STATEMENT variable value' +'' +SHOW SESSION VARIABLES LIKE 'binlog_format'; +Variable_name Value +binlog_format MIXED +'' +EXECUTE stmt1; +v1 v2 @@binlog_format +1 2 ROW +3 4 ROW +'' +'# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'binlog_format'; +Variable_name Value +binlog_format MIXED +'' +DEALLOCATE PREPARE stmt1; +'#------------------ STATEMENT Test 4 -----------------------#' +'# set initial variable value, make prepared statement +SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1; +'' +'# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; +Variable_name Value +myisam_sort_buffer_size 500000 +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; +Variable_name Value +myisam_repair_threads 1 +'' +SET STATEMENT myisam_sort_buffer_size=800000, +myisam_repair_threads=2 FOR OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +'' +'# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; +Variable_name Value +myisam_sort_buffer_size 500000 +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; +Variable_name Value +myisam_repair_threads 1 +'' +'#------------------ STATEMENT Test 5 -----------------------#' +'# Initialize variables to known setting' +SET SESSION sort_buffer_size=100000; +'' +'# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 100000 +'' +SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2; +ERROR 42S02: Table 'test.t2' doesn't exist +'' +'# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 100000 +'' +'#------------------ STATEMENT Test 6 -----------------------#' +'# Initialize variables to known setting' +SET SESSION keep_files_on_create=ON; +'' +'# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; +Variable_name Value +keep_files_on_create ON +'' +SET STATEMENT keep_files_on_create=OFF FOR SELECT *,@@keep_files_on_create FROM t1; +v1 v2 @@keep_files_on_create +1 2 0 +3 4 0 +'' +'# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; +Variable_name Value +keep_files_on_create ON +'' +'#------------------ STATEMENT Test 7 -----------------------#' +'# Initialize variables to known setting' +SET SESSION max_join_size=2222220000000; +'' +'# Pre-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'max_join_size'; +Variable_name Value +max_join_size 2222220000000 +'' +SET STATEMENT max_join_size=1000000000000 FOR SELECT *,@@max_join_size FROM t1; +v1 v2 @@max_join_size +1 2 1000000000000 +3 4 1000000000000 +'' +'# Post-STATEMENT variable value' +SHOW SESSION VARIABLES LIKE 'max_join_size'; +Variable_name Value +max_join_size 2222220000000 +'' +'#------------------Test 8-----------------------#' +'# Initialize test variables' +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size = 200000, +max_join_size=2222220000000, +keep_files_on_create=ON; +'' +'# LONG ' +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 200000 +SET STATEMENT sort_buffer_size = 100000 +FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 100000 +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; +Variable_name Value +sort_buffer_size 200000 +'' +'# MY_BOOL ' +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; +Variable_name Value +keep_files_on_create ON +SET STATEMENT keep_files_on_create=OFF +FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; +Variable_name Value +keep_files_on_create OFF +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; +Variable_name Value +keep_files_on_create ON +'' +'# INT/LONG ' +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; +Variable_name Value +myisam_repair_threads 1 +SET STATEMENT myisam_repair_threads=2 +FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; +Variable_name Value +myisam_repair_threads 2 +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; +Variable_name Value +myisam_repair_threads 1 +'' +'# ULONGLONG ' +SHOW SESSION VARIABLES LIKE 'max_join_size'; +Variable_name Value +max_join_size 2222220000000 +SET STATEMENT max_join_size=2000000000000 +FOR SHOW SESSION VARIABLES LIKE 'max_join_size'; +Variable_name Value +max_join_size 2000000000000 +SHOW SESSION VARIABLES LIKE 'max_join_size'; +Variable_name Value +max_join_size 2222220000000 +'' +'#------------------Test 9-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +CREATE FUNCTION myProc (cost DECIMAL(10,2)) +RETURNS DECIMAL(10,2) +SQL SECURITY DEFINER +tax: BEGIN +DECLARE order_tax DECIMAL(10,2); +SET order_tax = cost * .05 + @@sort_buffer_size; +RETURN order_tax; +END| +'' +'# During Execution values +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +SELECT myProc(123.45); +myProc(123.45) +200006.17 +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=300000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +SELECT myProc(123.45); +myProc(123.45) +300006.17 +'' +'# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2' +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +DROP FUNCTION myProc; +'' +'# Post-STATEMENT No 2 variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'#------------------Test 11-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +PREPARE stmt1 FROM +'SET STATEMENT binlog_format=row FOR SELECT *,@@binlog_format,@@sort_buffer_size FROM t1'; +'' +'Test No 1 Post Value & Test 2 Pre values' +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +EXECUTE stmt1; +v1 v2 @@binlog_format @@sort_buffer_size +1 2 ROW 200000 +3 4 ROW 200000 +'' +'# Post-STATEMENT No 2 +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +'#------------------Test 12-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +CREATE PROCEDURE p1() BEGIN +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +END| +'' +'Test No 1 Post Value & Test 2 Pre values' +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=400001, +myisam_repair_threads=3, +sort_buffer_size=200001, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000001 FOR +CALL p1(); +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +400001 3 200001 ROW 0 4444440000001 +'' +'# Post-STATEMENT No 2 +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +'#------------------Test 13-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'' +CREATE PROCEDURE p2() BEGIN +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=3, +sort_buffer_size=300000, +binlog_format=mixed, +keep_files_on_create=OFF, +max_join_size=3333330000000 FOR +CALL p1(); +END| +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +CALL p2(); +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +400000 3 300000 MIXED 0 3333330000000 +'' +'# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +'#------------------Test 14-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'' +CREATE PROCEDURE p3() BEGIN +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +SET STATEMENT myisam_sort_buffer_size=320000, +myisam_repair_threads=2, +sort_buffer_size=220022, +binlog_format=row, +keep_files_on_create=ON, +max_join_size=2222220000000 FOR +CALL p2(); +END| +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +CALL p3(); +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +400000 2 200000 ROW 0 4444440000000 +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +400000 3 300000 MIXED 0 3333330000000 +'' +'# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +'' +'' +'#------------------Test 15-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'' +CREATE PROCEDURE p4() BEGIN +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +SET STATEMENT myisam_sort_buffer_size=320000, +myisam_repair_threads=2, +sort_buffer_size=220022, +binlog_format=row, +keep_files_on_create=ON, +max_join_size=2222220000000 FOR +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +SET STATEMENT myisam_sort_buffer_size=320000, +myisam_repair_threads=2, +sort_buffer_size=220022, +binlog_format=row, +keep_files_on_create=ON, +max_join_size=2222220000000 FOR +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +SET STATEMENT myisam_sort_buffer_size=320000, +myisam_repair_threads=2, +sort_buffer_size=220022, +binlog_format=row, +keep_files_on_create=ON, +max_join_size=2222220000000 FOR +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +END| +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +CALL p4(); +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +400000 2 200000 ROW 0 4444440000000 +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +320000 2 220022 ROW 1 2222220000000 +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +320000 2 220022 ROW 1 2222220000000 +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +320000 2 220022 ROW 1 2222220000000 +'' +'# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +'#------------------Test 16-----------------------#' +'' +'# Pre-STATEMENT variable value +SELECT @@sql_mode; +@@sql_mode + +'' +'' +SET STATEMENT sql_mode='ansi' FOR PREPARE stmt FROM 'SELECT "t1".* FROM t1'; +execute stmt; +v1 v2 +1 2 +3 4 +ALTER TABLE t1 ADD COLUMN v3 int; +execute stmt; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.* FROM t1' at line 1 +ALTER TABLE t1 drop COLUMN v3; +deallocate prepare stmt; +'' +'# Post-STATEMENT +SELECT @@sql_mode; +@@sql_mode + +check the same behaviour in normal set +SET sql_mode='ansi'; +PREPARE stmt FROM 'SELECT "t1".* FROM t1'; +SET sql_mode=default; +execute stmt; +v1 v2 +1 2 +3 4 +ALTER TABLE t1 ADD COLUMN v3 int; +execute stmt; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.* FROM t1' at line 1 +ALTER TABLE t1 drop COLUMN v3; +deallocate prepare stmt; +SELECT @@sql_mode; +@@sql_mode + +SET sql_mode='ansi'; +SELECT @@sql_mode; +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +CREATE PROCEDURE p6() BEGIN +SELECT @@sql_mode; +SELECT "t1".* FROM t1; +END| +SET sql_mode=default; +call p6; +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +v1 v2 +1 2 +3 4 +ALTER TABLE t1 ADD COLUMN v3 int; +create view v1 as select * from t1; +drop view v1; +call p6; +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +v1 v2 v3 +1 2 NULL +3 4 NULL +ALTER TABLE t1 drop COLUMN v3; +drop procedure p6; +SELECT @@sql_mode; +@@sql_mode + +# SET and the statement parsed as one unit before the SET takes effect +SET STATEMENT sql_mode='ansi' FOR +CREATE PROCEDURE p6() BEGIN +SELECT @@sql_mode; +SELECT "t1".* FROM t1; +END| +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.* FROM t1; +END' at line 4 +SELECT @@sql_mode; +@@sql_mode + +SET sql_mode='ansi'; +SELECT @@sql_mode; +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +BEGIN NOT ATOMIC +SELECT @@sql_mode; +SELECT "t1".* FROM t1; +END| +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +v1 v2 +1 2 +3 4 +SET sql_mode=default; +SELECT @@sql_mode; +@@sql_mode + +# SET and the statement parsed as one unit before the SET takes effect +SET STATEMENT sql_mode='ansi' FOR +BEGIN NOT ATOMIC +SELECT @@sql_mode; +SELECT "t1".* FROM t1; +END| +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.* FROM t1; +END' at line 4 +SET STATEMENT sql_mode='ansi' FOR +BEGIN NOT ATOMIC +SELECT @@sql_mode; +SELECT * FROM t1; +SELECT @@sql_mode; +END| +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +v1 v2 +1 2 +3 4 +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +'' +'' +'#------------------Test 17-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +SET STATEMENT myisam_sort_buffer_size=320000, +myisam_repair_threads=2, +sort_buffer_size=220022, +binlog_format=row, +keep_files_on_create=ON, +max_join_size=2222220000000 +FOR SET SESSION +myisam_sort_buffer_size=260000, +myisam_repair_threads=3, +sort_buffer_size=230013, +binlog_format=row, +keep_files_on_create=ON, +max_join_size=2323230000000; +'' +'# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +'#------------------Test 18-----------------------#' +'# set initial variable values +SET SESSION myisam_sort_buffer_size=500000, +myisam_repair_threads=1, +sort_buffer_size=100000, +binlog_format=mixed, +keep_files_on_create=ON, +max_join_size=2222220000000; +'' +'# Pre-STATEMENT variable value +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +CREATE PROCEDURE p5() BEGIN +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +SET SESSION +myisam_sort_buffer_size=260000, +myisam_repair_threads=3, +sort_buffer_size=230013, +binlog_format=row, +keep_files_on_create=ON, +max_join_size=2323230000000; +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +END| +'' +'' +SET STATEMENT myisam_sort_buffer_size=400000, +myisam_repair_threads=2, +sort_buffer_size=200000, +binlog_format=row, +keep_files_on_create=OFF, +max_join_size=4444440000000 FOR +CALL p5(); +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +400000 2 200000 ROW 0 4444440000000 +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +260000 3 230013 ROW 1 2323230000000 +'' +'# Post-STATEMENT +SELECT @@myisam_sort_buffer_size, +@@myisam_repair_threads, +@@sort_buffer_size, +@@binlog_format, +@@keep_files_on_create, +@@max_join_size; +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size +500000 1 100000 MIXED 1 2222220000000 +'' +'' +'#------------------Test 19-----------------------#' +SET STATEMENT max_error_count=100 FOR; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 +SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT t1 VALUES (1,2)' at line 1 +SET STATEMENT FOR INSERT INTO t1 VALUES (1,2); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1 +SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1 +SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); +ERROR HY000: Unknown system variable 'GLOBAL' +SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2)' at line 1 +'' +'' +'#------------------Test 20-----------------------#' +SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2); +ERROR HY000: Variable 'connect_timeout' is a GLOBAL variable and should be set with SET GLOBAL +'' +'' +'#------------------Test 21-----------------------#' +SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; +@@myisam_sort_buffer_size @@sort_buffer_size +500000 100000 +SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000 +FOR SET STATEMENT myisam_sort_buffer_size=200000 +FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; +@@myisam_sort_buffer_size @@sort_buffer_size +200000 100000 +SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; +@@myisam_sort_buffer_size @@sort_buffer_size +500000 100000 +'' +'' +'#------------------Test 22-----------------------#' +CREATE TABLE STATEMENT(a INT); +DROP TABLE STATEMENT; +'' +'# Cleanup' +DROP TABLE t1; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; +DROP PROCEDURE p5; +CREATE TABLE t1 (v1 INT, v2 INT); +insert into t1 values (1,1); +CREATE FUNCTION myProc () +RETURNS INT +SQL SECURITY DEFINER +BEGIN +DECLARE mx INT; +SET mx = (select max(v1) from t1); +RETURN mx; +END| +SET STATEMENT myisam_repair_threads=(select max(v1) from t1) FOR +select 1; +ERROR 42000: SET STATEMENT does not support subqueries or stored functions. +SET STATEMENT myisam_repair_threads=myProc() FOR +select 1; +ERROR 42000: SET STATEMENT does not support subqueries or stored functions. +drop function myProc; +drop table t1; +set session binlog_format=mixed; +PREPARE stmt1 FROM 'SELECT @@binlog_format'; +execute stmt1; +@@binlog_format +MIXED +set statement binlog_format=row for execute stmt1; +@@binlog_format +ROW +execute stmt1; +@@binlog_format +MIXED +deallocate prepare stmt1; +set statement binlog_format=row for PREPARE stmt1 FROM 'SELECT @@binlog_format'; +execute stmt1; +@@binlog_format +MIXED +execute stmt1; +@@binlog_format +MIXED +deallocate prepare stmt1; +PREPARE stmt1 FROM 'set statement binlog_format=row for SELECT @@binlog_format'; +execute stmt1; +@@binlog_format +ROW +execute stmt1; +@@binlog_format +ROW +deallocate prepare stmt1; +set session binlog_format=default; +set session binlog_format=mixed; +SET STATEMENT sort_buffer_size=150000 FOR set session binlog_format=row; +SELECT @@binlog_format; +@@binlog_format +ROW +#Correctly set timestamp +set session timestamp=4646464; +select @@timestamp != 4646464; +@@timestamp != 4646464 +0 +select @@timestamp != 4646464; +@@timestamp != 4646464 +0 +#Correctly returned normal behaviour +set session timestamp=default; +select @@timestamp != 4646464; +@@timestamp != 4646464 +1 +select @@timestamp != 4646464; +@@timestamp != 4646464 +1 +#here timestamp should be set only for the statement then restored default +set statement timestamp=4646464 for select @@timestamp; +@@timestamp +4646464.000000 +set @save_tm=@@timestamp; +select @@timestamp != 4646464; +@@timestamp != 4646464 +1 +select @@timestamp != 4646464; +@@timestamp != 4646464 +1 +select @@timestamp != @save_tm; +@@timestamp != @save_tm +1 +set @save_dfs=@@default_storage_engine; +SET @@default_storage_engine=MyISAM; +SET STATEMENT default_storage_engine=MEMORY for CREATE TABLE t1 (a int); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +select @@default_storage_engine; +@@default_storage_engine +MyISAM +drop table t1; +SET STATEMENT default_storage_engine=MyISAM for CREATE TABLE t1 (a int); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +SET @@default_storage_engine=@save_dfs; +SELECT @@MAX_STATEMENT_TIME; +@@MAX_STATEMENT_TIME +0.000000 +SET STATEMENT MAX_STATEMENT_TIME=2 FOR SELECT SLEEP(1); +SLEEP(1) +0 +SHOW STATUS LIKE "max_statement_time_exceeded"; +Variable_name Value +Max_statement_time_exceeded 0 +SET STATEMENT MAX_STATEMENT_TIME=2 FOR SELECT SLEEP(3); +SLEEP(3) +1 +SHOW STATUS LIKE "max_statement_time_exceeded"; +Variable_name Value +Max_statement_time_exceeded 1 +SELECT @@MAX_STATEMENT_TIME; +@@MAX_STATEMENT_TIME +0.000000 |