diff options
Diffstat (limited to 'mysql-test/suite/funcs_1/r/innodb_trig_08.result')
| -rw-r--r-- | mysql-test/suite/funcs_1/r/innodb_trig_08.result | 507 |
1 files changed, 507 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/r/innodb_trig_08.result b/mysql-test/suite/funcs_1/r/innodb_trig_08.result new file mode 100644 index 00000000000..4388950aba1 --- /dev/null +++ b/mysql-test/suite/funcs_1/r/innodb_trig_08.result @@ -0,0 +1,507 @@ +SET @NO_REFRESH = IF( '' = '', 0, 1); +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 tinytext, +f122 text, +f123 mediumtext, +f124 longtext unicode, +f125 tinyblob, +f126 blob, +f127 mediumblob, +f128 longblob, +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 = innodb; +Warnings: +Note 1265 Data truncated for column 'f150' at row 1 +Note 1265 Data truncated for column 'f151' at row 1 +Note 1265 Data truncated for column 'f152' at row 1 +load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/innodb_tb3.txt' into table tb3 ; + +Testcase: 3.5: +-------------- +create User test_general@localhost; +set password for test_general@localhost = password('PWD'); +revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost; +create User test_super@localhost; +set password for test_super@localhost = password('PWD'); +grant ALL on *.* to test_super@localhost with grant OPTION; +connect(localhost,test_general,PWD,test,MASTER_MYPORT,MASTER_MYSOCK); +connect(localhost,test_super,PWD,test,MASTER_MYPORT,MASTER_MYSOCK); + +Testcase 3.5.8.1: (implied in previous tests) +--------------------------------------------- + +Testcase 3.5.8.2: (implied in previous tests) +--------------------------------------------- + +Testcase 3.5.8.3/4: +------------------- +create database db_test; +grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general; +grant LOCK TABLES on db_test.* to test_general; +Use db_test; +create table t1_i ( +i120 char ascii not null DEFAULT b'101', +i136 smallint zerofill not null DEFAULT 999, +i144 int zerofill not null DEFAULT 99999, +i163 decimal (63,30)) engine=innodb; +create table t1_u ( +u120 char ascii not null DEFAULT b'101', +u136 smallint zerofill not null DEFAULT 999, +u144 int zerofill not null DEFAULT 99999, +u163 decimal (63,30)) engine=innodb; +create table t1_d ( +d120 char ascii not null DEFAULT b'101', +d136 smallint zerofill not null DEFAULT 999, +d144 int zerofill not null DEFAULT 99999, +d163 decimal (63,30)) engine=innodb; +Insert into t1_u values ('a',111,99999,999.99); +Insert into t1_u values ('b',222,99999,999.99); +Insert into t1_u values ('c',333,99999,999.99); +Insert into t1_u values ('d',222,99999,999.99); +Insert into t1_u values ('e',222,99999,999.99); +Insert into t1_u values ('f',333,99999,999.99); +Insert into t1_d values ('a',111,99999,999.99); +Insert into t1_d values ('b',222,99999,999.99); +Insert into t1_d values ('c',333,99999,999.99); +Insert into t1_d values ('d',444,99999,999.99); +Insert into t1_d values ('e',222,99999,999.99); +Insert into t1_d values ('f',222,99999,999.99); + +3.5.8.4 - multiple SQL +---------------------- +use test; +Create trigger trg1 AFTER INSERT on tb3 for each row +BEGIN +insert into db_test.t1_i +values (new.f120, new.f136, new.f144, new.f163); +update db_test.t1_u +set u144=new.f144, u163=new.f163 +where u136=new.f136; +delete from db_test.t1_d where d136= new.f136; +select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u +where u136= new.f136; +END// +Use test; +set @test_var=0; +Insert into tb3 (f120, f122, f136, f144, f163) +values ('1', 'Test 3.5.8.4', 222, 23456, 1.05); +Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4'; +f120 f122 f136 f144 f163 +1 Test 3.5.8.4 00222 0000023456 1.050000000000000000000000000000 +select * from db_test.t1_i order by i120; +i120 i136 i144 i163 +1 00222 0000023456 1.050000000000000000000000000000 +select * from db_test.t1_u order by u120; +u120 u136 u144 u163 +a 00111 0000099999 999.990000000000000000000000000000 +b 00222 0000023456 1.050000000000000000000000000000 +c 00333 0000099999 999.990000000000000000000000000000 +d 00222 0000023456 1.050000000000000000000000000000 +e 00222 0000023456 1.050000000000000000000000000000 +f 00333 0000099999 999.990000000000000000000000000000 +select * from db_test.t1_d order by d120; +d120 d136 d144 d163 +a 00111 0000099999 999.990000000000000000000000000000 +c 00333 0000099999 999.990000000000000000000000000000 +d 00444 0000099999 999.990000000000000000000000000000 +select @test_var; +@test_var +3.150000000000000000000000000000 + +3.5.8.4 - single SQL - insert +----------------------------- +Create trigger trg2 BEFORE UPDATE on tb3 for each row +BEGIN +insert into db_test.t1_i +values (new.f120, new.f136, new.f144, new.f163); +END// +Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%'; +f120 f122 f136 f144 f163 +1 Test 3.5.8.4 00222 0000023456 1.050000000000000000000000000000 +select * from db_test.t1_i order by i120; +i120 i136 i144 i163 +1 00222 0000023456 1.050000000000000000000000000000 +update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert' + where f122='Test 3.5.8.4'; +Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%'; +f120 f122 f136 f144 f163 +I Test 3.5.8.4-Single Insert 00222 0000023456 1.050000000000000000000000000000 +select * from db_test.t1_i order by i120; +i120 i136 i144 i163 +1 00222 0000023456 1.050000000000000000000000000000 +I 00222 0000023456 1.050000000000000000000000000000 + +3.5.8.4 - single SQL - update +----------------------------- +drop trigger trg2; +Create trigger trg3 BEFORE UPDATE on tb3 for each row +update db_test.t1_u +set u120=new.f120 +where u136=new.f136; +update tb3 set f120='U', f122='Test 3.5.8.4-Single Update' + where f122='Test 3.5.8.4-Single Insert'; +Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%'; +f120 f122 f136 f144 f163 +U Test 3.5.8.4-Single Update 00222 0000023456 1.050000000000000000000000000000 +select * from db_test.t1_u order by u120; +u120 u136 u144 u163 +a 00111 0000099999 999.990000000000000000000000000000 +c 00333 0000099999 999.990000000000000000000000000000 +f 00333 0000099999 999.990000000000000000000000000000 +U 00222 0000023456 1.050000000000000000000000000000 +U 00222 0000023456 1.050000000000000000000000000000 +U 00222 0000023456 1.050000000000000000000000000000 + +3.5.8.3/4 - single SQL - delete +------------------------------- +drop trigger trg3; +Create trigger trg4 AFTER UPDATE on tb3 for each row +delete from db_test.t1_d where d136= new.f136; +update tb3 set f120='D', f136=444, +f122='Test 3.5.8.4-Single Delete' + where f122='Test 3.5.8.4-Single Update'; +Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%'; +f120 f122 f136 f144 f163 +D Test 3.5.8.4-Single Delete 00444 0000023456 1.050000000000000000000000000000 +select * from db_test.t1_d order by d120; +d120 d136 d144 d163 +a 00111 0000099999 999.990000000000000000000000000000 +c 00333 0000099999 999.990000000000000000000000000000 + +3.5.8.3/4 - single SQL - select +------------------------------- +drop trigger trg4; +Create trigger trg5 AFTER UPDATE on tb3 for each row +select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u +where u136= new.f136; +set @test_var=0; +update tb3 set f120='S', f136=111, +f122='Test 3.5.8.4-Single Select' + where f122='Test 3.5.8.4-Single Delete'; +Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%'; +f120 f122 f136 f144 f163 +S Test 3.5.8.4-Single Select 00111 0000023456 1.050000000000000000000000000000 +select @test_var; +@test_var +999.990000000000000000000000000000 +drop trigger trg1; +drop trigger trg5; +drop database if exists db_test; +delete from tb3 where f122 like 'Test 3.5.8.4%'; +revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; + +Testcase 3.5.8.5 (IF): +---------------------- +create trigger trg2 before insert on tb3 for each row +BEGIN +IF new.f120='1' then +set @test_var='one', new.f120='2'; +ELSEIF new.f120='2' then +set @test_var='two', new.f120='3'; +ELSEIF new.f120='3' then +set @test_var='three', new.f120='4'; +END IF; +IF (new.f120='4') and (new.f136=10) then +set @test_var2='2nd if', new.f120='d'; +ELSE +set @test_var2='2nd else', new.f120='D'; +END IF; +END// +set @test_var='Empty', @test_var2=0; +Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101); +select f120, f122, f136, @test_var, @test_var2 +from tb3 where f122 = 'Test 3.5.8.5-if' order by f136; +f120 f122 f136 @test_var @test_var2 +D Test 3.5.8.5-if 00101 one 2nd else +Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102); +select f120, f122, f136, @test_var, @test_var2 +from tb3 where f122 = 'Test 3.5.8.5-if' order by f136; +f120 f122 f136 @test_var @test_var2 +D Test 3.5.8.5-if 00101 two 2nd else +D Test 3.5.8.5-if 00102 two 2nd else +Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10); +select f120, f122, f136, @test_var, @test_var2 +from tb3 where f122 = 'Test 3.5.8.5-if' order by f136; +f120 f122 f136 @test_var @test_var2 +d Test 3.5.8.5-if 00010 three 2nd if +D Test 3.5.8.5-if 00101 three 2nd if +D Test 3.5.8.5-if 00102 three 2nd if +Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103); +select f120, f122, f136, @test_var, @test_var2 +from tb3 where f122 = 'Test 3.5.8.5-if' order by f136; +f120 f122 f136 @test_var @test_var2 +d Test 3.5.8.5-if 00010 three 2nd else +D Test 3.5.8.5-if 00101 three 2nd else +D Test 3.5.8.5-if 00102 three 2nd else +D Test 3.5.8.5-if 00103 three 2nd else +create trigger trg3 before update on tb3 for each row +BEGIN +ELSEIF new.f120='2' then +END IF; +END// +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 'ELSEIF new.f120='2' then +END IF; +END' at line 3 +drop trigger trg3// +create trigger trg4 before update on tb3 for each row +BEGIN +IF (new.f120='4') and (new.f136=10) then +set @test_var2='2nd if', new.f120='d'; +ELSE +set @test_var2='2nd else', new.f120='D'; +END// +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 '' at line 7 +drop trigger trg4; +drop trigger trg2; +delete from tb3 where f121='Test 3.5.8.5-if'; + +Testcase 3.5.8.5-case: +---------------------- +create trigger trg3 before insert on tb3 for each row +BEGIN +SET new.f120=char(ascii(new.f120)-32); +CASE +when new.f136<100 then set new.f136=new.f136+120; +when new.f136<10 then set new.f144=777; +when new.f136>100 then set new.f120=new.f136-1; +END case; +CASE +when new.f136=200 then set @test_var=CONCAT(new.f120, '='); +ELSE set @test_var=concat(new.f120, '*'); +END case; +CASE new.f144 +when 1 then set @test_var=concat(@test_var, 'one'); +when 2 then set @test_var=concat(@test_var, 'two'); +when 3 then set @test_var=concat(@test_var, 'three'); +when 4 then set @test_var=concat(@test_var, 'four'); +when 5 then set @test_var=concat(@test_var, 'five'); +when 6 then set @test_var=concat(@test_var, 'six'); +when 7 then set @test_var=concat(@test_var, 'seven'); +when 8 then set @test_var=concat(@test_var, 'eight'); +when 9 then set @test_var=concat(@test_var, 'nine'); +when 10 then set @test_var=concat(@test_var, 'ten'); +when 11 then set @test_var=concat(@test_var, 'eleven'); +when 12 then set @test_var=concat(@test_var, 'twelve'); +when 13 then set @test_var=concat(@test_var, 'thirteen'); +when 14 then set @test_var=concat(@test_var, 'fourteen'); +when 15 then set @test_var=concat(@test_var, 'fifteen'); +ELSE set @test_var=CONCAT(new.f120, '*', new.f144); +END case; +END// +set @test_var='Empty'; +Insert into tb3 (f120, f122, f136, f144) +values ('a', 'Test 3.5.8.5-case', 5, 7); +select f120, f122, f136, f144, @test_var +from tb3 where f122 = 'Test 3.5.8.5-case' order by f120; +f120 f122 f136 f144 @test_var +A Test 3.5.8.5-case 00125 0000000007 A*seven +Insert into tb3 (f120, f122, f136, f144) +values ('b', 'Test 3.5.8.5-case', 71,16); +select f120, f122, f136, f144, @test_var +from tb3 where f122 = 'Test 3.5.8.5-case' order by f120; +f120 f122 f136 f144 @test_var +A Test 3.5.8.5-case 00125 0000000007 B*0000000016 +B Test 3.5.8.5-case 00191 0000000016 B*0000000016 +Insert into tb3 (f120, f122, f136, f144) +values ('c', 'Test 3.5.8.5-case', 80,1); +select f120, f122, f136, f144, @test_var +from tb3 where f122 = 'Test 3.5.8.5-case' order by f120; +f120 f122 f136 f144 @test_var +A Test 3.5.8.5-case 00125 0000000007 C=one +B Test 3.5.8.5-case 00191 0000000016 C=one +C Test 3.5.8.5-case 00200 0000000001 C=one +Insert into tb3 (f120, f122, f136) +values ('d', 'Test 3.5.8.5-case', 152); +Warnings: +Warning 1265 Data truncated for column 'f120' at row 1 +select f120, f122, f136, f144, @test_var +from tb3 where f122 = 'Test 3.5.8.5-case' order by f120; +f120 f122 f136 f144 @test_var +1 Test 3.5.8.5-case 00152 0000099999 1*0000099999 +A Test 3.5.8.5-case 00125 0000000007 1*0000099999 +B Test 3.5.8.5-case 00191 0000000016 1*0000099999 +C Test 3.5.8.5-case 00200 0000000001 1*0000099999 +Insert into tb3 (f120, f122, f136, f144) +values ('e', 'Test 3.5.8.5-case', 200, 8); +Warnings: +Warning 1265 Data truncated for column 'f120' at row 1 +select f120, f122, f136, f144, @test_var +from tb3 where f122 = 'Test 3.5.8.5-case' order by f120; +f120 f122 f136 f144 @test_var +1 Test 3.5.8.5-case 00152 0000099999 1=eight +1 Test 3.5.8.5-case 00200 0000000008 1=eight +A Test 3.5.8.5-case 00125 0000000007 1=eight +B Test 3.5.8.5-case 00191 0000000016 1=eight +C Test 3.5.8.5-case 00200 0000000001 1=eight +Insert into tb3 (f120, f122, f136, f144) +values ('f', 'Test 3.5.8.5-case', 100, 8); +select f120, f122, f136, f144, @test_var +from tb3 where f122 = 'Test 3.5.8.5-case' order by f120; +f120 f122 f136 f144 @test_var +1 Test 3.5.8.5-case 00152 0000099999 1=eight +1 Test 3.5.8.5-case 00200 0000000008 1=eight +A Test 3.5.8.5-case 00125 0000000007 1=eight +B Test 3.5.8.5-case 00191 0000000016 1=eight +C Test 3.5.8.5-case 00200 0000000001 1=eight +create trigger trg3a before update on tb3 for each row +BEGIN +CASE +when new.f136<100 then set new.f120='p'; +END// +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 '' at line 5 +drop trigger trg3a; +drop trigger trg3; +delete from tb3 where f121='Test 3.5.8.5-case'; + +Testcase 3.5.8.5-loop/leave: +---------------------------- +Create trigger trg4 after insert on tb3 for each row +BEGIN +set @counter=0, @flag='Initial'; +Label1: loop +if new.f136<new.f144 then +set @counter='Nothing to loop'; +leave Label1; +else +set @counter=@counter+1; +if new.f136=new.f144+@counter then +set @counter=concat(@counter, ' loops'); +leave Label1; +end if; +end if; +iterate label1; +set @flag='Final'; +END loop Label1; +END// +Insert into tb3 (f122, f136, f144) +values ('Test 3.5.8.5-loop', 2, 8); +select @counter, @flag; +@counter @flag +Nothing to loop Initial +Insert into tb3 (f122, f136, f144) +values ('Test 3.5.8.5-loop', 11, 8); +select @counter, @flag; +@counter @flag +3 loops Initial +Create trigger trg4_2 after update on tb3 for each row +BEGIN +Label1: loop +set @counter=@counter+1; +END; +END// +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 '; +END' at line 5 +drop trigger trg4_2; +drop trigger trg4; +delete from tb3 where f122='Test 3.5.8.5-loop'; + +Testcase 3.5.8.5-repeat: +------------------------ +Create trigger trg6 after insert on tb3 for each row +BEGIN +rp_label: REPEAT +SET @counter1 = @counter1 + 1; +IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label; +END IF; +SET @counter2 = @counter2 + 1; +UNTIL @counter1> new.f136 END REPEAT rp_label; +END// +set @counter1= 0, @counter2= 0; +Insert into tb3 (f122, f136) +values ('Test 3.5.8.5-repeat', 13); +select @counter1, @counter2; +@counter1 @counter2 +15 8 +Create trigger trg6_2 after update on tb3 for each row +BEGIN +REPEAT +SET @counter2 = @counter2 + 1; +END// +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 'END' at line 5 +drop trigger trg6; +delete from tb3 where f122='Test 3.5.8.5-repeat'; + +Testcase 3.5.8.5-while: +----------------------- +Create trigger trg7 after insert on tb3 for each row +wl_label: WHILE @counter1 < new.f136 DO +SET @counter1 = @counter1 + 1; +IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label; +END IF; +SET @counter2 = @counter2 + 1; +END WHILE wl_label// +set @counter1= 0, @counter2= 0; +Insert into tb3 (f122, f136) +values ('Test 3.5.8.5-while', 7); +select @counter1, @counter2; +@counter1 @counter2 +7 4 +Create trigger trg7_2 after update on tb3 for each row +BEGIN +WHILE @counter1 < new.f136 +SET @counter1 = @counter1 + 1; +END// +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; +END' at line 4 +delete from tb3 where f122='Test 3.5.8.5-while'; +drop trigger trg7; + +Testcase 3.5.8.6: (requirement void) +------------------------------------ + +Testcase 3.5.8.7: (Disabled as a result of bug _____) +----------------------------------------------------- +drop user test_general@localhost; +drop user test_general; +drop user test_super@localhost; |