Description:
For identical table definitions and data, MySQL returns different results for the same query on partitioned vs non-partitioned tables. The partitioned table query only accesses specific partitions (p4 and p5 subpartitions) and returns incomplete results, while the non-partitioned table returns all expected records.
How to repeat:
1. Create Partitioned Table with Data
-- Clean up
DROP TABLE IF EXISTS `t0`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
SET sql_mode='';
-- Create partitioned table
CREATE TABLE `t0` (
`c0` mediumint(8) unsigned zerofill /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL COMMENT 'asdf',
`c1` year /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf',
`c2` year /*!50606 STORAGE DISK */ DEFAULT NULL,
`vc3` timestamp GENERATED ALWAYS AS (cast(`c1` as unsigned)) STORED NOT NULL,
`c4` float /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf',
`c5` time /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL,
`vc6` decimal(10,0) unsigned zerofill GENERATED ALWAYS AS (cast(`c1` as unsigned)) STORED,
UNIQUE KEY `i2` (`c0`,`vc6`,`c2` DESC,`c4`,`c5`,`c1`,`vc3` DESC),
KEY `i3` (`vc6`,`vc3` DESC,`c5`,`c4`),
KEY `i4` (`c5`,`c2`,`c1`,`vc3`,`vc6`) /*!80000 INVISIBLE */
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_AUTO_RECALC=0 CHECKSUM=1
/*!50100 PARTITION BY RANGE (`c1`)
SUBPARTITION BY KEY (c1)
SUBPARTITIONS 5
(PARTITION p1 VALUES LESS THAN (1971),
PARTITION p2 VALUES LESS THAN (1978),
PARTITION p3 VALUES LESS THAN (1999),
PARTITION p4 VALUES LESS THAN (2023),
PARTITION p5 VALUES LESS THAN MAXVALUE) */;
-- Insert test data
INSERT INTO `t0` (`c0`, `c1`, `c2`, `c4`, `c5`) VALUES
(NULL,0000,NULL,0.0680067,NULL),
(NULL,0000,NULL,0.199446,NULL),
(NULL,0000,NULL,0.24653,NULL),
(NULL,1924,NULL,0.668778,NULL),
(NULL,1970,2035,0.878674,'03:06:02'),
(00000000,0000,2004,0.10658,'23:59:59'),
(16777215,0000,2147,1197460000,'12:00:00'),
(16777215,1970,1974,0.5632,'22:58:14'),
(16777215,1970,0000,0.116107,'06:02:17'),
(NULL,1988,NULL,0.803036,NULL),
(NULL,2000,NULL,-1074570000,NULL),
(NULL,2067,NULL,1063820000,NULL),
(NULL,2087,NULL,0.573536,NULL),
(NULL,2137,NULL,0.368778,NULL),
(NULL,2150,NULL,0.30543,NULL),
(00000000,2075,0000,1771060000,NULL),
(00000000,2113,2108,0.251017,'720:00:00'),
(16777215,2023,2000,298344000,'12:34:57'),
(16777215,2112,2061,NULL,'09:42:55'),
(00000000,2052,1928,0.0180381,'04:20:31');
2. Execute Query on Partitioned Table
-- Test query
SELECT ALL
t0.c0 AS ref0,
t0.c1 AS ref1,
t0.c2 AS ref2,
t0.c4 AS ref3,
t0.vc3 AS ref4
FROM t0
WHERE ((CAST(t0.vc3 AS SIGNED)) IS NOT NULL) <= (t0.c1);
3. Result from partitioned table (10 rows):
+----------+------+------+-------------+---------------------+
| ref0 | ref1 | ref2 | ref3 | ref4 |
+----------+------+------+-------------+---------------------+
| NULL | 2000 | NULL | -1074570000 | 0000-00-00 00:00:00 |
| NULL | 2067 | NULL | 1063820000 | 0000-00-00 00:00:00 |
| NULL | 2087 | NULL | 0.573536 | 0000-00-00 00:00:00 |
| NULL | 2137 | NULL | 0.368778 | 0000-00-00 00:00:00 |
| NULL | 2150 | NULL | 0.30543 | 0000-00-00 00:00:00 |
| 00000000 | 2075 | 0000 | 1771060000 | 0000-00-00 00:00:00 |
| 00000000 | 2113 | 2108 | 0.251017 | 0000-00-00 00:00:00 |
| 16777215 | 2023 | 2000 | 298344000 | 0000-00-00 00:00:00 |
| 16777215 | 2112 | 2061 | NULL | 0000-00-00 00:00:00 |
| 00000000 | 2052 | 1928 | 0.0180381 | 0000-00-00 00:00:00 |
+----------+------+------+-------------+---------------------+
10 rows in set (0.00 sec)
3. Check Execution Plan for Partitioned Query
EXPLAIN SELECT ALL t0.c0 AS ref0, t0.c1 AS ref1, t0.c2 AS ref2, t0.c4 AS ref3, t0.vc3 AS ref4
FROM t0
WHERE ((CAST(t0.vc3 AS SIGNED)) IS NOT NULL) <= (t0.c1);
+----+-------------+-------+-------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | t0 | p4_p4sp0,p4_p4sp1,p4_p4sp2,p4_p4sp3,p4_p4sp4,p5_p5sp0,p5_p5sp1,p5_p5sp2,p5_p5sp3,p5_p5sp4 | index | i2 | i2 | 27 | NULL | 10 | 33.33 | Using where; Using index |
+----+-------------+-------+-------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
Key observation: The query only accesses partitions p4 and p5 (and their subpartitions), ignoring p1, p2, and p3.
4. Remove Partitioning
-- Remove partitioning to create identical non-partitioned table
ALTER TABLE t0 REMOVE PARTITIONING;
5. Execute Same Query on Non-Partitioned Table
-- Same query on non-partitioned table
SELECT ALL
t0.c0 AS ref0,
t0.c1 AS ref1,
t0.c2 AS ref2,
t0.c4 AS ref3,
t0.vc3 AS ref4
FROM t0
WHERE ((CAST(t0.vc3 AS SIGNED)) IS NOT NULL) <= (t0.c1);
Result from non-partitioned table (15 rows):
+----------+------+------+-------------+---------------------+
| ref0 | ref1 | ref2 | ref3 | ref4 |
+----------+------+------+-------------+---------------------+
| NULL | 1924 | NULL | 0.668778 | 0000-00-00 00:00:00 |
| NULL | 1970 | 2035 | 0.878674 | 0000-00-00 00:00:00 |
| NULL | 1988 | NULL | 0.803036 | 0000-00-00 00:00:00 |
| NULL | 2000 | NULL | -1074570000 | 0000-00-00 00:00:00 |
| NULL | 2067 | NULL | 1063820000 | 0000-00-00 00:00:00 |
| NULL | 2087 | NULL | 0.573536 | 0000-00-00 00:00:00 |
| NULL | 2137 | NULL | 0.368778 | 0000-00-00 00:00:00 |
| NULL | 2150 | NULL | 0.30543 | 0000-00-00 00:00:00 |
| 00000000 | 2052 | 1928 | 0.0180381 | 0000-00-00 00:00:00 |
| 00000000 | 2075 | 0000 | 1771060000 | 0000-00-00 00:00:00 |
| 00000000 | 2113 | 2108 | 0.251017 | 0000-00-00 00:00:00 |
| 16777215 | 1970 | 1974 | 0.5632 | 0000-00-00 00:00:00 |
| 16777215 | 1970 | 0000 | 0.116107 | 0000-00-00 00:00:00 |
| 16777215 | 2023 | 2000 | 298344000 | 0000-00-00 00:00:00 |
| 16777215 | 2112 | 2061 | NULL | 0000-00-00 00:00:00 |
+----------+------+------+-------------+---------------------+
15 rows in set (0.00 sec)
6. Expected Behavior
For identical table schema and data, queries should return identical results regardless of partitioning. The partitioned table should return all 15 matching records, not just those from partitions p4 and p5.
Description: For identical table definitions and data, MySQL returns different results for the same query on partitioned vs non-partitioned tables. The partitioned table query only accesses specific partitions (p4 and p5 subpartitions) and returns incomplete results, while the non-partitioned table returns all expected records. How to repeat: 1. Create Partitioned Table with Data -- Clean up DROP TABLE IF EXISTS `t0`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; SET sql_mode=''; -- Create partitioned table CREATE TABLE `t0` ( `c0` mediumint(8) unsigned zerofill /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL COMMENT 'asdf', `c1` year /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf', `c2` year /*!50606 STORAGE DISK */ DEFAULT NULL, `vc3` timestamp GENERATED ALWAYS AS (cast(`c1` as unsigned)) STORED NOT NULL, `c4` float /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf', `c5` time /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, `vc6` decimal(10,0) unsigned zerofill GENERATED ALWAYS AS (cast(`c1` as unsigned)) STORED, UNIQUE KEY `i2` (`c0`,`vc6`,`c2` DESC,`c4`,`c5`,`c1`,`vc3` DESC), KEY `i3` (`vc6`,`vc3` DESC,`c5`,`c4`), KEY `i4` (`c5`,`c2`,`c1`,`vc3`,`vc6`) /*!80000 INVISIBLE */ ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_AUTO_RECALC=0 CHECKSUM=1 /*!50100 PARTITION BY RANGE (`c1`) SUBPARTITION BY KEY (c1) SUBPARTITIONS 5 (PARTITION p1 VALUES LESS THAN (1971), PARTITION p2 VALUES LESS THAN (1978), PARTITION p3 VALUES LESS THAN (1999), PARTITION p4 VALUES LESS THAN (2023), PARTITION p5 VALUES LESS THAN MAXVALUE) */; -- Insert test data INSERT INTO `t0` (`c0`, `c1`, `c2`, `c4`, `c5`) VALUES (NULL,0000,NULL,0.0680067,NULL), (NULL,0000,NULL,0.199446,NULL), (NULL,0000,NULL,0.24653,NULL), (NULL,1924,NULL,0.668778,NULL), (NULL,1970,2035,0.878674,'03:06:02'), (00000000,0000,2004,0.10658,'23:59:59'), (16777215,0000,2147,1197460000,'12:00:00'), (16777215,1970,1974,0.5632,'22:58:14'), (16777215,1970,0000,0.116107,'06:02:17'), (NULL,1988,NULL,0.803036,NULL), (NULL,2000,NULL,-1074570000,NULL), (NULL,2067,NULL,1063820000,NULL), (NULL,2087,NULL,0.573536,NULL), (NULL,2137,NULL,0.368778,NULL), (NULL,2150,NULL,0.30543,NULL), (00000000,2075,0000,1771060000,NULL), (00000000,2113,2108,0.251017,'720:00:00'), (16777215,2023,2000,298344000,'12:34:57'), (16777215,2112,2061,NULL,'09:42:55'), (00000000,2052,1928,0.0180381,'04:20:31'); 2. Execute Query on Partitioned Table -- Test query SELECT ALL t0.c0 AS ref0, t0.c1 AS ref1, t0.c2 AS ref2, t0.c4 AS ref3, t0.vc3 AS ref4 FROM t0 WHERE ((CAST(t0.vc3 AS SIGNED)) IS NOT NULL) <= (t0.c1); 3. Result from partitioned table (10 rows): +----------+------+------+-------------+---------------------+ | ref0 | ref1 | ref2 | ref3 | ref4 | +----------+------+------+-------------+---------------------+ | NULL | 2000 | NULL | -1074570000 | 0000-00-00 00:00:00 | | NULL | 2067 | NULL | 1063820000 | 0000-00-00 00:00:00 | | NULL | 2087 | NULL | 0.573536 | 0000-00-00 00:00:00 | | NULL | 2137 | NULL | 0.368778 | 0000-00-00 00:00:00 | | NULL | 2150 | NULL | 0.30543 | 0000-00-00 00:00:00 | | 00000000 | 2075 | 0000 | 1771060000 | 0000-00-00 00:00:00 | | 00000000 | 2113 | 2108 | 0.251017 | 0000-00-00 00:00:00 | | 16777215 | 2023 | 2000 | 298344000 | 0000-00-00 00:00:00 | | 16777215 | 2112 | 2061 | NULL | 0000-00-00 00:00:00 | | 00000000 | 2052 | 1928 | 0.0180381 | 0000-00-00 00:00:00 | +----------+------+------+-------------+---------------------+ 10 rows in set (0.00 sec) 3. Check Execution Plan for Partitioned Query EXPLAIN SELECT ALL t0.c0 AS ref0, t0.c1 AS ref1, t0.c2 AS ref2, t0.c4 AS ref3, t0.vc3 AS ref4 FROM t0 WHERE ((CAST(t0.vc3 AS SIGNED)) IS NOT NULL) <= (t0.c1); +----+-------------+-------+-------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t0 | p4_p4sp0,p4_p4sp1,p4_p4sp2,p4_p4sp3,p4_p4sp4,p5_p5sp0,p5_p5sp1,p5_p5sp2,p5_p5sp3,p5_p5sp4 | index | i2 | i2 | 27 | NULL | 10 | 33.33 | Using where; Using index | +----+-------------+-------+-------------------------------------------------------------------------------------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) Key observation: The query only accesses partitions p4 and p5 (and their subpartitions), ignoring p1, p2, and p3. 4. Remove Partitioning -- Remove partitioning to create identical non-partitioned table ALTER TABLE t0 REMOVE PARTITIONING; 5. Execute Same Query on Non-Partitioned Table -- Same query on non-partitioned table SELECT ALL t0.c0 AS ref0, t0.c1 AS ref1, t0.c2 AS ref2, t0.c4 AS ref3, t0.vc3 AS ref4 FROM t0 WHERE ((CAST(t0.vc3 AS SIGNED)) IS NOT NULL) <= (t0.c1); Result from non-partitioned table (15 rows): +----------+------+------+-------------+---------------------+ | ref0 | ref1 | ref2 | ref3 | ref4 | +----------+------+------+-------------+---------------------+ | NULL | 1924 | NULL | 0.668778 | 0000-00-00 00:00:00 | | NULL | 1970 | 2035 | 0.878674 | 0000-00-00 00:00:00 | | NULL | 1988 | NULL | 0.803036 | 0000-00-00 00:00:00 | | NULL | 2000 | NULL | -1074570000 | 0000-00-00 00:00:00 | | NULL | 2067 | NULL | 1063820000 | 0000-00-00 00:00:00 | | NULL | 2087 | NULL | 0.573536 | 0000-00-00 00:00:00 | | NULL | 2137 | NULL | 0.368778 | 0000-00-00 00:00:00 | | NULL | 2150 | NULL | 0.30543 | 0000-00-00 00:00:00 | | 00000000 | 2052 | 1928 | 0.0180381 | 0000-00-00 00:00:00 | | 00000000 | 2075 | 0000 | 1771060000 | 0000-00-00 00:00:00 | | 00000000 | 2113 | 2108 | 0.251017 | 0000-00-00 00:00:00 | | 16777215 | 1970 | 1974 | 0.5632 | 0000-00-00 00:00:00 | | 16777215 | 1970 | 0000 | 0.116107 | 0000-00-00 00:00:00 | | 16777215 | 2023 | 2000 | 298344000 | 0000-00-00 00:00:00 | | 16777215 | 2112 | 2061 | NULL | 0000-00-00 00:00:00 | +----------+------+------+-------------+---------------------+ 15 rows in set (0.00 sec) 6. Expected Behavior For identical table schema and data, queries should return identical results regardless of partitioning. The partitioned table should return all 15 matching records, not just those from partitions p4 and p5.