variables.test 11.8 KB
Newer Older
1 2 3
#
# test variables
#
4 5 6 7
--disable_warnings
drop table if exists t1,t2;
--enable_warnings

8 9 10 11 12 13 14 15 16 17 18 19 20 21
# 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;
22
set @test_int=10,@test_double=1e-10,@test_string="abcdeghi",@test_string2="abcdefghij",@select=NULL;
unknown's avatar
unknown committed
23
--replace_result e-0 e- e+0 e+
24 25 26 27 28 29 30 31
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;
32
explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
33 34
select @t5;

unknown's avatar
unknown committed
35 36 37 38 39 40 41 42 43 44 45 46
#
# 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;
47 48 49 50 51

#
# Test system variables
#

unknown's avatar
unknown committed
52 53
set max_join_size=100;
show variables like 'max_join_size';
54
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
unknown's avatar
unknown committed
55 56 57 58
show global variables like 'max_join_size';
set GLOBAL max_join_size=2000;
show global variables like 'max_join_size';
set max_join_size=DEFAULT;
59
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
unknown's avatar
unknown committed
60 61
show variables like 'max_join_size';
set GLOBAL max_join_size=DEFAULT;
62
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
unknown's avatar
unknown committed
63 64 65 66
show 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;
67 68
select @@VERSION=version();
select last_insert_id(345);
69
explain extended select last_insert_id(345);
unknown's avatar
unknown committed
70
select @@IDENTITY,last_insert_id(), @@identity;
71
explain extended select @@IDENTITY,last_insert_id(), @@identity;
unknown's avatar
unknown committed
72 73 74 75 76 77 78 79 80 81 82 83 84 85

set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";

set global concurrent_insert=ON;
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=OFF;
show variables like 'concurrent_insert';
set global concurrent_insert=DEFAULT;
show variables like 'concurrent_insert';

unknown's avatar
unknown committed
86 87 88 89 90 91
set global timed_mutexes=1;
show variables like 'timed_mutexes';
set global timed_mutexes=0;
show variables like 'timed_mutexes';


unknown's avatar
unknown committed
92
set storage_engine=MYISAM, storage_engine="HEAP", global storage_engine="MERGE";
unknown's avatar
unknown committed
93 94
show local variables like 'storage_engine';
show global variables like 'storage_engine';
unknown's avatar
unknown committed
95 96
set GLOBAL query_cache_size=100000;

97
set GLOBAL myisam_max_sort_file_size=2000000;
unknown's avatar
unknown committed
98
show global variables like 'myisam_max_sort_file_size';
99
set GLOBAL myisam_max_sort_file_size=default;
100
--replace_result 2147483647 FILE_SIZE 9223372036854775807 FILE_SIZE
unknown's avatar
unknown committed
101 102
show variables like 'myisam_max_sort_file_size';

103
set global net_retry_count=10, session net_retry_count=10;
unknown's avatar
unknown committed
104 105 106 107 108 109 110 111 112 113 114 115 116
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;
117
show variables like "character_set_client";
unknown's avatar
unknown committed
118 119
select @@timestamp>0;

120 121 122
set @@rand_seed1=10000000,@@rand_seed2=1000000;
select ROUND(RAND(),5);

123 124 125 126 127 128 129 130 131 132 133 134
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%';
135

unknown's avatar
unknown committed
136 137 138 139 140 141
# The following should give errors

--error 1231
set big_tables=OFFF;
--error 1231
set big_tables="OFFF";
142
--error 1193
unknown's avatar
unknown committed
143 144 145
set unknown_variable=1;
--error 1232
set max_join_size="hello";
unknown's avatar
unknown committed
146
--error 1286
unknown's avatar
unknown committed
147
set storage_engine=UNKNOWN_TABLE_TYPE;
unknown's avatar
unknown committed
148
--error 1231
unknown's avatar
unknown committed
149
set storage_engine=MERGE, big_tables=2;
unknown's avatar
unknown committed
150
show local variables like 'storage_engine';
unknown's avatar
unknown committed
151 152 153
--error 1229
set SESSION query_cache_size=10000;
--error 1230
unknown's avatar
unknown committed
154
set GLOBAL storage_engine=DEFAULT;
unknown's avatar
unknown committed
155
--error 1115
156
set character_set_client=UNKNOWN_CHARACTER_SET;
unknown's avatar
unknown committed
157
--error 1273
158
set collation_connection=UNKNOWN_COLLATION;
159 160 161 162
--error 1231
set character_set_client=NULL;
--error 1231
set collation_connection=NULL;
unknown's avatar
unknown committed
163 164
--error 1228
set global autocommit=1;
165
--error 1238
unknown's avatar
unknown committed
166 167 168 169 170 171 172
select @@global.timestamp;
--error 1193
set @@version='';
--error 1229
set @@concurrent_insert=1;
--error 1228
set @@global.sql_auto_is_null=1;
173
--error 1238
unknown's avatar
unknown committed
174
select @@global.sql_auto_is_null;
175 176 177 178
--error 1229
set myisam_max_sort_file_size=100;
--error 1229
set myisam_max_extra_sort_file_size=100;
179 180
--error 1231
set @@SQL_WARNINGS=NULL;
unknown's avatar
unknown committed
181 182 183 184 185 186 187 188

# Test setting all variables

set autocommit=1;
set big_tables=1;
select @@autocommit, @@big_tables;
set global binlog_cache_size=100;
set bulk_insert_buffer_size=100;
unknown's avatar
unknown committed
189 190
set character set cp1251_koi8;
set character set default;
unknown's avatar
unknown committed
191 192
set @@global.concurrent_insert=1;
set global connect_timeout=100;
193 194 195 196 197
select @@delay_key_write;
set global delay_key_write="OFF";
select @@delay_key_write;
set global delay_key_write=ALL;
select @@delay_key_write;
unknown's avatar
unknown committed
198
set global delay_key_write=1;
199
select @@delay_key_write;
unknown's avatar
unknown committed
200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224
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;
225
set global myisam_max_extra_sort_file_size=100;
unknown's avatar
unknown committed
226
select @@myisam_max_extra_sort_file_size;
227
set global myisam_max_sort_file_size=100;
unknown's avatar
unknown committed
228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258
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 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_cache=100;
unknown's avatar
unknown committed
259
set storage_engine=myisam;
unknown's avatar
unknown committed
260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279
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;
280
select max(a) +1, max(a) +2 into @xx,@yy from t1;
unknown's avatar
unknown committed
281 282
drop table t1,t2;

283 284 285 286 287 288 289
#
# error conditions
#

--error 1193
select @@xxxxxxxxxx;
select 1;
290

291
--error 1238
unknown's avatar
unknown committed
292 293
select @@session.key_buffer_size;

294 295
--error 1229
set ft_boolean_syntax = @@init_connect;
unknown's avatar
unknown committed
296 297 298 299 300 301 302 303 304
--error 1231
set global ft_boolean_syntax = @@init_connect;
--error 1229
set init_connect = NULL;
set global init_connect = NULL;
--error 1229
set ft_boolean_syntax = @@init_connect;
--error 1231
set global ft_boolean_syntax = @@init_connect;
305

unknown's avatar
unknown committed
306 307 308 309 310
# 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 set

set global myisam_max_sort_file_size=4294967296;
311
--replace_result 4294967296 MAX_FILE_SIZE 2146435072 MAX_FILE_SIZE
unknown's avatar
unknown committed
312 313 314
show global variables like 'myisam_max_sort_file_size';
set global myisam_max_sort_file_size=default;

315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330
#
# 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;

331 332 333
#
# Bug#2586:Disallow global/session/local as structured var. instance names
#
334
--error 1064
335
set @@global.global.key_buffer_size= 1;
336
--error 1064
337
set GLOBAL global.key_buffer_size= 1;
338
--error 1064
339
SELECT @@global.global.key_buffer_size;
340
--error 1064
341
SELECT @@global.session.key_buffer_size;
342
--error 1064
343
SELECT @@global.local.key_buffer_size;
344 345 346 347 348 349 350 351 352 353

# 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';
unknown's avatar
unknown committed
354

355 356 357 358 359 360 361 362 363 364 365 366 367 368
#
# 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?
unknown's avatar
unknown committed
369 370
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;
371 372 373
show create table t1;
drop table t1;

374 375 376 377 378 379 380

#
# Bug #6993: myisam_data_pointer_size
#

SET GLOBAL MYISAM_DATA_POINTER_SIZE= 8;
SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE';
unknown's avatar
unknown committed
381 382 383 384 385 386 387 388

#
# Bug #6958: negative arguments to integer options wrap around
#

SET GLOBAL table_cache=-1;
SHOW VARIABLES LIKE 'table_cache';
SET GLOBAL table_cache=DEFAULT;
389 390 391 392 393 394 395 396 397

#
# Bug#6282 Packet error with SELECT INTO
# 
create table t1 (a int);
select a into @x from t1;
show warnings;
drop table t1;

398 399 400 401 402 403 404 405
#
# Bug #10339: read only variables.
#

--error 1238
set @@warning_count=1;
--error 1238
set @@global.error_count=1;