Description:
Create table with virtual column and partition table, select may return less data.
mysql> DROP TABLE IF EXISTS users2;
Query OK, 0 rows affected, 1 warning (0.012 sec)
mysql> CREATE TABLE `users2` (
-> `id` int NOT NULL,
-> `user_id` int COLLATE utf8mb4_bin NOT NULL,
-> `status` int COLLATE utf8mb4_bin NOT NULL,
-> `name` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
-> `score` int COLLATE utf8mb4_bin GENERATED ALWAYS AS (`status`*2),
-> PRIMARY KEY (`status`, `user_id`),
-> KEY `idx_user_score` (`user_id`, `score`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
-> PARTITION BY LIST (`status`)
-> SUBPARTITION BY HASH(`user_id`)
-> SUBPARTITIONS 2(
-> PARTITION p2 VALUES IN (2) ENGINE = InnoDB,
-> PARTITION p3 VALUES IN (3) ENGINE = InnoDB,
-> PARTITION p4 VALUES IN (4) ENGINE = InnoDB);
Query OK, 0 rows affected (0.122 sec)
mysql>
mysql> INSERT INTO users2 (id, user_id, status, name) VALUES (1, 20260120, 2, 'tj');
Query OK, 1 row affected (0.016 sec)
mysql> INSERT INTO users2 (id, user_id, status, name) VALUES (2, 20260120, 3, 'tj');
Query OK, 1 row affected (0.002 sec)
mysql>
mysql> -- 查询
Query OK, 0 rows affected (0.000 sec)
mysql> SELECT * FROM users2 WHERE user_id = 20260120;
+----+----------+--------+------+-------+
| id | user_id | status | name | score |
+----+----------+--------+------+-------+
| 2 | 20260120 | 3 | tj | 6 |
+----+----------+--------+------+-------+
1 row in set (0.004 sec)
mysql> quit
Bye
Insert two rows but select only return one.
If we reconnect the server, execute the statement before any other selection, return empty result.
./bin/mysql -S ./mysql-test/var/tmp/mysqld.1.sock -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 9.5.0-cynos-debug 20220401
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM users2 WHERE user_id = 20260120;
Empty set (0.003 sec)
mysql> quit
Bye
How to repeat:
see description
Suggested fix:
The problme is in function : get_partition_set:
```
/*
Set an indicator on all partition fields that are bound.
If at least one PF-field was bound it pays off to check whether
the PF or PPF or SPF has been bound.
(PF = Partition Function, SPF = Subpartition Function and
PPF = Partition Function part of subpartitioning)
*/
if ((found_part_field =
set_PF_fields_in_key(key_info, key_spec->length))) {
if (check_part_func_bound(part_info->full_part_field_array)) {
/*
We were able to bind all fields in the partition function even
by using only a part of the key. Calculate the partition to use.
*/
get_full_part_id_from_key(table, buf, key_info, key_spec, part_spec);
clear_indicator_in_key_fields(key_info);
/*
Check if range can be adjusted by looking in read_partitions
*/
prune_partition_set(table, part_spec);
return;
}
```
get_full_part_id_from_key set the wrong part_spec, the partition which has the target data pruned by mistake，
Fix:
if ((found_part_field =
set_PF_fields_in_key(key_info, key_spec->length))) {
- if (check_part_func_bound(part_info->full_part_field_array)) {
+ /*
+ CRITICAL FIX: For subpartitioned tables with virtual generated columns,
+ check_part_func_bound() may incorrectly return true even when the actual
+ partition field is not in the key, leading to wrong partition ID calculation.
+
+ Solution: Verify the index does not contain virtual columns before calling
+ get_full_part_id_from_key().
+ */
+ bool has_virtual_col_in_key = false;
+ if (part_info->is_sub_partitioned()) {
+ for (uint kp = 0; kp < key_info->user_defined_key_parts; kp++) {
+ if (key_info->key_part[kp].field->is_virtual_gcol()) {
+ has_virtual_col_in_key = true;
+ break;
+ }
+ }
+ }
+
+ if (!has_virtual_col_in_key &&
+ check_part_func_bound(part_info->full_part_field_array)) {
Description: Create table with virtual column and partition table, select may return less data. mysql> DROP TABLE IF EXISTS users2; Query OK, 0 rows affected, 1 warning (0.012 sec) mysql> CREATE TABLE `users2` ( -> `id` int NOT NULL, -> `user_id` int COLLATE utf8mb4_bin NOT NULL, -> `status` int COLLATE utf8mb4_bin NOT NULL, -> `name` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL, -> `score` int COLLATE utf8mb4_bin GENERATED ALWAYS AS (`status`*2), -> PRIMARY KEY (`status`, `user_id`), -> KEY `idx_user_score` (`user_id`, `score`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci -> PARTITION BY LIST (`status`) -> SUBPARTITION BY HASH(`user_id`) -> SUBPARTITIONS 2( -> PARTITION p2 VALUES IN (2) ENGINE = InnoDB, -> PARTITION p3 VALUES IN (3) ENGINE = InnoDB, -> PARTITION p4 VALUES IN (4) ENGINE = InnoDB); Query OK, 0 rows affected (0.122 sec) mysql> mysql> INSERT INTO users2 (id, user_id, status, name) VALUES (1, 20260120, 2, 'tj'); Query OK, 1 row affected (0.016 sec) mysql> INSERT INTO users2 (id, user_id, status, name) VALUES (2, 20260120, 3, 'tj'); Query OK, 1 row affected (0.002 sec) mysql> mysql> -- 查询 Query OK, 0 rows affected (0.000 sec) mysql> SELECT * FROM users2 WHERE user_id = 20260120; +----+----------+--------+------+-------+ | id | user_id | status | name | score | +----+----------+--------+------+-------+ | 2 | 20260120 | 3 | tj | 6 | +----+----------+--------+------+-------+ 1 row in set (0.004 sec) mysql> quit Bye Insert two rows but select only return one. If we reconnect the server, execute the statement before any other selection, return empty result. ./bin/mysql -S ./mysql-test/var/tmp/mysqld.1.sock -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 9.5.0-cynos-debug 20220401 Copyright (c) 2000, 2025, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT * FROM users2 WHERE user_id = 20260120; Empty set (0.003 sec) mysql> quit Bye How to repeat: see description Suggested fix: The problme is in function : get_partition_set: ``` /* Set an indicator on all partition fields that are bound. If at least one PF-field was bound it pays off to check whether the PF or PPF or SPF has been bound. (PF = Partition Function, SPF = Subpartition Function and PPF = Partition Function part of subpartitioning) */ if ((found_part_field = set_PF_fields_in_key(key_info, key_spec->length))) { if (check_part_func_bound(part_info->full_part_field_array)) { /* We were able to bind all fields in the partition function even by using only a part of the key. Calculate the partition to use. */ get_full_part_id_from_key(table, buf, key_info, key_spec, part_spec); clear_indicator_in_key_fields(key_info); /* Check if range can be adjusted by looking in read_partitions */ prune_partition_set(table, part_spec); return; } ``` get_full_part_id_from_key set the wrong part_spec, the partition which has the target data pruned by mistake， Fix: if ((found_part_field = set_PF_fields_in_key(key_info, key_spec->length))) { - if (check_part_func_bound(part_info->full_part_field_array)) { + /* + CRITICAL FIX: For subpartitioned tables with virtual generated columns, + check_part_func_bound() may incorrectly return true even when the actual + partition field is not in the key, leading to wrong partition ID calculation. + + Solution: Verify the index does not contain virtual columns before calling + get_full_part_id_from_key(). + */ + bool has_virtual_col_in_key = false; + if (part_info->is_sub_partitioned()) { + for (uint kp = 0; kp < key_info->user_defined_key_parts; kp++) { + if (key_info->key_part[kp].field->is_virtual_gcol()) { + has_virtual_col_in_key = true; + break; + } + } + } + + if (!has_virtual_col_in_key && + check_part_func_bound(part_info->full_part_field_array)) {