Description:
A secondary-index ref lookup that uses an implicitly appended primary-key column can affect a later lookup on the same cached TABLE object. The later lookup binds only tran_date, but scans one partition or none and returns 1 or 0 instead of 2. In the zero-row case, Handler_read_key and Handler_read_next remain 0; FLUSH TABLES restores the correct result. Reproduced on an 8.0.24-based server with standard MySQL 8.0 SQL. An Oracle Community binary has not been independently tested.
How to repeat:
CREATE DATABASE IF NOT EXISTS part_ext_key_bug;
USE part_ext_key_bug;
DROP TABLE IF EXISTS t;
CREATE TABLE t (
client_no INT NOT NULL,
tran_date DATETIME NOT NULL,
internal_key BIGINT DEFAULT NULL,
fee_type VARCHAR(20) NOT NULL,
sc_seq_no INT NOT NULL,
PRIMARY KEY (client_no, tran_date, sc_seq_no),
KEY idx_5m (tran_date, internal_key, fee_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST (MOD(client_no, 16))
SUBPARTITION BY HASH (TO_DAYS(tran_date)) SUBPARTITIONS 2 (
PARTITION p12 VALUES IN (12),
PARTITION p15 VALUES IN (15)
);
INSERT INTO t VALUES
(12, '2026-09-22 00:00:00', 100, 'FT0', 1),
(15, '2026-09-22 00:00:00', 100, 'FT0', 2);
-- Returns 2 (correct).
SELECT COUNT(client_no) FROM t FORCE INDEX (idx_5m)
WHERE tran_date = '2026-09-22 00:00:00';
-- Binds client_no through the implicit secondary-index extension; returns 1.
SELECT COUNT(*) FROM t FORCE INDEX (idx_5m)
WHERE tran_date = '2026-09-22 00:00:00'
AND internal_key = 100 AND fee_type = 'FT0' AND client_no = 12;
-- Returns 1 (wrong; expected 2).
SELECT COUNT(client_no) FROM t FORCE INDEX (idx_5m)
WHERE tran_date = '2026-09-22 00:00:00';
-- Legitimate empty lookup; no LIST partition for client_no = 3.
SELECT COUNT(*) FROM t FORCE INDEX (idx_5m)
WHERE tran_date = '2026-09-22 00:00:00'
AND internal_key = 100 AND fee_type = 'FT0' AND client_no = 3;
-- Returns 0 (wrong; expected 2).
SELECT COUNT(client_no) FROM t FORCE INDEX (idx_5m)
WHERE tran_date = '2026-09-22 00:00:00';
FLUSH TABLES t;
-- Returns 2 again.
SELECT COUNT(client_no) FROM t FORCE INDEX (idx_5m)
WHERE tran_date = '2026-09-22 00:00:00';
Suggested fix:
In sql/sql_partition.cc, set_PF_fields_in_key() can mark implicit index-extension columns as bound, but clear_indicator_in_key_fields() clears only user_defined_key_parts. Clear the same key parts that were marked for each ref lookup, including implicit extension columns.
