diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/federated.result | 28 | ||||
| -rw-r--r-- | mysql-test/r/ndb_replace.result | 47 | ||||
| -rw-r--r-- | mysql-test/r/ndb_trigger.result | 119 | ||||
| -rw-r--r-- | mysql-test/t/federated.test | 42 | ||||
| -rw-r--r-- | mysql-test/t/ndb_replace.test | 37 | ||||
| -rw-r--r-- | mysql-test/t/ndb_trigger.test | 92 |
6 files changed, 364 insertions, 1 deletions
diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result index f11da4ee62f..07396d17ed5 100644 --- a/mysql-test/r/federated.result +++ b/mysql-test/r/federated.result @@ -1601,6 +1601,34 @@ fld_cid fld_name fld_parentid fld_delt 5 Torkel 0 0 DROP TABLE federated.t1; DROP TABLE federated.bug_17377_table; +drop table if exists federated.t1; +create table federated.t1 (a int, b int, c int); +drop table if exists federated.t1; +drop table if exists federated.t2; +create table federated.t1 (a int, b int, c int) engine=federated connection='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1'; +create trigger federated.t1_bi before insert on federated.t1 for each row set new.c= new.a * new.b; +create table federated.t2 (a int, b int); +insert into federated.t2 values (13, 17), (19, 23); +insert into federated.t1 (a, b) values (1, 2), (3, 5), (7, 11); +select * from federated.t1; +a b c +1 2 2 +3 5 15 +7 11 77 +delete from federated.t1; +insert into federated.t1 (a, b) select * from federated.t2; +select * from federated.t1; +a b c +13 17 221 +19 23 437 +delete from federated.t1; +load data infile '../std_data_ln/loaddata5.dat' into table federated.t1 fields terminated by '' enclosed by '' ignore 1 lines (a, b); +select * from federated.t1; +a b c +3 4 12 +5 6 30 +drop tables federated.t1, federated.t2; +drop table federated.t1; DROP TABLE IF EXISTS federated.t1; DROP DATABASE IF EXISTS federated; DROP TABLE IF EXISTS federated.t1; diff --git a/mysql-test/r/ndb_replace.result b/mysql-test/r/ndb_replace.result index cdfcd6a7a43..4d63c397d60 100644 --- a/mysql-test/r/ndb_replace.result +++ b/mysql-test/r/ndb_replace.result @@ -30,7 +30,8 @@ REPLACE INTO t1 (i,j) VALUES (17,2); SELECT * from t1 ORDER BY i; i j k 3 1 42 -17 2 24 +17 2 NULL +DROP TABLE t1; CREATE TABLE t2 (a INT(11) NOT NULL, b INT(11) NOT NULL, c INT(11) NOT NULL, @@ -52,3 +53,47 @@ SELECT * FROM t2 ORDER BY id; a b c x y z id i 1 1 1 b b b 5 2 DROP TABLE t2; +drop table if exists t1; +create table t1 (pk int primary key, apk int unique, data int) engine=ndbcluster; +insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3); +replace into t1 (pk, apk) values (4, 1), (5, 2); +select * from t1 order by pk; +pk apk data +3 3 3 +4 1 NULL +5 2 NULL +delete from t1; +insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3); +replace into t1 (pk, apk) values (1, 4), (2, 5); +select * from t1 order by pk; +pk apk data +1 4 NULL +2 5 NULL +3 3 3 +delete from t1; +insert into t1 values (1, 1, 1), (4, 4, 4), (6, 6, 6); +load data infile '../std_data_ln/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk); +select * from t1 order by pk; +pk apk data +1 1 1 +3 4 NULL +5 6 NULL +delete from t1; +insert into t1 values (1, 1, 1), (3, 3, 3), (5, 5, 5); +load data infile '../std_data_ln/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk); +select * from t1 order by pk; +pk apk data +1 1 1 +3 4 NULL +5 6 NULL +delete from t1; +insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3); +replace into t1 (pk, apk) select 4, 1; +replace into t1 (pk, apk) select 2, 4; +select * from t1 order by pk; +pk apk data +2 4 NULL +3 3 3 +4 1 NULL +drop table t1; +End of 5.0 tests. diff --git a/mysql-test/r/ndb_trigger.result b/mysql-test/r/ndb_trigger.result new file mode 100644 index 00000000000..27f83df70c9 --- /dev/null +++ b/mysql-test/r/ndb_trigger.result @@ -0,0 +1,119 @@ +drop table if exists t1, t2, t3; +create table t1 (id int primary key, a int not null, b decimal (63,30) default 0) engine=ndb; +create table t2 (op char(1), a int not null, b decimal (63,30)); +create table t3 select 1 as i; +create trigger t1_bu before update on t1 for each row +begin +insert into t2 values ("u", old.a, old.b); +set new.b = old.b + 10; +end;// +create trigger t1_bd before delete on t1 for each row +begin +insert into t2 values ("d", old.a, old.b); +end;// +insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (3, 3, 3.05), (4, 4, 4.05); +update t1 set a=5 where a != 3; +select * from t1 order by id; +id a b +1 5 11.050000000000000000000000000000 +2 5 12.050000000000000000000000000000 +3 3 3.050000000000000000000000000000 +4 5 14.050000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +u 1 1.050000000000000000000000000000 +u 2 2.050000000000000000000000000000 +u 4 4.050000000000000000000000000000 +delete from t2; +update t1, t3 set a=6 where a = 5; +select * from t1 order by id; +id a b +1 6 21.050000000000000000000000000000 +2 6 22.050000000000000000000000000000 +3 3 3.050000000000000000000000000000 +4 6 24.050000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +u 5 11.050000000000000000000000000000 +u 5 12.050000000000000000000000000000 +u 5 14.050000000000000000000000000000 +delete from t2; +delete from t1 where a != 3; +select * from t1 order by id; +id a b +3 3 3.050000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +d 6 21.050000000000000000000000000000 +d 6 22.050000000000000000000000000000 +d 6 24.050000000000000000000000000000 +delete from t2; +insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (4, 4, 4.05); +delete t1 from t1, t3 where a != 3; +select * from t1 order by id; +id a b +3 3 3.050000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +d 1 1.050000000000000000000000000000 +d 2 2.050000000000000000000000000000 +d 4 4.050000000000000000000000000000 +delete from t2; +insert into t1 values (4, 4, 4.05); +insert into t1 (id, a) values (4, 1), (3, 1) on duplicate key update a= a + 1; +select * from t1 order by id; +id a b +3 4 13.050000000000000000000000000000 +4 5 14.050000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +u 3 3.050000000000000000000000000000 +u 4 4.050000000000000000000000000000 +delete from t2; +delete from t3; +insert into t3 values (4), (3); +insert into t1 (id, a) (select i, 1 from t3) on duplicate key update a= a + 1; +select * from t1 order by id; +id a b +3 5 23.050000000000000000000000000000 +4 6 24.050000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +u 4 13.050000000000000000000000000000 +u 5 14.050000000000000000000000000000 +delete from t2; +replace into t1 (id, a) values (4, 1), (3, 1); +select * from t1 order by id; +id a b +3 1 0.000000000000000000000000000000 +4 1 0.000000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +d 5 23.050000000000000000000000000000 +d 6 24.050000000000000000000000000000 +delete from t1; +delete from t2; +insert into t1 values (3, 1, 1.05), (4, 1, 2.05); +replace into t1 (id, a) (select i, 2 from t3); +select * from t1 order by id; +id a b +3 2 0.000000000000000000000000000000 +4 2 0.000000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +d 1 1.050000000000000000000000000000 +d 1 2.050000000000000000000000000000 +delete from t1; +delete from t2; +insert into t1 values (3, 1, 1.05), (5, 2, 2.05); +load data infile '../std_data_ln/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (id, a); +select * from t1 order by id; +id a b +3 4 0.000000000000000000000000000000 +5 6 0.000000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +d 1 1.050000000000000000000000000000 +d 2 2.050000000000000000000000000000 +drop tables t1, t2, t3; +End of 5.0 tests diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test index 80b31c610a2..6e53b1e3adb 100644 --- a/mysql-test/t/federated.test +++ b/mysql-test/t/federated.test @@ -1310,4 +1310,46 @@ connection slave; DROP TABLE federated.bug_17377_table; +# +# Additional test for bug#18437 "Wrong values inserted with a before +# update trigger on NDB table". SQL-layer didn't properly inform +# handler about fields which were read and set in triggers. In some +# cases this resulted in incorrect (garbage) values of OLD variables +# and lost changes to NEW variables. +# Since for federated engine only operation which is affected by wrong +# fields mark-up is handler::write_row() this file constains coverage +# for ON INSERT triggers only. Tests for other types of triggers reside +# in ndb_trigger.test. +# +--disable_warnings +drop table if exists federated.t1; +--enable_warnings +create table federated.t1 (a int, b int, c int); +connection master; +--disable_warnings +drop table if exists federated.t1; +drop table if exists federated.t2; +--enable_warnings +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval create table federated.t1 (a int, b int, c int) engine=federated connection='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; +create trigger federated.t1_bi before insert on federated.t1 for each row set new.c= new.a * new.b; +create table federated.t2 (a int, b int); +insert into federated.t2 values (13, 17), (19, 23); +# Each of three statements should correctly set values for all three fields +# insert +insert into federated.t1 (a, b) values (1, 2), (3, 5), (7, 11); +select * from federated.t1; +delete from federated.t1; +# insert ... select +insert into federated.t1 (a, b) select * from federated.t2; +select * from federated.t1; +delete from federated.t1; +# load +load data infile '../std_data_ln/loaddata5.dat' into table federated.t1 fields terminated by '' enclosed by '' ignore 1 lines (a, b); +select * from federated.t1; +drop tables federated.t1, federated.t2; + +connection slave; +drop table federated.t1; + source include/federated_cleanup.inc; diff --git a/mysql-test/t/ndb_replace.test b/mysql-test/t/ndb_replace.test index 94a11f7dfb2..476a607ed44 100644 --- a/mysql-test/t/ndb_replace.test +++ b/mysql-test/t/ndb_replace.test @@ -39,6 +39,7 @@ INSERT INTO t1 VALUES (1,1,23),(2,2,24); REPLACE INTO t1 (j,k) VALUES (1,42); REPLACE INTO t1 (i,j) VALUES (17,2); SELECT * from t1 ORDER BY i; +DROP TABLE t1; # bug#19906 CREATE TABLE t2 (a INT(11) NOT NULL, @@ -64,4 +65,40 @@ SELECT * FROM t2 ORDER BY id; DROP TABLE t2; +# +# Bug #20728 "REPLACE does not work correctly for NDB table with PK and +# unique index" +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (pk int primary key, apk int unique, data int) engine=ndbcluster; +# Test for plain replace which updates pk +insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3); +replace into t1 (pk, apk) values (4, 1), (5, 2); +select * from t1 order by pk; +delete from t1; +# Another test for plain replace which doesn't touch pk +insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3); +replace into t1 (pk, apk) values (1, 4), (2, 5); +select * from t1 order by pk; +delete from t1; +# Test for load data replace which updates pk +insert into t1 values (1, 1, 1), (4, 4, 4), (6, 6, 6); +load data infile '../std_data_ln/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk); +select * from t1 order by pk; +delete from t1; +# Now test for load data replace which doesn't touch pk +insert into t1 values (1, 1, 1), (3, 3, 3), (5, 5, 5); +load data infile '../std_data_ln/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk); +select * from t1 order by pk; +delete from t1; +# Finally test for both types of replace ... select +insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3); +replace into t1 (pk, apk) select 4, 1; +replace into t1 (pk, apk) select 2, 4; +select * from t1 order by pk; +# Clean-up +drop table t1; +--echo End of 5.0 tests. diff --git a/mysql-test/t/ndb_trigger.test b/mysql-test/t/ndb_trigger.test new file mode 100644 index 00000000000..2521ef17842 --- /dev/null +++ b/mysql-test/t/ndb_trigger.test @@ -0,0 +1,92 @@ +# Tests which involve triggers and NDB storage engine +--source include/have_ndb.inc +--source include/not_embedded.inc + +# +# Test for bug#18437 "Wrong values inserted with a before update +# trigger on NDB table". SQL-layer didn't properly inform handler +# about fields which were read and set in triggers. In some cases +# this resulted in incorrect (garbage) values of OLD variables and +# lost changes to NEW variables. +# You can find similar tests for ON INSERT triggers in federated.test +# since this engine so far is the only engine in MySQL which cares +# about field mark-up during handler::write_row() operation. +# + +--disable_warnings +drop table if exists t1, t2, t3; +--enable_warnings + +create table t1 (id int primary key, a int not null, b decimal (63,30) default 0) engine=ndb; +create table t2 (op char(1), a int not null, b decimal (63,30)); +create table t3 select 1 as i; + +delimiter //; +create trigger t1_bu before update on t1 for each row +begin + insert into t2 values ("u", old.a, old.b); + set new.b = old.b + 10; +end;// +create trigger t1_bd before delete on t1 for each row +begin + insert into t2 values ("d", old.a, old.b); +end;// +delimiter ;// +insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (3, 3, 3.05), (4, 4, 4.05); + +# Check that usual update works as it should +update t1 set a=5 where a != 3; +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t2; +# Check that everything works for multi-update +update t1, t3 set a=6 where a = 5; +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t2; +# Check for delete +delete from t1 where a != 3; +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t2; +# Check for multi-delete +insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (4, 4, 4.05); +delete t1 from t1, t3 where a != 3; +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t2; +# Check for insert ... on duplicate key update +insert into t1 values (4, 4, 4.05); +insert into t1 (id, a) values (4, 1), (3, 1) on duplicate key update a= a + 1; +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t2; +# Check for insert ... select ... on duplicate key update +delete from t3; +insert into t3 values (4), (3); +insert into t1 (id, a) (select i, 1 from t3) on duplicate key update a= a + 1; +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t2; +# Check for replace +replace into t1 (id, a) values (4, 1), (3, 1); +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t1; +delete from t2; +# Check for replace ... select ... +insert into t1 values (3, 1, 1.05), (4, 1, 2.05); +replace into t1 (id, a) (select i, 2 from t3); +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t1; +delete from t2; +# Check for load data replace +insert into t1 values (3, 1, 1.05), (5, 2, 2.05); +load data infile '../std_data_ln/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (id, a); +select * from t1 order by id; +select * from t2 order by op, a, b; + +drop tables t1, t2, t3; + +--echo End of 5.0 tests |