Description:
When a table is BOTH partitioned and subpartitioned (e.g. PARTITION BY RANGE ...
SUBPARTITION BY HASH ...), and a secondary index contains a VIRTUAL generated
column, a query that the optimizer serves from that index computes the
partition set incorrectly and silently drops qualifying rows.
A plain SELECT (no hints) returns FEWER rows than actually exist. The exact same
query forced to a table scan (IGNORE INDEX) returns the correct rows, and the
non-partitioned/plain-partitioned equivalents are correct. EXPLAIN shows the
optimizer estimates the correct number of rows (rows=40) but execution returns
only 14, so the discrepancy is in execution-time partition-set computation for
an index whose key contains a virtual generated column.
The bug requires ALL of:
(1) the table is subpartitioned (RANGE/LIST + SUBPARTITION BY HASH/KEY), and
(2) the query is served by a secondary index whose key contains a VIRTUAL
generated column.
Removing the subpartitioning, using a non-virtual index, or forcing a table
scan all yield the correct result.
The discrepancy is in the execution-time partition-set computation for a
subpartitioned table (get_partition_set / check_part_func_bound /
get_full_part_id_from_key): an index whose key contains a virtual generated
column is treated as binding the partition function, so a single (sub)partition
id is computed and the partitions that actually hold the qualifying rows are
skipped.
How to repeat:
DROP DATABASE IF EXISTS bugtest;
CREATE DATABASE bugtest;
USE bugtest;
CREATE TABLE t (
a INT,
b INT,
c INT,
g INT AS (b + c*3) VIRTUAL, -- virtual generated column ...
KEY kag (a, g) -- ... contained in a secondary index
) ENGINE=InnoDB
PARTITION BY RANGE (a) SUBPARTITION BY HASH (b) SUBPARTITIONS 3 (
PARTITION p0 VALUES LESS THAN (50),
PARTITION p1 VALUES LESS THAN MAXVALUE
);
-- 40 rows, EVERY row has a = NULL
INSERT INTO t (a,b,c)
WITH RECURSIVE seq(n) AS (SELECT 0 UNION ALL SELECT n+1 FROM seq WHERE n < 39)
SELECT NULL, n, 1 FROM seq;
SELECT COUNT(*) FROM t; -- 40 (all rows)
SELECT COUNT(*) FROM t WHERE a IS NULL; -- 14 <-- WRONG (expected 40)
SELECT COUNT(*) FROM t FORCE INDEX (kag) WHERE a IS NULL; -- 14 <-- WRONG (deterministic)
SELECT COUNT(*) FROM t IGNORE INDEX (kag) WHERE a IS NULL;-- 40 (correct)
EXPLAIN FORMAT=TREE SELECT COUNT(*) FROM t WHERE a IS NULL;
Suggested fix:
When computing the partition/subpartition set from an index key
(get_partition_set() / check_part_func_bound() / get_full_part_id_from_key()),
do not treat an index whose key contains a virtual generated column as having
"bound" the partition function; in that case fall back to scanning the
candidate partitions instead of computing a single (sub)partition id.
Description: When a table is BOTH partitioned and subpartitioned (e.g. PARTITION BY RANGE ... SUBPARTITION BY HASH ...), and a secondary index contains a VIRTUAL generated column, a query that the optimizer serves from that index computes the partition set incorrectly and silently drops qualifying rows. A plain SELECT (no hints) returns FEWER rows than actually exist. The exact same query forced to a table scan (IGNORE INDEX) returns the correct rows, and the non-partitioned/plain-partitioned equivalents are correct. EXPLAIN shows the optimizer estimates the correct number of rows (rows=40) but execution returns only 14, so the discrepancy is in execution-time partition-set computation for an index whose key contains a virtual generated column. The bug requires ALL of: (1) the table is subpartitioned (RANGE/LIST + SUBPARTITION BY HASH/KEY), and (2) the query is served by a secondary index whose key contains a VIRTUAL generated column. Removing the subpartitioning, using a non-virtual index, or forcing a table scan all yield the correct result. The discrepancy is in the execution-time partition-set computation for a subpartitioned table (get_partition_set / check_part_func_bound / get_full_part_id_from_key): an index whose key contains a virtual generated column is treated as binding the partition function, so a single (sub)partition id is computed and the partitions that actually hold the qualifying rows are skipped. How to repeat: DROP DATABASE IF EXISTS bugtest; CREATE DATABASE bugtest; USE bugtest; CREATE TABLE t ( a INT, b INT, c INT, g INT AS (b + c*3) VIRTUAL, -- virtual generated column ... KEY kag (a, g) -- ... contained in a secondary index ) ENGINE=InnoDB PARTITION BY RANGE (a) SUBPARTITION BY HASH (b) SUBPARTITIONS 3 ( PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN MAXVALUE ); -- 40 rows, EVERY row has a = NULL INSERT INTO t (a,b,c) WITH RECURSIVE seq(n) AS (SELECT 0 UNION ALL SELECT n+1 FROM seq WHERE n < 39) SELECT NULL, n, 1 FROM seq; SELECT COUNT(*) FROM t; -- 40 (all rows) SELECT COUNT(*) FROM t WHERE a IS NULL; -- 14 <-- WRONG (expected 40) SELECT COUNT(*) FROM t FORCE INDEX (kag) WHERE a IS NULL; -- 14 <-- WRONG (deterministic) SELECT COUNT(*) FROM t IGNORE INDEX (kag) WHERE a IS NULL;-- 40 (correct) EXPLAIN FORMAT=TREE SELECT COUNT(*) FROM t WHERE a IS NULL; Suggested fix: When computing the partition/subpartition set from an index key (get_partition_set() / check_part_func_bound() / get_full_part_id_from_key()), do not treat an index whose key contains a virtual generated column as having "bound" the partition function; in that case fall back to scanning the candidate partitions instead of computing a single (sub)partition id.