Bug#26342 auto_increment_increment AND auto_increment_offset REALLY REALLY anger NDB cluster, implemented support for auto_increment_offset and auto_increment_increment for Ndb

2 files changed, 306 insertions, 0 deletions

diff --git a/mysql-test/r/ndb_insert.result b/mysql-test/r/ndb_insert.result
index e7275bde2b8..b5349ecb59c 100644
--- a/mysql-test/r/ndb_insert.result
+++ b/mysql-test/r/ndb_insert.result
@@ -657,3 +657,172 @@ a	b
 2	NULL
 3	NULL
 drop table t1;
+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;
+pk	b	c
+1	1	0
+11	2	1
+21	3	2
+SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
+COUNT(t1.pk)
+3
+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;
+pk	b	c
+5	1	0
+15	2	1
+25	3	2
+27	4	3
+35	5	4
+99	6	5
+105	7	6
+SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
+COUNT(t1.pk)
+7
+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;
+pk	b	c
+1	1	0
+3	2	1
+5	3	2
+SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
+COUNT(t1.pk)
+3
+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;
+pk	b	c
+7	1	0
+8	2	1
+9	3	2
+SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
+COUNT(t1.pk)
+3
+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;
+pk	b	c
+5	1	0
+15	2	1
+25	3	2
+SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
+COUNT(t1.pk)
+3
+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;
+pk	b	c
+15	1	0
+25	2	1
+35	3	2
+SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
+COUNT(t1.pk)
+3
+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;
+pk	b	c
+5	1	0
+15	2	1
+25	3	2
+SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
+COUNT(t1.pk)
+3
+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;
+pk	b	c
+105	1	0
+115	2	1
+125	3	2
+SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c;
+COUNT(t1.pk)
+3
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/ndb_insert.test b/mysql-test/t/ndb_insert.test
index f346b7dc4ab..b8f00d6f6aa 100644
--- a/mysql-test/t/ndb_insert.test
+++ b/mysql-test/t/ndb_insert.test
@@ -639,4 +639,141 @@ 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