Below is the list of changes that have just been committed into a local
5.1 repository of ksm. When ksm does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2006-11-14 16:08:42+03:00, skozlov@stripped +4 -0
updated by WL#2862
mysql-test/r/ndb_basic2.result@stripped, 2006-11-14 16:08:39+03:00, skozlov@stripped +406 -0
updated result
mysql-test/r/ndb_basic2.result@stripped, 2006-11-14 16:08:39+03:00, skozlov@stripped +0 -0
mysql-test/r/ndb_partition_range.result@stripped, 2006-11-14 16:08:39+03:00, skozlov@stripped +14 -0
updated result
mysql-test/t/ndb_basic2.test@stripped, 2006-11-14 16:08:39+03:00, skozlov@stripped +318 -0
updated by WL#2862
mysql-test/t/ndb_basic2.test@stripped, 2006-11-14 16:08:39+03:00, skozlov@stripped +0 -0
mysql-test/t/ndb_partition_range.test@stripped, 2006-11-14 16:08:39+03:00, skozlov@stripped +7 -1
updated by WL#2862
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: skozlov
# Host: virtop.localdomain
# Root: /home/ksm/mysql-5.1/mysql-5.1
--- 1.3/mysql-test/r/ndb_partition_range.result 2006-11-14 16:08:54 +03:00
+++ 1.4/mysql-test/r/ndb_partition_range.result 2006-11-14 16:08:54 +03:00
@@ -46,6 +46,20 @@
1 1 1
6 1 1
10 1 1
+alter table t1 add partition (partition x4 values less than (30));
+select * from information_schema.partitions where table_name= 't1';
+TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME
+NULL test t1 x1 NULL 1 NULL RANGE NULL a NULL 5 0 0 0 NULL 0 0 NULL NULL NULL NULL default default default
+NULL test t1 x2 NULL 2 NULL RANGE NULL a NULL 10 0 0 0 NULL 0 0 NULL NULL NULL NULL default default default
+NULL test t1 x3 NULL 3 NULL RANGE NULL a NULL 20 0 0 0 NULL 0 0 NULL NULL NULL NULL default default default
+NULL test t1 x4 NULL 4 NULL RANGE NULL a NULL 30 0 0 0 NULL 0 0 NULL NULL NULL NULL default default default
+alter table t1 reorganize partition x4 into (partition x4 values less than (40));
+select * from information_schema.partitions where table_name= 't1';
+TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME
+NULL test t1 x1 NULL 1 NULL RANGE NULL a NULL 5 0 0 0 NULL 0 0 NULL NULL NULL NULL default default default
+NULL test t1 x2 NULL 2 NULL RANGE NULL a NULL 10 0 0 0 NULL 0 0 NULL NULL NULL NULL default default default
+NULL test t1 x3 NULL 3 NULL RANGE NULL a NULL 20 0 0 0 NULL 0 0 NULL NULL NULL NULL default default default
+NULL test t1 x4 NULL 4 NULL RANGE NULL a NULL 40 0 0 0 NULL 0 0 NULL NULL NULL NULL default default default
drop table t1;
CREATE TABLE t1 (
a int not null,
--- 1.2/mysql-test/t/ndb_partition_range.test 2006-11-14 16:08:54 +03:00
+++ 1.3/mysql-test/t/ndb_partition_range.test 2006-11-14 16:08:54 +03:00
@@ -15,7 +15,7 @@
--enable_warnings
#
-# Partition by range, basic
+# Partition by range, basic plus add/drop partition
#
CREATE TABLE t1 (
a int not null,
@@ -48,6 +48,12 @@
select * from t1 where a in (1,6,10,21) order by a;
select * from t1 where b=1 and a in (1,6,10,21) order by a;
+alter table t1 add partition (partition x4 values less than (30));
+select * from information_schema.partitions where table_name= 't1';
+alter table t1 reorganize partition x4 into (partition x4 values less than (40));
+select * from information_schema.partitions where table_name= 't1';
+#alter table t1 drop partition x4;
+#select * from information_schema.partitions where table_name= 't1';
drop table t1;
#
--- New file ---
+++ mysql-test/r/ndb_basic2.result 06/11/14 16:08:39
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7;
DROP DATABASE IF EXISTS mysqltest;
CREATE TABLE t1 (
a INT NOT NULL PRIMARY KEY,
b INT NOT NULL DEFAULT 0,
c VARCHAR(254)
) ENGINE=NDB;
INSERT INTO t1 VALUES (1, 10, 'test 1-10'), (2, 20, 'test 2-20');
ALTER TABLE t1 ADD COLUMN b1 BLOB AFTER b;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL DEFAULT '0',
`b1` blob,
`c` varchar(254) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
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;
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;
Warnings:
Note 1050 Table 't1' already exists
DROP TABLE t1;
CREATE TABLE t1 (
a TINYINT NOT NULL PRIMARY KEY,
b REAL,
c NUMERIC,
d TIME,
e BINARY,
f TINYBLOB,
g MEDIUMBLOB
) ENGINE=NDB;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` tinyint(4) NOT NULL,
`b` double DEFAULT NULL,
`c` decimal(10,0) DEFAULT NULL,
`d` time DEFAULT NULL,
`e` binary(1) DEFAULT NULL,
`f` tinyblob,
`g` mediumblob,
PRIMARY KEY (`a`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
Warnings:
Note 1051 Unknown table 't2'
CREATE TABLE t1 (
a INT NOT NULL PRIMARY KEY,
b INT NOT NULL DEFAULT 0,
c VARCHAR(254)
) ENGINE=NDB;
CREATE TABLE t2 (
a INT NOT NULL PRIMARY KEY,
b INT NOT NULL DEFAULT 0,
c VARCHAR(254)
) ENGINE=NDB;
SHOW TABLES;
Tables_in_test
t1
t2
RENAME TABLE t1 TO t10, t2 TO t20;
SHOW TABLES;
Tables_in_test
t10
t20
DROP TABLE t10,t20;
CREATE TABLE t1 (
a INT NOT NULL PRIMARY KEY,
b INT NOT NULL,
c INT NOT NULL
) ENGINE=NDB;
INSERT INTO t1 VALUES (1,1,1), (2,2,2), (3,3,2);
CREATE UNIQUE INDEX t1_index_b ON t1(b);
CREATE UNIQUE INDEX t1_index_c ON t1(c);
ERROR 23000: Can't write, because of unique constraint, to table 't1'
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 3 NULL NULL BTREE
t1 0 t1_index_b 1 b A 3 NULL NULL BTREE
DROP TABLE t1;
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;
INSERT INTO t1 VALUES (1,10), (2,10), (3,30), (4, 40), (5, 40);
SELECT * FROM t1 ORDER BY a;
a b
1 10
2 10
3 30
4 40
5 40
SELECT * FROM t1 GROUP BY b ORDER BY a;
a b
1 10
3 30
5 40
SELECT * INTO OUTFILE '../tmp/ndb_basic2.out'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM t1;
SELECT * INTO DUMPFILE '../tmp/ndb_basic2.dump' FROM t1 WHERE a = 1;
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);
SELECT * FROM t1 WHERE a = ANY (SELECT c FROM t2 WHERE c = 1) ORDER BY a;
a b
1 10
SELECT * FROM t1 WHERE a IN (SELECT c FROM t2 WHERE c = 1) ORDER BY a;
a b
1 10
SELECT * FROM t1 WHERE a <> SOME (SELECT c FROM t2 WHERE c = 1) ORDER BY a;
a b
2 10
3 30
4 30
SELECT * FROM t1 WHERE a > ALL (SELECT c FROM t2 WHERE c = 1) ORDER BY a;
a b
2 10
3 30
4 30
SELECT * FROM t1 WHERE ROW(1,10) = (SELECT c,d FROM t2 WHERE c = 1) ORDER BY a;
a b
1 10
2 10
3 30
4 30
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE c = 1) ORDER BY a;
a b
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
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,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;
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;
//
CALL test_cursor();
SELECT * FROM t2 ORDER BY a;
a b
1 10
2 20
3 30
4 40
DROP TABLE t1,t2;
CREATE TABLE t1 (
a INT NOT NULL PRIMARY KEY,
b INT
) ENGINE=NDB;
CREATE TABLE t2 (
a INT NOT NULL PRIMARY KEY,
b INT
) ENGINE=NDB;
INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4, 4);
INSERT INTO t2 VALUES (1,0);
CREATE TRIGGER test_tr1 AFTER UPDATE ON t1
FOR EACH ROW BEGIN
UPDATE t2 SET b = b+1;
END;
//
CREATE TRIGGER test_tr2 AFTER DELETE ON t1
FOR EACH ROW BEGIN
UPDATE t2 SET b = b+1;
END;
//
UPDATE t1 SET b = 10 WHERE a = 1;
SELECT * FROM t2 ORDER BY a;
a b
1 1
UPDATE t2 SET b = 0;
DELETE FROM t1 WHERE a = 1;
SELECT * FROM t2 ORDER BY a;
a b
1 1
DROP TRIGGER test_tr1;
DROP TRIGGER test_tr2;
DROP TABLE t1,t2;
CREATE TABLE t1 (
a INT NOT NULL PRIMARY KEY,
b INT
) ENGINE=NDB;
INSERT INTO t1 VALUES (1,100), (2,200), (3,100), (4, 200);
CREATE VIEW test_view AS SELECT * FROM t1 WHERE b = 100 ORDER BY a;
SELECT * FROM test_view;
a b
1 100
3 100
ALTER VIEW test_view AS SELECT * FROM t1 WHERE b = 200 ORDER BY a;
SELECT * FROM test_view;
a b
2 200
4 200
CREATE OR REPLACE VIEW test_view AS SELECT * FROM t1 WHERE a < 3 ORDER BY a;
SELECT * FROM test_view;
a b
1 100
2 200
DROP VIEW IF EXISTS test_view_2;
DROP VIEW test_view;
End of 5.1 tests
--- New file ---
+++ mysql-test/t/ndb_basic2.test 06/11/14 16:08:39
##############################################################
# Author: Serge Kozlov
# Date: 2006-11-01
# Purpose: Test some SQL queries missed in other ndb tests
##############################################################
--source include/have_ndb.inc
--source include/not_embedded.inc
--disable_warnings
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7;
DROP DATABASE IF EXISTS mysqltest;
--enable_warnings
# ALTER TABLE ... ADD COLUMN
CREATE TABLE t1 (
a INT NOT NULL PRIMARY KEY,
b INT NOT NULL DEFAULT 0,
c VARCHAR(254)
) ENGINE=NDB;
INSERT INTO t1 VALUES (1, 10, 'test 1-10'), (2, 20, 'test 2-20');
ALTER TABLE t1 ADD COLUMN b1 BLOB AFTER b;
SHOW CREATE TABLE t1;
DROP TABLE t1;
# ALTER TABLE ... 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 TABLE ... 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 TABLE .. 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;
# CREATE TABLE IF NOT EXISTS
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 LIKE
#CREATE TABLE t2 LIKE t1;
DROP TABLE t1;
# CREATE TABLE ...
CREATE TABLE t1 (
a TINYINT NOT NULL PRIMARY KEY,
b REAL,
c NUMERIC,
d TIME,
e BINARY,
f TINYBLOB,
g MEDIUMBLOB
) ENGINE=NDB;
SHOW CREATE TABLE t1;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
# Multi RENAME
CREATE TABLE t1 (
a INT NOT NULL PRIMARY KEY,
b INT NOT NULL DEFAULT 0,
c VARCHAR(254)
) ENGINE=NDB;
CREATE TABLE t2 (
a INT NOT NULL PRIMARY KEY,
b INT NOT NULL DEFAULT 0,
c VARCHAR(254)
) ENGINE=NDB;
SHOW TABLES;
RENAME TABLE t1 TO t10, t2 TO t20;
SHOW TABLES;
DROP TABLE t10,t20;
# CREATE UNIQUE INDEX;
CREATE TABLE t1 (
a INT NOT NULL PRIMARY KEY,
b INT NOT NULL,
c INT NOT NULL
) ENGINE=NDB;
INSERT INTO t1 VALUES (1,1,1), (2,2,2), (3,3,2);
CREATE UNIQUE INDEX t1_index_b ON t1(b);
--error 1169
CREATE UNIQUE INDEX t1_index_c ON t1(c);
SHOW INDEX FROM t1;
DROP TABLE t1;
# 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;
# SELECT
CREATE TABLE t1 (
a INT NOT NULL PRIMARY KEY,
b INT NOT NULL
) ENGINE=NDB;
INSERT INTO t1 VALUES (1,10), (2,10), (3,30), (4, 40), (5, 40);
SELECT * FROM t1 ORDER BY a;
SELECT * FROM t1 GROUP BY b ORDER BY a;
SELECT * INTO OUTFILE '../tmp/ndb_basic2.out'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM t1;
SELECT * INTO DUMPFILE '../tmp/ndb_basic2.dump' FROM t1 WHERE a = 1;
DROP TABLE t1;
# SUBQUERY
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);
SELECT * FROM t1 WHERE a = ANY (SELECT c FROM t2 WHERE c = 1) ORDER BY a;
SELECT * FROM t1 WHERE a IN (SELECT c FROM t2 WHERE c = 1) ORDER BY a;
SELECT * FROM t1 WHERE a <> SOME (SELECT c FROM t2 WHERE c = 1) ORDER BY a;
SELECT * FROM t1 WHERE a > ALL (SELECT c FROM t2 WHERE c = 1) ORDER BY a;
SELECT * FROM t1 WHERE ROW(1,10) = (SELECT c,d FROM t2 WHERE c = 1) ORDER BY a;
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE c = 1) ORDER BY a;
# UPDATE
--error 1062
UPDATE t1 SET a = 1 WHERE a = 3;
SELECT * FROM t1 ORDER BY a;
#UPDATE IGNORE 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;
# PROCEDURE
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;
# CURSORS
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 ;//
CALL test_cursor();
SELECT * FROM t2 ORDER BY a;
DROP TABLE t1,t2;
# TRIGGER
CREATE TABLE t1 (
a INT NOT NULL PRIMARY KEY,
b INT
) ENGINE=NDB;
CREATE TABLE t2 (
a INT NOT NULL PRIMARY KEY,
b INT
) ENGINE=NDB;
INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4, 4);
INSERT INTO t2 VALUES (1,0);
DELIMITER //;
CREATE TRIGGER test_tr1 AFTER UPDATE ON t1
FOR EACH ROW BEGIN
UPDATE t2 SET b = b+1;
END;
//
CREATE TRIGGER test_tr2 AFTER DELETE ON t1
FOR EACH ROW BEGIN
UPDATE t2 SET b = b+1;
END;
//
DELIMITER ;//
UPDATE t1 SET b = 10 WHERE a = 1;
SELECT * FROM t2 ORDER BY a;
UPDATE t2 SET b = 0;
DELETE FROM t1 WHERE a = 1;
SELECT * FROM t2 ORDER BY a;
DROP TRIGGER test_tr1;
DROP TRIGGER test_tr2;
DROP TABLE t1,t2;
# VIEW
CREATE TABLE t1 (
a INT NOT NULL PRIMARY KEY,
b INT
) ENGINE=NDB;
INSERT INTO t1 VALUES (1,100), (2,200), (3,100), (4, 200);
CREATE VIEW test_view AS SELECT * FROM t1 WHERE b = 100 ORDER BY a;
SELECT * FROM test_view;
ALTER VIEW test_view AS SELECT * FROM t1 WHERE b = 200 ORDER BY a;
SELECT * FROM test_view;
CREATE OR REPLACE VIEW test_view AS SELECT * FROM t1 WHERE a < 3 ORDER BY a;
SELECT * FROM test_view;
--disable_warnings
DROP VIEW IF EXISTS test_view_2;
--enable_warnings
DROP VIEW test_view;
--echo End of 5.1 tests
| Thread |
|---|
| • bk commit into 5.1 tree (skozlov:1.2360) | Serge Kozlov | 14 Nov |
