| Bug #117275 | when index_merge is on, the result is incorrect | ||
|---|---|---|---|
| Submitted: | 23 Jan 7:14 | Modified: | 23 Jan 8:39 |
| Reporter: | haizhen xue | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.40 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[23 Jan 7:16]
haizhen xue
set sql_mode='';
[23 Jan 8:19]
MySQL Verification Team
Hello haizhen xue, Thank you for the report and test case. regards, Umesh
[23 Jan 8:32]
MySQL Verification Team
Hello haizhen xue,
I checked on latest release builds and not seeing any issues. Could you please confirm? Thank you.
-- 8.0.41
bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.41 MySQL Community Server - GPL
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> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t;
", 9 ),
(" f7e806c69d2c489cb5e338ab2bf4864", "AR000JOGJD_011 ", 3 ),
(" f7e806c69d2c489cb5e338ab2bfb5655", "com.xiachufang.recipe ", 9 ),
(" f7e806c69d2c489cb5e338ab2bfb6656", "cQuery OK, 0 rows affected, 1 warning (0.00 sec)
om.example.install_faile_yyx ", 2 ),
(" f7e806c69d2c489cb5e338ab2bfb7657", " ", 2 ),
(" f7e806c69d2c489cb5e338ab2bfb8658", "NULL ", 2 ),
(" f7e806c69d2c489cb5e338ab2bfb9659", "com.example.install_faile_yyx mysql> ", 2 ),
( " f7e806c69d2c489cb5e338ab2bfb11f", "com.atomicservice.5765880207845661477 ", 3 ),
(" f7e806c69d2c489cb5e338ab2bfb825f", "com.example.install_faile_yyx ", 2 ),
(" f7 CREATE TABLE t (
-> ID varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
-> PACKAGE_NAME varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '??????????????profile?????',
-> STATE tinyint NOT NULL COMMENT '???0?????3?????1????????2????????8?????9?????',
-> PRIMARY KEY (ID DESC) USING BTREE,
-> KEY IDX_PROFILE_TASK_PKGNAME (PACKAGE_NAME) USING BTREE,
-> KEY IDX_PROFILE_TASK_STATE (STATE) USING BTREE
-> ) ENGINE=InnoDB;
e806c69d2c489cb5e338ab2bfb835f", "com.example.install_faile_yyx ", 3 );
SELECT /*+ set_var(optimizer_switch='index_merge=off') */ PACKAGE_NAME,STATE FROM t WHERE PACKAGE_NAME='com.example.install_faile_yyx' AND STATE=3;
SELECT /*+ set_var(optimizer_switch='index_merge=on') */ PACKAGE_NAME,STATE FROM t WHERE PACKAGE_NAME='com.example.install_faile_yyx' AND STATE=3;Query OK, 0 rows affected, 4 warnings (0.03 sec)
mysql>
mysql> insert into t values
-> (" f7e806c69d2c489cb5e338ab2bfb1151", "com.example.install_faile_yyx ", 9 ),
-> (" f7e806c69d2c489cb5e338ab2bfb252", "com.a01tech.massagercn ", 2 ),
-> (" f7e806c69d2c489cb5e338ab2bfb3653", "com.ctrip.harmonynext ", 9 ),
-> (" f7e806c69d2c489cb5e338ab2bf4864", "AR000JOGJD_011 ", 3 ),
-> (" f7e806c69d2c489cb5e338ab2bfb5655", "com.xiachufang.recipe ", 9 ),
-> (" f7e806c69d2c489cb5e338ab2bfb6656", "com.example.install_faile_yyx ", 2 ),
-> (" f7e806c69d2c489cb5e338ab2bfb7657", " ", 2 ),
-> (" f7e806c69d2c489cb5e338ab2bfb8658", "NULL ", 2 ),
-> (" f7e806c69d2c489cb5e338ab2bfb9659", "com.example.install_faile_yyx ", 2 ),
-> ( " f7e806c69d2c489cb5e338ab2bfb11f", "com.atomicservice.5765880207845661477 ", 3 ),
-> (" f7e806c69d2c489cb5e338ab2bfb825f", "com.example.install_faile_yyx ", 2 ),
-> (" f7e806c69d2c489cb5e338ab2bfb835f", "com.example.install_faile_yyx ", 3 );
Query OK, 12 rows affected, 9 warnings (0.01 sec)
Records: 12 Duplicates: 0 Warnings: 9
mysql>
mysql> SELECT /*+ set_var(optimizer_switch='index_merge=off') */ PACKAGE_NAME,STATE FROM t WHERE PACKAGE_NAME='com.example.install_faile_yyx' AND STATE=3;
+----------------------------------------------+-------+
| PACKAGE_NAME | STATE |
+----------------------------------------------+-------+
| com.example.install_faile_yyx | 3 |
+----------------------------------------------+-------+
1 row in set (0.00 sec)
mysql> SELECT /*+ set_var(optimizer_switch='index_merge=on') */ PACKAGE_NAME,STATE FROM t WHERE PACKAGE_NAME='com.example.install_faile_yyx' AND STATE=3;
+----------------------------------------------+-------+
| PACKAGE_NAME | STATE |
+----------------------------------------------+-------+
| com.example.install_faile_yyx | 3 |
+----------------------------------------------+-------+
1 row in set (0.00 sec)
mysql>
regards,
Umesh
[23 Jan 8:34]
MySQL Verification Team
-- 8.0.41+ not affected
bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.4.4 MySQL Community Server - GPL
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> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test
Database changed
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE t (
-> ID varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
-> PACKAGE_NAME varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '??????????????profile?????',
-> STATE tinyint NOT NULL COMMENT '???0?????3?????1????????2????????8?????9?????',
-> PRIMARY KEY (ID DESC) USING BTREE,
-> KEY IDX_PROFILE_TASK_PKGNAME (PACKAGE_NAME) USING BTREE,
-> KEY IDX_PROFILE_TASK_STATE (STATE) USING BTREE
-> ) ENGINE=InnoDB;
", 9 ),
(" f7e806c69d2c489cb5e338ab2bf4864", "AR000JOGJD_011 ", 3 ),
(" f7e806c69d2c489cb5e338ab2bfb5655", "com.xiachufang.recipe ", 9 ),
(" f7e806c69d2c489cb5e338ab2bfb6656", "com.example.install_faile_yyx ", 2 ),
(" f7e806c69d2c489cb5e338ab2bfb7657", " ", 2 ),
(" f7e806c69d2c489cb5e338ab2bfb8658", "NULL ", 2 ),
(" f7e806c69d2c489cb5e338ab2bfb9659", "com.example.install_faile_yyx ", 2 ),
( " f7e806c69d2c489cb5e338ab2bfb11f", "com.atomicservice.5765880207845661477 ", 3 ),
(" f7e806c69d2c489cb5e338ab2bfb825f", "com.example.install_faile_yyx ", 2 ),
(" f7e806c69d2c489cb5e338ab2bfb835f", "com.example.install_faile_yyx ", 3 );Query OK, 0 rows affected, 4 warnings (0.03 sec)
mysql>
mysql> insert into t values
-> (" f7e806c69d2c489cb5e338ab2bfb1151", "com.example.install_faile_yyx ", 9 ),
-> (" f7e806c69d2c489cb5e338ab2bfb252", "com.a01tech.massagercn ", 2 ),
-> (" f7e806c69d2c489cb5e338ab2bfb3653", "com.ctrip.harmonynext ", 9 ),
-> (" f7e806c69d2c489cb5e338ab2bf4864", "AR000JOGJD_011 ", 3 ),
-> (" f7e806c69d2c489cb5e338ab2bfb5655", "com.xiachufang.recipe ", 9 ),
-> (" f7e806c69d2c489cb5e338ab2bfb6656", "com.example.install_faile_yyx ", 2 ),
-> (" f7e806c69d2c489cb5e338ab2bfb7657", " ", 2 ),
-> (" f7e806c69d2c489cb5e338ab2bfb8658", "NULL ", 2 ),
-> (" f7e806c69d2c489cb5e338ab2bfb9659", "com.example.install_faile_yyx ", 2 ),
-> ( " f7e806c69d2c489cb5e338ab2bfb11f", "com.atomicservice.5765880207845661477 ", 3 ),
-> (" f7e806c69d2c489cb5e338ab2bfb825f", "com.example.install_faile_yyx ", 2 ),
-> (" f7e806c69d2c489cb5e338ab2bfb835f", "com.example.install_faile_yyx ", 3 );
Query OK, 12 rows affected, 9 warnings (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 9
mysql> SELECT /*+ set_var(optimizer_switch='index_merge=off') */ PACKAGE_NAME,STATE FROM t WHERE PACKAGE_NAME='com.example.install_faile_yyx' AND STATE=3;
+----------------------------------------------+-------+
| PACKAGE_NAME | STATE |
+----------------------------------------------+-------+
| com.example.install_faile_yyx | 3 |
+----------------------------------------------+-------+
1 row in set (0.00 sec)
mysql> SELECT /*+ set_var(optimizer_switch='index_merge=on') */ PACKAGE_NAME,STATE FROM t WHERE PACKAGE_NAME='com.example.install_faile_yyx' AND STATE=3;
+----------------------------------------------+-------+
| PACKAGE_NAME | STATE |
+----------------------------------------------+-------+
| com.example.install_faile_yyx | 3 |
+----------------------------------------------+-------+
1 row in set (0.00 sec)
mysql>
mysql>
[23 Jan 8:38]
haizhen xue
I verify 8.0.37 has this problem. 8.0.41 has not this problem, Thank you!
[23 Jan 8:39]
MySQL Verification Team
Thank you, for confirming. Closing the report now. regards, Umesh
Description: Expect one row, but when index_merge is on, return empty. mysql> SELECT /*+ set_var(optimizer_switch='index_merge=off') */ PACKAGE_NAME,STATE FROM `t` WHERE PACKAGE_NAME='com.example.install_faile_yyx' AND STATE=3; +----------------------------------------------+-------+ | PACKAGE_NAME | STATE | +----------------------------------------------+-------+ | com.example.install_faile_yyx | 3 | +----------------------------------------------+-------+ 1 row in set (0.00 sec) mysql> SELECT /*+ set_var(optimizer_switch='index_merge=on') */ PACKAGE_NAME,STATE FROM `t` WHERE PACKAGE_NAME='com.example.install_faile_yyx' AND STATE=3; Empty set (0.00 sec) How to repeat: set sql_mod=''; drop table if exists t; CREATE TABLE `t` ( `ID` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `PACKAGE_NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '??????????????profile?????', `STATE` tinyint NOT NULL COMMENT '???0?????3?????1????????2????????8?????9?????', PRIMARY KEY (`ID` DESC) USING BTREE, KEY `IDX_PROFILE_TASK_PKGNAME` (`PACKAGE_NAME`) USING BTREE, KEY `IDX_PROFILE_TASK_STATE` (`STATE`) USING BTREE ) ENGINE=InnoDB; insert into t values (" f7e806c69d2c489cb5e338ab2bfb1151", "com.example.install_faile_yyx ", 9 ), (" f7e806c69d2c489cb5e338ab2bfb252", "com.a01tech.massagercn ", 2 ), (" f7e806c69d2c489cb5e338ab2bfb3653", "com.ctrip.harmonynext ", 9 ), (" f7e806c69d2c489cb5e338ab2bf4864", "AR000JOGJD_011 ", 3 ), (" f7e806c69d2c489cb5e338ab2bfb5655", "com.xiachufang.recipe ", 9 ), (" f7e806c69d2c489cb5e338ab2bfb6656", "com.example.install_faile_yyx ", 2 ), (" f7e806c69d2c489cb5e338ab2bfb7657", " ", 2 ), (" f7e806c69d2c489cb5e338ab2bfb8658", "NULL ", 2 ), (" f7e806c69d2c489cb5e338ab2bfb9659", "com.example.install_faile_yyx ", 2 ), ( " f7e806c69d2c489cb5e338ab2bfb11f", "com.atomicservice.5765880207845661477 ", 3 ), (" f7e806c69d2c489cb5e338ab2bfb825f", "com.example.install_faile_yyx ", 2 ), (" f7e806c69d2c489cb5e338ab2bfb835f", "com.example.install_faile_yyx ", 3 ); SELECT /*+ set_var(optimizer_switch='index_merge=off') */ PACKAGE_NAME,STATE FROM `t` WHERE PACKAGE_NAME='com.example.install_faile_yyx' AND STATE=3; SELECT /*+ set_var(optimizer_switch='index_merge=on') */ PACKAGE_NAME,STATE FROM `t` WHERE PACKAGE_NAME='com.example.install_faile_yyx' AND STATE=3;