~drizzle-trunk/drizzle/development

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
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
225
226
227
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
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
#
# test variables
#
--disable_warnings
drop table if exists t1,t2;
--enable_warnings

#
# Bug #19263: variables.test doesn't clean up after itself (I/II -- save)
#
set @my_max_heap_table_size       =@@global.max_heap_table_size;
set @my_max_join_size             =@@global.max_join_size;
set @my_mysql_protocol_buffer_length =@@global.mysql_protocol_buffer_length;
set @my_server_id                 =@@global.server_id;
set @my_storage_engine            =@@global.storage_engine;
set @my_myisam_sort_buffer_size	  =@@global.myisam_sort_buffer_size;
set @my_tx_isolation	  =@@global.tx_isolation;

# 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;
# Expected result "1e-10", windows returns "1e-010"
--replace_result 1e-010 1e-10
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 NOT NULL, c_name VARCHAR(250), c_country VARCHAR(250), 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 GLOBAL max_join_size=10;
set max_join_size=100;
show variables like 'max_join_size';
select * from data_dictionary.session_variables where variable_name like 'max_join_size';
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
show global variables like 'max_join_size';
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
select * from data_dictionary.global_variables where variable_name like 'max_join_size';
set GLOBAL max_join_size=2000;
show global variables like 'max_join_size';
select * from data_dictionary.global_variables where variable_name like 'max_join_size';
set max_join_size=DEFAULT;
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
show variables like 'max_join_size';
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
select * from data_dictionary.session_variables where variable_name like 'max_join_size';
set GLOBAL max_join_size=DEFAULT;
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
show global variables like 'max_join_size';
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
select * from data_dictionary.global_variables where variable_name 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 global timed_mutexes=ON;
show variables like 'timed_mutexes';
select * from data_dictionary.session_variables where variable_name like 'timed_mutexes';
set global timed_mutexes=0;
show variables like 'timed_mutexes';
select * from data_dictionary.session_variables where variable_name like 'timed_mutexes';

set storage_engine=MYISAM, storage_engine="MEMORY";
show local variables like 'storage_engine';
select * from data_dictionary.session_variables where variable_name like 'storage_engine';
show global variables like 'storage_engine';
select * from data_dictionary.global_variables where variable_name like 'storage_engine';

set GLOBAL myisam_max_sort_file_size=2000000;
show global variables like 'myisam_max_sort_file_size';
select * from data_dictionary.global_variables where variable_name like 'myisam_max_sort_file_size';
set GLOBAL myisam_max_sort_file_size=default;
--replace_result 9223372036853727232 FILE_SIZE 2146435072 FILE_SIZE
show global variables like 'myisam_max_sort_file_size';
--replace_result 9223372036853727232 FILE_SIZE 2146435072 FILE_SIZE
select * from data_dictionary.global_variables where variable_name like 'myisam_max_sort_file_size';

set global mysql_protocol_buffer_length=1024;
show global variables like 'mysql_protocol_buffer_%';
select * from data_dictionary.global_variables where variable_name like 'mysql_protocol_buffer_%';
show global variables like 'mysql_protocol_buffer_%';
select * from data_dictionary.global_variables where variable_name like 'mysql_protocol_buffer_%';
set global mysql_protocol_buffer_length=1;
show variables like 'mysql_protocol_buffer_length';
#warning 1292
set global mysql_protocol_buffer_length=2000000000;
show variables like 'mysql_protocol_buffer_length';

show variables like '%alloc%';
select * from data_dictionary.session_variables where variable_name like '%alloc%';
set @@range_alloc_block_size=1024*16;
set @@query_alloc_block_size=1024*17+2;
set @@query_prealloc_size=1024*18;
select @@query_alloc_block_size;
show variables like '%alloc%';
select * from data_dictionary.session_variables where variable_name like '%alloc%';
set @@range_alloc_block_size=default;
set @@query_alloc_block_size=default, @@query_prealloc_size=default;
show variables like '%alloc%';
select * from data_dictionary.session_variables where variable_name 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 ER_UNKNOWN_SYSTEM_VARIABLE
set unknown_variable=1;
--error ER_WRONG_TYPE_FOR_VAR
set max_join_size="hello";
--error ER_UNKNOWN_STORAGE_ENGINE
set storage_engine=UNKNOWN_TABLE_TYPE;
--error ER_NO_DEFAULT
set GLOBAL storage_engine=DEFAULT;
--error ER_LOCAL_VARIABLE
set global autocommit=1;
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
select @@global.timestamp;
--error ER_INCORRECT_GLOBAL_LOCAL_VAR 
set @@version='';
--error ER_GLOBAL_VARIABLE
set myisam_max_sort_file_size=100;
--error ER_WRONG_VALUE_FOR_VAR
set @@SQL_WARNINGS=NULL;

# Test setting all variables

set autocommit=1;
select @@autocommit;
set bulk_insert_buffer_size=100;
set join_buffer_size=100;
set last_insert_id=1;
set max_allowed_packet=100;
set max_heap_table_size=100;
set max_join_size=100;
set max_sort_length=100;
set global max_write_lock_count=100;
set global myisam_sort_buffer_size=100;
set global mysql_protocol_buffer_length=100;
set read_buffer_size=100;
set read_rnd_buffer_size=100;
set global server_id=100;
set sort_buffer_size=100;
set sql_big_selects=1;
set sql_buffer_result=1;
set sql_select_limit=1;
# reset it, so later tests don't get confused
set sql_select_limit=default;
set sql_warnings=1;
set global table_open_cache=100;
set storage_engine=myisam;
set timestamp=1, timestamp=default;
set tmp_table_size=100;
set tx_isolation="READ-COMMITTED";

# Bug#3754 SET GLOBAL myisam_max_sort_file_size doesn't work as
# expected: check that there is no overflow when 64-bit
# variables are set

set global myisam_max_sort_file_size=4294967296;
--replace_result 4294967296 MAX_FILE_SIZE 2146435072 MAX_FILE_SIZE
show global variables like 'myisam_max_sort_file_size';
--replace_result 4294967296 MAX_FILE_SIZE 2146435072 MAX_FILE_SIZE
select * from data_dictionary.global_variables where variable_name like 'myisam_max_sort_file_size';
set global myisam_max_sort_file_size=default;

## Bug #311084
## swap
##
#select @@global.myisam_max_sort_file_size,@@local.max_join_size;
#set @svc=@@global.myisam_max_sort_file_size, @svj=@@local.max_join_size;
#select @@global.myisam_max_sort_file_size,@@local.max_join_size;
#set @@global.myisam_max_sort_file_size=111,@@local.max_join_size=222;
#select @@global.myisam_max_sort_file_size,@@local.max_join_size;
#set @@global.myisam_max_sort_file_size=@@local.max_join_size,@@local.max_join_size=@@global.myisam_max_sort_file_size;
#select @@global.myisam_max_sort_file_size,@@local.max_join_size;
#set @@global.myisam_max_sort_file_size=@svc, @@local.max_join_size=@svj;
#select @@global.myisam_max_sort_file_size,@@local.max_join_size;
#set @a=1, @b=2;
#set @a=@b, @b=@a;
#select @a, @b;

#
# BUG#4788 show create table provides incorrect statement
#
# What default width have numeric types?
create temporary table t1 (
  c1 int,
  c2 int,
  c3 int,
  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 temporary table t1 as select @arg00 as c1, @arg01 as c2, @arg02 as c3, @arg03 as c4;
show create table t1;
drop table t1;

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

SET GLOBAL table_open_cache=-1;
SHOW VARIABLES LIKE 'table_open_cache';
SELECT * FROM DATA_DICTIONARY.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'table_open_cache';
SET GLOBAL table_open_cache=DEFAULT;

#
# Tests for lc_time_names
# Note, when adding new locales, please fix ID accordingly:
# - to test the last ID (currently 108)
# - and the next after the last (currently 109)
#
--echo *** Various tests with LC_TIME_NAMES
--echo *** LC_TIME_NAMES: testing case insensitivity
set @@lc_time_names='ru_ru';
select @@lc_time_names;
--echo *** LC_TIME_NAMES: testing with a user variable
set @lc='JA_JP';
set @@lc_time_names=@lc;
select @@lc_time_names;
--echo *** LC_TIME_NAMES: testing with string expressions
set lc_time_names=concat('de','_','DE');
select @@lc_time_names;
--error ER_UNKNOWN_ERROR
set lc_time_names=concat('de','+','DE');
select @@lc_time_names;
--echo LC_TIME_NAMES: testing with numeric expressions
set @@lc_time_names=1+2;
select @@lc_time_names;
--error ER_WRONG_TYPE_FOR_VAR
set @@lc_time_names=1/0;
select @@lc_time_names;
set lc_time_names=en_US;
--echo LC_TIME_NAMES: testing NULL and a negative number:
--error ER_WRONG_VALUE_FOR_VAR
set lc_time_names=NULL;
--error ER_UNKNOWN_ERROR
set lc_time_names=-1;
select @@lc_time_names;
--echo LC_TIME_NAMES: testing locale with the last ID:
set lc_time_names=108;
select @@lc_time_names;
--echo LC_TIME_NAMES: testing a number beyond the valid ID range:
--error ER_UNKNOWN_ERROR
set lc_time_names=109;
select @@lc_time_names;
--echo LC_TIME_NAMES: testing that 0 is en_US:
set lc_time_names=0;
select @@lc_time_names;

#
# Bug #22648 LC_TIME_NAMES: Setting GLOBAL has no effect
#
select @@global.lc_time_names, @@lc_time_names;
set @@global.lc_time_names=fr_FR;
select @@global.lc_time_names, @@lc_time_names;
--echo New connection
connect (con1,localhost,root,,);
connection con1;
select @@global.lc_time_names, @@lc_time_names;
set @@lc_time_names=ru_RU;
select @@global.lc_time_names, @@lc_time_names;
disconnect con1;
connection default;
--echo Returnung to default connection
select @@global.lc_time_names, @@lc_time_names;
set lc_time_names=default;
select @@global.lc_time_names, @@lc_time_names;
set @@global.lc_time_names=default;
select @@global.lc_time_names, @@lc_time_names;
set @@lc_time_names=default;
select @@global.lc_time_names, @@lc_time_names;


#
# Bug #13334: query_prealloc_size default less than minimum
#
set @test = @@query_prealloc_size;
set @@query_prealloc_size = @test;
select @@query_prealloc_size = @test;

--echo End of 4.1 tests

#
# Bug#6282 Packet error with SELECT INTO
# 

create temporary table t1 (a int);
select a into @x from t1;
show warnings;
drop table t1;

#
# Bug #10339: read only variables.
#

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

#
# Bug #10351: Setting ulong variable to > MAX_ULONG fails on 32-bit platform
#
--disable_query_log
select VARIABLE_VALUE from data_dictionary.GLOBAL_VARIABLES where VARIABLE_NAME like "VERSION_COMPILE_MACHINE" into @arch;
--enable_query_log
# We technically do not care about 32bit hardware. -Brian
#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;
--enable_warnings
#
# Bug #11775 Variable character_set_system does not exist (sometimes)
#
--error ER_UNKNOWN_SYSTEM_VARIABLE
select @@character_set_system;
--error ER_UNKNOWN_SYSTEM_VARIABLE
set global character_set_system = utf8;
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
set @@global.version_compile_os='234';

#
# Check character_set_filesystem variable invalid for Drizzle
#
--error ER_UNKNOWN_SYSTEM_VARIABLE
set @@global.character_set_filesystem=utf8;
--error ER_UNKNOWN_SYSTEM_VARIABLE
set character_set_filesystem=utf8;

#
# Bug #17849: Show sql_big_selects in SHOW VARIABLES
#
set @sql_big_selects = @@sql_big_selects;
set @@sql_big_selects = 1;
show variables like 'sql_big_selects';
# Bug 311025 Segmentation fault when accessing DATA_DICTIONARY
#select * from data_dictionary.session_variables where variable_name like 'sql_big_selects';
set @@sql_big_selects = @sql_big_selects;

#
# Bug #16195: SHOW VARIABLES doesn't report correctly sql_warnings and
# sql_notes values
# 
set @@sql_notes = 0, @@sql_warnings = 0;
show variables like 'sql_notes';
# Bug 311025 Segmentation fault when accessing DATA_DICTIONARY
#select * from data_dictionary.session_variables where variable_name like 'sql_notes';
show variables like 'sql_warnings';
# Bug 311025 Segmentation fault when accessing DATA_DICTIONARY
#select * from data_dictionary.session_variables where variable_name like 'sql_warnings';
set @@sql_notes = 1, @@sql_warnings = 1;
show variables like 'sql_notes';
# Bug 311025 Segmentation fault when accessing DATA_DICTIONARY
#select * from data_dictionary.session_variables where variable_name like 'sql_notes';
show variables like 'sql_warnings';
# Bug 311025 Segmentation fault when accessing DATA_DICTIONARY
#select * from data_dictionary.session_variables where variable_name like 'sql_warnings';

#
# Bug #15684: system variables cannot be SELECTed (e.g. @@version_comment)
#
# Don't actually output, since it depends on the system
--replace_column 1 # 2 # 3 # 4 #
select @@version, @@version_comment, @@version_compile_machine,
       @@version_compile_os;

#
# Bug #1039: make tmpdir and datadir available as @@variables (also included
# basedir)
#
# Don't actually output, since it depends on the system
--replace_column 1 # 2 # 3 #
select @@basedir, @@datadir, @@tmpdir;
--replace_column 2 #
show variables like 'basedir';
--replace_column 2 #
# Bug 311025 Segmentation fault when accessing DATA_DICTIONARY
#select * from data_dictionary.session_variables where variable_name like 'basedir';
--replace_column 2 #
show variables like 'datadir';
--replace_column 2 #
# Bug 311025 Segmentation fault when accessing DATA_DICTIONARY
#select * from data_dictionary.session_variables where variable_name like 'datadir';
--replace_column 2 #
show variables like 'tmpdir';
--replace_column 2 #

#
# Bug#20908: Crash if select @@""
#
--error ER_PARSE_ERROR
select @@"";
--error ER_PARSE_ERROR
select @@&;
--error ER_PARSE_ERROR
select @@@;

#
# Bug#20166 mysql-test-run.pl does not test system privilege tables creation
#
# Don't actually output, since it depends on the system
--replace_column 1 #
select @@hostname;
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
set @@hostname= "anothername";
--replace_column 2 #
show variables like 'hostname';

--echo End of 5.0 tests

# This is at the very after the versioned tests, since it involves doing
# cleanup
#
# Bug #19263: variables.test doesn't clean up after itself (II/II --
# restore)
#
--error ER_UNKNOWN_SYSTEM_VARIABLE
set global flush_time                =@my_flush_time;
set global max_heap_table_size       =@my_max_heap_table_size;
set global max_join_size             =@my_max_join_size;
# No default
set global max_write_lock_count      =default;
set global mysql_protocol_buffer_length= @my_mysql_protocol_buffer_length;
set global server_id                 =@my_server_id;
set global storage_engine            =@my_storage_engine;
--error ER_UNKNOWN_SYSTEM_VARIABLE
set global thread_cache_size         =@my_thread_cache_size;
set global myisam_sort_buffer_size   =@my_myisam_sort_buffer_size;

SHOW GLOBAL VARIABLES LIKE 'max_join_size';
SHOW LOCAL VARIABLES LIKE 'max_join_size';

set GLOBAL bulk_insert_buffer_size=DEFAULT;
set GLOBAL join_buffer_size=DEFAULT;
set GLOBAL max_allowed_packet=DEFAULT;
set GLOBAL max_heap_table_size=DEFAULT;
set GLOBAL max_join_size=DEFAULT;
set GLOBAL max_sort_length=DEFAULT;
set GLOBAL max_write_lock_count=DEFAULT;
set GLOBAL myisam_sort_buffer_size=DEFAULT;
set GLOBAL mysql_protocol_buffer_length=DEFAULT;
set GLOBAL read_buffer_size=DEFAULT;
set GLOBAL read_rnd_buffer_size=DEFAULT;
set GLOBAL server_id=DEFAULT;
set GLOBAL sort_buffer_size=DEFAULT;
set GLOBAL table_open_cache=DEFAULT;
set GLOBAL storage_engine= @my_storage_engine;
set GLOBAL tmp_table_size=DEFAULT;
set GLOBAL tx_isolation= @my_tx_isolation;

set SESSION bulk_insert_buffer_size=DEFAULT;
set SESSION join_buffer_size=DEFAULT;
set SESSION max_allowed_packet=DEFAULT;
set SESSION max_heap_table_size=DEFAULT;
set SESSION max_join_size=DEFAULT;
set SESSION max_sort_length=DEFAULT;
set SESSION read_buffer_size=DEFAULT;
set SESSION read_rnd_buffer_size=DEFAULT;
set SESSION sort_buffer_size=DEFAULT;
set SESSION sql_big_selects=DEFAULT;
set SESSION sql_buffer_result=DEFAULT;
set SESSION sql_select_limit=DEFAULT;
set SESSION sql_warnings=DEFAULT;
set SESSION storage_engine= @my_storage_engine;
set SESSION tmp_table_size=DEFAULT;
set SESSION tx_isolation= @my_tx_isolation;

#
# Bug#28580 Repeatation of status variables
#

--replace_column 2 #
show global variables where variable_name='table_definition_cache' or Variable_name='table_lock_wait_timeout';