summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorskozlov/ksm@mysql.com/virtop.localdomain <>2006-12-17 23:08:04 +0300
committerskozlov/ksm@mysql.com/virtop.localdomain <>2006-12-17 23:08:04 +0300
commit5b7c2c91c187af254a42f3783fcde626b0e9d380 (patch)
tree8070fc34631aab4ad6e47f2149a98253b06c451d
parente67d2a07ef70ca1c561338c6264f086e3032d4f1 (diff)
downloadmariadb-git-5b7c2c91c187af254a42f3783fcde626b0e9d380.tar.gz
WL#2862
-rw-r--r--mysql-test/r/ndb_alter_table.result45
-rw-r--r--mysql-test/r/ndb_basic.result76
-rw-r--r--mysql-test/r/ndb_cursor.result39
-rw-r--r--mysql-test/r/ndb_sp.result45
-rw-r--r--mysql-test/r/ndb_subquery.result39
-rw-r--r--mysql-test/r/ndb_trigger.result31
-rw-r--r--mysql-test/t/ndb_alter_table.test27
-rw-r--r--mysql-test/t/ndb_basic.test53
-rw-r--r--mysql-test/t/ndb_cursor.test46
-rw-r--r--mysql-test/t/ndb_sp.test43
-rw-r--r--mysql-test/t/ndb_subquery.test18
-rw-r--r--mysql-test/t/ndb_trigger.test30
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