mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
268 lines
9.9 KiB
Text
268 lines
9.9 KiB
Text
SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
|
|
USE test;
|
|
drop table if exists tb3;
|
|
create table tb3 (
|
|
f118 char not null DEFAULT 'a',
|
|
f119 char binary not null DEFAULT b'101',
|
|
f120 char ascii not null DEFAULT b'101',
|
|
f121 char(50),
|
|
f122 char(50),
|
|
f129 binary not null DEFAULT b'101',
|
|
f130 tinyint not null DEFAULT 99,
|
|
f131 tinyint unsigned not null DEFAULT 99,
|
|
f132 tinyint zerofill not null DEFAULT 99,
|
|
f133 tinyint unsigned zerofill not null DEFAULT 99,
|
|
f134 smallint not null DEFAULT 999,
|
|
f135 smallint unsigned not null DEFAULT 999,
|
|
f136 smallint zerofill not null DEFAULT 999,
|
|
f137 smallint unsigned zerofill not null DEFAULT 999,
|
|
f138 mediumint not null DEFAULT 9999,
|
|
f139 mediumint unsigned not null DEFAULT 9999,
|
|
f140 mediumint zerofill not null DEFAULT 9999,
|
|
f141 mediumint unsigned zerofill not null DEFAULT 9999,
|
|
f142 int not null DEFAULT 99999,
|
|
f143 int unsigned not null DEFAULT 99999,
|
|
f144 int zerofill not null DEFAULT 99999,
|
|
f145 int unsigned zerofill not null DEFAULT 99999,
|
|
f146 bigint not null DEFAULT 999999,
|
|
f147 bigint unsigned not null DEFAULT 999999,
|
|
f148 bigint zerofill not null DEFAULT 999999,
|
|
f149 bigint unsigned zerofill not null DEFAULT 999999,
|
|
f150 decimal not null DEFAULT 999.999,
|
|
f151 decimal unsigned not null DEFAULT 999.17,
|
|
f152 decimal zerofill not null DEFAULT 999.999,
|
|
f153 decimal unsigned zerofill,
|
|
f154 decimal (0),
|
|
f155 decimal (64),
|
|
f156 decimal (0) unsigned,
|
|
f157 decimal (64) unsigned,
|
|
f158 decimal (0) zerofill,
|
|
f159 decimal (64) zerofill,
|
|
f160 decimal (0) unsigned zerofill,
|
|
f161 decimal (64) unsigned zerofill,
|
|
f162 decimal (0,0),
|
|
f163 decimal (63,30),
|
|
f164 decimal (0,0) unsigned,
|
|
f165 decimal (63,30) unsigned,
|
|
f166 decimal (0,0) zerofill,
|
|
f167 decimal (63,30) zerofill,
|
|
f168 decimal (0,0) unsigned zerofill,
|
|
f169 decimal (63,30) unsigned zerofill,
|
|
f170 numeric,
|
|
f171 numeric unsigned,
|
|
f172 numeric zerofill,
|
|
f173 numeric unsigned zerofill,
|
|
f174 numeric (0),
|
|
f175 numeric (64)
|
|
) engine = <engine_to_be_used>;
|
|
Warnings:
|
|
Note 1265 Data truncated for column 'f150' at row 0
|
|
Note 1265 Data truncated for column 'f151' at row 0
|
|
Note 1265 Data truncated for column 'f152' at row 0
|
|
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/memory_tb3.txt'
|
|
into table tb3;
|
|
|
|
Testcase 3.5.9.1/2:
|
|
-------------------
|
|
Create trigger trg1 BEFORE UPDATE on tb3 for each row
|
|
set new.f142 = 94087, @counter=@counter+1;
|
|
TotalRows
|
|
10
|
|
Affected
|
|
9
|
|
NotAffected
|
|
1
|
|
NewValuew
|
|
0
|
|
set @counter=0;
|
|
Update tb3 Set f142='1' where f130<100;
|
|
select count(*) as ExpectedChanged, @counter as TrigCounter
|
|
from tb3 where f142=94087;
|
|
ExpectedChanged TrigCounter
|
|
9 9
|
|
select count(*) as ExpectedNotChange from tb3
|
|
where f130<100 and f142<>94087;
|
|
ExpectedNotChange
|
|
0
|
|
select count(*) as NonExpectedChanged from tb3
|
|
where f130>=130 and f142=94087;
|
|
NonExpectedChanged
|
|
0
|
|
drop trigger trg1;
|
|
|
|
Testcase 3.5.9.3:
|
|
-----------------
|
|
Create trigger trg2_a before update on tb3 for each row
|
|
set @tr_var_b4_118=old.f118, @tr_var_b4_121=old.f121,
|
|
@tr_var_b4_122=old.f122, @tr_var_b4_136=old.f136,
|
|
@tr_var_b4_163=old.f163;
|
|
Create trigger trg2_b after update on tb3 for each row
|
|
set @tr_var_af_118=old.f118, @tr_var_af_121=old.f121,
|
|
@tr_var_af_122=old.f122, @tr_var_af_136=old.f136,
|
|
@tr_var_af_163=old.f163;
|
|
Create trigger trg2_c before delete on tb3 for each row
|
|
set @tr_var_b4_118=old.f118, @tr_var_b4_121=old.f121,
|
|
@tr_var_b4_122=old.f122, @tr_var_b4_136=old.f136,
|
|
@tr_var_b4_163=old.f163;
|
|
Create trigger trg2_d after delete on tb3 for each row
|
|
set @tr_var_af_118=old.f118, @tr_var_af_121=old.f121,
|
|
@tr_var_af_122=old.f122, @tr_var_af_136=old.f136,
|
|
@tr_var_af_163=old.f163;
|
|
@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_163
|
|
0 0 0 0 0
|
|
@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_163
|
|
0 0 0 0 0
|
|
Insert into tb3 (f122, f136, f163)
|
|
values ('Test 3.5.9.3', 7, 123.17);
|
|
Update tb3 Set f136=8 where f122='Test 3.5.9.3';
|
|
select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3' order by f136;
|
|
f118 f121 f122 f136 f163
|
|
a NULL Test 3.5.9.3 00008 123.170000000000000000000000000000
|
|
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
|
|
@tr_var_b4_136, @tr_var_b4_163;
|
|
@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_163
|
|
a NULL Test 3.5.9.3 7 123.170000000000000000000000000000
|
|
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
|
|
@tr_var_af_136, @tr_var_af_163;
|
|
@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_163
|
|
a NULL Test 3.5.9.3 7 123.170000000000000000000000000000
|
|
@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_163
|
|
0 0 0 0 0
|
|
@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_163
|
|
0 0 0 0 0
|
|
delete from tb3 where f122='Test 3.5.9.3';
|
|
select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3' order by f136;
|
|
f118 f121 f122 f136 f163
|
|
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
|
|
@tr_var_b4_136, @tr_var_b4_163;
|
|
@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_163
|
|
a NULL Test 3.5.9.3 8 123.170000000000000000000000000000
|
|
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
|
|
@tr_var_af_136, @tr_var_af_163;
|
|
@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_163
|
|
a NULL Test 3.5.9.3 8 123.170000000000000000000000000000
|
|
drop trigger trg2_a;
|
|
drop trigger trg2_b;
|
|
drop trigger trg2_c;
|
|
drop trigger trg2_d;
|
|
|
|
Testcase 3.5.9.4:
|
|
-----------------
|
|
Create trigger trg3_a before insert on tb3 for each row
|
|
set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121,
|
|
@tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136,
|
|
@tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163;
|
|
Create trigger trg3_b after insert on tb3 for each row
|
|
set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121,
|
|
@tr_var_af_122=new.f122, @tr_var_af_136=new.f136,
|
|
@tr_var_af_151=new.f151, @tr_var_af_163=new.f163;
|
|
Create trigger trg3_c before update on tb3 for each row
|
|
set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121,
|
|
@tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136,
|
|
@tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163;
|
|
Create trigger trg3_d after update on tb3 for each row
|
|
set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121,
|
|
@tr_var_af_122=new.f122, @tr_var_af_136=new.f136,
|
|
@tr_var_af_151=new.f151, @tr_var_af_163=new.f163;
|
|
@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163
|
|
0 0 0 0 0 0
|
|
@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163
|
|
0 0 0 0 0 0
|
|
Insert into tb3 (f122, f136, f151, f163)
|
|
values ('Test 3.5.9.4', 7, DEFAULT, 995.24);
|
|
select f118, f121, f122, f136, f151, f163 from tb3
|
|
where f122 like 'Test 3.5.9.4%' order by f163;
|
|
f118 f121 f122 f136 f151 f163
|
|
a NULL Test 3.5.9.4 00007 999 995.240000000000000000000000000000
|
|
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
|
|
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
|
|
@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163
|
|
a NULL Test 3.5.9.4 7 999 995.240000000000000000000000000000
|
|
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
|
|
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
|
|
@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163
|
|
a NULL Test 3.5.9.4 7 999 995.240000000000000000000000000000
|
|
@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163
|
|
0 0 0 0 0 0
|
|
@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163
|
|
0 0 0 0 0 0
|
|
update ignore tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL
|
|
where f122='Test 3.5.9.4';
|
|
Warnings:
|
|
Warning 1048 Column 'f136' cannot be null
|
|
select f118, f121, f122, f136, f151, f163 from tb3
|
|
where f122 like 'Test 3.5.9.4-trig' order by f163;
|
|
f118 f121 f122 f136 f151 f163
|
|
a NULL Test 3.5.9.4-trig 00000 999 NULL
|
|
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
|
|
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
|
|
@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163
|
|
a NULL Test 3.5.9.4-trig NULL 999 NULL
|
|
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
|
|
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
|
|
@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163
|
|
a NULL Test 3.5.9.4-trig 0 999 NULL
|
|
drop trigger trg3_a;
|
|
drop trigger trg3_b;
|
|
drop trigger trg3_c;
|
|
drop trigger trg3_d;
|
|
delete from tb3 where f122='Test 3.5.9.4-trig';
|
|
|
|
Testcase 3.5.9.5: (implied in previous tests)
|
|
---------------------------------------------
|
|
|
|
Testcase 3.5.9.6:
|
|
-----------------
|
|
create trigger trg4a before insert on tb3 for each row
|
|
set @temp1= old.f120;
|
|
ERROR HY000: There is no OLD row in on INSERT trigger
|
|
create trigger trg4b after insert on tb3 for each row
|
|
set old.f120= 'test';
|
|
ERROR HY000: Updating of OLD row is not allowed in trigger
|
|
drop trigger trg4a;
|
|
drop trigger trg4b;
|
|
|
|
Testcase 3.5.9.7: (implied in previous tests)
|
|
---------------------------------------------
|
|
|
|
Testcase 3.5.9.8: (implied in previous tests)
|
|
---------------------------------------------
|
|
|
|
Testcase 3.5.9.9:
|
|
-----------------
|
|
create trigger trg5a before DELETE on tb3 for each row
|
|
set @temp1=new.f122;
|
|
ERROR HY000: There is no NEW row in on DELETE trigger
|
|
create trigger trg5b after DELETE on tb3 for each row
|
|
set new.f122='test';
|
|
ERROR HY000: There is no NEW row in on DELETE trigger
|
|
drop trigger trg5a;
|
|
drop trigger trg5b;
|
|
|
|
Testcase 3.5.9.10: (implied in previous tests)
|
|
----------------------------------------------
|
|
|
|
Testcase 3.5.9.11: covered by 3.5.9.9
|
|
-------------------------------------
|
|
|
|
Testcase 3.5.9.12: covered by 3.5.9.6
|
|
-------------------------------------
|
|
|
|
Testcase 3.5.9.13:
|
|
------------------
|
|
create trigger trg6a before UPDATE on tb3 for each row
|
|
set old.f118='C', new.f118='U';
|
|
ERROR HY000: Updating of OLD row is not allowed in trigger
|
|
create trigger trg6b after INSERT on tb3 for each row
|
|
set old.f136=163, new.f118='U';
|
|
ERROR HY000: Updating of OLD row is not allowed in trigger
|
|
create trigger trg6c after UPDATE on tb3 for each row
|
|
set old.f136=NULL;
|
|
ERROR HY000: Updating of OLD row is not allowed in trigger
|
|
drop trigger trg6a;
|
|
drop trigger trg6b;
|
|
drop trigger trg6c;
|
|
|
|
Testcase 3.5.9.14: (implied in previous tests)
|
|
----------------------------------------------
|
|
DROP TABLE test.tb3;
|