~drizzle-trunk/drizzle/development

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 = myisam;
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/myisam_tb3.txt' into table tb3 ;

Testcase: 3.5.1.1:
------------------
use test;
Create trigger trg1_1 BEFORE INSERT 
on tb3 for each row set @test_before = 2, new.f142 = @test_before;
Create trigger trg1_2 AFTER INSERT 
on tb3 for each row set @test_after = 6;
Create trigger trg1_4 BEFORE UPDATE 
on tb3 for each row set @test_before = 27, 
new.f142 = @test_before, 
new.f122 = 'Before Update Trigger';
Create trigger trg1_3 AFTER UPDATE 
on tb3 for each row set @test_after = '15';
Create trigger trg1_5 BEFORE DELETE on tb3 for each row  
select count(*) into @test_before from tb3 as tr_tb3 
where f121 = 'Test 3.5.1.1';
Create trigger trg1_6 AFTER DELETE on tb3 for each row  
select count(*) into @test_after from tb3 as tr_tb3 
where f121 = 'Test 3.5.1.1';
set @test_before = 1;
set @test_after = 5;
select @test_before, @test_after;
@test_before	@test_after
1	5
Insert into tb3 (f121, f122, f142, f144, f134) 
values ('Test 3.5.1.1', 'First Row', @test_before, @test_after, 1);
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
f121	f122	f142	f144	f134
Test 3.5.1.1	First Row	2	0000000005	1
select @test_before, @test_after;
@test_before	@test_after
2	6
set @test_before = 18;
set @test_after = 8;
select @test_before, @test_after;
@test_before	@test_after
18	8
Update tb3 set  tb3.f122 = 'Update', 
tb3.f142 = @test_before, 
tb3.f144 = @test_after 
where tb3.f121 = 'Test 3.5.1.1';
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
f121	f122	f142	f144	f134
Test 3.5.1.1	Before Update Trigger	27	0000000008	1
select @test_before, @test_after;
@test_before	@test_after
27	15
Insert into tb3 (f121, f122, f142, f144, f134) 
values ('Test 3.5.1.1', 'Second Row', 5, 6, 2);
set @test_before = 0;
set @test_after = 0;
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
f121	f122	f142	f144	f134
Test 3.5.1.1	Before Update Trigger	27	0000000008	1
Test 3.5.1.1	Second Row	2	0000000006	2
select @test_before, @test_after;
@test_before	@test_after
0	0
Delete from tb3 where f121 = 'Test 3.5.1.1' and f134 = 2;
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
f121	f122	f142	f144	f134
Test 3.5.1.1	Before Update Trigger	27	0000000008	1
select @test_before, @test_after;
@test_before	@test_after
2	1
drop trigger trg1_1;
drop trigger trg1_2;
drop trigger trg1_3;
drop trigger trg1_4;
drop trigger trg1_5;
drop trigger trg1_6;
delete from tb3 where f121='Test 3.5.1.1';

Testcase: 3.5.1.2:
------------------
Create trigger trg_1 after insert 
on tb3 for each statement set @x= 1;
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 'statement set @x= 1' at line 2
drop trigger trg_1;

Testcase 3.5.1.3:
-----------------
CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't';
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 'on tb3 BEFORE INSERT for each row set new.f120 = 't'' at line 1
CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's';
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 'trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's'' at line 1
CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row;
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 @ret1 = 'test' for each row' at line 1
CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row;
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 'DELETE AFTER on tb3 set @ret1 = 'test' for each row' at line 1
CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test';
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 'for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test'' at line 1
drop trigger trg3_1;
drop trigger trg3_2;
drop trigger trg3_3;
drop trigger trg3_4;
drop trigger trg3_5;

Testcase: 3.5.1.5:
------------------
CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e';
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 'on tb3 for each row set new.f120 = 'e'' at line 1
CREATE TRIGGER trg4_2 INSERT on tb3 for each set row  new.f120 = 'f';
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 'INSERT on tb3 for each set row  new.f120 = 'f'' at line 1
CREATE TRIGGER trg4_3 BEFORE INSERT tb3 for each row set new.f120 = 'g';
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 'tb3 for each row set new.f120 = 'g'' at line 1
CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g';
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 new.f120 = 'g'' at line 1
CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g';
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 'trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g'' at line 1
CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g';
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 'for each row set new.f120 = 'g'' at line 1
drop trigger trg4_1;
drop trigger trg4_2;
drop trigger trg4_3;
drop trigger trg4_4;
drop trigger trg4_5;
drop trigger trg4_6;

Testcase 3.5.1.6: - Need to fix
-------------------------------

Testcase 3.5.1.7: - need to fix
-------------------------------
drop table if exists t1;
create table t1 (f1 int, f2 char(25),f3 int) engine=myisam;
CREATE TRIGGER trg5_1 BEFORE INSERT on test.t1 
for each row set new.f3 = '14';
CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ
BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long
CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
insert into t1 (f2) values ('insert 3.5.1.7');
select * from t1;
f1	f2	f3
NULL	insert 3.5.1.7	14
update t1 set f2='update 3.5.1.7';
select * from t1;
f1	f2	f3
NULL	update 3.5.1.7	42
select trigger_name from information_schema.triggers order by trigger_name;
trigger_name
trg5_1
trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
drop trigger trg5_1;
drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ;
ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long
drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX;
drop table t1;

Testcase 3.5.1.8:
-----------------
CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't';
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 '* before insert on tb3 for each row set new.f120 = 't'' at line 1
CREATE TRIGGER trigger before insert on tb3 for each row set new.f120 = 't';
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 'trigger before insert on tb3 for each row set new.f120 = 't'' at line 1
CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't';
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 '100 before insert on tb3 for each row set new.f120 = 't'' at line 1
CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't';
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 '@@view before insert on tb3 for each row set new.f120 = 't'' at line 1
CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't';
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 '@name before insert on tb3 for each row set new.f120 = 't'' at line 1
CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3 
for each row set new.f120 ='X';
ERROR HY000: Trigger in wrong schema
drop database if exists trig_db;
create database trig_db;
use trig_db;
create table t1 (f1 integer) engine = myisam;
use test;
CREATE TRIGGER trig_db.trg6_2 AFTER INSERT on tb3 
for each row set @ret_trg6_2 = 5;
ERROR 42S02: Table 'trig_db.tb3' doesn't exist
use trig_db;
CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3 
for each row set @ret_trg6_3 = 18;
ERROR HY000: Trigger in wrong schema
use test;
drop database trig_db;
drop trigger trg6_1;
drop trigger trg6_3;

Testcase 3.5.1.9:(cannot be inplemented at this point)
------------------------------------------------------

Testcase 3.5.1.10:
------------------
CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X';
CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y';
ERROR HY000: Trigger already exists
drop trigger trg7_1;

Testcase 3.5.1.?:
-----------------
drop table if exists t1;
drop table if exists t2;
create table t1 (f1 char(50), f2 integer) engine = myisam;
create table t2 (f1 char(50), f2 integer) engine = myisam;
create trigger trig before insert on t1 
for each row set new.f1 ='trig t1';
create trigger trig before update on t2 
for each row set new.f1 ='trig t2';
ERROR HY000: Trigger already exists
insert into t1 value ('insert to t1',1);
select * from t1;
f1	f2
trig t1	1
update t1 set f1='update to t1';
select * from t1;
f1	f2
update to t1	1
insert into t2 value ('insert to t2',2);
update t2 set f1='update to t1';
select * from t2;
f1	f2
update to t1	2
drop table t1;
drop table t2;
drop trigger trig;

Testcase 3.5.1.11:
------------------
drop database if exists trig_db1;
drop database if exists trig_db2;
drop database if exists trig_db3;
create database trig_db1;
create database trig_db2;
create database trig_db3;
use trig_db1;
create table t1 (f1 char(50), f2 integer) engine = myisam;
create trigger trig before insert on t1 
for each row set new.f1 ='trig1', @test_var1='trig1';
use trig_db2;
create table t2 (f1 char(50), f2 integer) engine = myisam;
create trigger trig before insert on t2 
for each row set new.f1 ='trig2', @test_var2='trig2';
use trig_db3;
create table t1 (f1 char(50), f2 integer) engine = myisam;
create trigger trig before insert on t1 
for each row set new.f1 ='trig3', @test_var3='trig3';
set @test_var1= '', @test_var2= '', @test_var3= '';
use trig_db1;
insert into t1 (f1,f2) values ('insert to db1 t1',1);
insert into trig_db1.t1 (f1,f2) values ('insert to db1 t1 from db1',2);
insert into trig_db2.t2 (f1,f2) values ('insert to db2 t2 from db1',3);
insert into trig_db3.t1 (f1,f2) values ('insert to db3 t1 from db1',4);
select @test_var1, @test_var2, @test_var3;
@test_var1	@test_var2	@test_var3
trig1	trig2	trig3
select * from t1 order by f2;
f1	f2
trig1	1
trig1	2
select * from trig_db2.t2;
f1	f2
trig2	3
select * from trig_db3.t1;
f1	f2
trig3	4
select * from t1 order by f2;
f1	f2
trig1	1
trig1	2
use test;
drop database trig_db1;
drop database trig_db2;
drop database trig_db3;

Testcase 3.5.2.1/2/3:
---------------------
drop database if exists trig_db1;
drop database if exists trig_db2;
create database trig_db1;
create database trig_db2;
use trig_db1;
create table t1 (f1 char(50), f2 integer) engine = myisam;
create table trig_db2.t1 (f1 char(50), f2 integer) engine = myisam;
create trigger trig1_b before insert on t1 
for each row set @test_var1='trig1_b';
create trigger trig_db1.trig1_a after insert on t1 
for each row set @test_var2='trig1_a';
create trigger trig_db2.trig2 before insert on trig_db2.t1 
for each row set @test_var3='trig2';
select trigger_schema, trigger_name, event_object_table
from information_schema.triggers order by trigger_name;
trigger_schema	trigger_name	event_object_table
trig_db1	trig1_a	t1
trig_db1	trig1_b	t1
trig_db2	trig2	t1
set @test_var1= '', @test_var2= '', @test_var3= '';
insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352);
insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352);
select @test_var1, @test_var2, @test_var3;
@test_var1	@test_var2	@test_var3
trig1_b	trig1_a	trig2
drop database trig_db1;
drop database trig_db2;
DROP TABLE test.tb3;