[ Avaa Bypassed ]

Upload:

Command:

hmhc3928@13.59.58.68: ~ $ 
DROP TABLE IF EXISTS t1;
# test without partitioning for reference
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1))
ENGINE='Memory';
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=MEMORY 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;
UPDATE t1 SET c1 = -1 WHERE c1 = 40;
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
-1
0
2
5
6
10
20
21
22
23
29
30
31
51
52
53
DROP TABLE t1;
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1))
ENGINE='Memory';
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`)
) ENGINE=MEMORY 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=MEMORY 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=MEMORY AUTO_INCREMENT=5 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=MEMORY AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
DELETE FROM t1;
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (-1);
INSERT INTO t1 VALUES (NULL);
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=MEMORY AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
SELECT * FROM t1 ORDER BY c1;
c1
-1
6
7
8
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=MEMORY AUTO_INCREMENT=2 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=MEMORY AUTO_INCREMENT=102 DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE TABLE t1
(a INT NULL AUTO_INCREMENT,
UNIQUE KEY (a))
ENGINE='Memory';
SET LAST_INSERT_ID = 999;
SET INSERT_ID = 0;
INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
999
SELECT * FROM t1;
a
1
INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
999
SELECT * FROM t1;
a
0
UPDATE t1 SET a = 1 WHERE a IS NULL;
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
999
SELECT * FROM t1;
a
0
UPDATE t1 SET a = NULL WHERE a = 1;
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
999
SELECT * FROM t1;
a
0
DROP TABLE t1;
SET INSERT_ID = 1;
# Simple test with NULL
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1))
ENGINE='Memory'
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=MEMORY AUTO_INCREMENT=2 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='Memory'
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	2
DROP TABLE t1;
CREATE TABLE t1 (
c1 INT,
c2 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c2))
ENGINE='Memory'
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 = 'NO_ENGINE_SUBSTITUTION';
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='Memory'
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='Memory'
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='Memory'
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=MEMORY AUTO_INCREMENT=27 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=MEMORY AUTO_INCREMENT=2 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=MEMORY AUTO_INCREMENT=102 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 = 'Memory'
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 = 'Memory';
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
2
4
5
10
11
12
16
19
21
22
23
COMMIT;
SELECT * FROM t1 ORDER BY c1;
c1
2
4
5
10
11
12
16
19
21
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 = 'Memory'
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
2
4
5
10
11
12
16
19
21
22
23
COMMIT;
SELECT * FROM t1 ORDER BY c1;
c1
2
4
5
10
11
12
16
19
21
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 = 'Memory'
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 = 'Memory'
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 = 'Memory'
PARTITION BY HASH(c2)
PARTITIONS 2;
ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
# Test AUTO_INCREMENT in CREATE
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
ENGINE = 'Memory'
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=MEMORY AUTO_INCREMENT=15 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=MEMORY AUTO_INCREMENT=15 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=MEMORY AUTO_INCREMENT=16 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=MEMORY AUTO_INCREMENT=17 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=MEMORY AUTO_INCREMENT=301 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=MEMORY AUTO_INCREMENT=301 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=MEMORY AUTO_INCREMENT=302 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 = 'NO_ENGINE_SUBSTITUTION';
DROP TABLE t1;
# Test SET INSERT_ID
CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
ENGINE = 'Memory'
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=MEMORY 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=MEMORY AUTO_INCREMENT=2 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=MEMORY AUTO_INCREMENT=2 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=MEMORY AUTO_INCREMENT=24 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
22
23
24
DROP TABLE t1;
# Testing with FLUSH TABLE
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (c1))
ENGINE='Memory'
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=MEMORY 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=MEMORY 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=MEMORY AUTO_INCREMENT=5 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=MEMORY AUTO_INCREMENT=6 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='Memory' 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='Memory' 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='Memory' 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='Memory' 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='Memory' 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='Memory' 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);
INSERT INTO t VALUES (129,60);
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='Memory' 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);
INSERT INTO t VALUES (-130,60);
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='Memory' 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='Memory' 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;
CREATE TABLE t1
(a INT NULL AUTO_INCREMENT,
UNIQUE KEY (a))
ENGINE='Memory'
PARTITION BY KEY(a) PARTITIONS 2;
SET LAST_INSERT_ID = 999;
SET INSERT_ID = 0;
INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
999
SELECT * FROM t1;
a
1
INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
999
SELECT * FROM t1;
a
0
UPDATE t1 SET a = 1 WHERE a IS NULL;
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
999
SELECT * FROM t1;
a
0
UPDATE t1 SET a = NULL WHERE a = 1;
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
999
SELECT * FROM t1;
a
0
DROP TABLE t1;
##############################################################################

Filemanager

DIR : / usr/ share/ mysql-test/ suite/ parts/ r/
Name Type Size Permission Actions
part_blocked_sql_func_innodb.result File 159.68 KB 0644
part_blocked_sql_func_myisam.result File 159.68 KB 0644
part_ctype_utf32.result File 355 B 0644
part_supported_sql_func_innodb.result File 238.66 KB 0644
part_supported_sql_func_myisam.result File 238.66 KB 0644
partition-dml-1-1-innodb-modes.result File 19.2 KB 0644
partition-dml-1-1-innodb.result File 8.38 KB 0644
partition-dml-1-1-myisam-modes.result File 19.2 KB 0644
partition-dml-1-1-myisam.result File 8.38 KB 0644
partition-dml-1-10-innodb.result File 5.37 KB 0644
partition-dml-1-10-myisam.result File 5.29 KB 0644
partition-dml-1-11-innodb.result File 7.08 KB 0644
partition-dml-1-11-myisam.result File 7.08 KB 0644
partition-dml-1-2-innodb.result File 6.11 KB 0644
partition-dml-1-2-myisam.result File 6.11 KB 0644
partition-dml-1-3-innodb.result File 7.27 KB 0644
partition-dml-1-4-innodb.result File 5.5 KB 0644
partition-dml-1-5-innodb.result File 5.52 KB 0644
partition-dml-1-6-innodb.result File 5.52 KB 0644
partition-dml-1-7-innodb.result File 6.96 KB 0644
partition-dml-1-8-innodb.result File 6.94 KB 0644
partition-dml-1-9-innodb.result File 18.38 KB 0644
partition-dml-1-9-myisam.result File 18.38 KB 0644
partition_alter1_1_2_innodb.result File 1020.49 KB 0644
partition_alter1_1_2_myisam.result File 303.96 KB 0644
partition_alter1_1_innodb.result File 596.87 KB 0644
partition_alter1_1_myisam.result File 309.05 KB 0644
partition_alter1_2_innodb.result File 1.26 MB 0644
partition_alter1_2_myisam.result File 539.78 KB 0644
partition_alter2_1_1_innodb.result File 718.67 KB 0644
partition_alter2_1_2_innodb.result File 719.61 KB 0644
partition_alter2_1_myisam.result File 875.52 KB 0644
partition_alter2_2_1_innodb.result File 720.91 KB 0644
partition_alter2_2_2_innodb.result File 725.47 KB 0644
partition_alter2_2_myisam.result File 880.39 KB 0644
partition_alter3_innodb.result File 27.19 KB 0644
partition_alter3_myisam.result File 28.01 KB 0644
partition_alter4_innodb.result File 3.12 MB 0644
partition_alter4_myisam.result File 3.18 MB 0644
partition_auto_increment_archive.result File 21.62 KB 0644
partition_auto_increment_blackhole.result File 24.09 KB 0644
partition_auto_increment_innodb.result File 23.72 KB 0644
partition_auto_increment_memory.result File 23.73 KB 0644
partition_auto_increment_myisam.result File 23.89 KB 0644
partition_basic_innodb.result File 1.11 MB 0644
partition_basic_myisam.result File 566.34 KB 0644
partition_basic_symlink_innodb.result File 4.11 KB 0644
partition_basic_symlink_myisam.result File 753.67 KB 0644
partition_bit_innodb.result File 6.45 KB 0644
partition_bit_myisam.result File 6.45 KB 0644
partition_char_innodb.result File 56.13 KB 0644
partition_char_myisam.result File 56.13 KB 0644
partition_datetime_innodb.result File 37.62 KB 0644
partition_datetime_myisam.result File 37.62 KB 0644
partition_debug.result File 57.71 KB 0644
partition_debug_innodb.result File 243.04 KB 0644
partition_debug_myisam.result File 192.25 KB 0644
partition_debug_sync_innodb.result File 2.56 KB 0644
partition_decimal_innodb.result File 5.53 KB 0644
partition_decimal_myisam.result File 5.54 KB 0644
partition_engine_innodb.result File 182.75 KB 0644
partition_engine_myisam.result File 184.74 KB 0644
partition_exch_innodb.result File 6.52 KB 0644
partition_exch_myisam.result File 6.52 KB 0644
partition_exch_myisam_innodb.result File 484 B 0644
partition_exch_qa.result File 6.52 KB 0644
partition_exch_qa_10.result File 1.67 KB 0644
partition_exch_qa_11.result File 1.36 KB 0644
partition_exch_qa_12.result File 2.4 KB 0644
partition_exch_qa_13.result File 3.87 KB 0644
partition_exch_qa_14.result File 1.32 KB 0644
partition_exch_qa_15.result File 624 B 0644
partition_exch_qa_1_innodb.result File 4.06 KB 0644
partition_exch_qa_1_myisam.result File 4.06 KB 0644
partition_exch_qa_2.result File 3.32 KB 0644
partition_exch_qa_3.result File 644 B 0644
partition_exch_qa_4_innodb.result File 1.48 KB 0644
partition_exch_qa_4_myisam.result File 1.48 KB 0644
partition_exch_qa_5_innodb.result File 3.09 KB 0644
partition_exch_qa_5_myisam.result File 3.09 KB 0644
partition_exch_qa_6.result File 2.93 KB 0644
partition_exch_qa_7_innodb.result File 1.28 KB 0644
partition_exch_qa_7_myisam.result File 1.28 KB 0644
partition_exch_qa_8_innodb.result File 1.88 KB 0644
partition_exch_qa_8_myisam.result File 1.88 KB 0644
partition_exchange_archive.result File 10.41 KB 0644
partition_exchange_blackhole.result File 440 B 0644
partition_exchange_innodb.result File 12.85 KB 0644
partition_exchange_memory.result File 12.85 KB 0644
partition_exchange_myisam.result File 12.85 KB 0644
partition_float_innodb.result File 3.64 KB 0644
partition_float_myisam.result File 3.64 KB 0644
partition_innodb_status_file.result File 439 B 0644
partition_int_innodb.result File 11.66 KB 0644
partition_int_myisam.result File 11.66 KB 0644
partition_max_parts_hash_innodb.result File 1.72 KB 0644
partition_max_parts_hash_myisam.result File 1.72 KB 0644
partition_max_parts_inv_innodb.result File 748.54 KB 0644
partition_max_parts_inv_myisam.result File 748.54 KB 0644
partition_max_parts_key_innodb.result File 1.64 KB 0644
partition_max_parts_key_myisam.result File 1.64 KB 0644
partition_max_parts_list_innodb.result File 413.37 KB 0644
partition_max_parts_list_myisam.result File 413.37 KB 0644
partition_max_parts_range_innodb.result File 335 KB 0644
partition_max_parts_range_myisam.result File 335 KB 0644
partition_max_sub_parts_key_list_innodb.result File 201.48 KB 0644
partition_max_sub_parts_key_list_myisam.result File 201.48 KB 0644
partition_max_sub_parts_key_range_innodb.result File 167.07 KB 0644
partition_max_sub_parts_key_range_myisam.result File 167.07 KB 0644
partition_max_sub_parts_list_innodb.result File 201.46 KB 0644
partition_max_sub_parts_list_myisam.result File 201.46 KB 0644
partition_max_sub_parts_range_innodb.result File 167.04 KB 0644
partition_max_sub_parts_range_myisam.result File 167.04 KB 0644
partition_mgm_lc0_archive.result File 19.6 KB 0644
partition_mgm_lc0_innodb.result File 24.07 KB 0644
partition_mgm_lc0_memory.result File 24.12 KB 0644
partition_mgm_lc0_myisam.result File 24.07 KB 0644
partition_mgm_lc1_archive.result File 19.99 KB 0644
partition_mgm_lc1_innodb.result File 24.47 KB 0644
partition_mgm_lc1_memory.result File 24.52 KB 0644
partition_mgm_lc1_myisam.result File 24.47 KB 0644
partition_mgm_lc2_archive.result File 19.99 KB 0644
partition_mgm_lc2_innodb.result File 24.47 KB 0644
partition_mgm_lc2_memory.result File 24.52 KB 0644
partition_mgm_lc2_myisam.result File 24.47 KB 0644
partition_recover_myisam.result File 1.71 KB 0644
partition_reorganize_innodb.result File 7.06 KB 0644
partition_reorganize_myisam.result File 5.72 KB 0644
partition_repair_myisam.result File 16.07 KB 0644
partition_special_innodb.result File 18.36 KB 0644
partition_special_myisam.result File 14.85 KB 0644
partition_syntax_innodb.result File 61.36 KB 0644
partition_syntax_myisam.result File 62.92 KB 0644
partition_t55.out File 4.5 KB 0644
partition_value_innodb.result File 7.36 KB 0644
partition_value_myisam.result File 7.36 KB 0644
rpl-partition-dml-1-1-innodb.result File 6.78 KB 0644
rpl-partition-dml-1-1-myisam.result File 6.78 KB 0644
rpl_partition.result File 5.51 KB 0644