## test variables#--disable_warningsdrop table if exists t1,t2;--enable_warnings# case insensitivity tests (new in 5.0)set @`test`=1;select @test, @`test`, @TEST, @`TEST`, @"teSt";set @TEST=2;select @test, @`test`, @TEST, @`TEST`, @"teSt";set @"tEST"=3;select @test, @`test`, @TEST, @`TEST`, @"teSt";set @`TeST`=4;select @test, @`test`, @TEST, @`TEST`, @"teSt";select @`teST`:=5;select @test, @`test`, @TEST, @`TEST`, @"teSt";set @select=2,@t5=1.23456;select @`select`,@not_used;set @test_int=10,@test_double=1e-10,@test_string="abcdeghi",@test_string2="abcdefghij",@select=NULL;--replace_result e-0 e- e+0 e+select @test_int,@test_double,@test_string,@test_string2,@select;set @test_int="hello",@test_double="hello",@test_string="hello",@test_string2="hello";select @test_int,@test_double,@test_string,@test_string2;set @test_int="hellohello",@test_double="hellohello",@test_string="hellohello",@test_string2="hellohello";select @test_int,@test_double,@test_string,@test_string2;set @test_int=null,@test_double=null,@test_string=null,@test_string2=null;select @test_int,@test_double,@test_string,@test_string2;select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;select @t5;## Test problem with WHERE and variables#CREATE TABLE t1 (c_id INT(4) NOT NULL, c_name CHAR(20), c_country CHAR(3), PRIMARY KEY(c_id));INSERT INTO t1 VALUES (1,'Bozo','USA'),(2,'Ronald','USA'),(3,'Kinko','IRE'),(4,'Mr. Floppy','GB');SELECT @min_cid:=min(c_id), @max_cid:=max(c_id) from t1;SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid;SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid OR c_id=666;ALTER TABLE t1 DROP PRIMARY KEY;select * from t1 where c_id=@min_cid OR c_id=@max_cid;drop table t1;## Test system variables#set max_join_size=100;show variables like 'max_join_size';--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERRORshow global variables like 'max_join_size';set GLOBAL max_join_size=2000;show global variables like 'max_join_size';set max_join_size=DEFAULT;--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERRORshow variables like 'max_join_size';set GLOBAL max_join_size=DEFAULT;--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERRORshow global variables like 'max_join_size';set @@max_join_size=1000, @@global.max_join_size=2000;select @@local.max_join_size, @@global.max_join_size;select @@identity,  length(@@version)>0;select @@VERSION=version();select last_insert_id(345);explain extended select last_insert_id(345);select @@IDENTITY,last_insert_id(), @@identity;explain extended select @@IDENTITY,last_insert_id(), @@identity;set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";set global concurrent_insert=2;show variables like 'concurrent_insert';set global concurrent_insert=1;show variables like 'concurrent_insert';set global concurrent_insert=0;show variables like 'concurrent_insert';set global concurrent_insert=DEFAULT;select @@concurrent_insert;set global timed_mutexes=ON;show variables like 'timed_mutexes';set global timed_mutexes=0;show variables like 'timed_mutexes';set storage_engine=MYISAM, storage_engine="HEAP", global storage_engine="MERGE";show local variables like 'storage_engine';show global variables like 'storage_engine';set GLOBAL query_cache_size=100000;set GLOBAL myisam_max_sort_file_size=2000000;show global variables like 'myisam_max_sort_file_size';set GLOBAL myisam_max_sort_file_size=default;--replace_result 2147483647 FILE_SIZE 9223372036854775807 FILE_SIZEshow variables like 'myisam_max_sort_file_size';set global net_retry_count=10, session net_retry_count=10;set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300;set session net_buffer_length=2048, net_write_timeout=500, net_read_timeout=600;show global variables like 'net_%';show session variables like 'net_%';set session net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000;show global variables like 'net_%';show session variables like 'net_%';set net_buffer_length=1;show variables like 'net_buffer_length';set net_buffer_length=2000000000;show variables like 'net_buffer_length';set character set cp1251_koi8;show variables like "character_set_client";select @@timestamp>0;set @@rand_seed1=10000000,@@rand_seed2=1000000;select ROUND(RAND(),5);show variables like '%alloc%';set @@range_alloc_block_size=1024*16;set @@query_alloc_block_size=1024*17+2;set @@query_prealloc_size=1024*18;set @@transaction_alloc_block_size=1024*20-1;set @@transaction_prealloc_size=1024*21-1;select @@query_alloc_block_size;show variables like '%alloc%';set @@range_alloc_block_size=default;set @@query_alloc_block_size=default, @@query_prealloc_size=default;set transaction_alloc_block_size=default, @@transaction_prealloc_size=default;show variables like '%alloc%';## Bug #10904 Illegal mix of collations between# a system variable and a constant#SELECT @@version LIKE 'non-existent';SELECT @@version_compile_os LIKE 'non-existent';# The following should give errors--error 1231set big_tables=OFFF;--error 1231set big_tables="OFFF";--error 1193set unknown_variable=1;--error 1232set max_join_size="hello";--error 1286set storage_engine=UNKNOWN_TABLE_TYPE;--error 1231set storage_engine=MERGE, big_tables=2;show local variables like 'storage_engine';--error 1229set SESSION query_cache_size=10000;--error 1230set GLOBAL storage_engine=DEFAULT;--error 1115set character_set_client=UNKNOWN_CHARACTER_SET;--error 1273set collation_connection=UNKNOWN_COLLATION;--error 1231set character_set_client=NULL;--error 1231set collation_connection=NULL;--error 1228set global autocommit=1;--error 1238select @@global.timestamp;--error 1193set @@version='';--error 1229set @@concurrent_insert=1;--error 1228set @@global.sql_auto_is_null=1;--error 1238select @@global.sql_auto_is_null;--error 1229set myisam_max_sort_file_size=100;--error 1231set @@SQL_WARNINGS=NULL;# Test setting all variablesset autocommit=1;set big_tables=1;select @@autocommit, @@big_tables;set global binlog_cache_size=100;set bulk_insert_buffer_size=100;set character set cp1251_koi8;set character set default;set @@global.concurrent_insert=1;set global connect_timeout=100;select @@delay_key_write;set global delay_key_write="OFF";select @@delay_key_write;set global delay_key_write=ALL;select @@delay_key_write;set global delay_key_write=1;select @@delay_key_write;set global delayed_insert_limit=100;set global delayed_insert_timeout=100;set global delayed_queue_size=100;set global flush=1;set global flush_time=100;set insert_id=1;set interactive_timeout=100;set join_buffer_size=100;set last_insert_id=1;set global local_infile=1;set long_query_time=100;set low_priority_updates=1;set max_allowed_packet=100;set global max_binlog_cache_size=100;set global max_binlog_size=100;set global max_connect_errors=100;set global max_connections=100;set global max_delayed_threads=100;set max_heap_table_size=100;set max_join_size=100;set max_sort_length=100;set max_tmp_tables=100;set global max_user_connections=100;select @@max_user_connections;set global max_write_lock_count=100;set myisam_sort_buffer_size=100;set net_buffer_length=100;set net_read_timeout=100;set net_write_timeout=100;set global query_cache_limit=100;set global query_cache_size=100;set global query_cache_type=demand;set read_buffer_size=100;set read_rnd_buffer_size=100;set global rpl_recovery_rank=100;set global server_id=100;set global slow_launch_time=100;set sort_buffer_size=100;set @@max_sp_recursion_depth=10;select @@max_sp_recursion_depth;set @@max_sp_recursion_depth=0;select @@max_sp_recursion_depth;set sql_auto_is_null=1;select @@sql_auto_is_null;set @@sql_auto_is_null=0;select @@sql_auto_is_null;set sql_big_selects=1;set sql_big_tables=1;set sql_buffer_result=1;set sql_log_bin=1;set sql_log_off=1;set sql_log_update=1;set sql_low_priority_updates=1;set sql_max_join_size=200;select @@sql_max_join_size,@@max_join_size;set sql_quote_show_create=1;set sql_safe_updates=1;set sql_select_limit=1;set sql_warnings=1;set global table_open_cache=100;set storage_engine=myisam;set global thread_cache_size=100;set timestamp=1, timestamp=default;set tmp_table_size=100;set tx_isolation="READ-COMMITTED";set wait_timeout=100;set log_warnings=1;## key buffer#create table t1 (a int not null auto_increment, primary key(a));create table t2 (a int not null auto_increment, primary key(a));insert into t1 values(null),(null),(null);insert into t2 values(null),(null),(null);set global key_buffer_size=100000;select @@key_buffer_size;select * from t1 where a=2;select * from t2 where a=3;check table t1,t2;select max(a) +1, max(a) +2 into @xx,@yy from t1;drop table t1,t2;## error conditions#--error 1193select @@xxxxxxxxxx;select 1;--error 1238select @@session.key_buffer_size;--error 1229set ft_boolean_syntax = @@init_connect;--error 1231set global ft_boolean_syntax = @@init_connect;--error 1229set init_connect = NULL;set global init_connect = NULL;--error 1229set ft_boolean_syntax = @@init_connect;--error 1231set global ft_boolean_syntax = @@init_connect;# Bug#3754 SET GLOBAL myisam_max_sort_file_size doesn't work as# expected: check that there is no overflow when 64-bit unsigned# variables are setset global myisam_max_sort_file_size=4294967296;--replace_result 4294967296 MAX_FILE_SIZE 2146435072 MAX_FILE_SIZEshow global variables like 'myisam_max_sort_file_size';set global myisam_max_sort_file_size=default;## swap#select @@global.max_user_connections,@@local.max_join_size;set @svc=@@global.max_user_connections, @svj=@@local.max_join_size;select @@global.max_user_connections,@@local.max_join_size;set @@global.max_user_connections=111,@@local.max_join_size=222;select @@global.max_user_connections,@@local.max_join_size;set @@global.max_user_connections=@@local.max_join_size,@@local.max_join_size=@@global.max_user_connections;select @@global.max_user_connections,@@local.max_join_size;set @@global.max_user_connections=@svc, @@local.max_join_size=@svj;select @@global.max_user_connections,@@local.max_join_size;set @a=1, @b=2;set @a=@b, @b=@a;select @a, @b;## Bug#2586:Disallow global/session/local as structured var. instance names#--error 1064set @@global.global.key_buffer_size= 1;--error 1064set GLOBAL global.key_buffer_size= 1;--error 1064SELECT @@global.global.key_buffer_size;--error 1064SELECT @@global.session.key_buffer_size;--error 1064SELECT @@global.local.key_buffer_size;# BUG#5135: cannot turn on log_warnings with SET in 4.1 (and 4.0)set @tstlw = @@log_warnings;show global variables like 'log_warnings';set global log_warnings = 0;show global variables like 'log_warnings';set global log_warnings = 42;show global variables like 'log_warnings';set global log_warnings = @tstlw;show global variables like 'log_warnings';## BUG#4788 show create table provides incorrect statement## What default width have numeric types?create table t1 (  c1 tinyint,  c2 smallint,  c3 mediumint,  c4 int,  c5 bigint);show create table t1;drop table t1;## What types and widths have variables?set @arg00= 8, @arg01= 8.8, @arg02= 'a string', @arg03= 0.2e0;create table t1 as select @arg00 as c1, @arg01 as c2, @arg02 as c3, @arg03 as c4;show create table t1;drop table t1;## Bug #6993: myisam_data_pointer_size# Wrong bug report, data pointer size must be restricted to 7,# setting to 8 will not work on all computers, myisamchk and# the server may see a wrong value, such as 0 or negative number# if 8 bytes is set.#SET GLOBAL MYISAM_DATA_POINTER_SIZE= 7;SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE';## Bug #6958: negative arguments to integer options wrap around#SET GLOBAL table_open_cache=-1;SHOW VARIABLES LIKE 'table_open_cache';SET GLOBAL table_open_cache=DEFAULT;## Bugs12363: character_set_results is nullable,# but value_ptr returns string "NULL"#set character_set_results=NULL;select ifnull(@@character_set_results,"really null");set names latin1;## Bug #9613: @@have_innodb#--replace_column 1 #select @@have_innodb;## Bug #13334: query_prealloc_size default less than minimum#set @test = @@query_prealloc_size;set @@query_prealloc_size = @test;select @@query_prealloc_size = @test;# End of 4.1 tests## Bug#6282 Packet error with SELECT INTO# create table t1 (a int);select a into @x from t1;show warnings;drop table t1;## Bug #10339: read only variables.#--error 1238set @@warning_count=1;--error 1238set @@global.error_count=1;## Bug #10351: Setting ulong variable to > MAX_ULONG fails on 32-bit platform#set @@max_heap_table_size= 4294967296;select @@max_heap_table_size > 0;set global max_heap_table_size= 4294967296;select @@max_heap_table_size > 0;set @@max_heap_table_size= 4294967296;select @@max_heap_table_size > 0;## Bug #11775 Variable character_set_system does not exist (sometimes)#select @@character_set_system;--error 1238set global character_set_system = latin1;--error 1238set @@global.version_compile_os='234';# End of 5.0 tests