ndb_insert.test

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· TEST 代码 · 共 780 行 · 第 1/3 页

begin;

--error 1022
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);

--error 1296
SELECT * FROM t1 WHERE pk1=10;

--error 1296
SELECT * FROM t1 WHERE pk1=10;

--error 1296
commit;

select * from t1 where pk1=1;
select * from t1 where pk1=10;
select count(*) from t1 where pk1 <= 10 order by pk1;
select count(*) from t1;


#
# Insert duplicate rows, inside transaction
# then try to do another insert
#
begin;

--error 1022
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);

--error 1296
INSERT INTO t1 values (4000, 40, 44);

rollback;

select * from t1 where pk1=1;
select * from t1 where pk1=10;
select count(*) from t1 where pk1 <= 10 order by pk1;
select count(*) from t1;

#
# Insert duplicate rows using "insert .. select" 

#
--error 1022
insert into t1 select * from t1 where b < 10 order by pk1;

DELETE FROM t1 WHERE pk1=2;

begin;
INSERT IGNORE INTO t1 VALUES(1,2,3),(2,3,4);
select * from t1 where pk1 < 3 order by pk1;
rollback;

INSERT IGNORE INTO t1 VALUES(1,2,3),(2,3,4);
select * from t1 where pk1 < 3 order by pk1;

REPLACE INTO t1 values(1, 78, 3);
select * from t1 where pk1=1;

INSERT INTO t1 VALUES(1,1,1),(3,4,5) ON DUPLICATE KEY UPDATE b=79;
select * from t1 where pk1 < 4 order by pk1;

INSERT INTO t1 VALUES(1,1,1),(3,4,5) ON DUPLICATE KEY UPDATE b=pk1+c;
select * from t1 where pk1 < 4 order by pk1;

DELETE FROM t1 WHERE pk1 = 2 OR pk1 = 4 OR pk1 = 6;
INSERT INTO t1 VALUES(1,1,1),(2,2,17),(3,4,5) ON DUPLICATE KEY UPDATE pk1=b;
select * from t1 where pk1 = b and b != c order by pk1;

# Test handling of duplicate unique
DELETE FROM t1;
CREATE UNIQUE INDEX bi ON t1(b);
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);
INSERT INTO t1 VALUES(0,1,0),(21,21,21) ON DUPLICATE KEY UPDATE pk1=b+10,b=b+10;
select * from t1 order by pk1;

DROP TABLE t1;

#
# Bug #6331: problem with 'insert ignore'
#

CREATE TABLE t1(a INT) ENGINE=ndb;
INSERT IGNORE INTO t1 VALUES (1);
INSERT IGNORE INTO t1 VALUES (1);
INSERT IGNORE INTO t1 SELECT a FROM t1;
INSERT IGNORE INTO t1 SELECT a FROM t1;
INSERT IGNORE INTO t1 SELECT a FROM t1;
INSERT IGNORE INTO t1 VALUES (1);
INSERT IGNORE INTO t1 VALUES (1);
SELECT * FROM t1 ORDER BY a;
DELETE FROM t1;
CREATE UNIQUE INDEX ai ON t1(a);
INSERT IGNORE INTO t1 VALUES (1);
INSERT IGNORE INTO t1 VALUES (1);
INSERT IGNORE INTO t1 VALUES (NULL),(2);
SELECT * FROM t1 ORDER BY a;
DROP TABLE t1;

# Ignore and NULL values
CREATE TABLE t1(pk INT NOT NULL PRIMARY KEY, a INT, UNIQUE (a)) ENGINE=ndb;
INSERT IGNORE INTO t1 VALUES (1,1),(2,2),(3,3);
INSERT IGNORE INTO t1 VALUES (4,NULL),(5,NULL),(6,NULL),(7,4);
SELECT * FROM t1 ORDER BY pk;
DROP TABLE t1;

#
# Bug #27980  	INSERT IGNORE wrongly ignores NULLs in unique index
#

create table t1(a int primary key, b int, unique key(b)) engine=ndb;
insert ignore into t1 values (1,0), (2,0), (2,null), (3,null);
select * from t1 order by a;
drop table t1;

# Bug#26342 auto_increment_increment AND auto_increment_offset REALLY REALLY anger NDB cluster

CREATE TABLE t1 (
  pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  b INT NOT NULL,
  c INT NOT NULL UNIQUE
) ENGINE=NDBCLUSTER;

CREATE TABLE t2 (
  pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  b INT NOT NULL,
  c INT NOT NULL UNIQUE
) ENGINE=MYISAM;

SET @@session.auto_increment_increment=10;
INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
SELECT * FROM t1 ORDER BY pk;
SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
TRUNCATE t1; 
TRUNCATE t2;
SET @@session.auto_increment_offset=5;
INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
INSERT INTO t1 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6);
INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
INSERT INTO t2 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6);
SELECT * FROM t1 ORDER BY pk;
SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
TRUNCATE t1; 
TRUNCATE t2;
SET @@session.auto_increment_increment=2;
INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
SELECT * FROM t1 ORDER BY pk;
SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
DROP TABLE t1, t2;

CREATE TABLE t1 (
  pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  b INT NOT NULL,
  c INT NOT NULL UNIQUE
) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7;

CREATE TABLE t2 (
  pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  b INT NOT NULL,
  c INT NOT NULL UNIQUE
) ENGINE=MYISAM AUTO_INCREMENT = 7;

SET @@session.auto_increment_offset=1;
SET @@session.auto_increment_increment=1;
INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
SELECT * FROM t1 ORDER BY pk;
SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
DROP TABLE t1, t2;

CREATE TABLE t1 (
  pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  b INT NOT NULL,
  c INT NOT NULL UNIQUE
) ENGINE=NDBCLUSTER AUTO_INCREMENT = 3;

CREATE TABLE t2 (
  pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  b INT NOT NULL,
  c INT NOT NULL UNIQUE
) ENGINE=MYISAM AUTO_INCREMENT = 3;

SET @@session.auto_increment_offset=5;
SET @@session.auto_increment_increment=10;
INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
SELECT * FROM t1 ORDER BY pk;
SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
DROP TABLE t1, t2;

CREATE TABLE t1 (
  pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  b INT NOT NULL,
  c INT NOT NULL UNIQUE
) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7;

CREATE TABLE t2 (
  pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  b INT NOT NULL,
  c INT NOT NULL UNIQUE
) ENGINE=MYISAM AUTO_INCREMENT = 7;

SET @@session.auto_increment_offset=5;
SET @@session.auto_increment_increment=10;
INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
SELECT * FROM t1 ORDER BY pk;
SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
DROP TABLE t1, t2;

CREATE TABLE t1 (
  pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  b INT NOT NULL,
  c INT NOT NULL UNIQUE
) ENGINE=NDBCLUSTER AUTO_INCREMENT = 5;

CREATE TABLE t2 (
  pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  b INT NOT NULL,
  c INT NOT NULL UNIQUE
) ENGINE=MYISAM AUTO_INCREMENT = 5;

SET @@session.auto_increment_offset=5;
SET @@session.auto_increment_increment=10;
INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
SELECT * FROM t1 ORDER BY pk;
SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
DROP TABLE t1, t2;

CREATE TABLE t1 (
  pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  b INT NOT NULL,
  c INT NOT NULL UNIQUE
) ENGINE=NDBCLUSTER AUTO_INCREMENT = 100;

CREATE TABLE t2 (
  pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  b INT NOT NULL,
  c INT NOT NULL UNIQUE
) ENGINE=MYISAM AUTO_INCREMENT = 100;

SET @@session.auto_increment_offset=5;
SET @@session.auto_increment_increment=10;
INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2);
INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2);
SELECT * FROM t1 ORDER BY pk;
SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
DROP TABLE t1, t2;

# End of 4.1 tests