diff options
author | skozlov/ksm@mysql.com/virtop.localdomain <> | 2006-12-17 23:08:04 +0300 |
---|---|---|
committer | skozlov/ksm@mysql.com/virtop.localdomain <> | 2006-12-17 23:08:04 +0300 |
commit | 5b7c2c91c187af254a42f3783fcde626b0e9d380 (patch) | |
tree | 8070fc34631aab4ad6e47f2149a98253b06c451d | |
parent | e67d2a07ef70ca1c561338c6264f086e3032d4f1 (diff) | |
download | mariadb-git-5b7c2c91c187af254a42f3783fcde626b0e9d380.tar.gz |
WL#2862
-rw-r--r-- | mysql-test/r/ndb_alter_table.result | 45 | ||||
-rw-r--r-- | mysql-test/r/ndb_basic.result | 76 | ||||
-rw-r--r-- | mysql-test/r/ndb_cursor.result | 39 | ||||
-rw-r--r-- | mysql-test/r/ndb_sp.result | 45 | ||||
-rw-r--r-- | mysql-test/r/ndb_subquery.result | 39 | ||||
-rw-r--r-- | mysql-test/r/ndb_trigger.result | 31 | ||||
-rw-r--r-- | mysql-test/t/ndb_alter_table.test | 27 | ||||
-rw-r--r-- | mysql-test/t/ndb_basic.test | 53 | ||||
-rw-r--r-- | mysql-test/t/ndb_cursor.test | 46 | ||||
-rw-r--r-- | mysql-test/t/ndb_sp.test | 43 | ||||
-rw-r--r-- | mysql-test/t/ndb_subquery.test | 18 | ||||
-rw-r--r-- | mysql-test/t/ndb_trigger.test | 30 |
12 files changed, 477 insertions, 15 deletions
diff --git a/mysql-test/r/ndb_alter_table.result b/mysql-test/r/ndb_alter_table.result index 7facb5fa286..be258f41c52 100644 --- a/mysql-test/r/ndb_alter_table.result +++ b/mysql-test/r/ndb_alter_table.result @@ -354,3 +354,48 @@ select * from t1 where a = 12; a b c 12 403 NULL drop table t1; +create table t1 (a int not null, b varchar(10)) engine=ndb; +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +alter table t1 add primary key (a); +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 PRIMARY 1 a A 0 NULL NULL BTREE +alter table t1 drop primary key; +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +drop table t1; +create table t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` int(11) NOT NULL DEFAULT '0', + `c` varchar(254) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 +alter table t1 alter b set default 1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` int(11) NOT NULL DEFAULT '1', + `c` varchar(254) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=ndbcluster DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (a int not null, b int not null) engine=ndb; +insert into t1 values (1, 300), (2, 200), (3, 100); +select * from t1; +a b +3 100 +2 200 +1 300 +alter table t1 order by b; +select * from t1; +a b +1 300 +2 200 +3 100 +drop table t1; +End of 5.1 tests diff --git a/mysql-test/r/ndb_basic.result b/mysql-test/r/ndb_basic.result index b7e893faf29..821b80e349b 100644 --- a/mysql-test/r/ndb_basic.result +++ b/mysql-test/r/ndb_basic.result @@ -773,4 +773,80 @@ a b 2 2 3 3 drop table t1, t2; +create table t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; +create table if not exists t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; +create table t2 like t1; +rename table t1 to t10, t2 to t20; +drop table t10,t20; +create table t1 (a int not null primary key, b int not null) engine=ndb; +create table t2 (a int not null primary key, b int not null) engine=ndb; +insert into t1 values (1,10), (2,20), (3,30); +insert into t2 values (1,10), (2,20), (3,30); +select * from t1 order by a; +a b +1 10 +2 20 +3 30 +delete from t1 where a > 0 order by a desc limit 1; +select * from t1 order by a; +a b +1 10 +2 20 +delete from t1,t2 using t1,t2 where t1.a = t2.a; +select * from t2 order by a; +a b +3 30 +drop table t1,t2; +create table t1 (a int not null primary key, b int not null) engine=ndb; +insert into t1 values (1,10), (2,20), (3,30); +insert into t1 set a=1, b=100; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +insert ignore into t1 set a=1, b=100; +select * from t1 order by a; +a b +1 10 +2 20 +3 30 +insert into t1 set a=1, b=1000 on duplicate key update b=b+1; +select * from t1 order by a; +a b +1 11 +2 20 +3 30 +drop table t1; +create table t1 (a int not null primary key, b int not null) engine=ndb; +create table t2 (c int not null primary key, d int not null) engine=ndb; +insert into t1 values (1,10), (2,10), (3,30), (4, 30); +insert into t2 values (1,10), (2,10), (3,30), (4, 30); +update t1 set a = 1 where a = 3; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +select * from t1 order by a; +a b +1 10 +2 10 +3 30 +4 30 +update t1 set b = 1 where a > 1 order by a desc limit 1; +select * from t1 order by a; +a b +1 10 +2 10 +3 30 +4 1 +update t1,t2 set a = 1, c = 1 where a = 3 and c = 3; +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +select * from t1 order by a; +a b +1 10 +2 10 +3 30 +4 1 +update ignore t1,t2 set a = 1, c = 1 where a = 3 and c = 3; +select * from t1 order by a; +a b +1 10 +2 10 +3 1 +4 1 +drop table t1,t2; End of 5.1 tests diff --git a/mysql-test/r/ndb_cursor.result b/mysql-test/r/ndb_cursor.result new file mode 100644 index 00000000000..d11c549d45e --- /dev/null +++ b/mysql-test/r/ndb_cursor.result @@ -0,0 +1,39 @@ +drop table if exists t1; +drop table if exists t2; +create table t1 ( +a int not null primary key, +b int not null +) engine=ndb; +create table t2 ( +a int not null primary key, +b int not null +) engine=ndb; +insert into t1 values (1,10), (2,20), (3,30), (4, 40); +create procedure test_cursor () +begin +declare done int default 0; +declare temp_a int; +declare temp_b int; +declare cur1 cursor for select a,b from t1; +declare continue handler for sqlstate '02000' set done = 1; +open cur1; +repeat +fetch cur1 into temp_a, temp_b; +if not done then +insert into t2 values (temp_a, temp_b); +end if; +until done end repeat; +close cur1; +end; +// +select * from t2 order by a; +a b +call test_cursor(); +select * from t2 order by a; +a b +1 10 +2 20 +3 30 +4 40 +drop table t1,t2; +end of 5.1 tests diff --git a/mysql-test/r/ndb_sp.result b/mysql-test/r/ndb_sp.result new file mode 100644 index 00000000000..847b3a7eb0f --- /dev/null +++ b/mysql-test/r/ndb_sp.result @@ -0,0 +1,45 @@ +drop table if exists t1; +drop table if exists t2; +create table t1 ( +a int not null primary key, +b int not null +) engine=ndb; +insert into t1 values (1,10), (2,20), (3,100), (4, 100); +create procedure test_proc1 (in var_in int) +begin +select * from t1 where a = var_in; +end; +create procedure test_proc2 (out var_out int) +begin +select b from t1 where a = 1 into var_out; +end; +create procedure test_proc3 (inout var_inout int) +begin +select b from t1 where a = var_inout into var_inout; +end; +// +call test_proc1(1); +a b +1 10 +call test_proc2(@test_var); +select @test_var; +@test_var +10 +set @test_var = 1; +call test_proc3(@test_var); +select @test_var; +@test_var +10 +alter procedure test_proc1 comment 'new comment'; +show create procedure test_proc1; +Procedure sql_mode Create Procedure +test_proc1 CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc1`(in var_in int) + COMMENT 'new comment' +begin +select * from t1 where a = var_in; +end +drop procedure test_proc1; +drop procedure test_proc2; +drop procedure test_proc3; +drop table t1; +End of 5.1 tests diff --git a/mysql-test/r/ndb_subquery.result b/mysql-test/r/ndb_subquery.result index 312711ab4b7..f2c9972f774 100644 --- a/mysql-test/r/ndb_subquery.result +++ b/mysql-test/r/ndb_subquery.result @@ -1,11 +1,14 @@ -drop table if exists t1; -drop table if exists t2; +drop table if exists t1, t2, t3, t4; create table t1 (p int not null primary key, u int not null, o int not null, unique (u), key(o)) engine=ndb; create table t2 (p int not null primary key, u int not null, o int not null, unique (u), key(o)) engine=ndb; +create table t3 (a int not null primary key, b int not null) engine=ndb; +create table t4 (c int not null primary key, d int not null) engine=ndb; insert into t1 values (1,1,1),(2,2,2),(3,3,3); insert into t2 values (1,1,1),(2,2,2),(3,3,3), (4,4,4), (5,5,5); +insert into t3 values (1,10), (2,10), (3,30), (4, 30); +insert into t4 values (1,10), (2,10), (3,30), (4, 30); explain select * from t2 where p NOT IN (select p from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL # Using where @@ -57,5 +60,33 @@ p u 1 1 2 2 3 3 -drop table t1; -drop table t2; +select * from t3 where a = any (select c from t4 where c = 1) order by a; +a b +1 10 +select * from t3 where a in (select c from t4 where c = 1) order by a; +a b +1 10 +select * from t3 where a <> some (select c from t4 where c = 1) order by a; +a b +2 10 +3 30 +4 30 +select * from t3 where a > all (select c from t4 where c = 1) order by a; +a b +2 10 +3 30 +4 30 +select * from t3 where row(1,10) = (select c,d from t4 where c = 1) order by a; +a b +1 10 +2 10 +3 30 +4 30 +select * from t3 where exists (select * from t4 where c = 1) order by a; +a b +1 10 +2 10 +3 30 +4 30 +drop table if exists t1, t2, t3, t4; +End of 5.1 tests diff --git a/mysql-test/r/ndb_trigger.result b/mysql-test/r/ndb_trigger.result index 27f83df70c9..2aeca5db2d3 100644 --- a/mysql-test/r/ndb_trigger.result +++ b/mysql-test/r/ndb_trigger.result @@ -1,7 +1,9 @@ -drop table if exists t1, t2, t3; +drop table if exists t1, t2, t3, t4, t5; 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 table t4 (a int not null primary key, b int) engine=ndb; +create table t5 (a int not null primary key, b int) engine=ndb; create trigger t1_bu before update on t1 for each row begin insert into t2 values ("u", old.a, old.b); @@ -11,7 +13,19 @@ create trigger t1_bd before delete on t1 for each row begin insert into t2 values ("d", old.a, old.b); end;// +create trigger t4_au after update on t4 +for each row begin +update t5 set b = b+1; +end; +// +create trigger t4_ad after delete on t4 +for each row begin +update t5 set b = b+1; +end; +// insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (3, 3, 3.05), (4, 4, 4.05); +insert into t4 values (1,1), (2,2), (3,3), (4, 4); +insert into t5 values (1,0); update t1 set a=5 where a != 3; select * from t1 order by id; id a b @@ -115,5 +129,16 @@ 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 +update t4 set b = 10 where a = 1; +select * from t5 order by a; +a b +1 1 +update t5 set b = 0; +delete from t4 where a = 1; +select * from t5 order by a; +a b +1 1 +drop trigger t4_au; +drop trigger t4_ad; +drop table t1, t2, t3, t4, t5; +End of 5.1 tests diff --git a/mysql-test/t/ndb_alter_table.test b/mysql-test/t/ndb_alter_table.test index fde9f1479f8..28a03cb4595 100644 --- a/mysql-test/t/ndb_alter_table.test +++ b/mysql-test/t/ndb_alter_table.test @@ -410,3 +410,30 @@ alter table t2 rename t1; insert into t1 (b) values (401),(402),(403); select * from t1 where a = 12; drop table t1; + +# some other ALTER combinations +# add/drop pk +create table t1 (a int not null, b varchar(10)) engine=ndb; +show index from t1; +alter table t1 add primary key (a); +show index from t1; +alter table t1 drop primary key; +show index from t1; +drop table t1; + +# alter .. alter +create table t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; +show create table t1; +alter table t1 alter b set default 1; +show create table t1; +drop table t1; + +# alter .. order by +create table t1 (a int not null, b int not null) engine=ndb; +insert into t1 values (1, 300), (2, 200), (3, 100); +select * from t1; +alter table t1 order by b; +select * from t1; +drop table t1; + +--echo End of 5.1 tests diff --git a/mysql-test/t/ndb_basic.test b/mysql-test/t/ndb_basic.test index 3348a94c044..8541c0ac0c5 100644 --- a/mysql-test/t/ndb_basic.test +++ b/mysql-test/t/ndb_basic.test @@ -747,4 +747,57 @@ select * from t1 order by a; select * from t2 order by a; drop table t1, t2; +# create table if not exists +--disable_warnings +create table t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; +create table if not exists t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb; +--enable_warnings + +# create like +create table t2 like t1; + +# multi rename +rename table t1 to t10, t2 to t20; +drop table t10,t20; + +# delete +create table t1 (a int not null primary key, b int not null) engine=ndb; +create table t2 (a int not null primary key, b int not null) engine=ndb; +insert into t1 values (1,10), (2,20), (3,30); +insert into t2 values (1,10), (2,20), (3,30); +select * from t1 order by a; +delete from t1 where a > 0 order by a desc limit 1; +select * from t1 order by a; +delete from t1,t2 using t1,t2 where t1.a = t2.a; +select * from t2 order by a; +drop table t1,t2; + +# insert ignore +create table t1 (a int not null primary key, b int not null) engine=ndb; +insert into t1 values (1,10), (2,20), (3,30); +--error 1062 +insert into t1 set a=1, b=100; +insert ignore into t1 set a=1, b=100; +select * from t1 order by a; +insert into t1 set a=1, b=1000 on duplicate key update b=b+1; +select * from t1 order by a; +drop table t1; + +# update +create table t1 (a int not null primary key, b int not null) engine=ndb; +create table t2 (c int not null primary key, d int not null) engine=ndb; +insert into t1 values (1,10), (2,10), (3,30), (4, 30); +insert into t2 values (1,10), (2,10), (3,30), (4, 30); +--error 1062 +update t1 set a = 1 where a = 3; +select * from t1 order by a; +update t1 set b = 1 where a > 1 order by a desc limit 1; +select * from t1 order by a; +--error 1062 +update t1,t2 set a = 1, c = 1 where a = 3 and c = 3; +select * from t1 order by a; +update ignore t1,t2 set a = 1, c = 1 where a = 3 and c = 3; +select * from t1 order by a; +drop table t1,t2; + --echo End of 5.1 tests diff --git a/mysql-test/t/ndb_cursor.test b/mysql-test/t/ndb_cursor.test new file mode 100644 index 00000000000..88ebf283e6d --- /dev/null +++ b/mysql-test/t/ndb_cursor.test @@ -0,0 +1,46 @@ +-- source include/have_ndb.inc +-- source include/not_embedded.inc + +--disable_warnings +drop table if exists t1; +drop table if exists t2; +--enable_warnings + +create table t1 ( + a int not null primary key, + b int not null +) engine=ndb; + +create table t2 ( + a int not null primary key, + b int not null +) engine=ndb; + +insert into t1 values (1,10), (2,20), (3,30), (4, 40); + +delimiter //; +create procedure test_cursor () +begin + declare done int default 0; + declare temp_a int; + declare temp_b int; + declare cur1 cursor for select a,b from t1; + declare continue handler for sqlstate '02000' set done = 1; + open cur1; + repeat + fetch cur1 into temp_a, temp_b; + if not done then + insert into t2 values (temp_a, temp_b); + end if; + until done end repeat; + close cur1; +end; +// +delimiter ;// + +select * from t2 order by a; +call test_cursor(); +select * from t2 order by a; +drop table t1,t2; + +--echo end of 5.1 tests diff --git a/mysql-test/t/ndb_sp.test b/mysql-test/t/ndb_sp.test new file mode 100644 index 00000000000..754f1e94e66 --- /dev/null +++ b/mysql-test/t/ndb_sp.test @@ -0,0 +1,43 @@ +-- source include/have_ndb.inc +-- source include/not_embedded.inc + +--disable_warnings +drop table if exists t1; +drop table if exists t2; +--enable_warnings + +create table t1 ( + a int not null primary key, + b int not null +) engine=ndb; +insert into t1 values (1,10), (2,20), (3,100), (4, 100); + +delimiter //; +create procedure test_proc1 (in var_in int) +begin + select * from t1 where a = var_in; +end; +create procedure test_proc2 (out var_out int) +begin + select b from t1 where a = 1 into var_out; +end; +create procedure test_proc3 (inout var_inout int) +begin + select b from t1 where a = var_inout into var_inout; +end; +// +delimiter ;// +call test_proc1(1); +call test_proc2(@test_var); +select @test_var; +set @test_var = 1; +call test_proc3(@test_var); +select @test_var; +alter procedure test_proc1 comment 'new comment'; +show create procedure test_proc1; +drop procedure test_proc1; +drop procedure test_proc2; +drop procedure test_proc3; +drop table t1; + +--echo End of 5.1 tests diff --git a/mysql-test/t/ndb_subquery.test b/mysql-test/t/ndb_subquery.test index 9cf6527dd98..6282c31c922 100644 --- a/mysql-test/t/ndb_subquery.test +++ b/mysql-test/t/ndb_subquery.test @@ -2,8 +2,7 @@ -- source include/not_embedded.inc --disable_warnings -drop table if exists t1; -drop table if exists t2; +drop table if exists t1, t2, t3, t4; --enable_warnings ########## @@ -14,8 +13,13 @@ unique (u), key(o)) engine=ndb; create table t2 (p int not null primary key, u int not null, o int not null, unique (u), key(o)) engine=ndb; +create table t3 (a int not null primary key, b int not null) engine=ndb; +create table t4 (c int not null primary key, d int not null) engine=ndb; + insert into t1 values (1,1,1),(2,2,2),(3,3,3); insert into t2 values (1,1,1),(2,2,2),(3,3,3), (4,4,4), (5,5,5); +insert into t3 values (1,10), (2,10), (3,30), (4, 30); +insert into t4 values (1,10), (2,10), (3,30), (4, 30); # Use pk --replace_column 9 # @@ -63,7 +67,13 @@ where t1.u = t2.u select * from t2 order by 1; -drop table t1; -drop table t2; +select * from t3 where a = any (select c from t4 where c = 1) order by a; +select * from t3 where a in (select c from t4 where c = 1) order by a; +select * from t3 where a <> some (select c from t4 where c = 1) order by a; +select * from t3 where a > all (select c from t4 where c = 1) order by a; +select * from t3 where row(1,10) = (select c,d from t4 where c = 1) order by a; +select * from t3 where exists (select * from t4 where c = 1) order by a; +drop table if exists t1, t2, t3, t4; +--echo End of 5.1 tests diff --git a/mysql-test/t/ndb_trigger.test b/mysql-test/t/ndb_trigger.test index 2521ef17842..7a4e58033a9 100644 --- a/mysql-test/t/ndb_trigger.test +++ b/mysql-test/t/ndb_trigger.test @@ -14,13 +14,15 @@ # --disable_warnings -drop table if exists t1, t2, t3; +drop table if exists t1, t2, t3, t4, t5; --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; - +create table t4 (a int not null primary key, b int) engine=ndb; +create table t5 (a int not null primary key, b int) engine=ndb; + delimiter //; create trigger t1_bu before update on t1 for each row begin @@ -31,8 +33,21 @@ create trigger t1_bd before delete on t1 for each row begin insert into t2 values ("d", old.a, old.b); end;// +create trigger t4_au after update on t4 + for each row begin + update t5 set b = b+1; + end; +// +create trigger t4_ad after delete on t4 + for each row begin + update t5 set b = b+1; + end; +// delimiter ;// + insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (3, 3, 3.05), (4, 4, 4.05); +insert into t4 values (1,1), (2,2), (3,3), (4, 4); +insert into t5 values (1,0); # Check that usual update works as it should update t1 set a=5 where a != 3; @@ -86,7 +101,14 @@ 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; +update t4 set b = 10 where a = 1; +select * from t5 order by a; +update t5 set b = 0; +delete from t4 where a = 1; +select * from t5 order by a; +drop trigger t4_au; +drop trigger t4_ad; -drop tables t1, t2, t3; +drop table t1, t2, t3, t4, t5; ---echo End of 5.0 tests +--echo End of 5.1 tests |