From c587c8b5db0618181614b005e7e0c6acb7d91e03 Mon Sep 17 00:00:00 2001 From: hopelee Date: Thu, 12 Jan 2023 15:12:45 +0800 Subject: [PATCH] Support partition pruning when autoinc field is assigned non-null value in INSERT command Description ----------- When all rows in insert values have assigned the autoinc field a non-null value and is not 0 or 0 (with NO_AUTO_VALUE_ON_ZERO sql_mode), the value of autoinc field is determined and not generated dynamically during execution. In this case, we can apply partition pruning in prepare stage. --- .../r/partition_autoinc_non_null_prune.result | 741 ++++++++++++++++++ .../t/partition_autoinc_non_null_prune.test | 514 ++++++++++++ sql/partition_info.cc | 63 +- sql/partition_info.h | 1 + sql/sql_insert.cc | 2 +- 5 files changed, 1311 insertions(+), 10 deletions(-) create mode 100644 mysql-test/suite/parts/r/partition_autoinc_non_null_prune.result create mode 100644 mysql-test/suite/parts/t/partition_autoinc_non_null_prune.test diff --git a/mysql-test/suite/parts/r/partition_autoinc_non_null_prune.result b/mysql-test/suite/parts/r/partition_autoinc_non_null_prune.result new file mode 100644 index 00000000000..7e7d7bf729c --- /dev/null +++ b/mysql-test/suite/parts/r/partition_autoinc_non_null_prune.result @@ -0,0 +1,741 @@ +# +# When partition expression contains auto increment field and +# its value is given explicitly by INSERT command, partition pruning +# can be applied in this case. +# +CREATE TABLE `sbtest1` ( +`id` int NOT NULL AUTO_INCREMENT, +`k` int NOT NULL DEFAULT '0', +`c` char(120) NOT NULL DEFAULT '', +`pad` char(60) NOT NULL DEFAULT '', +PRIMARY KEY (`id`), +KEY `k_1` (`k`) +) ENGINE=InnoDB +PARTITION BY RANGE (`id`) +(PARTITION p0 VALUES LESS THAN (1001) ENGINE = InnoDB, +PARTITION p1 VALUES LESS THAN (2001) ENGINE = InnoDB, +PARTITION p2 VALUES LESS THAN (3001) ENGINE = InnoDB, +PARTITION p3 VALUES LESS THAN (4001) ENGINE = InnoDB, +PARTITION p4 VALUES LESS THAN (5001) ENGINE = InnoDB); + +# INSERT VALUES (expr_1, ...), ... + +EXPLAIN INSERT INTO sbtest1 VALUES (5, 10, 'aaz', 'kox'), (100, 92, 'hope', 'carmen'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT sbtest1 p0 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`sbtest1` values (5,10,'aaz','kox'),(100,92,'hope','carmen') +INSERT INTO sbtest1 VALUES (5, 10, 'aaz', 'kox'), (100, 92, 'hope', 'carmen'); +SELECT * FROM sbtest1; +id k c pad +100 92 hope carmen +5 10 aaz kox +INSERT INTO sbtest1 VALUES (5, 10, 'aaz', 'kox'); +ERROR 23000: Duplicate entry '5' for key 'sbtest1.PRIMARY' + +# With NULL value + +EXPLAIN INSERT INTO sbtest1 VALUES (999, 520, 'green', 'couple'), (NULL, 87, 'red', 'glass'), (87, 91, 'cat', 'freeze'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT sbtest1 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`sbtest1` values (999,520,'green','couple'),(NULL,87,'red','glass'),(87,91,'cat','freeze') +INSERT INTO sbtest1 VALUES (999, 520, 'green', 'couple'), (NULL, 87, 'red', 'glass'), (87, 91, 'cat', 'freeze'); +SELECT * FROM sbtest1; +id k c pad +100 92 hope carmen +1000 87 red glass +5 10 aaz kox +87 91 cat freeze +999 520 green couple + +# With zero value + +EXPLAIN INSERT INTO sbtest1 VALUES (92, 100, 'white', 'earphone'), (0, 76, 'pitch', 'lee'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT sbtest1 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`sbtest1` values (92,100,'white','earphone'),(0,76,'pitch','lee') +INSERT INTO sbtest1 VALUES (92, 100, 'white', 'earphone'), (0, 76, 'pitch', 'lee'); +SELECT * FROM sbtest1; +id k c pad +100 92 hope carmen +1000 87 red glass +1001 76 pitch lee +5 10 aaz kox +87 91 cat freeze +92 100 white earphone +999 520 green couple + +# INSERT VALUES (), ... + +EXPLAIN INSERT INTO sbtest1 VALUES (), (); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT sbtest1 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`sbtest1` values (),() +INSERT INTO sbtest1 VALUES (), (); +SELECT * FROM sbtest1; +id k c pad +100 92 hope carmen +1000 87 red glass +1001 76 pitch lee +1002 0 +1003 0 +5 10 aaz kox +87 91 cat freeze +92 100 white earphone +999 520 green couple + +# INSERT (columns) VALUES ... + +EXPLAIN INSERT INTO sbtest1(c, k, pad) VALUES ('mm', 21, 'poy'), ('zero', 98, 'roy'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT sbtest1 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`sbtest1` (`test`.`sbtest1`.`c`,`test`.`sbtest1`.`k`,`test`.`sbtest1`.`pad`) values ('mm',21,'poy'),('zero',98,'roy') +INSERT INTO sbtest1(c, k, pad) VALUES ('mm', 21, 'poy'), ('zero', 98, 'roy'); +SELECT * FROM sbtest1; +id k c pad +100 92 hope carmen +1000 87 red glass +1001 76 pitch lee +1002 0 +1003 0 +1004 21 mm poy +1005 98 zero roy +5 10 aaz kox +87 91 cat freeze +92 100 white earphone +999 520 green couple +EXPLAIN INSERT INTO sbtest1(k, id, pad) VALUES (11, 94, 'wire'), (8, 1500, 'trigger'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT sbtest1 p0,p1 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`sbtest1` (`test`.`sbtest1`.`k`,`test`.`sbtest1`.`id`,`test`.`sbtest1`.`pad`) values (11,94,'wire'),(8,1500,'trigger') +INSERT INTO sbtest1(k, id, pad) VALUES (11, 94, 'wire'), (8, 1500, 'trigger'); +SELECT * FROM sbtest1; +id k c pad +100 92 hope carmen +1000 87 red glass +1001 76 pitch lee +1002 0 +1003 0 +1004 21 mm poy +1005 98 zero roy +1500 8 trigger +5 10 aaz kox +87 91 cat freeze +92 100 white earphone +94 11 wire +999 520 green couple + +# With zero value + +EXPLAIN INSERT INTO sbtest1(k, id, pad) VALUES (2, 1008, 'screen'), (8, 0, 'window'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT sbtest1 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`sbtest1` (`test`.`sbtest1`.`k`,`test`.`sbtest1`.`id`,`test`.`sbtest1`.`pad`) values (2,1008,'screen'),(8,0,'window') +INSERT INTO sbtest1(k, id, pad) VALUES (2, 1008, 'screen'), (8, 0, 'window'); +SELECT * FROM sbtest1; +id k c pad +100 92 hope carmen +1000 87 red glass +1001 76 pitch lee +1002 0 +1003 0 +1004 21 mm poy +1005 98 zero roy +1008 2 screen +1500 8 trigger +1501 8 window +5 10 aaz kox +87 91 cat freeze +92 100 white earphone +94 11 wire +999 520 green couple + +# With NULL value + +EXPLAIN INSERT INTO sbtest1(k, id, pad) VALUES (2, 3002, 'screen'), (8, NULL, 'window'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT sbtest1 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`sbtest1` (`test`.`sbtest1`.`k`,`test`.`sbtest1`.`id`,`test`.`sbtest1`.`pad`) values (2,3002,'screen'),(8,NULL,'window') +INSERT INTO sbtest1(k, id, pad) VALUES (2, 3002, 'screen'), (8, NULL, 'window'); +SELECT * FROM sbtest1; +id k c pad +100 92 hope carmen +1000 87 red glass +1001 76 pitch lee +1002 0 +1003 0 +1004 21 mm poy +1005 98 zero roy +1008 2 screen +1500 8 trigger +1501 8 window +3002 2 screen +3003 8 window +5 10 aaz kox +87 91 cat freeze +92 100 white earphone +94 11 wire +999 520 green couple +DROP TABLE sbtest1; + +# With sql_mode NO_AUTO_VALUE_ON_ZERO + +SET @sql_mode_saved=@@sql_mode; +CREATE TABLE animals ( +id MEDIUMINT NOT NULL AUTO_INCREMENT, +name CHAR(30) NOT NULL, +KEY (id, name) +) +PARTITION BY HASH(id) PARTITIONS 5; +EXPLAIN INSERT INTO animals VALUES (68, 'hope'), (0, 'chai'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT animals p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`animals` values (68,'hope'),(0,'chai') +INSERT INTO animals VALUES (68, 'hope'), (0, 'chai'); +SELECT * FROM animals; +id name +68 hope +69 chai +SET sql_mode='NO_AUTO_VALUE_ON_ZERO'; +EXPLAIN INSERT INTO animals VALUES (68, 'hope'), (0, 'chai'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT animals p0,p3 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`animals` values (68,'hope'),(0,'chai') +INSERT INTO animals VALUES (68, 'hope'), (0, 'chai'); +SELECT * FROM animals; +id name +0 chai +68 hope +68 hope +69 chai +SET sql_mode=@sql_mode_saved; +EXPLAIN INSERT INTO animals(id, name) VALUES (100, 'yellow'), (-0, 'pig'), (65, 'taitan'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT animals p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`animals` (`test`.`animals`.`id`,`test`.`animals`.`name`) values (100,'yellow'),(-(0),'pig'),(65,'taitan') +INSERT INTO animals(id, name) VALUES (100, 'yellow'), (-0, 'pig'), (65, 'taitan'); +SELECT * FROM animals; +id name +0 chai +100 yellow +101 pig +65 taitan +68 hope +68 hope +69 chai +SET sql_mode='NO_AUTO_VALUE_ON_ZERO'; +EXPLAIN INSERT INTO animals(id, name) VALUES (100, 'yellow'), (-0, 'pig'), (65, 'taitan'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT animals p0 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`animals` (`test`.`animals`.`id`,`test`.`animals`.`name`) values (100,'yellow'),(-(0),'pig'),(65,'taitan') +INSERT INTO animals(id, name) VALUES (100, 'yellow'), (-0, 'pig'), (65, 'taitan'); +SELECT * FROM animals; +id name +0 chai +0 pig +100 yellow +100 yellow +101 pig +65 taitan +65 taitan +68 hope +68 hope +69 chai +SET sql_mode=@sql_mode_saved; +EXPLAIN INSERT INTO animals(id, name) VALUES (23, 'red'), (NULL, 'cup'), (155, 'face'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT animals p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`animals` (`test`.`animals`.`id`,`test`.`animals`.`name`) values (23,'red'),(NULL,'cup'),(155,'face') +INSERT INTO animals(id, name) VALUES (23, 'red'), (NULL, 'cup'), (155, 'face'); +SELECT * FROM animals; +id name +0 chai +0 pig +65 taitan +65 taitan +100 yellow +100 yellow +155 face +101 pig +102 cup +23 red +68 hope +68 hope +69 chai +SET sql_mode='NO_AUTO_VALUE_ON_ZERO'; +EXPLAIN INSERT INTO animals(id, name) VALUES (23, 'red'), (NULL, 'cup'), (155, 'face'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT animals p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`animals` (`test`.`animals`.`id`,`test`.`animals`.`name`) values (23,'red'),(NULL,'cup'),(155,'face') +INSERT INTO animals(id, name) VALUES (23, 'red'), (NULL, 'cup'), (155, 'face'); +SELECT * FROM animals; +id name +0 chai +0 pig +100 yellow +100 yellow +101 pig +102 cup +155 face +155 face +156 cup +23 red +23 red +65 taitan +65 taitan +68 hope +68 hope +69 chai +SET sql_mode=@sql_mode_saved; +EXPLAIN INSERT INTO animals(id, name) VALUES (92, 'clean'), (116, 'tissue'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT animals p1,p2 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`animals` (`test`.`animals`.`id`,`test`.`animals`.`name`) values (92,'clean'),(116,'tissue') +INSERT INTO animals(id, name) VALUES (92, 'clean'), (116, 'tissue'); +SELECT * FROM animals; +id name +0 chai +0 pig +100 yellow +100 yellow +101 pig +102 cup +116 tissue +155 face +155 face +156 cup +23 red +23 red +65 taitan +65 taitan +68 hope +68 hope +69 chai +92 clean +SET sql_mode='NO_AUTO_VALUE_ON_ZERO'; +EXPLAIN INSERT INTO animals(id, name) VALUES (92, 'clean'), (116, 'tissue'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT animals p1,p2 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`animals` (`test`.`animals`.`id`,`test`.`animals`.`name`) values (92,'clean'),(116,'tissue') +INSERT INTO animals(id, name) VALUES (92, 'clean'), (116, 'tissue'); +SELECT * FROM animals; +id name +0 chai +0 pig +100 yellow +100 yellow +101 pig +102 cup +116 tissue +116 tissue +155 face +155 face +156 cup +23 red +23 red +65 taitan +65 taitan +68 hope +68 hope +69 chai +92 clean +92 clean +DROP TABLE animals; +SET sql_mode=@sql_mode_saved; + +# INSERT ... SELECT will not prune + +CREATE TABLE t1 (id INT, price FLOAT); +INSERT INTO t1 VALUES (8, 2.3), (2, 1.2), (0, 392), (-1, 29), (100, 2), (NULL, 23); +CREATE TABLE tp ( +id INT AUTO_INCREMENT, +price FLOAT, +PRIMARY KEY (id) +) PARTITION BY RANGE (id) ( +PARTITION p0 VALUES LESS THAN (10), +PARTITION p1 VALUES LESS THAN (150), +PARTITION p2 VALUES LESS THAN (300) +); +EXPLAIN INSERT INTO tp SELECT * FROM t1; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT tp p0,p1,p2 ALL NULL NULL NULL NULL NULL NULL NULL +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 100.00 NULL +Warnings: +Note 1003 insert into `test`.`tp` /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`price` AS `price` from `test`.`t1` +INSERT INTO tp SELECT * FROM t1; +SELECT * FROM tp; +id price +-1 29 +100 2 +101 23 +2 1.2 +8 2.3 +9 392 +DROP TABLE t1, tp; + +# INSERT ... ON DUPLICATE KEY UPDATE + +CREATE TABLE tr ( +a INT AUTO_INCREMENT, +b INT, +c INT, +PRIMARY KEY (a, b) +) PARTITION BY RANGE COLUMNS (a, b) ( +PARTITION p0 VALUES LESS THAN (5, 10), +PARTITION p1 VALUES LESS THAN (10, 20), +PARTITION p2 VALUES LESS THAN (50, 40), +PARTITION p3 VALUES LESS THAN (100, 100) +); +EXPLAIN INSERT INTO tr VALUES (2, 7, 19), (4, 2, 8), (6, 1, 21); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT tr p0,p1 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`tr` values (2,7,19),(4,2,8),(6,1,21) +INSERT INTO tr VALUES (2, 7, 19), (4, 2, 8), (6, 1, 21); +SELECT * FROM tr; +a b c +2 7 19 +4 2 8 +6 1 21 +EXPLAIN INSERT INTO tr VALUES (9, 2, 7), (-0, 9, 17); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT tr p0,p1,p2,p3 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`tr` values (9,2,7),(-(0),9,17) +INSERT INTO tr VALUES (9, 2, 7), (-0, 9, 17); +SELECT * FROM tr; +a b c +10 9 17 +2 7 19 +4 2 8 +6 1 21 +9 2 7 + +# Updated field isn't included in partition expression. + +EXPLAIN INSERT INTO tr VALUES (7, 12, 3), (7, 5, 21) ON DUPLICATE KEY UPDATE c=c+1; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT tr p1 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`tr` values (7,12,3),(7,5,21) on duplicate key update `test`.`tr`.`c` = (`test`.`tr`.`c` + 1) +INSERT INTO tr VALUES (7, 12, 3), (7, 5, 21) ON DUPLICATE KEY UPDATE c=c+1; +SELECT * FROM tr; +a b c +10 9 17 +2 7 19 +4 2 8 +6 1 21 +7 12 3 +7 5 21 +9 2 7 +EXPLAIN INSERT INTO tr VALUES (7, 12, 3), (2, 7, 21) ON DUPLICATE KEY UPDATE c=c+1; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT tr p0,p1 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`tr` values (7,12,3),(2,7,21) on duplicate key update `test`.`tr`.`c` = (`test`.`tr`.`c` + 1) +INSERT INTO tr VALUES (7, 12, 3), (2, 7, 21) ON DUPLICATE KEY UPDATE c=c+1; +SELECT * FROM tr; +a b c +10 9 17 +2 7 20 +4 2 8 +6 1 21 +7 12 4 +7 5 21 +9 2 7 +EXPLAIN INSERT INTO tr VALUES (7, 12, 3), (-0, 7, 21), (NULL, 21, 10) ON DUPLICATE KEY UPDATE c=c+1; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT tr p0,p1,p2,p3 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`tr` values (7,12,3),(-(0),7,21),(NULL,21,10) on duplicate key update `test`.`tr`.`c` = (`test`.`tr`.`c` + 1) +INSERT INTO tr VALUES (7, 12, 3), (-0, 7, 21), (NULL, 21, 10) ON DUPLICATE KEY UPDATE c=c+1; +SELECT * FROM tr; +a b c +10 9 17 +11 7 21 +12 21 10 +2 7 20 +4 2 8 +6 1 21 +7 12 5 +7 5 21 +9 2 7 + +# Updated field is included in partition expression. Will not prune. + +EXPLAIN INSERT INTO tr VALUES (7, 12, 3), (7, 5, 21) ON DUPLICATE KEY UPDATE b=b+1; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT tr p0,p1,p2,p3 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`tr` values (7,12,3),(7,5,21) on duplicate key update `test`.`tr`.`b` = (`test`.`tr`.`b` + 1) +INSERT INTO tr VALUES (7, 12, 3), (7, 5, 21) ON DUPLICATE KEY UPDATE b=b+1; +SELECT * FROM tr; +a b c +10 9 17 +11 7 21 +12 21 10 +2 7 20 +4 2 8 +6 1 21 +7 13 5 +7 6 21 +9 2 7 +EXPLAIN INSERT INTO tr VALUES (7, 12, 3), (7, 5, 21) ON DUPLICATE KEY UPDATE a=a+c; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT tr p0,p1,p2,p3 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`tr` values (7,12,3),(7,5,21) on duplicate key update `test`.`tr`.`a` = (`test`.`tr`.`a` + `test`.`tr`.`c`) +INSERT INTO tr VALUES (7, 12, 3), (7, 5, 21) ON DUPLICATE KEY UPDATE a=a+c; +SELECT * FROM tr; +a b c +10 9 17 +11 7 21 +12 21 10 +2 7 20 +4 2 8 +6 1 21 +7 12 3 +7 13 5 +7 5 21 +7 6 21 +9 2 7 +DROP TABLE tr; + +# The decimal auto_increment type is not allowed in partition expression + +CREATE TABLE t1 ( +id FLOAT NOT NULL AUTO_INCREMENT, +name CHAR(30) NOT NULL, +KEY (id, name) +) PARTITION BY RANGE COLUMNS (id) ( +PARTITION p0 VALUES LESS THAN (10), +PARTITION p1 VALUES LESS THAN (30), +PARTITION p2 VALUES LESS THAN (40) +); +ERROR HY000: Field 'id' is of a not allowed type for this type of partitioning +CREATE TABLE t1 ( +id FLOAT NOT NULL AUTO_INCREMENT, +name CHAR(30) NOT NULL, +KEY (id, name) +) PARTITION BY RANGE (FLOOR(id)) ( +PARTITION p0 VALUES LESS THAN (10), +PARTITION p1 VALUES LESS THAN (30), +PARTITION p2 VALUES LESS THAN (40) +); +ERROR HY000: The PARTITION function returns the wrong type + +# REPLACE command + +CREATE TABLE tr ( +a INT AUTO_INCREMENT, +b INT, +c INT, +PRIMARY KEY (a, b) +) PARTITION BY RANGE COLUMNS (a, b) ( +PARTITION p0 VALUES LESS THAN (5, 10), +PARTITION p1 VALUES LESS THAN (10, 20), +PARTITION p2 VALUES LESS THAN (50, 40), +PARTITION p3 VALUES LESS THAN (100, 100) +); +EXPLAIN REPLACE INTO tr VALUES (7, 12, 3), (7, 5, 21); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 REPLACE tr p1 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 replace into `test`.`tr` values (7,12,3),(7,5,21) +REPLACE INTO tr VALUES (7, 12, 3), (7, 5, 21); +SELECT * FROM tr; +a b c +7 12 3 +7 5 21 +EXPLAIN REPLACE INTO tr VALUES (7, 12, 10), (0, 5, 12); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 REPLACE tr p0,p1,p2,p3 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 replace into `test`.`tr` values (7,12,10),(0,5,12) +REPLACE INTO tr VALUES (7, 12, 10), (0, 5, 12); +SELECT * FROM tr; +a b c +7 12 10 +7 5 21 +8 5 12 +EXPLAIN REPLACE INTO tr SET c = 5; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 REPLACE tr p0,p1,p2,p3 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 replace into `test`.`tr` (`test`.`tr`.`c`) values (5) +REPLACE INTO tr SET c = 5; +ERROR HY000: Field 'b' doesn't have a default value +SELECT * FROM tr; +a b c +7 12 10 +7 5 21 +8 5 12 +EXPLAIN REPLACE INTO tr(b, c) VALUES (8, 29), (26, 1); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 REPLACE tr p0,p1,p2,p3 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 replace into `test`.`tr` (`test`.`tr`.`b`,`test`.`tr`.`c`) values (8,29),(26,1) +REPLACE INTO tr(b, c) VALUES (8, 29), (26, 1); +SELECT * FROM tr; +a b c +10 26 1 +7 12 10 +7 5 21 +8 5 12 +9 8 29 +EXPLAIN REPLACE INTO tr(b, a, c) VALUES (8, 21, 29), (26, 7, 1), (21, NULL, 76); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 REPLACE tr p0,p1,p2,p3 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 replace into `test`.`tr` (`test`.`tr`.`b`,`test`.`tr`.`a`,`test`.`tr`.`c`) values (8,21,29),(26,7,1),(21,NULL,76) +REPLACE INTO tr(b, a, c) VALUES (8, 21, 29), (26, 7, 1), (21, NULL, 76); +SELECT * FROM tr; +a b c +10 26 1 +21 8 29 +22 21 76 +7 12 10 +7 26 1 +7 5 21 +8 5 12 +9 8 29 +DROP TABLE tr; + +# Partitioned table with virtual columns + +CREATE TABLE tp ( +c1 INT, +c2 INT, +c3 INT GENERATED ALWAYS AS (c1 + 1), +c4 INT AS (c3 - c2) STORED, +c5 INT NOT NULL AUTO_INCREMENT, +UNIQUE KEY(c5, c2) +) +PARTITION BY RANGE (c5) ( +PARTITION p0 VALUES LESS THAN (10), +PARTITION p1 VALUES LESS THAN (20), +PARTITION p2 VALUES LESS THAN (30) +); +INSERT INTO tp VALUES (1, 2, 4), (6, 9, 3), (10, 1, 2); +ERROR 21S01: Column count doesn't match value count at row 1 +INSERT INTO tp VALUES (1, 2, 4, 5, 7); +ERROR HY000: The value specified for generated column 'c3' in table 'tp' is not allowed. +INSERT INTO tp VALUES (1, 2, NULL, NULL, 7); +ERROR HY000: The value specified for generated column 'c3' in table 'tp' is not allowed. +EXPLAIN INSERT INTO tp (c1, c2, c5) VALUES (1, 2, 4), (6, 9, 3), (10, 1, 2); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT tp p0 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`tp` (`test`.`tp`.`c1`,`test`.`tp`.`c2`,`test`.`tp`.`c5`) values (1,2,4),(6,9,3),(10,1,2) +INSERT INTO tp (c1, c2, c5) VALUES (1, 2, 4), (6, 9, 3), (10, 1, 2); +SELECT * FROM tp; +c1 c2 c3 c4 c5 +1 2 2 0 4 +10 1 11 10 2 +6 9 7 -2 3 +EXPLAIN INSERT INTO tp (c1, c2, c5) VALUES (7, 1, 1), (8, 8, 0), (2, 10, 12); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT tp p0,p1,p2 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`tp` (`test`.`tp`.`c1`,`test`.`tp`.`c2`,`test`.`tp`.`c5`) values (7,1,1),(8,8,0),(2,10,12) +INSERT INTO tp (c1, c2, c5) VALUES (7, 1, 1), (8, 8, 0), (2, 10, 12); +SELECT * FROM tp; +c1 c2 c3 c4 c5 +1 2 2 0 4 +10 1 11 10 2 +2 10 3 -7 12 +6 9 7 -2 3 +7 1 8 7 1 +8 8 9 1 5 +EXPLAIN INSERT INTO tp (c5, c1) VALUES (15, 1), (13, 8); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT tp p1 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`tp` (`test`.`tp`.`c5`,`test`.`tp`.`c1`) values (15,1),(13,8) +INSERT INTO tp (c5, c1) VALUES (15, 1), (13, 8); +SELECT * FROM tp; +c1 c2 c3 c4 c5 +1 2 2 0 4 +1 NULL 2 NULL 15 +10 1 11 10 2 +2 10 3 -7 12 +6 9 7 -2 3 +7 1 8 7 1 +8 8 9 1 5 +8 NULL 9 NULL 13 +EXPLAIN INSERT INTO tp (c2, c5) VALUES (10, 9), (12, NULL), (13, 0); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT tp p0,p1,p2 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`tp` (`test`.`tp`.`c2`,`test`.`tp`.`c5`) values (10,9),(12,NULL),(13,0) +INSERT INTO tp (c2, c5) VALUES (10, 9), (12, NULL), (13, 0); +SELECT * FROM tp; +c1 c2 c3 c4 c5 +1 2 2 0 4 +1 NULL 2 NULL 15 +10 1 11 10 2 +2 10 3 -7 12 +6 9 7 -2 3 +7 1 8 7 1 +8 8 9 1 5 +8 NULL 9 NULL 13 +NULL 10 NULL NULL 9 +NULL 12 NULL NULL 16 +NULL 13 NULL NULL 17 +DROP TABLE tp; +CREATE TABLE `sbtest1` ( +`id` bigint NOT NULL AUTO_INCREMENT, +`k` int NOT NULL DEFAULT '0', +`c` char(120) NOT NULL DEFAULT '', +`pad` char(60) NOT NULL DEFAULT '', +PRIMARY KEY (`id`), +KEY `k_1` (`k`) +) ENGINE=InnoDB +PARTITION BY RANGE (`id`) +(PARTITION p0 VALUES LESS THAN (1001) ENGINE = InnoDB, +PARTITION p1 VALUES LESS THAN (2001) ENGINE = InnoDB, +PARTITION p2 VALUES LESS THAN (3001) ENGINE = InnoDB, +PARTITION p3 VALUES LESS THAN (4001) ENGINE = InnoDB, +PARTITION p4 VALUES LESS THAN (5001) ENGINE = InnoDB, +PARTITION p5 VALUES LESS THAN MAXVALUE); +EXPLAIN INSERT INTO sbtest1 VALUES (100 * RAND(1), 10, 'aaz', 'kox'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT sbtest1 p0,p1,p2,p3,p4,p5 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`sbtest1` values ((100 * rand(1)),10,'aaz','kox') +INSERT INTO sbtest1 VALUES (100 * RAND(1), 10, 'aaz', 'kox'); +EXPLAIN INSERT INTO sbtest1 VALUES (LAST_INSERT_ID() + 1, 10, 'aaz', 'kox'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT sbtest1 p0 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`sbtest1` values ((last_insert_id() + 1),10,'aaz','kox') +INSERT INTO sbtest1 VALUES (LAST_INSERT_ID() + 1, 10, 'aaz', 'kox'); +SELECT * FROM sbtest1; +id k c pad +17 10 aaz kox +41 10 aaz kox +EXPLAIN INSERT INTO sbtest1 VALUES (current_timestamp(), 10, 'aaz', 'kox'); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT sbtest1 p0,p1,p2,p3,p4,p5 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`sbtest1` values (now(),10,'aaz','kox') +INSERT INTO sbtest1 VALUES (current_timestamp(), 10, 'aaz', 'kox'); +SELECT COUNT(*) FROM sbtest1; +COUNT(*) +3 +DROP TABLE sbtest1; diff --git a/mysql-test/suite/parts/t/partition_autoinc_non_null_prune.test b/mysql-test/suite/parts/t/partition_autoinc_non_null_prune.test new file mode 100644 index 00000000000..f452b0cddad --- /dev/null +++ b/mysql-test/suite/parts/t/partition_autoinc_non_null_prune.test @@ -0,0 +1,514 @@ +--echo # +--echo # When partition expression contains auto increment field and +--echo # its value is given explicitly by INSERT command, partition pruning +--echo # can be applied in this case. +--echo # + +CREATE TABLE `sbtest1` ( + `id` int NOT NULL AUTO_INCREMENT, + `k` int NOT NULL DEFAULT '0', + `c` char(120) NOT NULL DEFAULT '', + `pad` char(60) NOT NULL DEFAULT '', + PRIMARY KEY (`id`), + KEY `k_1` (`k`) +) ENGINE=InnoDB +PARTITION BY RANGE (`id`) +(PARTITION p0 VALUES LESS THAN (1001) ENGINE = InnoDB, + PARTITION p1 VALUES LESS THAN (2001) ENGINE = InnoDB, + PARTITION p2 VALUES LESS THAN (3001) ENGINE = InnoDB, + PARTITION p3 VALUES LESS THAN (4001) ENGINE = InnoDB, + PARTITION p4 VALUES LESS THAN (5001) ENGINE = InnoDB); + +--echo +--echo # INSERT VALUES (expr_1, ...), ... +--echo +let $query= +INSERT INTO sbtest1 VALUES (5, 10, 'aaz', 'kox'), (100, 92, 'hope', 'carmen'); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM sbtest1; + +--error ER_DUP_ENTRY +INSERT INTO sbtest1 VALUES (5, 10, 'aaz', 'kox'); + +--echo +--echo # With NULL value +--echo +let $query= +INSERT INTO sbtest1 VALUES (999, 520, 'green', 'couple'), (NULL, 87, 'red', 'glass'), (87, 91, 'cat', 'freeze'); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM sbtest1; + +--echo +--echo # With zero value +--echo +let $query= +INSERT INTO sbtest1 VALUES (92, 100, 'white', 'earphone'), (0, 76, 'pitch', 'lee'); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM sbtest1; + +--echo +--echo # INSERT VALUES (), ... +--echo +let $query= +INSERT INTO sbtest1 VALUES (), (); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM sbtest1; + +--echo +--echo # INSERT (columns) VALUES ... +--echo +let $query= +INSERT INTO sbtest1(c, k, pad) VALUES ('mm', 21, 'poy'), ('zero', 98, 'roy'); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM sbtest1; + +let $query= +INSERT INTO sbtest1(k, id, pad) VALUES (11, 94, 'wire'), (8, 1500, 'trigger'); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM sbtest1; + +--echo +--echo # With zero value +--echo +let $query= +INSERT INTO sbtest1(k, id, pad) VALUES (2, 1008, 'screen'), (8, 0, 'window'); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM sbtest1; + +--echo +--echo # With NULL value +--echo +let $query= +INSERT INTO sbtest1(k, id, pad) VALUES (2, 3002, 'screen'), (8, NULL, 'window'); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM sbtest1; + +DROP TABLE sbtest1; + +--echo +--echo # With sql_mode NO_AUTO_VALUE_ON_ZERO +--echo +SET @sql_mode_saved=@@sql_mode; + +CREATE TABLE animals ( + id MEDIUMINT NOT NULL AUTO_INCREMENT, + name CHAR(30) NOT NULL, + KEY (id, name) +) +PARTITION BY HASH(id) PARTITIONS 5; + +let $query= +INSERT INTO animals VALUES (68, 'hope'), (0, 'chai'); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM animals; + +SET sql_mode='NO_AUTO_VALUE_ON_ZERO'; + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM animals; + +SET sql_mode=@sql_mode_saved; + +let $query= +INSERT INTO animals(id, name) VALUES (100, 'yellow'), (-0, 'pig'), (65, 'taitan'); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM animals; + +SET sql_mode='NO_AUTO_VALUE_ON_ZERO'; + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM animals; + +SET sql_mode=@sql_mode_saved; + +let $query= +INSERT INTO animals(id, name) VALUES (23, 'red'), (NULL, 'cup'), (155, 'face'); + +eval EXPLAIN $query; +eval $query; + +SELECT * FROM animals; + +SET sql_mode='NO_AUTO_VALUE_ON_ZERO'; + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM animals; + +SET sql_mode=@sql_mode_saved; + +let $query= +INSERT INTO animals(id, name) VALUES (92, 'clean'), (116, 'tissue'); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM animals; + +SET sql_mode='NO_AUTO_VALUE_ON_ZERO'; + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM animals; + +DROP TABLE animals; + +SET sql_mode=@sql_mode_saved; + +--echo +--echo # INSERT ... SELECT will not prune +--echo +CREATE TABLE t1 (id INT, price FLOAT); + +INSERT INTO t1 VALUES (8, 2.3), (2, 1.2), (0, 392), (-1, 29), (100, 2), (NULL, 23); + +CREATE TABLE tp ( + id INT AUTO_INCREMENT, + price FLOAT, + PRIMARY KEY (id) +) PARTITION BY RANGE (id) ( + PARTITION p0 VALUES LESS THAN (10), + PARTITION p1 VALUES LESS THAN (150), + PARTITION p2 VALUES LESS THAN (300) +); + +let $query= +INSERT INTO tp SELECT * FROM t1; + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tp; + +DROP TABLE t1, tp; + +--echo +--echo # INSERT ... ON DUPLICATE KEY UPDATE +--echo +CREATE TABLE tr ( + a INT AUTO_INCREMENT, + b INT, + c INT, + PRIMARY KEY (a, b) +) PARTITION BY RANGE COLUMNS (a, b) ( + PARTITION p0 VALUES LESS THAN (5, 10), + PARTITION p1 VALUES LESS THAN (10, 20), + PARTITION p2 VALUES LESS THAN (50, 40), + PARTITION p3 VALUES LESS THAN (100, 100) +); + +let $query= +INSERT INTO tr VALUES (2, 7, 19), (4, 2, 8), (6, 1, 21); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tr; + +let $query= +INSERT INTO tr VALUES (9, 2, 7), (-0, 9, 17); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tr; + +--echo +--echo # Updated field isn't included in partition expression. +--echo +let $query= +INSERT INTO tr VALUES (7, 12, 3), (7, 5, 21) ON DUPLICATE KEY UPDATE c=c+1; + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tr; + +let $query= +INSERT INTO tr VALUES (7, 12, 3), (2, 7, 21) ON DUPLICATE KEY UPDATE c=c+1; + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tr; + +let $query= +INSERT INTO tr VALUES (7, 12, 3), (-0, 7, 21), (NULL, 21, 10) ON DUPLICATE KEY UPDATE c=c+1; + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tr; + +--echo +--echo # Updated field is included in partition expression. Will not prune. +--echo +let $query= +INSERT INTO tr VALUES (7, 12, 3), (7, 5, 21) ON DUPLICATE KEY UPDATE b=b+1; + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tr; + +let $query= +INSERT INTO tr VALUES (7, 12, 3), (7, 5, 21) ON DUPLICATE KEY UPDATE a=a+c; + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tr; + +DROP TABLE tr; + +--echo +--echo # The decimal auto_increment type is not allowed in partition expression +--echo +--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD +CREATE TABLE t1 ( + id FLOAT NOT NULL AUTO_INCREMENT, + name CHAR(30) NOT NULL, + KEY (id, name) +) PARTITION BY RANGE COLUMNS (id) ( + PARTITION p0 VALUES LESS THAN (10), + PARTITION p1 VALUES LESS THAN (30), + PARTITION p2 VALUES LESS THAN (40) +); + +--error ER_PARTITION_FUNC_NOT_ALLOWED_ERROR +CREATE TABLE t1 ( + id FLOAT NOT NULL AUTO_INCREMENT, + name CHAR(30) NOT NULL, + KEY (id, name) +) PARTITION BY RANGE (FLOOR(id)) ( + PARTITION p0 VALUES LESS THAN (10), + PARTITION p1 VALUES LESS THAN (30), + PARTITION p2 VALUES LESS THAN (40) +); + +--echo +--echo # REPLACE command +--echo +CREATE TABLE tr ( + a INT AUTO_INCREMENT, + b INT, + c INT, + PRIMARY KEY (a, b) +) PARTITION BY RANGE COLUMNS (a, b) ( + PARTITION p0 VALUES LESS THAN (5, 10), + PARTITION p1 VALUES LESS THAN (10, 20), + PARTITION p2 VALUES LESS THAN (50, 40), + PARTITION p3 VALUES LESS THAN (100, 100) +); + +let $query= +REPLACE INTO tr VALUES (7, 12, 3), (7, 5, 21); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tr; + +let $query= +REPLACE INTO tr VALUES (7, 12, 10), (0, 5, 12); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tr; + +let $query= +REPLACE INTO tr SET c = 5; + +eval EXPLAIN $query; +--error ER_NO_DEFAULT_FOR_FIELD +eval $query; + +--sorted_result +SELECT * FROM tr; + +let $query= +REPLACE INTO tr(b, c) VALUES (8, 29), (26, 1); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tr; + +let $query= +REPLACE INTO tr(b, a, c) VALUES (8, 21, 29), (26, 7, 1), (21, NULL, 76); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tr; + +DROP TABLE tr; + +--echo +--echo # Partitioned table with virtual columns +--echo +CREATE TABLE tp ( + c1 INT, + c2 INT, + c3 INT GENERATED ALWAYS AS (c1 + 1), + c4 INT AS (c3 - c2) STORED, + c5 INT NOT NULL AUTO_INCREMENT, + UNIQUE KEY(c5, c2) +) +PARTITION BY RANGE (c5) ( + PARTITION p0 VALUES LESS THAN (10), + PARTITION p1 VALUES LESS THAN (20), + PARTITION p2 VALUES LESS THAN (30) +); + +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO tp VALUES (1, 2, 4), (6, 9, 3), (10, 1, 2); + +--error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN +INSERT INTO tp VALUES (1, 2, 4, 5, 7); + +--error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN +INSERT INTO tp VALUES (1, 2, NULL, NULL, 7); + +let $query= +INSERT INTO tp (c1, c2, c5) VALUES (1, 2, 4), (6, 9, 3), (10, 1, 2); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tp; + +let $query= +INSERT INTO tp (c1, c2, c5) VALUES (7, 1, 1), (8, 8, 0), (2, 10, 12); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tp; + +let $query= +INSERT INTO tp (c5, c1) VALUES (15, 1), (13, 8); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tp; + +let $query= +INSERT INTO tp (c2, c5) VALUES (10, 9), (12, NULL), (13, 0); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM tp; + +DROP TABLE tp; + +CREATE TABLE `sbtest1` ( + `id` bigint NOT NULL AUTO_INCREMENT, + `k` int NOT NULL DEFAULT '0', + `c` char(120) NOT NULL DEFAULT '', + `pad` char(60) NOT NULL DEFAULT '', + PRIMARY KEY (`id`), + KEY `k_1` (`k`) +) ENGINE=InnoDB +PARTITION BY RANGE (`id`) +(PARTITION p0 VALUES LESS THAN (1001) ENGINE = InnoDB, + PARTITION p1 VALUES LESS THAN (2001) ENGINE = InnoDB, + PARTITION p2 VALUES LESS THAN (3001) ENGINE = InnoDB, + PARTITION p3 VALUES LESS THAN (4001) ENGINE = InnoDB, + PARTITION p4 VALUES LESS THAN (5001) ENGINE = InnoDB, + PARTITION p5 VALUES LESS THAN MAXVALUE); + +let $query= +INSERT INTO sbtest1 VALUES (100 * RAND(1), 10, 'aaz', 'kox'); + +eval EXPLAIN $query; +eval $query; + +let $query= +INSERT INTO sbtest1 VALUES (LAST_INSERT_ID() + 1, 10, 'aaz', 'kox'); + +eval EXPLAIN $query; +eval $query; + +--sorted_result +SELECT * FROM sbtest1; + +let $query= +INSERT INTO sbtest1 VALUES (current_timestamp(), 10, 'aaz', 'kox'); + +eval EXPLAIN $query; +eval $query; + +SELECT COUNT(*) FROM sbtest1; + +DROP TABLE sbtest1; diff --git a/sql/partition_info.cc b/sql/partition_info.cc index a3c89ecf706..7f78aa20cec 100644 --- a/sql/partition_info.cc +++ b/sql/partition_info.cc @@ -296,6 +296,7 @@ bool partition_info::set_partition_bitmaps(TABLE_LIST *table_list) { @param update_fields In case of ON DUPLICATE UPDATE, which fields to update @param fields Listed fields @param empty_values True if values is empty (only defaults) + @param insert_values Values to insert @param[out] prune_needs_default_values Set on return if copying of default values is needed @param[out] can_prune_partitions Enum showing if possible to prune @@ -311,6 +312,7 @@ bool partition_info::can_prune_insert( THD *thd, enum_duplicates duplic, COPY_INFO &update, const mem_root_deque &update_fields, const mem_root_deque &fields, bool empty_values, + const mem_root_deque *> &insert_values, enum_can_prune *can_prune_partitions, bool *prune_needs_default_values, MY_BITMAP *used_partitions) { *can_prune_partitions = PRUNE_NO; @@ -361,15 +363,58 @@ bool partition_info::can_prune_insert( } } - if (table->found_next_number_field) { - /* - If the field is used in the partitioning expression, we cannot prune. - TODO: If all rows have not null values and - is not 0 (with NO_AUTO_VALUE_ON_ZERO sql_mode), then pruning is possible! - */ - if (bitmap_is_set(&full_part_field_set, - table->found_next_number_field->field_index())) - return false; + /* + If the field is used in the partitioning expression and isn't assigned in + insert values, we cannot prune. If all rows have non-null values and is + 0 (with NO_AUTO_VALUE_ON_ZERO sql_mode) or not 0, then pruning is possible! + */ + if (table->found_next_number_field && + bitmap_is_set(&full_part_field_set, + table->found_next_number_field->field_index())) { + // Index of next_number_field in insert value list + uint16 autoinc_index; + if (fields.empty()) { + // INSERT VALUES (), all columns including autoinc field need a default. + if (empty_values) return false; + + // INSERT VALUES (expr_1, ...), ... + autoinc_index = table->found_next_number_field->field_index(); + } else { + // INSERT (columns) VALUES ... + auto its = fields.begin(); + while (its != fields.end()) { + Item_field *const field = (*its)->field_for_view_update(); + assert(field && field->table_ref->table == table); + if (field->field == table->found_next_number_field) break; + its++; + } + + /* + Not found next_number_field in insert field list, so the value of it + will be generated when executing. + */ + if (its == fields.end()) return false; + + autoinc_index = its - fields.begin(); + } + + auto its = insert_values.begin(); + while (its != insert_values.end()) { + const mem_root_deque *values = *its++; + Item *next_number_value = (*values)[autoinc_index]; + /* + If the next number value is NULL or its value is 0 and SQL mode is not + MODE_NO_AUTO_VALUE_ON_ZERO, then the next number value will be generated + when executing. In this case, we can't prune the partitions. + + And only if the value is constant, we can apply partition pruning. Refer + to: partition_info::set_used_partition(). + */ + if (!next_number_value->const_item() || next_number_value->is_null() || + (next_number_value->val_int() == 0 && + !(thd->variables.sql_mode & MODE_NO_AUTO_VALUE_ON_ZERO))) + return false; + } } /* diff --git a/sql/partition_info.h b/sql/partition_info.h index e33d543049d..b6f4faa501e 100644 --- a/sql/partition_info.h +++ b/sql/partition_info.h @@ -524,6 +524,7 @@ class partition_info { bool can_prune_insert(THD *thd, enum_duplicates duplic, COPY_INFO &update, const mem_root_deque &update_fields, const mem_root_deque &fields, bool empty_values, + const mem_root_deque *> &insert_values, enum_can_prune *can_prune_partitions, bool *prune_needs_default_values, MY_BITMAP *used_partitions); diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 60719f6fd4d..1b093955804 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1449,7 +1449,7 @@ bool Sql_cmd_insert_base::prepare_inner(THD *thd) { bool prune_needs_default_values = false; if (insert_table->part_info->can_prune_insert( thd, duplicates, update, update_field_list, insert_field_list, - value_count == 0, &can_prune_partitions, + value_count == 0, insert_many_values, &can_prune_partitions, &prune_needs_default_values, &used_partitions)) return true; /* purecov: inspected */ -- 2.19.1.6.gb485710b