diff options
Diffstat (limited to 'mysql-test/suite/parts/r/partition_auto_increment_ndb.result')
| -rw-r--r-- | mysql-test/suite/parts/r/partition_auto_increment_ndb.result | 1047 |
1 files changed, 0 insertions, 1047 deletions
diff --git a/mysql-test/suite/parts/r/partition_auto_increment_ndb.result b/mysql-test/suite/parts/r/partition_auto_increment_ndb.result deleted file mode 100644 index 40387b9777a..00000000000 --- a/mysql-test/suite/parts/r/partition_auto_increment_ndb.result +++ /dev/null @@ -1,1047 +0,0 @@ -SET new=on; -DROP TABLE IF EXISTS t1; -# test without partitioning for reference -CREATE TABLE t1 ( -c1 INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY (c1)) -ENGINE='NDB'; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' -AND TABLE_NAME='t1'; -AUTO_INCREMENT -1 -INSERT INTO t1 VALUES (2); -INSERT INTO t1 VALUES (4); -INSERT INTO t1 VALUES (NULL); -SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' -AND TABLE_NAME='t1'; -AUTO_INCREMENT -6 -SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' -AND TABLE_NAME='t1'; -AUTO_INCREMENT -6 -INSERT INTO t1 VALUES (0); -INSERT INTO t1 VALUES (5), (16); -INSERT INTO t1 VALUES (17); -INSERT INTO t1 VALUES (19), (NULL); -INSERT INTO t1 VALUES (NULL), (10), (NULL); -INSERT INTO t1 VALUES (NULL); -SET INSERT_ID = 30; -INSERT INTO t1 VALUES (NULL); -SET INSERT_ID = 29; -INSERT INTO t1 VALUES (NULL), (NULL), (NULL); -INSERT INTO t1 VALUES (NULL); -UPDATE t1 SET c1 = 50 WHERE c1 = 17; -UPDATE t1 SET c1 = 51 WHERE c1 = 19; -FLUSH TABLES; -UPDATE t1 SET c1 = 40 WHERE c1 = 50; -SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' - AND TABLE_NAME='t1'; -AUTO_INCREMENT -52 -UPDATE t1 SET c1 = NULL WHERE c1 = 4; -Warnings: -Warning 1048 Column 'c1' cannot be null -INSERT INTO t1 VALUES (NULL); -INSERT INTO t1 VALUES (NULL); -SELECT * FROM t1 ORDER BY c1; -c1 -0 -2 -5 -6 -10 -20 -21 -22 -23 -24 -30 -40 -51 -52 -53 -DROP TABLE t1; -CREATE TABLE t1 ( -c1 INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY (c1)) -ENGINE='NDB'; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -FLUSH TABLE; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -INSERT INTO t1 VALUES (4); -FLUSH TABLE; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -INSERT INTO t1 VALUES (NULL); -FLUSH TABLE; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -DELETE FROM t1; -INSERT INTO t1 VALUES (NULL); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -SELECT * FROM t1 ORDER BY c1; -c1 -6 -TRUNCATE TABLE t1; -INSERT INTO t1 VALUES (NULL); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -SELECT * FROM t1 ORDER BY c1; -c1 -1 -INSERT INTO t1 VALUES (100); -INSERT INTO t1 VALUES (NULL); -DELETE FROM t1 WHERE c1 >= 100; -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize note The storage engine for the table doesn't support optimize -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -DROP TABLE t1; -# Simple test with NULL -CREATE TABLE t1 ( -c1 INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY (c1)) -ENGINE='NDB' -PARTITION BY HASH(c1) -PARTITIONS 2; -INSERT INTO t1 VALUES (NULL); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -SELECT * FROM t1; -c1 -1 -DROP TABLE t1; -# Test with sql_mode and first insert as 0 -CREATE TABLE t1 ( -c1 INT, -c2 INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY (c2)) -ENGINE='NDB' -PARTITION BY HASH(c2) -PARTITIONS 2; -INSERT INTO t1 VALUES (1, NULL); -INSERT INTO t1 VALUES (1, 1), (99, 99); -INSERT INTO t1 VALUES (1, NULL); -SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; -INSERT INTO t1 VALUES (1, 0); -SELECT * FROM t1 ORDER BY c1, c2; -c1 c2 -1 0 -1 1 -1 100 -DROP TABLE t1; -CREATE TABLE t1 ( -c1 INT, -c2 INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY (c2)) -ENGINE='NDB' -PARTITION BY HASH(c2) -PARTITIONS 2; -INSERT INTO t1 VALUES (1, 0); -INSERT INTO t1 VALUES (1, 1), (1, NULL); -INSERT INTO t1 VALUES (2, NULL), (4, 7); -INSERT INTO t1 VALUES (1, NULL); -SELECT * FROM t1 ORDER BY c1, c2; -c1 c2 -1 0 -1 1 -1 2 -1 8 -2 3 -4 7 -SET @@session.sql_mode = ''; -DROP TABLE t1; -# Simple test with NULL, 0 and explicit values both incr. and desc. -CREATE TABLE t1 ( -c1 INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY (c1)) -ENGINE='NDB' -PARTITION BY HASH(c1) -PARTITIONS 2; -INSERT INTO t1 VALUES (2), (4), (NULL); -INSERT INTO t1 VALUES (0); -INSERT INTO t1 VALUES (5), (16); -INSERT INTO t1 VALUES (17), (19), (NULL); -INSERT INTO t1 VALUES (NULL), (10), (NULL); -INSERT INTO t1 VALUES (NULL), (9); -INSERT INTO t1 VALUES (59), (55); -INSERT INTO t1 VALUES (NULL), (90); -INSERT INTO t1 VALUES (NULL); -UPDATE t1 SET c1 = 150 WHERE c1 = 17; -UPDATE t1 SET c1 = 151 WHERE c1 = 19; -FLUSH TABLES; -UPDATE t1 SET c1 = 140 WHERE c1 = 150; -SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' - AND TABLE_NAME='t1'; -AUTO_INCREMENT -152 -UPDATE t1 SET c1 = NULL WHERE c1 = 4; -Warnings: -Warning 1048 Column 'c1' cannot be null -INSERT INTO t1 VALUES (NULL); -INSERT INTO t1 VALUES (NULL); -SELECT * FROM t1 ORDER BY c1; -c1 -0 -2 -5 -6 -9 -10 -20 -21 -22 -23 -55 -59 -60 -90 -91 -140 -151 -152 -153 -DROP TABLE t1; -# Test with auto_increment_increment and auto_increment_offset. -CREATE TABLE t1 ( -c1 INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY (c1)) -ENGINE='NDB' -PARTITION BY HASH(c1) -PARTITIONS 2; -SET @@session.auto_increment_increment = 10; -SET @@session.auto_increment_offset = 5; -INSERT INTO t1 VALUES (1); -INSERT INTO t1 VALUES (NULL), (NULL), (NULL); -SET @@session.auto_increment_increment = 5; -SET @@session.auto_increment_offset = 3; -INSERT INTO t1 VALUES (NULL); -INSERT INTO t1 VALUES (33 + 1); -INSERT INTO t1 VALUES (NULL); -INSERT INTO t1 VALUES (38 + 2); -INSERT INTO t1 VALUES (NULL); -INSERT INTO t1 VALUES (43 + 3); -INSERT INTO t1 VALUES (NULL); -INSERT INTO t1 VALUES (48 + 4); -INSERT INTO t1 VALUES (NULL); -INSERT INTO t1 VALUES (53 + 5); -INSERT INTO t1 VALUES (NULL); -INSERT INTO t1 VALUES (63 + 6); -INSERT INTO t1 VALUES (NULL); -SET @@session.auto_increment_increment = 1; -SET @@session.auto_increment_offset = 1; -SELECT * FROM t1 ORDER BY c1; -c1 -1 -5 -15 -25 -33 -34 -38 -40 -43 -46 -48 -52 -53 -58 -63 -69 -73 -DROP TABLE t1; -# Test reported auto_increment value -CREATE TABLE t1 ( -c1 INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY (c1)) -ENGINE='NDB' -PARTITION BY HASH (c1) -PARTITIONS 2; -SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' -AND TABLE_NAME='t1'; -AUTO_INCREMENT -1 -INSERT INTO t1 VALUES (2); -SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' -AND TABLE_NAME='t1'; -AUTO_INCREMENT -3 -INSERT INTO t1 VALUES (4); -INSERT INTO t1 VALUES (NULL); -SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' -AND TABLE_NAME='t1'; -AUTO_INCREMENT -6 -INSERT INTO t1 VALUES (NULL); -INSERT INTO t1 VALUES (17); -INSERT INTO t1 VALUES (19); -INSERT INTO t1 VALUES (NULL); -INSERT INTO t1 VALUES (NULL); -SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' -AND TABLE_NAME='t1'; -AUTO_INCREMENT -22 -SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' -AND TABLE_NAME='t1'; -AUTO_INCREMENT -22 -INSERT INTO t1 VALUES (10); -SELECT * FROM t1 ORDER BY c1; -c1 -2 -4 -5 -6 -10 -17 -19 -20 -21 -INSERT INTO t1 VALUES (NULL); -SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' -AND TABLE_NAME='t1'; -AUTO_INCREMENT -23 -INSERT INTO t1 VALUES (NULL); -INSERT INTO t1 VALUES (15); -INSERT INTO t1 VALUES (NULL); -SELECT * FROM t1 ORDER BY c1; -c1 -2 -4 -5 -6 -10 -15 -17 -19 -20 -21 -22 -23 -24 -INSERT INTO t1 VALUES (NULL); -DELETE FROM t1; -INSERT INTO t1 VALUES (NULL); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -SELECT * FROM t1 ORDER BY c1; -c1 -26 -TRUNCATE TABLE t1; -INSERT INTO t1 VALUES (NULL); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -SELECT * FROM t1 ORDER BY c1; -c1 -1 -INSERT INTO t1 VALUES (100); -INSERT INTO t1 VALUES (NULL); -DELETE FROM t1 WHERE c1 >= 100; -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize note The storage engine for the table doesn't support optimize -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -DROP TABLE t1; -# Test with two threads -# con default -CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) -ENGINE = 'NDB' -PARTITION BY HASH(c1) -PARTITIONS 2; -INSERT INTO t1 (c1) VALUES (2); -INSERT INTO t1 (c1) VALUES (4); -# con1 -INSERT INTO t1 (c1) VALUES (NULL); -INSERT INTO t1 (c1) VALUES (10); -# con default -INSERT INTO t1 (c1) VALUES (NULL); -INSERT INTO t1 (c1) VALUES (NULL); -INSERT INTO t1 (c1) VALUES (19); -INSERT INTO t1 (c1) VALUES (21); -# con1 -INSERT INTO t1 (c1) VALUES (NULL); -# con default -INSERT INTO t1 (c1) VALUES (16); -# con1 -INSERT INTO t1 (c1) VALUES (NULL); -# con default -INSERT INTO t1 (c1) VALUES (NULL); -SELECT * FROM t1 ORDER BY c1; -c1 -2 -4 -5 -10 -11 -12 -16 -19 -21 -22 -23 -24 -DROP TABLE t1; -# Test with two threads + start transaction NO PARTITIONING -# con default -CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) -ENGINE = 'NDB'; -START TRANSACTION; -INSERT INTO t1 (c1) VALUES (2); -INSERT INTO t1 (c1) VALUES (4); -# con1 -START TRANSACTION; -INSERT INTO t1 (c1) VALUES (NULL); -INSERT INTO t1 (c1) VALUES (10); -# con default -INSERT INTO t1 (c1) VALUES (NULL); -INSERT INTO t1 (c1) VALUES (NULL); -INSERT INTO t1 (c1) VALUES (19); -INSERT INTO t1 (c1) VALUES (21); -# con1 -INSERT INTO t1 (c1) VALUES (NULL); -# con default -INSERT INTO t1 (c1) VALUES (16); -# con1 -INSERT INTO t1 (c1) VALUES (NULL); -SELECT * FROM t1 ORDER BY c1; -c1 -5 -10 -22 -23 -COMMIT; -SELECT * FROM t1 ORDER BY c1; -c1 -5 -10 -22 -23 -# con default -INSERT INTO t1 (c1) VALUES (NULL); -SELECT * FROM t1 ORDER BY c1; -c1 -2 -4 -5 -10 -11 -12 -16 -19 -21 -22 -23 -24 -COMMIT; -SELECT * FROM t1 ORDER BY c1; -c1 -2 -4 -5 -10 -11 -12 -16 -19 -21 -22 -23 -24 -DROP TABLE t1; -# Test with two threads + start transaction -# con default -CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) -ENGINE = 'NDB' -PARTITION BY HASH(c1) -PARTITIONS 2; -START TRANSACTION; -INSERT INTO t1 (c1) VALUES (2); -INSERT INTO t1 (c1) VALUES (4); -# con1 -START TRANSACTION; -INSERT INTO t1 (c1) VALUES (NULL), (10); -# con default -INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19); -INSERT INTO t1 (c1) VALUES (21); -# con1 -INSERT INTO t1 (c1) VALUES (NULL); -# con default -INSERT INTO t1 (c1) VALUES (16); -# con1 -INSERT INTO t1 (c1) VALUES (NULL); -SELECT * FROM t1 ORDER BY c1; -c1 -5 -10 -22 -23 -COMMIT; -SELECT * FROM t1 ORDER BY c1; -c1 -5 -10 -22 -23 -# con default -INSERT INTO t1 (c1) VALUES (NULL); -SELECT * FROM t1 ORDER BY c1; -c1 -2 -4 -5 -10 -11 -12 -16 -19 -21 -22 -23 -24 -COMMIT; -SELECT * FROM t1 ORDER BY c1; -c1 -2 -4 -5 -10 -11 -12 -16 -19 -21 -22 -23 -24 -DROP TABLE t1; -# Test with another column after -CREATE TABLE t1 ( -c1 INT NOT NULL AUTO_INCREMENT, -c2 INT, -PRIMARY KEY (c1,c2)) -ENGINE = 'NDB' -PARTITION BY HASH(c2) -PARTITIONS 2; -INSERT INTO t1 VALUES (1, 0); -INSERT INTO t1 VALUES (1, 1); -INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3); -INSERT INTO t1 VALUES (NULL, 3); -INSERT INTO t1 VALUES (2, 0), (NULL, 2); -INSERT INTO t1 VALUES (2, 2); -INSERT INTO t1 VALUES (2, 22); -INSERT INTO t1 VALUES (NULL, 2); -SELECT * FROM t1 ORDER BY c1,c2; -c1 c2 -1 0 -1 1 -2 0 -2 1 -2 2 -2 22 -3 2 -4 3 -5 3 -6 2 -7 2 -DROP TABLE t1; -# Test with another column before -CREATE TABLE t1 ( -c1 INT, -c2 INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY (c2)) -ENGINE = 'NDB' -PARTITION BY HASH(c2) -PARTITIONS 2; -INSERT INTO t1 VALUES (1, 0); -INSERT INTO t1 VALUES (1, 1); -INSERT INTO t1 VALUES (1, NULL); -INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0); -INSERT INTO t1 VALUES (2, NULL); -INSERT INTO t1 VALUES (2, 2); -INSERT INTO t1 VALUES (2, 22); -INSERT INTO t1 VALUES (2, NULL); -SELECT * FROM t1 ORDER BY c1,c2; -c1 c2 -1 1 -1 2 -2 3 -2 13 -2 14 -2 22 -2 23 -3 11 -3 12 -DROP TABLE t1; -# Test with auto_increment on secondary column in multi-column-index -CREATE TABLE t1 ( -c1 INT, -c2 INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY (c1,c2)) -ENGINE = 'NDB' -PARTITION BY HASH(c2) -PARTITIONS 2; -INSERT INTO t1 VALUES (1, 0); -INSERT INTO t1 VALUES (1, 1); -INSERT INTO t1 VALUES (1, NULL); -INSERT INTO t1 VALUES (2, NULL); -INSERT INTO t1 VALUES (3, NULL); -INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL); -INSERT INTO t1 VALUES (2, 2); -# ERROR (only OK if Blackhole/NDB) should give ER_DUP_KEY or ER_DUP_ENTRY -# mysql_errno: 0 -INSERT INTO t1 VALUES (2, 22), (2, NULL); -SELECT * FROM t1 ORDER BY c1,c2; -c1 c2 -1 1 -1 2 -2 2 -2 3 -2 6 -2 7 -2 22 -2 23 -3 4 -3 5 -DROP TABLE t1; -# Test AUTO_INCREMENT in CREATE -CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) -ENGINE = 'NDB' -AUTO_INCREMENT = 15 -PARTITION BY HASH(c1) -PARTITIONS 2; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -INSERT INTO t1 (c1) VALUES (4); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -INSERT INTO t1 (c1) VALUES (0); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -INSERT INTO t1 (c1) VALUES (NULL); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -SELECT * FROM t1 ORDER BY c1; -c1 -4 -15 -16 -# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO' -SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; -INSERT INTO t1 (c1) VALUES (300); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -INSERT INTO t1 (c1) VALUES (0); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -INSERT INTO t1 (c1) VALUES (NULL); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -SELECT * FROM t1 ORDER BY c1; -c1 -0 -4 -15 -16 -300 -301 -SET @@session.sql_mode = ''; -DROP TABLE t1; -# Test SET INSERT_ID -CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) -ENGINE = 'NDB' -PARTITION BY HASH(c1) -PARTITIONS 2; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -INSERT INTO t1 (c1) VALUES (NULL); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -SELECT * FROM t1; -c1 -1 -SET INSERT_ID = 23; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -INSERT INTO t1 (c1) VALUES (NULL); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -SET INSERT_ID = 22; -INSERT INTO t1 VALUES (NULL), (NULL), (NULL); -INSERT INTO t1 VALUES (NULL); -SELECT * FROM t1 ORDER BY c1; -c1 -1 -2 -23 -DROP TABLE t1; -# Testing with FLUSH TABLE -CREATE TABLE t1 ( -c1 INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY (c1)) -ENGINE='NDB' -PARTITION BY HASH(c1) -PARTITIONS 2; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -FLUSH TABLE; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -INSERT INTO t1 VALUES (4); -FLUSH TABLE; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -INSERT INTO t1 VALUES (NULL); -FLUSH TABLE; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) NOT NULL AUTO_INCREMENT, - PRIMARY KEY (`c1`) -) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (c1) -PARTITIONS 2 */ -SELECT * FROM t1 ORDER BY c1; -c1 -4 -5 -DROP TABLE t1; -############################################################################# -# Bug #45823 - Assertion failure in file row/row0mysql.c line 1386 -# Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31 -############################################################################## -# Inserting negative autoincrement values into a partition table (partitions >= 4) -CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), -c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4; -INSERT INTO t(c2) VALUES (10); -INSERT INTO t(c2) VALUES (20); -INSERT INTO t VALUES (-1,-10); -INSERT INTO t(c2) VALUES (30); -INSERT INTO t(c2) VALUES (40); -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 --1 -10 -1 10 -2 20 -3 30 -4 40 -DROP TABLE t; -# Reading from a partition table (partitions >= 2 ) after inserting a negative -# value into the auto increment column -CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), -c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 2; -INSERT INTO t VALUES (-2,-20); -INSERT INTO t(c2) VALUES (30); -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 --2 -20 -1 30 -DROP TABLE t; -# Inserting negative auto increment value into a partition table (partitions >= 2) -# auto increment value > 2. -CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), -c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 2; -INSERT INTO t VALUES (-4,-20); -INSERT INTO t(c2) VALUES (30); -INSERT INTO t(c2) VALUES (40); -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 --4 -20 -1 30 -2 40 -DROP TABLE t; -# Inserting -1 into autoincrement column of a partition table (partition >= 4) -CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), -c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4; -INSERT INTO t(c2) VALUES (10); -INSERT INTO t(c2) VALUES (20); -INSERT INTO t VALUES (-1,-10); -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 --1 -10 -1 10 -2 20 -INSERT INTO t(c2) VALUES (30); -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 --1 -10 -1 10 -2 20 -3 30 -DROP TABLE t; -# Deleting from an auto increment table after inserting negative values -CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), -c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4; -INSERT INTO t(c2) VALUES (10); -INSERT INTO t(c2) VALUES (20); -INSERT INTO t VALUES (-1,-10); -INSERT INTO t(c2) VALUES (30); -INSERT INTO t VALUES (-3,-20); -INSERT INTO t(c2) VALUES (40); -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 --3 -20 --1 -10 -1 10 -2 20 -3 30 -4 40 -DELETE FROM t WHERE c1 > 1; -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 --3 -20 --1 -10 -1 10 -DROP TABLE t; -# Inserting a positive value that exceeds maximum allowed value for an -# Auto Increment column (positive maximum) -CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), -c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4; -INSERT INTO t(c2) VALUES (10); -INSERT INTO t(c2) VALUES (20); -INSERT INTO t VALUES (126,30); -INSERT INTO t VALUES (127,40); -INSERT INTO t VALUES (128,50); -ERROR 23000: Duplicate entry '127' for key 'PRIMARY' -INSERT INTO t VALUES (129,60); -ERROR 23000: Duplicate entry '127' for key 'PRIMARY' -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 -1 10 -2 20 -126 30 -127 40 -DROP TABLE t; -# Inserting a negative value that goes below minimum allowed value for an -# Auto Increment column (negative minimum) -CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), -c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4; -INSERT INTO t(c2) VALUES (10); -INSERT INTO t(c2) VALUES (20); -INSERT INTO t VALUES (-127,30); -INSERT INTO t VALUES (-128,40); -INSERT INTO t VALUES (-129,50); -ERROR 23000: Duplicate entry '-128' for key 'PRIMARY' -INSERT INTO t VALUES (-130,60); -ERROR 23000: Duplicate entry '-128' for key 'PRIMARY' -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 --128 40 --127 30 -1 10 -2 20 -DROP TABLE t; -# Updating the partition table with a negative Auto Increment value -CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), -c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4; -INSERT INTO t(c2) VALUES (10); -INSERT INTO t(c2) VALUES (20); -INSERT INTO t VALUES (-1,-10); -INSERT INTO t(c2) VALUES (30); -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 --1 -10 -1 10 -2 20 -3 30 -UPDATE t SET c1 = -6 WHERE c1 = 2; -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 --6 20 --1 -10 -1 10 -3 30 -INSERT INTO t(c2) VALUES (40); -INSERT INTO t(c2) VALUES (50); -UPDATE t SET c1 = -6 WHERE c1 = 2; -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 --6 20 --1 -10 -1 10 -3 30 -4 40 -5 50 -DROP TABLE t; -# Updating the partition table with a value that crosses the upper limits -# on both the positive and the negative side. -CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), -c2 INT) ENGINE='NDB' PARTITION BY HASH(c1) PARTITIONS 4; -INSERT INTO t(c2) VALUES (10); -INSERT INTO t(c2) VALUES (20); -INSERT INTO t VALUES (126,30); -INSERT INTO t VALUES (127,40); -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 -1 10 -2 20 -126 30 -127 40 -UPDATE t SET c1 = 130 where c1 = 127; -Warnings: -Warning 1264 Out of range value for column 'c1' at row 1 -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 -1 10 -2 20 -126 30 -127 40 -UPDATE t SET c1 = -140 where c1 = 126; -Warnings: -Warning 1264 Out of range value for column 'c1' at row 1 -SELECT * FROM t ORDER BY c1 ASC; -c1 c2 --128 30 -1 10 -2 20 -127 40 -DROP TABLE t; -############################################################################## |