Description:
When executing a NATURAL JOIN self-join on a table that has an expression index (involving a partition column) and is partitioned by KEY, the first execution returns the correct number of rows (2), but subsequent executions of the exact same query return only one row.
Dropping the expression index i4 makes the problem disappear and the query consistently returns 2 rows. The issue appears to be related to the index i4 which is defined on (c3, ((c1 % 100)) DESC, c12) and the table is partitioned by KEY (c3, c1).
How to repeat:
Run the following SQL script in a clean MySQL instance:
SET SQL_MODE='';
DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (
`c13` mediumblob,
`c1` tinyint DEFAULT NULL,
`c2` date DEFAULT NULL,
`c3` time(6) DEFAULT NULL,
`c4` datetime(4) DEFAULT NULL,
`c5` timestamp NOT NULL,
`c6` year DEFAULT NULL,
`c7` varchar(159) DEFAULT NULL,
`c8` varbinary(19),
`c9` tinyblob,
`vc1` tinyint GENERATED ALWAYS AS (`c2`) STORED,
`vc2` datetime(4) GENERATED ALWAYS AS (ifnull(`c5`,NULL)) VIRTUAL,
`c12` double DEFAULT NULL,
KEY `i1` ((char_length(`c2`))),
KEY `i4` (`c3`,((`c1` % 100)) DESC,`c12`) USING BTREE
) engine=innodb DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY KEY (c3,c1)
PARTITIONS 16*/;
INSERT INTO `t3` (`c13`, `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c12`) VALUES
(_binary '.',48,'1997-11-13','10:15:32.000000','2035-03-09 09:20:30.0000','1976-12-31 11:40:33',2010,NULL,_binary 'Q',_binary '.iKDu}%MT.C.@+~W]8&nY&sQf@h}u3',2718911),
(_binary '`jvW',127,NULL,'04:59:46.000000','2033-09-17 00:40:50.0000','2018-08-25 04:59:54',NULL,'k>]$o<!q+e6Y2(~H&[}',_binary 'BpZO$bmsr7[{dtnfvkY',_binary 'W',1.3502190828944468e308),
(_binary '~+#@Yh%@^F-1f@s.`Sy9#^j1qiU!2tijz@LEg$Z<zR$G17j.}7`-J_EXx1lJHot~EN*A@53V.HCaF6L[O5L0R<{.y_S5',-8,'2013-11-25','07:06:22.000000','1992-09-17 03:17:47.0000','1980-07-24 07:30:00',1974,'H',_binary 'INL>nx`gP_mL%[UE*R@',_binary 'A0|hnI$A0yNUc555j|f)FNbIv,YC>ka`9`p&J15x!mN~hMpxZh1$HJGF<]^HtP~?8ld8l<N<S*Hfap`#2}.]|r0QKwT-',2.9097653110417406e38),
(_binary 'B',127,'1992-01-12','09:47:13.000000','1993-07-29 18:46:28.0000','2014-03-18 12:45:22',2009,'H',_binary 'aG7euLszj%Z{7wH_a8u',NULL,24595),
(_binary '4',6,'2024-08-07','18:39:00.000000','2000-04-13 23:33:00.0000','2019-09-14 15:22:28',1999,'^[!M,~>xOn975}sM=^r[?qpQHZkcMW(~{u7!iP{4@a`Q]Q?y_)vj>2n~kIRFE2sZjTN|@-X&YOBP5h(XXzKfproQtZUR',_binary '1#8|x?U-tNRn6swj_Pb',_binary '^*fg',-126);
-- Execute the query first time:
SELECT t3.c8 AS ca1, ta1.vc2 AS ca2, t3.c1 AS ca3 FROM t3 NATURAL JOIN t3 AS ta1;
-- Expected output: 2 rows (see below)
mysql> select t3.c8 as ca1 ,ta1.vc2 as ca2 ,t3.c1 as ca3 from t3 natural join t3 as ta1;
+------------------------------------------+--------------------------+------+
| ca1 | ca2 | ca3 |
+------------------------------------------+--------------------------+------+
| 0x494E4C3E6E786067505F6D4C255B55452A5240 | 1980-07-24 07:30:00.0000 | -8 |
| 0x3123387C783F552D744E526E3673776A5F5062 | 2019-09-14 15:22:28.0000 | 6 |
+------------------------------------------+--------------------------+------+
2 rows in set (0.00 sec)
-- Execute the same query again immediately:
SELECT t3.c8 AS ca1, ta1.vc2 AS ca2, t3.c1 AS ca3 FROM t3 NATURAL JOIN t3 AS ta1;
mysql> select t3.c8 as ca1 ,ta1.vc2 as ca2 ,t3.c1 as ca3 from t3 natural join t3 as ta1;
+------------------------------------------+--------------------------+------+
| ca1 | ca2 | ca3 |
+------------------------------------------+--------------------------+------+
| 0x3123387C783F552D744E526E3673776A5F5062 | 2019-09-14 15:22:28.0000 | 6 |
+------------------------------------------+--------------------------+------+
1 row in set (0.00 sec)
-- The execution plan is as follows:
mysql> explain select t3.c8 as ca1 ,ta1.vc2 as ca2 ,t3.c1 as ca3 from t3 natural join t3 as ta1;
+----+-------------+-------+-------------------------------------------------------+------+---------------+------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------------------------------------------------+------+---------------+------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | t3 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15 | ALL | i4 | NULL | NULL | NULL | 5 | 100.00 | Using where |
| 1 | SIMPLE | ta1 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15 | ref | i4 | i4 | 7 | db121191668_38.t3.c3 | 3 | 20.00 | Using where |
+----+-------------+-------+-------------------------------------------------------+------+---------------+------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
-- After the i4 index is deleted, the results of the continuous queries are correct.
mysql> select t3.c8 as ca1 ,ta1.vc2 as ca2 ,t3.c1 as ca3 from t3 natural join t3 as ta1;
+------------------------------------------+--------------------------+------+
| ca1 | ca2 | ca3 |
+------------------------------------------+--------------------------+------+
| 0x494E4C3E6E786067505F6D4C255B55452A5240 | 1980-07-24 07:30:00.0000 | -8 |
| 0x3123387C783F552D744E526E3673776A5F5062 | 2019-09-14 15:22:28.0000 | 6 |
+------------------------------------------+--------------------------+------+
2 rows in set (0.00 sec)
mysql> select t3.c8 as ca1 ,ta1.vc2 as ca2 ,t3.c1 as ca3 from t3 natural join t3 as ta1;
+------------------------------------------+--------------------------+------+
| ca1 | ca2 | ca3 |
+------------------------------------------+--------------------------+------+
| 0x494E4C3E6E786067505F6D4C255B55452A5240 | 1980-07-24 07:30:00.0000 | -8 |
| 0x3123387C783F552D744E526E3673776A5F5062 | 2019-09-14 15:22:28.0000 | 6 |
+------------------------------------------+--------------------------+------+
2 rows in set (0.00 sec)
-- The execution plan now is as follows:
mysql> explain select t3.c8 as ca1 ,ta1.vc2 as ca2 ,t3.c1 as ca3 from t3 natural join t3 as ta1;
+----+-------------+-------+-------------------------------------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------------------------------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | t3 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15 | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
| 1 | SIMPLE | ta1 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15 | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+-------------------------------------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
© 2026, Oracle Corporation and/or its affiliates
Description: When executing a NATURAL JOIN self-join on a table that has an expression index (involving a partition column) and is partitioned by KEY, the first execution returns the correct number of rows (2), but subsequent executions of the exact same query return only one row. Dropping the expression index i4 makes the problem disappear and the query consistently returns 2 rows. The issue appears to be related to the index i4 which is defined on (c3, ((c1 % 100)) DESC, c12) and the table is partitioned by KEY (c3, c1). How to repeat: Run the following SQL script in a clean MySQL instance: SET SQL_MODE=''; DROP TABLE IF EXISTS `t3`; CREATE TABLE `t3` ( `c13` mediumblob, `c1` tinyint DEFAULT NULL, `c2` date DEFAULT NULL, `c3` time(6) DEFAULT NULL, `c4` datetime(4) DEFAULT NULL, `c5` timestamp NOT NULL, `c6` year DEFAULT NULL, `c7` varchar(159) DEFAULT NULL, `c8` varbinary(19), `c9` tinyblob, `vc1` tinyint GENERATED ALWAYS AS (`c2`) STORED, `vc2` datetime(4) GENERATED ALWAYS AS (ifnull(`c5`,NULL)) VIRTUAL, `c12` double DEFAULT NULL, KEY `i1` ((char_length(`c2`))), KEY `i4` (`c3`,((`c1` % 100)) DESC,`c12`) USING BTREE ) engine=innodb DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC /*!50100 PARTITION BY KEY (c3,c1) PARTITIONS 16*/; INSERT INTO `t3` (`c13`, `c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`, `c12`) VALUES (_binary '.',48,'1997-11-13','10:15:32.000000','2035-03-09 09:20:30.0000','1976-12-31 11:40:33',2010,NULL,_binary 'Q',_binary '.iKDu}%MT.C.@+~W]8&nY&sQf@h}u3',2718911), (_binary '`jvW',127,NULL,'04:59:46.000000','2033-09-17 00:40:50.0000','2018-08-25 04:59:54',NULL,'k>]$o<!q+e6Y2(~H&[}',_binary 'BpZO$bmsr7[{dtnfvkY',_binary 'W',1.3502190828944468e308), (_binary '~+#@Yh%@^F-1f@s.`Sy9#^j1qiU!2tijz@LEg$Z<zR$G17j.}7`-J_EXx1lJHot~EN*A@53V.HCaF6L[O5L0R<{.y_S5',-8,'2013-11-25','07:06:22.000000','1992-09-17 03:17:47.0000','1980-07-24 07:30:00',1974,'H',_binary 'INL>nx`gP_mL%[UE*R@',_binary 'A0|hnI$A0yNUc555j|f)FNbIv,YC>ka`9`p&J15x!mN~hMpxZh1$HJGF<]^HtP~?8ld8l<N<S*Hfap`#2}.]|r0QKwT-',2.9097653110417406e38), (_binary 'B',127,'1992-01-12','09:47:13.000000','1993-07-29 18:46:28.0000','2014-03-18 12:45:22',2009,'H',_binary 'aG7euLszj%Z{7wH_a8u',NULL,24595), (_binary '4',6,'2024-08-07','18:39:00.000000','2000-04-13 23:33:00.0000','2019-09-14 15:22:28',1999,'^[!M,~>xOn975}sM=^r[?qpQHZkcMW(~{u7!iP{4@a`Q]Q?y_)vj>2n~kIRFE2sZjTN|@-X&YOBP5h(XXzKfproQtZUR',_binary '1#8|x?U-tNRn6swj_Pb',_binary '^*fg',-126); -- Execute the query first time: SELECT t3.c8 AS ca1, ta1.vc2 AS ca2, t3.c1 AS ca3 FROM t3 NATURAL JOIN t3 AS ta1; -- Expected output: 2 rows (see below) mysql> select t3.c8 as ca1 ,ta1.vc2 as ca2 ,t3.c1 as ca3 from t3 natural join t3 as ta1; +------------------------------------------+--------------------------+------+ | ca1 | ca2 | ca3 | +------------------------------------------+--------------------------+------+ | 0x494E4C3E6E786067505F6D4C255B55452A5240 | 1980-07-24 07:30:00.0000 | -8 | | 0x3123387C783F552D744E526E3673776A5F5062 | 2019-09-14 15:22:28.0000 | 6 | +------------------------------------------+--------------------------+------+ 2 rows in set (0.00 sec) -- Execute the same query again immediately: SELECT t3.c8 AS ca1, ta1.vc2 AS ca2, t3.c1 AS ca3 FROM t3 NATURAL JOIN t3 AS ta1; mysql> select t3.c8 as ca1 ,ta1.vc2 as ca2 ,t3.c1 as ca3 from t3 natural join t3 as ta1; +------------------------------------------+--------------------------+------+ | ca1 | ca2 | ca3 | +------------------------------------------+--------------------------+------+ | 0x3123387C783F552D744E526E3673776A5F5062 | 2019-09-14 15:22:28.0000 | 6 | +------------------------------------------+--------------------------+------+ 1 row in set (0.00 sec) -- The execution plan is as follows: mysql> explain select t3.c8 as ca1 ,ta1.vc2 as ca2 ,t3.c1 as ca3 from t3 natural join t3 as ta1; +----+-------------+-------+-------------------------------------------------------+------+---------------+------+---------+----------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------------------------------------------------+------+---------------+------+---------+----------------------+------+----------+-------------+ | 1 | SIMPLE | t3 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15 | ALL | i4 | NULL | NULL | NULL | 5 | 100.00 | Using where | | 1 | SIMPLE | ta1 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15 | ref | i4 | i4 | 7 | db121191668_38.t3.c3 | 3 | 20.00 | Using where | +----+-------------+-------+-------------------------------------------------------+------+---------------+------+---------+----------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) -- After the i4 index is deleted, the results of the continuous queries are correct. mysql> select t3.c8 as ca1 ,ta1.vc2 as ca2 ,t3.c1 as ca3 from t3 natural join t3 as ta1; +------------------------------------------+--------------------------+------+ | ca1 | ca2 | ca3 | +------------------------------------------+--------------------------+------+ | 0x494E4C3E6E786067505F6D4C255B55452A5240 | 1980-07-24 07:30:00.0000 | -8 | | 0x3123387C783F552D744E526E3673776A5F5062 | 2019-09-14 15:22:28.0000 | 6 | +------------------------------------------+--------------------------+------+ 2 rows in set (0.00 sec) mysql> select t3.c8 as ca1 ,ta1.vc2 as ca2 ,t3.c1 as ca3 from t3 natural join t3 as ta1; +------------------------------------------+--------------------------+------+ | ca1 | ca2 | ca3 | +------------------------------------------+--------------------------+------+ | 0x494E4C3E6E786067505F6D4C255B55452A5240 | 1980-07-24 07:30:00.0000 | -8 | | 0x3123387C783F552D744E526E3673776A5F5062 | 2019-09-14 15:22:28.0000 | 6 | +------------------------------------------+--------------------------+------+ 2 rows in set (0.00 sec) -- The execution plan now is as follows: mysql> explain select t3.c8 as ca1 ,ta1.vc2 as ca2 ,t3.c1 as ca3 from t3 natural join t3 as ta1; +----+-------------+-------+-------------------------------------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------------------------------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | t3 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15 | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL | | 1 | SIMPLE | ta1 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15 | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+-------------------------------------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)