Bug #115711 Contribution by Tencent: Virtual column cause empty result in subpartition
Submitted: 29 Jul 6:56 Modified: 29 Jul 7:14
Reporter: lu yangyang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:8.0, 8.0.30,8.0.39, 8.4.2, 9.0.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: subpartition, virtual generated column

[29 Jul 6:56] lu yangyang 
Description:
When the index contains subpartition keys and virtual columns, and the query used this index, no data can be queried.

How to repeat:
release: 
------------------

mysql> CREATE TABLE `t1` (
  `id` int NOT NULL,
  `sub_key` int COLLATE utf8mb4_bin NOT NULL,
  `part_key` int COLLATE utf8mb4_bin NOT NULL,
  `pad` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `virtual_column` int COLLATE utf8mb4_bin GENERATED ALWAYS AS (`part_key`*2) virtual,
  PRIMARY KEY (`part_key`,`sub_key`),
  KEY `idx2`(`sub_key`,`virtual_column`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
/*!50100 PARTITION BY LIST (`part_key`)
    SUBPARTITION BY HASH( `sub_key` )
    SUBPARTITIONS 2( 
    PARTITION p2 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p3 VALUES IN (3) ENGINE = InnoDB,
 PARTITION p4 VALUES IN (4) ENGINE = InnoDB) */;

mysql> insert into t1(id,sub_key,part_key,pad) values (1,1,2,'123');

mysql> select count(*) from t1 where sub_key = 1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+

mysql> flush tables;

mysql> select count(*) from t1 where sub_key = 1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

mysql> explain select count(*) from t1 where sub_key = 1;
+----+-------------+-------+----------------------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions                 | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+----------------------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | p2_p2sp1,p3_p3sp1,p4_p4sp1 | ref  | PRIMARY,idx2  | idx2 | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+----------------------------+------+---------------+------+---------+-------+------+----------+-------------+

When result is not queried through this index, the data can still be obtained after last query
---------------------

mysql> select * from t1;
+----+---------+----------+------+----------------+
| id | sub_key | part_key | pad  | virtual_column |
+----+---------+----------+------+----------------+
|  1 |       1 |        2 | 123  |              4 |
+----+---------+----------+------+----------------+

mysql> select count(*) from t1 where sub_key = 1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+

debug:
------------------
mysql> select count(*) from t1 where sub_key = 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

The crash stack is as follows:

mysqld: /data2/workspace/mysql-server-offical/sql/field.cc:3766: virtual longlong Field_long::val_int() const: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index()))' failed.
2024-07-29T06:23:01Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=dddb6d77eef3cc81fdc5f4603ad63ab5f926eee4
Thread pointer: 0x7f8760000da0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f88206f69f0 thread_stack 0x100000
 #0 0x35537a0 print_fatal_signal at /data2/workspace/mysql-server-offical/sql/signal_handler.cc:319
 #1 0x35539fc _Z19handle_fatal_signaliP9siginfo_tPv at /data2/workspace/mysql-server-offical/sql/signal_handler.cc:399
 #2 0x7f8850e8f62f <unknown>
 #3 0x7f884f1b4387 <unknown>
 #4 0x7f884f1b5a77 <unknown>
 #5 0x7f884f1ad1a5 <unknown>
 #6 0x7f884f1ad251 <unknown>
 #7 0x36b8a5a _ZNK10Field_long7val_intEv at /data2/workspace/mysql-server-offical/sql/field.cc:3766
 #8 0x37582cd _ZN10Item_field7val_intEv at /data2/workspace/mysql-server-offical/sql/item.cc:3174
 #9 0x33241d2 part_val_int at /data2/workspace/mysql-server-offical/sql/sql_partition.cc:2504
 #10 0x3324a6d get_partition_id_list at /data2/workspace/mysql-server-offical/sql/sql_partition.cc:2854
 #11 0x33258a3 get_partition_id_with_sub at /data2/workspace/mysql-server-offical/sql/sql_partition.cc:3271
 #12 0x3325f1f _Z25get_full_part_id_from_keyPK5TABLEPhP3KEYPK9key_rangeP13part_id_range at /data2/workspace/mysql-server-offical/sql/sql_partition.cc:3515
 #13 0x3326720 _Z17get_partition_setPK5TABLEPhjPK9key_rangeP13part_id_range at /data2/workspace/mysql-server-offical/sql/sql_partition.cc:3749
 #14 0x313f565 _ZN16Partition_helper21partition_scan_set_upEPhb at /data2/workspace/mysql-server-offical/sql/partitioning/partition_handler.cc:2226
 #15 0x313e982 _ZN16Partition_helper17common_index_readEPhb at /data2/workspace/mysql-server-offical/sql/partitioning/partition_handler.cc:1859
 #16 0x313e7f2 _ZN16Partition_helper17ph_index_read_mapEPhPKhm16ha_rkey_function at /data2/workspace/mysql-server-offical/sql/partitioning/partition_handler.cc:1840
 #17 0x4ac67d2 _ZN11ha_innopart14index_read_mapEPhPKhm16ha_rkey_function at /data2/workspace/mysql-server-offical/storage/innobase/handler/ha_innopart.h:1151
 #18 0x36ff900 _ZN7handler17ha_index_read_mapEPhPKhm16ha_rkey_function at /data2/workspace/mysql-server-offical/sql/handler.cc:3289
 #19 0x3b11716 _ZN11RefIteratorILb0EE4ReadEv at /data2/workspace/mysql-server-offical/sql/iterators/ref_row_iterators.cc:381
 #20 0x3af0e1b _ZN17AggregateIterator4ReadEv at /data2/workspace/mysql-server-offical/sql/iterators/composite_iterators.cc:258
 #21 0x344842c _ZN16Query_expression20ExecuteIteratorQueryEP3THD at /data2/workspace/mysql-server-offical/sql/sql_union.cc:1778
 #22 0x34487ab _ZN16Query_expression7executeEP3THD at /data2/workspace/mysql-server-offical/sql/sql_union.cc:1834
 #23 0x3397753 _ZN11Sql_cmd_dml13execute_innerEP3THD at /data2/workspace/mysql-server-offical/sql/sql_select.cc:1059
 #24 0x3396ab9 _ZN11Sql_cmd_dml7executeEP3THD at /data2/workspace/mysql-server-offical/sql/sql_select.cc:782
 #25 0x33120f2 _Z21mysql_execute_commandP3THDb at /data2/workspace/mysql-server-offical/sql/sql_parse.cc:4737
 #26 0x3314412 _Z20dispatch_sql_commandP3THDP12Parser_state at /data2/workspace/mysql-server-offical/sql/sql_parse.cc:5392
 #27 0x330a50e _Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command at /data2/workspace/mysql-server-offical/sql/sql_parse.cc:2136
 #28 0x330835c _Z10do_commandP3THD at /data2/workspace/mysql-server-offical/sql/sql_parse.cc:1465
 #29 0x353e308 handle_connection at /data2/workspace/mysql-server-offical/sql/conn_handler/connection_handler_per_thread.cc:304
 #30 0x52806b3 pfs_spawn_thread at /data2/workspace/mysql-server-offical/storage/perfschema/pfs.cc:3051
 #31 0x7f8850e87ea4 <unknown>
 #32 0x7f884f27cb0c <unknown>
 #33 0xffffffffffffffff <unknown>

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f876001bea0): select count(*) from t1 where sub_key = 1
Connection ID (thread ID): 9
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
Writing a core file
safe_process[6321]: Child process: 6322, killed by signal: 6
[29 Jul 7:14] MySQL Verification Team 
Hello lu yangyang,

Thank you for the report and feedback.

regards,
Umesh
[5 Aug 9:46] huahua xu 
Hi all:

Based on the below test cases, I believe the most likely reason is that the partition fields are dependent on some generated columns.

mysql> CREATE TABLE `t1` (
  `id` int NOT NULL,
  `sub_key` int COLLATE utf8mb4_bin NOT NULL,
  `part_key` int COLLATE utf8mb4_bin NOT NULL,
  `pad` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `virtual_column` int COLLATE utf8mb4_bin GENERATED ALWAYS AS (`id`*2) virtual,
  PRIMARY KEY (`part_key`,`sub_key`),
  KEY `idx2`(`sub_key`,`virtual_column`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
/*!50100 PARTITION BY LIST (`part_key`)
    SUBPARTITION BY HASH( `sub_key` )
    SUBPARTITIONS 2( 
    PARTITION p2 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p3 VALUES IN (3) ENGINE = InnoDB,
 PARTITION p4 VALUES IN (4) ENGINE = InnoDB) */;
 

mysql> insert into t1(id,sub_key,part_key,pad) values (1,1,2,'123');

mysql> select count(*) from t1 where sub_key = 1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

`` The mysqld does not crash.
[5 Aug 15:15] huahua xu 
It may be a feasible solution to fix the bug.

Attachment: check_part_func_bound_with_read_set.patch (application/octet-stream, text), 2.47 KiB.
[3 Sep 11:09] lu yangyang 
I think a feasible solution is to clean up GET_FIXED_FIELDS_FLAG after unpacking virtual_column

Attachment: clear_fixed_field_flag.patch (application/octet-stream, text), 577 bytes.