Description:
query result change after removing partitioning, such as the below:
mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.vc4 AS ref3 FROM t1 WHERE (NOT ((t1.c0) NOT IN ("1900-02-29", (CAST('1971-10-29 07:58:33' AS SIGNED)) IN ((- (NULL)))))) ;
Empty set, 1 warning (0.00 sec)
mysql> alter table t1 remove partitioning;
Query OK, 25 rows affected (0.59 sec)
Records: 25 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.vc4 AS ref3 FROM t1 WHERE (NOT ((t1.c0) NOT IN ("1900-02-29", (CAST('1971-10-29 07:58:33' AS SIGNED)) IN ((- (NULL)))))) ;
+---------------------+----------+---------------------+---------------------+
| ref0 | ref1 | ref2 | ref3 |
+---------------------+----------+---------------------+---------------------+
| 0000-00-00 00:00:00 | NULL | NULL | NULL |
| 0000-00-00 00:00:00 | 0.642151 | 1979-03-25 08:56:15 | 1979-03-25 08:56:15 |
| 0000-00-00 00:00:00 | 0.128648 | 2024-02-28 16:00:00 | 2024-02-28 16:00:00 |
| 0000-00-00 00:00:00 | 0.130981 | 2037-12-31 15:59:59 | 2037-12-31 15:59:59 |
+---------------------+----------+---------------------+---------------------+
How to repeat:
set sql_mode='';
CREATE TABLE `t1` (
`c0` datetime DEFAULT NULL COMMENT 'asdf',
`c1` float /*!50606 STORAGE DISK */ DEFAULT NULL,
`c2` timestamp NULL DEFAULT NULL,
`vc4` varchar(500) GENERATED ALWAYS AS (lower(cast(`c2` as char(30) charset utf8mb4))) VIRTUAL,
KEY `i0` (`vc4`(2)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST ((month(`c0`) % 9))
SUBPARTITION BY HASH (dayofmonth(`c0`))
SUBPARTITIONS 6
(PARTITION p1 VALUES IN (9,3,1) ENGINE = InnoDB,
PARTITION a2 VALUES IN (4,7) ENGINE = InnoDB,
PARTITION a3 VALUES IN (0,5) ENGINE = InnoDB,
PARTITION a4 VALUES IN (8,6) ENGINE = InnoDB,
PARTITION a5 VALUES IN (2) ENGINE = InnoDB) */;
INSERT INTO `t1` (`c0`, `c1`, `c2`) VALUES ('1999-12-31 23:59:59',0.145734,NULL),('0001-01-01 00:00:00',0.244125,NULL),('9999-12-31 23:59:59',NULL,'2018-06-28 12:03:42'),('1975-10-19 09:12:31',0.17005,'2025-01-12 00:00:46'),('2035-03-14 05:20:00',NULL,'2028-12-18 00:10:13'),('2033-12-02 17:30:38',0.849016,'1993-03-23 08:00:16'),('1977-10-27 22:33:41',NULL,'2032-06-01 00:47:43'),('1976-10-09 03:17:53',NULL,'1999-12-31 15:59:59'),('1978-10-21 10:14:22',-1779120000,'2023-12-31 16:00:00'),('2030-01-28 22:51:34',0.455077,NULL),('1984-12-28 04:19:17',NULL,'0000-00-00 00:00:00'),('1998-10-11 18:38:04',0.100319,NULL),('1971-05-12 09:40:18',NULL,'2003-07-19 10:11:35'),('0000-00-00 00:00:00',NULL,NULL),('0000-00-00 00:00:00',0.642151,'1979-03-25 08:56:15'),('0000-00-00 00:00:00',0.128648,'2024-02-28 16:00:00'),('0000-00-00 00:00:00',0.130981,'2037-12-31 15:59:59'),('2028-08-13 12:47:49',0.23129,'0000-00-00 00:00:00'),('1990-08-28 09:27:40',NULL,'2002-01-27 05:12:30'),('2003-06-29 05:53:03',0.290315,NULL),('1979-02-26 03:58:19',NULL,'2001-04-16 16:34:46'),('2019-02-08 01:44:06',0.0368723,'2009-12-16 07:58:25'),('2021-11-28 19:30:50',NULL,'2004-09-10 04:25:31'),('2031-02-04 06:50:39',0.950512,NULL),('1998-02-04 20:10:12',1805060000,'1971-01-20 23:58:47');
SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.vc4 AS ref3 FROM t1 WHERE (NOT ((t1.c0) NOT IN ("1900-02-29", (CAST('1971-10-29 07:58:33' AS SIGNED)) IN ((- (NULL)))))) ;
alter table t1 remove partitioning;
SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.vc4 AS ref3 FROM t1 WHERE (NOT ((t1.c0) NOT IN ("1900-02-29", (CAST('1971-10-29 07:58:33' AS SIGNED)) IN ((- (NULL)))))) ;
Description: query result change after removing partitioning, such as the below: mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.vc4 AS ref3 FROM t1 WHERE (NOT ((t1.c0) NOT IN ("1900-02-29", (CAST('1971-10-29 07:58:33' AS SIGNED)) IN ((- (NULL)))))) ; Empty set, 1 warning (0.00 sec) mysql> alter table t1 remove partitioning; Query OK, 25 rows affected (0.59 sec) Records: 25 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.vc4 AS ref3 FROM t1 WHERE (NOT ((t1.c0) NOT IN ("1900-02-29", (CAST('1971-10-29 07:58:33' AS SIGNED)) IN ((- (NULL)))))) ; +---------------------+----------+---------------------+---------------------+ | ref0 | ref1 | ref2 | ref3 | +---------------------+----------+---------------------+---------------------+ | 0000-00-00 00:00:00 | NULL | NULL | NULL | | 0000-00-00 00:00:00 | 0.642151 | 1979-03-25 08:56:15 | 1979-03-25 08:56:15 | | 0000-00-00 00:00:00 | 0.128648 | 2024-02-28 16:00:00 | 2024-02-28 16:00:00 | | 0000-00-00 00:00:00 | 0.130981 | 2037-12-31 15:59:59 | 2037-12-31 15:59:59 | +---------------------+----------+---------------------+---------------------+ How to repeat: set sql_mode=''; CREATE TABLE `t1` ( `c0` datetime DEFAULT NULL COMMENT 'asdf', `c1` float /*!50606 STORAGE DISK */ DEFAULT NULL, `c2` timestamp NULL DEFAULT NULL, `vc4` varchar(500) GENERATED ALWAYS AS (lower(cast(`c2` as char(30) charset utf8mb4))) VIRTUAL, KEY `i0` (`vc4`(2)) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY LIST ((month(`c0`) % 9)) SUBPARTITION BY HASH (dayofmonth(`c0`)) SUBPARTITIONS 6 (PARTITION p1 VALUES IN (9,3,1) ENGINE = InnoDB, PARTITION a2 VALUES IN (4,7) ENGINE = InnoDB, PARTITION a3 VALUES IN (0,5) ENGINE = InnoDB, PARTITION a4 VALUES IN (8,6) ENGINE = InnoDB, PARTITION a5 VALUES IN (2) ENGINE = InnoDB) */; INSERT INTO `t1` (`c0`, `c1`, `c2`) VALUES ('1999-12-31 23:59:59',0.145734,NULL),('0001-01-01 00:00:00',0.244125,NULL),('9999-12-31 23:59:59',NULL,'2018-06-28 12:03:42'),('1975-10-19 09:12:31',0.17005,'2025-01-12 00:00:46'),('2035-03-14 05:20:00',NULL,'2028-12-18 00:10:13'),('2033-12-02 17:30:38',0.849016,'1993-03-23 08:00:16'),('1977-10-27 22:33:41',NULL,'2032-06-01 00:47:43'),('1976-10-09 03:17:53',NULL,'1999-12-31 15:59:59'),('1978-10-21 10:14:22',-1779120000,'2023-12-31 16:00:00'),('2030-01-28 22:51:34',0.455077,NULL),('1984-12-28 04:19:17',NULL,'0000-00-00 00:00:00'),('1998-10-11 18:38:04',0.100319,NULL),('1971-05-12 09:40:18',NULL,'2003-07-19 10:11:35'),('0000-00-00 00:00:00',NULL,NULL),('0000-00-00 00:00:00',0.642151,'1979-03-25 08:56:15'),('0000-00-00 00:00:00',0.128648,'2024-02-28 16:00:00'),('0000-00-00 00:00:00',0.130981,'2037-12-31 15:59:59'),('2028-08-13 12:47:49',0.23129,'0000-00-00 00:00:00'),('1990-08-28 09:27:40',NULL,'2002-01-27 05:12:30'),('2003-06-29 05:53:03',0.290315,NULL),('1979-02-26 03:58:19',NULL,'2001-04-16 16:34:46'),('2019-02-08 01:44:06',0.0368723,'2009-12-16 07:58:25'),('2021-11-28 19:30:50',NULL,'2004-09-10 04:25:31'),('2031-02-04 06:50:39',0.950512,NULL),('1998-02-04 20:10:12',1805060000,'1971-01-20 23:58:47'); SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.vc4 AS ref3 FROM t1 WHERE (NOT ((t1.c0) NOT IN ("1900-02-29", (CAST('1971-10-29 07:58:33' AS SIGNED)) IN ((- (NULL)))))) ; alter table t1 remove partitioning; SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.vc4 AS ref3 FROM t1 WHERE (NOT ((t1.c0) NOT IN ("1900-02-29", (CAST('1971-10-29 07:58:33' AS SIGNED)) IN ((- (NULL)))))) ;