1
SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
2
3
drop table if exists tb3;
4
f118 char not null DEFAULT 'a',
5
f119 char binary not null DEFAULT b'101',
6
f120 char ascii not null DEFAULT b'101',
9
f129 binary not null DEFAULT b'101',
10
f130 tinyint not null DEFAULT 99,
11
f131 tinyint unsigned not null DEFAULT 99,
12
f132 tinyint zerofill not null DEFAULT 99,
13
f133 tinyint unsigned zerofill not null DEFAULT 99,
14
f134 smallint not null DEFAULT 999,
15
f135 smallint unsigned not null DEFAULT 999,
16
f136 smallint zerofill not null DEFAULT 999,
17
f137 smallint unsigned zerofill not null DEFAULT 999,
18
f138 mediumint not null DEFAULT 9999,
19
f139 mediumint unsigned not null DEFAULT 9999,
20
f140 mediumint zerofill not null DEFAULT 9999,
21
f141 mediumint unsigned zerofill not null DEFAULT 9999,
22
f142 int not null DEFAULT 99999,
23
f143 int unsigned not null DEFAULT 99999,
24
f144 int zerofill not null DEFAULT 99999,
25
f145 int unsigned zerofill not null DEFAULT 99999,
26
f146 bigint not null DEFAULT 999999,
27
f147 bigint unsigned not null DEFAULT 999999,
28
f148 bigint zerofill not null DEFAULT 999999,
29
f149 bigint unsigned zerofill not null DEFAULT 999999,
30
f150 decimal not null DEFAULT 999.999,
31
f151 decimal unsigned not null DEFAULT 999.17,
32
f152 decimal zerofill not null DEFAULT 999.999,
33
f153 decimal unsigned zerofill,
36
f156 decimal (0) unsigned,
37
f157 decimal (64) unsigned,
38
f158 decimal (0) zerofill,
39
f159 decimal (64) zerofill,
40
f160 decimal (0) unsigned zerofill,
41
f161 decimal (64) unsigned zerofill,
44
f164 decimal (0,0) unsigned,
45
f165 decimal (63,30) unsigned,
46
f166 decimal (0,0) zerofill,
47
f167 decimal (63,30) zerofill,
48
f168 decimal (0,0) unsigned zerofill,
49
f169 decimal (63,30) unsigned zerofill,
51
f171 numeric unsigned,
52
f172 numeric zerofill,
53
f173 numeric unsigned zerofill,
5
f118 char not null DEFAULT 'a',
6
f119 char binary not null DEFAULT b'101',
7
f120 char ascii not null DEFAULT b'101',
10
f129 binary not null DEFAULT b'101',
11
f130 tinyint not null DEFAULT 99,
12
f131 tinyint unsigned not null DEFAULT 99,
13
f132 tinyint zerofill not null DEFAULT 99,
14
f133 tinyint unsigned zerofill not null DEFAULT 99,
15
f134 smallint not null DEFAULT 999,
16
f135 smallint unsigned not null DEFAULT 999,
17
f136 smallint zerofill not null DEFAULT 999,
18
f137 smallint unsigned zerofill not null DEFAULT 999,
19
f138 mediumint not null DEFAULT 9999,
20
f139 mediumint unsigned not null DEFAULT 9999,
21
f140 mediumint zerofill not null DEFAULT 9999,
22
f141 mediumint unsigned zerofill not null DEFAULT 9999,
23
f142 int not null DEFAULT 99999,
24
f143 int unsigned not null DEFAULT 99999,
25
f144 int zerofill not null DEFAULT 99999,
26
f145 int unsigned zerofill not null DEFAULT 99999,
27
f146 bigint not null DEFAULT 999999,
28
f147 bigint unsigned not null DEFAULT 999999,
29
f148 bigint zerofill not null DEFAULT 999999,
30
f149 bigint unsigned zerofill not null DEFAULT 999999,
31
f150 decimal not null DEFAULT 999.999,
32
f151 decimal unsigned not null DEFAULT 999.17,
33
f152 decimal zerofill not null DEFAULT 999.999,
34
f153 decimal unsigned zerofill,
37
f156 decimal (0) unsigned,
38
f157 decimal (64) unsigned,
39
f158 decimal (0) zerofill,
40
f159 decimal (64) zerofill,
41
f160 decimal (0) unsigned zerofill,
42
f161 decimal (64) unsigned zerofill,
45
f164 decimal (0,0) unsigned,
46
f165 decimal (63,30) unsigned,
47
f166 decimal (0,0) zerofill,
48
f167 decimal (63,30) zerofill,
49
f168 decimal (0,0) unsigned zerofill,
50
f169 decimal (63,30) unsigned zerofill,
52
f171 numeric unsigned,
53
f172 numeric zerofill,
54
f173 numeric unsigned zerofill,
57
) engine = <engine_to_be_used>;
58
59
Note 1265 Data truncated for column 'f150' at row 1
59
60
Note 1265 Data truncated for column 'f151' at row 1
60
61
Note 1265 Data truncated for column 'f152' at row 1
61
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/memory_tb3.txt' into table tb3 ;
83
83
grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
84
84
grant LOCK TABLES on db_test.* to test_general;
87
87
i120 char ascii not null DEFAULT b'101',
88
88
i136 smallint zerofill not null DEFAULT 999,
89
89
i144 int zerofill not null DEFAULT 99999,
90
i163 decimal (63,30)) engine=memory;
90
i163 decimal (63,30)) engine=<engine_to_be_used>;
92
92
u120 char ascii not null DEFAULT b'101',
93
93
u136 smallint zerofill not null DEFAULT 999,
94
94
u144 int zerofill not null DEFAULT 99999,
95
u163 decimal (63,30)) engine=memory;
95
u163 decimal (63,30)) engine=<engine_to_be_used>;
97
97
d120 char ascii not null DEFAULT b'101',
98
98
d136 smallint zerofill not null DEFAULT 999,
99
99
d144 int zerofill not null DEFAULT 99999,
100
d163 decimal (63,30)) engine=memory;
100
d163 decimal (63,30)) engine=<engine_to_be_used>;
101
101
Insert into t1_u values ('a',111,99999,999.99);
102
102
Insert into t1_u values ('b',222,99999,999.99);
103
103
Insert into t1_u values ('c',333,99999,999.99);
248
248
IF (new.f120='4') and (new.f136=10) then
249
249
set @test_var2='2nd if', new.f120='d';
251
251
set @test_var2='2nd else', new.f120='D';
254
254
set @test_var='Empty', @test_var2=0;
255
255
Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
256
select f120, f122, f136, @test_var, @test_var2
256
select f120, f122, f136, @test_var, @test_var2
257
257
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
258
258
f120 f122 f136 @test_var @test_var2
259
259
D Test 3.5.8.5-if 00101 one 2nd else
260
260
Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
261
select f120, f122, f136, @test_var, @test_var2
261
select f120, f122, f136, @test_var, @test_var2
262
262
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
263
263
f120 f122 f136 @test_var @test_var2
264
264
D Test 3.5.8.5-if 00101 two 2nd else
265
265
D Test 3.5.8.5-if 00102 two 2nd else
266
266
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
267
select f120, f122, f136, @test_var, @test_var2
267
select f120, f122, f136, @test_var, @test_var2
268
268
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
269
269
f120 f122 f136 @test_var @test_var2
270
270
d Test 3.5.8.5-if 00010 three 2nd if
271
271
D Test 3.5.8.5-if 00101 three 2nd if
272
272
D Test 3.5.8.5-if 00102 three 2nd if
273
273
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
274
select f120, f122, f136, @test_var, @test_var2
274
select f120, f122, f136, @test_var, @test_var2
275
275
from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
276
276
f120 f122 f136 @test_var @test_var2
277
277
d Test 3.5.8.5-if 00010 three 2nd else
335
335
set @test_var='Empty';
336
Insert into tb3 (f120, f122, f136, f144)
336
Insert into tb3 (f120, f122, f136, f144)
337
337
values ('a', 'Test 3.5.8.5-case', 5, 7);
338
select f120, f122, f136, f144, @test_var
338
select f120, f122, f136, f144, @test_var
339
339
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
340
340
f120 f122 f136 f144 @test_var
341
341
A Test 3.5.8.5-case 00125 0000000007 A*seven
342
Insert into tb3 (f120, f122, f136, f144)
342
Insert into tb3 (f120, f122, f136, f144)
343
343
values ('b', 'Test 3.5.8.5-case', 71,16);
344
select f120, f122, f136, f144, @test_var
344
select f120, f122, f136, f144, @test_var
345
345
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
346
346
f120 f122 f136 f144 @test_var
347
347
A Test 3.5.8.5-case 00125 0000000007 B*0000000016
348
348
B Test 3.5.8.5-case 00191 0000000016 B*0000000016
349
Insert into tb3 (f120, f122, f136, f144)
349
Insert into tb3 (f120, f122, f136, f144)
350
350
values ('c', 'Test 3.5.8.5-case', 80,1);
351
select f120, f122, f136, f144, @test_var
351
select f120, f122, f136, f144, @test_var
352
352
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
353
353
f120 f122 f136 f144 @test_var
354
354
A Test 3.5.8.5-case 00125 0000000007 C=one
355
355
B Test 3.5.8.5-case 00191 0000000016 C=one
356
356
C Test 3.5.8.5-case 00200 0000000001 C=one
357
Insert into tb3 (f120, f122, f136)
357
Insert into tb3 (f120, f122, f136)
358
358
values ('d', 'Test 3.5.8.5-case', 152);
360
360
Warning 1265 Data truncated for column 'f120' at row 1
361
select f120, f122, f136, f144, @test_var
361
select f120, f122, f136, f144, @test_var
362
362
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
363
363
f120 f122 f136 f144 @test_var
364
364
1 Test 3.5.8.5-case 00152 0000099999 1*0000099999
365
365
A Test 3.5.8.5-case 00125 0000000007 1*0000099999
366
366
B Test 3.5.8.5-case 00191 0000000016 1*0000099999
367
367
C Test 3.5.8.5-case 00200 0000000001 1*0000099999
368
Insert into tb3 (f120, f122, f136, f144)
368
Insert into tb3 (f120, f122, f136, f144)
369
369
values ('e', 'Test 3.5.8.5-case', 200, 8);
371
371
Warning 1265 Data truncated for column 'f120' at row 1
372
select f120, f122, f136, f144, @test_var
372
select f120, f122, f136, f144, @test_var
373
373
from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
374
374
f120 f122 f136 f144 @test_var
375
375
1 Test 3.5.8.5-case 00152 0000099999 1=eight
400
400
Testcase 3.5.8.5-loop/leave:
401
401
----------------------------
402
402
Create trigger trg4 after insert on tb3 for each row
404
404
set @counter=0, @flag='Initial';
406
406
if new.f136<new.f144 then
407
407
set @counter='Nothing to loop';
410
410
set @counter=@counter+1;
411
411
if new.f136=new.f144+@counter then
412
412
set @counter=concat(@counter, ' loops');
417
417
set @flag='Final';
420
Insert into tb3 (f122, f136, f144)
420
Insert into tb3 (f122, f136, f144)
421
421
values ('Test 3.5.8.5-loop', 2, 8);
422
422
select @counter, @flag;
424
424
Nothing to loop Initial
425
Insert into tb3 (f122, f136, f144)
425
Insert into tb3 (f122, f136, f144)
426
426
values ('Test 3.5.8.5-loop', 11, 8);
427
427
select @counter, @flag;
430
430
Create trigger trg4_2 after update on tb3 for each row
433
433
set @counter=@counter+1;
436
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
436
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
438
438
drop trigger trg4_2;
439
439
drop trigger trg4;
468
468
Testcase 3.5.8.5-while:
469
469
-----------------------
470
470
Create trigger trg7 after insert on tb3 for each row
471
wl_label: WHILE @counter1 < new.f136 DO
472
SET @counter1 = @counter1 + 1;
471
wl_label: WHILE @counter1 < new.f136 DO
472
SET @counter1 = @counter1 + 1;
473
473
IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label;
475
SET @counter2 = @counter2 + 1;
475
SET @counter2 = @counter2 + 1;
476
476
END WHILE wl_label//
477
477
set @counter1= 0, @counter2= 0;
478
Insert into tb3 (f122, f136)
478
Insert into tb3 (f122, f136)
479
479
values ('Test 3.5.8.5-while', 7);
480
480
select @counter1, @counter2;
481
481
@counter1 @counter2
483
483
Create trigger trg7_2 after update on tb3 for each row
485
WHILE @counter1 < new.f136
486
SET @counter1 = @counter1 + 1;
485
WHILE @counter1 < new.f136
486
SET @counter1 = @counter1 + 1;
488
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @counter1 = @counter1 + 1;
488
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @counter1 = @counter1 + 1;
490
490
delete from tb3 where f122='Test 3.5.8.5-while';
491
491
drop trigger trg7;