Description:
In MySQL 9.6.0, when the optimizer chooses an Index Merge Union plan for a SELECT query with OR conditions (e.g., WHERE c7 = '' OR c2 = 843150457), the deduplication logic in the index merge union algorithm fails to correctly eliminate a row that satisfies both OR predicates. This results in the query returning 67 rows instead of the correct 66 rows (one duplicate row is produced).
When the same query is executed with index merge disabled:
SET SESSION optimizer_switch = 'index_merge_intersection=off,index_merge_sort_union=off,index_merge_union=off';
the correct result of 66 rows is returned.
The root cause is that the index_merge access method scans two indexes separately and merges the results. For rows that satisfy BOTH OR conditions (i.e., rows where c7 = '' AND c2 = 843150457), the deduplication step in the union merge fails to recognize one such overlapping row as a duplicate, producing it twice in the final result set.
Verification using inclusion-exclusion on the individual conditions:
SELECT COUNT(*) FROM idx_t0 WHERE c7 = ''; -- N1 (65)
SELECT COUNT(*) FROM idx_t0 WHERE c2 = 843150457; -- N2 (2)
SELECT COUNT(*) FROM idx_t0 WHERE c7 = '' AND c2 = 843150457; -- N_overlap(1)
Correct result = N1 + N2 - N_overlap = 66
Index merge result = 67 (one EXTRA duplicate)
This is a correctness bug -- the optimizer produces a semantically wrong query result under default settings. Since index_merge_union is enabled by default, any workload that triggers index merge union on tables with multiple secondary indexes and OR predicates may silently return incorrect results.
How to repeat:
See the attached file min_mysql_index_merge_union_repro.sql or the script below for the complete standalone reproduction.
Expected result:
Both queries should return the same count. The inclusion-exclusion verification confirms the correct count is 66.
Actual result:
- With default optimizer
+-----------------+
| cnt_index_merge |
+-----------------+
| 67 | <- WRONG!
+-----------------+
1 row in set (0.00 sec)
- With index_merge_intersection,index_merge_sort_union,index_merge_union off
+--------------------+
| cnt_no_index_merge |
+--------------------+
| 66 |<- CORRECT
+--------------------+
1 row in set (0.00 sec)
Inclusion-exclusion verification:
mysql> SELECT COUNT(*) AS cnt_c7 FROM idx_t0 WHERE c7 = '';
+--------+
| cnt_c7 |
+--------+
| 65 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) AS cnt_c2 FROM idx_t0 WHERE c2 = 843150457;
+--------+
| cnt_c2 |
+--------+
| 2 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) AS cnt_both FROM idx_t0 WHERE c7 = '' AND c2 = 843150457;
+----------+
| cnt_both |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
expected result = cnt_c7 + cnt_c2 - cnt_both = 65 + 2 - 1 = 66 (NOT 67 !)
Below is the full script:
DROP DATABASE IF EXISTS test_db7_bug;
CREATE DATABASE test_db7_bug;
USE test_db7_bug;
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
DROP TABLE IF EXISTS idx_t0;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE idx_t0 (
`c0` int NOT NULL,
`c2` int DEFAULT NULL,
`c4` int NOT NULL,
`c7` varchar(255) NOT NULL,
UNIQUE KEY `i19` (`c0` DESC,`c4`) USING BTREE,
UNIQUE KEY `i93` (`c7`,`c2`),
UNIQUE KEY `i40` (`c2`,`c7`(4),`c4`),
UNIQUE KEY `i72` (`c4` DESC,`c2`,`c0`,`c7`(3) DESC),
KEY `idx_0_c0` (`c0`),
KEY `idx_0_c0c1` (`c0`),
KEY `loose_idx_3121` (`c4`),
KEY `sq_in_3119` (`c2`),
KEY `ordlim_idx_3119` (`c2`),
KEY `sq_out_3121` (`c4`),
KEY `cover_idx_3119` (`c2`,`c7`),
KEY `prefix_idx_3124` (`c7`(5)),
KEY `null_idx_3121` (`c4`),
KEY `loose_idx_3124` (`c7`,`c0`),
KEY `cover_idx_3124` (`c7`),
KEY `null_idx_3119` (`c2`),
KEY `range_idx_3117` (`c0`),
KEY `cover_idx_3117` (`c0`,`c4`),
KEY `null_idx_3117` (`c0`),
KEY `null_idx_3124` (`c7`),
KEY `hash_idx_3124` (`c7`),
KEY `btree_idx_3124` (`c7`) USING BTREE,
KEY `loose_idx_3117` (`c0`,`c4`),
KEY `loose_idx_3119` (`c2`,`c4`),
KEY `hash_idx_3119` (`c2`),
KEY `btree_idx_3119` (`c2`) USING BTREE,
KEY `range_idx_3121` (`c4`),
KEY `cover_idx_3121` (`c4`,`c2`),
KEY `sq_out_3117` (`c0`),
KEY `hash_idx_3117` (`c0`),
KEY `btree_idx_3117` (`c0`) USING BTREE,
KEY `range_idx_3119` (`c2`),
KEY `dropcol_idx_3124` (`c7`),
KEY `imerge_b_3121` (`c4`),
KEY `dropcol_idx_3119` (`c2`),
KEY `sq_in_3117` (`c0`),
KEY `imerge_a_3121` (`c4`),
KEY `range_idx_3124` (`c7`),
KEY `imerge_a_3119` (`c2`),
KEY `ordlim_idx_3117` (`c0`),
KEY `ordlim_idx_3124` (`c7`),
KEY `loose_idx_3122` (`c0`),
KEY `sq_out_3124` (`c7`),
KEY `sq_in_3124` (`c7`),
KEY `i32` (`c2`,`c4`),
KEY `imerge_a_3124` (`c7`),
KEY `imerge_b_3119` (`c2`),
KEY `sq_out_3119` (`c2`),
KEY `hash_idx_3121` (`c4`),
KEY `btree_idx_3121` (`c4`) USING BTREE,
KEY `sq_in_3121` (`c4`),
KEY `inv_idx_3119` (`c2`),
KEY `i28` (`c7`(2)),
KEY `i23` (`c0`,`c7`(2)),
KEY `i57` (`c2`),
KEY `imerge_a_3117` (`c0`),
KEY `ordlim_idx_3121` (`c4`),
KEY `i66` (`c2`,`c7`) USING BTREE,
KEY `imerge_b_3117` (`c0`),
KEY `i15` (`c4`,`c0`) USING BTREE,
KEY `imerge_b_3124` (`c7`),
KEY `i5` (`c4`,`c0`,`c2` DESC,`c7`),
KEY `i84` (`c7` DESC,`c0`),
FULLTEXT KEY `ft_idx_3124` (`c7`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
LOCK TABLES idx_t0 WRITE;
/*!40000 ALTER TABLE idx_t0 DISABLE KEYS */;
INSERT INTO idx_t0 VALUES (2147045168,NULL,14841676,''),(2117345031,NULL,1667410790,''),(1904387696,NULL,2095835898,''),(1880758046,NULL,1358744737,''),(1779573111,NULL,1213338139,''),(1674945258,NULL,1132079235,''),(1548567565,NULL,1788669696,''),(1523759413,NULL,611431630,''),(1523759413,NULL,1057960958,''),(1371492984,NULL,118940865,''),(1370657502,NULL,549028794,''),(1359041638,NULL,1842778344,''),(1252668140,NULL,350075089,''),(974153138,NULL,1771363725,''),(934828754,NULL,642845854,''),(930734054,NULL,1255038522,''),(919550890,NULL,1519192346,''),(774856002,NULL,762286399,''),(771800201,NULL,145402466,''),(362390838,NULL,1797585080,''),(358336914,NULL,1414626667,''),(119560663,NULL,183402085,''),(54378892,NULL,1118891475,''),(16412241,NULL,225171780,''),(41413,3989,73076,''),(51613,8291,32413,''),(82183,10014,81431,''),(92346,13657,62414,''),(28526,14584,80601,''),(55680,16349,84786,''),(45450,18819,61109,''),(71612,27933,21235,''),(91287,30787,94399,''),(84038,43830,99895,''),(19921,46432,11719,''),(36914,53646,73882,''),(99613,53817,71114,''),(73723,54926,33555,''),(13000,58578,4459,''),(65426,63725,71361,''),(91352,72617,29648,''),(89343,73969,94321,''),(39176,74590,86895,''),(65656,75810,38446,''),(19968,80150,75358,''),(40796,81110,23614,''),(51333,99812,58046,''),(172449481,34503578,1218873856,''),(191280998,84272344,107637559,''),(1371492984,92057120,1489589624,''),(20721479,92364601,1567451520,''),(2116423639,322998246,1062740533,''),(587213560,372882233,1001284786,''),(949040915,642563902,970485927,''),(807711719,737677516,16412241,''),(890281334,764203018,1771363725,''),(561732697,843150457,279416460,''),(227747812,854268660,686609524,''),(1388399793,1254650565,1926222297,''),(2113474910,1317507866,1116759449,''),(1385262738,1495855544,1878058977,''),(2062108484,1512136435,1228987089,''),(105765466,1592983694,2001072104,''),(380252171,1741026444,1793181195,'֧'),(871471114,2103424995,1343680601,''),(95889847,1243573363,1470117261,' '),(1056762373,NULL,980801455,'\rB(l'),(443622876,NULL,1771363725,' ('),(1051874117,NULL,443622876,' 4/IN '),(1182273969,843150457,1879313000,' 4/in '),(333531928,1057960958,356297673,' Gik쩓h*z*'),(8284,80418,1,' P'),(326081289,1692107145,845770512,' s^U2\\z'),(3178,34310,11958,'_mcG6z'),(580438611,NULL,20721479,'_w'),(20706,18347,5184,'-❞2dbt/bR'),(87702,1,80942,'-1057960958'),(1469381110,480938097,561454693,'-1057960958'),(362390838,NULL,329942308,'-1191899812'),(1781077776,NULL,871471114,'-1241522003'),(1554534456,NULL,378916813,'-1241522003'),(1050662283,NULL,2487134,'-1343680601'),(408595227,NULL,1295682690,'-1685273882'),(92984,30132,48325,'-1832100748'),(57441,12272,81832,'-1e500'),(53105,38611,19548,'-2062108484'),(715942397,NULL,450714359,'-2116423639'),(453144095,661968304,906870547,'-2116423639'),(912139514,2001180561,1563785845,'-21404459'),(1182586366,NULL,1126139364,'-220249012'),(920681927,1424268161,708253706,'-220249012'),(807711719,2116423639,21404459,'-220249012'),(1005203368,869190947,2061995811,'-250196810'),(0,49528,902,'-408595227'),(358336914,NULL,16412241,'-423301552'),(924319116,1993390117,180429877,'-423301552'),(67621,82736,79450,'-443622876'),(40865,39514,16460,'-549028794'),(20721479,343558237,344805234,'-549028794'),(1182586366,617244655,16412241,'-586294399'),(85585,26667,81334,'-630961109'),(1865085218,965713763,1243573363,'-630961109'),(577774322,NULL,191280998,'-73191683'),(549028794,2014407528,27151559,'-862010229'),(630961109,1371492984,384112869,'-974153138'),(18306,79235,86366,'-e'),(53727,83285,70866,'-f'),(2375,48933,58931,'-MB'),(60271,7866,19293,', \'^zh(*'),(71642055,15842355,1735053105,', \'^zh(*'),(1160598670,NULL,1086683207,',4\\'),(279416460,1974943830,14841676,',4\\'),(367486838,1198112375,1426707971,',QFqF|7'),(93852,7145,84555,',z'),(23639,50890,35094,',\''),(91854,32436,34682,'!b5P'),(93923,38611,40865,'!郞'),(460149540,899783616,2018232057,'?r'),(1467972847,NULL,142330059,'?TYO\n)c'),(1504755073,839257345,1683980069,'?z}'),(58046,1607,36260,'\'2'),(17767,94399,62414,'('),(2044763791,551618347,2058250660,'(XW'),(362390838,NULL,221765426,')^i jX'),(22003,71114,68611,')^i jX'),(1371492984,1523759413,1371492984,')+5'),(71802,30245,19548,')a &0Es+'),(18733,10229,56548,'[ VF'),(99812,22297,72617,'[{9>oG#&'),(1623900117,1164472617,564280717,'[0TFF'),(61109,97218,20838,'[j'),(45298,79869,71114,'}ό'),(577774322,NULL,1525916804,'*'),(1054806299,1454526118,1771363725,'*)4eU{b'),(22876,16906,40347,'**'),(1589844072,NULL,806038463,'*d[mf'),(891129563,NULL,146280645,'\\]()'),(7621,63725,42131,'\\9KD鏑{A!2'),(20958,47327,37345,'&撲u,'),(361980816,NULL,1137793127,'֍әǵӈ'),(10908,22297,5717,'+<vO'),(1179,90301,78605,'+1'),(87583018,NULL,912139514,'<%m'),(1439691453,NULL,1235021869,'<x_m&]Ag'),(1879313000,1199587702,1904387696,'>) }HJ'),(229343591,NULL,1556253586,'>n'),(60958,40684,4123,'|'),(1618776903,NULL,1634162414,'|-'),(69380,80717,16460,'👏'),(15486,81110,91683,'💧'),(385272972,1795751975,756927950,'📵'),(1317507866,NULL,880545259,'🔗'),(48364,59972,49284,'🔤'),(9709,47443,87795,'🔮'),(1124145450,NULL,1712741501,'🕱'),(1243573363,1391341853,1244409266,'🗜'),(51240,51824,80601,'͟0'),(69765,53138,61099,'0.08063267171602795'),(1634162414,NULL,871471114,'0.13265588521057825'),(198744902,NULL,1235021869,'0.13265588521057825'),(1235021869,371614899,549028794,'0.13265588521057825'),(851511388,487719986,1419219921,'0.13265588521057825'),(923836642,549028794,26663531,'0.13265588521057825'),(546538079,1154830544,434418733,'0.13265588521057825'),(224294066,1300807824,2076579769,'0.13265588521057825'),(80791,21677,63596,'0.27010397262033736'),(63016,80998,99010,'0.6569566770221136'),(807711719,NULL,652656668,'0.7507612272572769'),(709364539,NULL,835842862,'0.7507612272572769'),(373855867,NULL,260612742,'0.7507612272572769'),(358336914,NULL,453404894,'0.7507612272572769'),(27151559,NULL,423301552,'0.7507612272572769'),(177631023,1574574306,179232384,'0.7507612272572769'),(1454733821,NULL,1271525728,'0.7649744287375929'),(1101796340,NULL,1741466624,'0.7649744287375929'),(561454693,NULL,1243573363,'0.7649744287375929'),(134260054,1500870195,1646709511,'0.7649744287375929'),(1559011559,1932766588,198503284,'0.7649744287375929'),(1775113177,NULL,409882188,'0.8153759212902829'),(552051858,164632809,1629985478,'0.8153759212902829'),(31630,80998,74292,'0.838836989722932'),(75003299,NULL,16412241,'0.9212992395527393'),(1948821569,837432783,556139,'0.9212992395527393'),(21819,31887,39514,'0.9399888989885958'),(73363,59682,7716,'0.9399888989885958'),(87215,7866,64,'0N'),(67931,66593,40066,'1)T'),(36775,10695,11719,'1001284786'),(1057960958,NULL,406219548,'1051874117'),(16460,14518,85927,'1051874117'),(47483,24517,68787,'1164472617'),(1566317696,NULL,1124145450,'1235021869'),(66849,4795,63725,'14'),(1051874117,1221761375,330479218,'14'),(85927,41464,90838,'1469381110'),(43108,87696,57237,'1519192346'),(75089,89405,57552,'1530304774'),(26667,60749,54693,'1692107145'),(94061,2375,88881,'1879313000'),(348767468,NULL,1802847867,'1e500'),(2118,7866,40067,'1w.嵜'),(15220,29648,91468,'20c(8'),(912536587,278687259,31963796,'236568492'),(83606,89703,61109,'2J'),(5862,2388,29678,'3'),(4459,7145,74443,'329414518'),(727281431,1832100748,1414626667,'329414518'),(24111,8301,93131,'350075089'),(41620,71293,4939,'362390838'),(70157,67457,30896,'3gI/\''),(1883956833,992051206,1101754451,'3zgz(\''),(561732697,350075089,1886137088,'3㶍'),(934828754,807711719,329984408,'3㶍'),(1369772720,NULL,221765426,'4∈'),(1583084524,NULL,789541909,'434418733'),(80601,82289,20267,'4C]'),(640875905,NULL,350075089,'551618347'),(28754,18426,50457,'551618347'),(1519192346,1640507708,359709906,'551618347'),(11680,17321,79235,'564280717'),(45356,66970,23639,'564280717'),(45264,96440,27203,'5TR'),(0,96810,49467,'5uJ| d,8>'),(1788220973,NULL,2013798175,'6J|\''),(1780399043,NULL,1197790371,'6S'),(24352,56281,93276,'727281431'),(1904387696,NULL,1566317696,'774856002'),(1585337499,513303545,1781077776,'8'),(1001399426,NULL,862010229,'807711719'),(261815325,NULL,577774322,'807711719'),(-2147483648,279416460,1247774623,'871471114'),(30504,21869,88294,'871507806'),(29454,84016,89677,'8I'),(87583018,NULL,20721479,'912139514'),(63790,75905,85284,'919550890'),(611431630,NULL,236568492,'A'),(85927,42863,89306,'am6'),(275744475,NULL,710967318,'BB>52>崏'),(1845834495,NULL,423301552,'dES準> g'),(564280717,NULL,1716900262,'DkoLp'),(83018,88249,67391,'DW<g'),(24147,92984,59413,'DZ'),(27151559,NULL,344447193,'D㤳/B\'ᔐF['),(1685273882,1524072166,953881024,'ȡ'),(1688408093,NULL,1131207608,'E'),(88044,72124,73969,'Ē'),(143820551,1643857588,807711719,'e\n'),(1565964175,NULL,32351243,'E<k'),(2133390919,704568624,496368985,'ễz5kI!'),(1288729236,868033548,970485927,'ᵌB,?w1'),(47559,51741,81832,'FKYV'),(8682202,87583018,2107303178,'FN'),(1432731230,NULL,161994208,'fO묤*3'),(1458577698,586294399,1243573363,'FtVi\r'),(15048,74385,42556,'fWNTJoqNI'),(90842,1106,95249,'G'),(19548,63725,4459,'g'),(75783,65567,75905,'g&j坳'),(98897,81110,61961,'g#'),(4774,96659,10229,'G>-3짮J'),(1999691333,NULL,1307716693,'GqqL<Z賢TQ'),(99812,83018,38611,'hz怂'),(807711719,NULL,845984469,'Ī'),(41676,51004,11719,'I'),(696272602,447530912,1126419150,'ȊƓ'),(83026,75905,95227,'Ɩ'),(350075089,2051328615,1694356334,'j7N樲껊BWN'),(1660699380,1912153622,561732697,'jTDG熛<'),(82835,63013,83566,'j'),(47520,5573,81832,'k2!1y)?'),(406219548,NULL,1519192346,'kp'),(1739583531,712254610,1164472617,'kZ+|EH?j\r'),(329414518,NULL,1523759413,'k'),(507877287,NULL,1236462242,'K'),(1350336388,NULL,2110421182,'L&H'),(20617,42653,63725,'Lj '),(51046,44902,60543,'lkRS\'Ob'),(777271438,NULL,807711719,'M6'),(53138,57601,56687,'MO&c6^ꖬ0v'),(75905,87696,73882,'N MeCm'),(62414,49939,61130,'Ƞ/'),(73363,51866,39374,'o8Scf -ﳾ'),(1951526747,NULL,919550890,'ow'),(50749,17582,88691,'P+'),(862154366,NULL,1376527744,'Q%'),(77757,55484,70907,'R⢦'),(1832100748,970485927,423301552,'rgY\n'),(86366,49232,34168,'RH'),(8104,63399,29617,'R䘰4O2 z,'),(165485230,1771363725,1164472617,'S'),(1382029738,NULL,1665319861,'s*,krr'),(8484,7866,35595,'s*,krr'),(1288838675,NULL,718735138,'S厧*'),(793851458,1919435095,871471114,'t^d'),(1241624496,NULL,730610614,'tFu_w#'),(43978,16460,71466,'t'),(1726720993,1738405153,1104307523,'T'),(748,74117,56786,'U忇P'),(54693,1419,11719,'Vc'),(577774322,848801641,987449479,'vhd2hw'),(274190074,NULL,685200280,'vy᯽M뼂臨J'),(1182586366,NULL,1904387696,'VZq8~࿕J'),(1601176533,329414518,1418069388,'w'),(2134406122,1193115426,1029440889,'W4'),(85927,1606,54306,'ֻX'),(2711,89771,30648,'Xk'),(68722,42055,16368,'YC'),(1247580230,NULL,20721479,'ys'),(853542900,1326765244,655949939,'ys'),(83531,15411,85799,'Y뢼'),(80537,49012,22715,'Y當'),(7595,75624,47251,'Źá'),(1138732822,NULL,2091722139,'Zq'),(2086382100,595650010,1006198310,'zW^IPnm\n~'),(47495,90493,80998,'z퐹Mj'),(582129112,NULL,1252934415,'Σ.'),(74322,76638,3945,'Ҙ.'),(655949939,NULL,71642055,'ԩ.'),(1735053105,231757348,1076688688,'ѽ.'),(7866,29888,42930,'Ҹ.'),(909691285,NULL,1969887323,'Ⴗ_نqྦྷV'),(81832,81110,12242,'טױ'),(1739583531,904181832,1634162414,'ޝ0'),(1869574126,18963449,1425581430,'뾪Ȿgl^r냺,b'),(1739583531,NULL,1942592986,'쒋'),(8329,62503,14783,'쒋'),(781468457,2062108484,406219548,'쒋'),(99283,7145,21869,'퍪t'),(419940846,NULL,261152598,'亳'),(18079,92984,76290,'勌ྦྷ|4m'),(2062108484,460880322,1904387696,'勌ྦྷ|4m'),(59245,3053,33185,'勢'),(54693,54936,92346,'妭葬'),(2375,66849,35405,'娕玱'),(81832,29009,80186,'旳38Zen '),(1597155469,1904387696,1132079235,'煴}ZJ'),(6692,43259,63376,'瘑'),(329414518,NULL,880593820,'蓀'),(45450,37629,27915,'褱'),(29648,36015,12132,'蹒狮'),(1260052930,NULL,1926222297,'遂'),(1343680601,727281431,693416001,'銐'),(85927,95841,47218,'雖*/點('),(21479,2117,43129,'雙'),(1574065109,NULL,1385865252,'ֱ֕A'),(44792,17696,14258,'⓺8G'),(73075,3178,53524,'5'),(1359676851,NULL,1221239486,'_'),(611431630,NULL,518541331,'l'),(14841676,1739583531,2147483647,'dm\\Hu%?,');
/*!40000 ALTER TABLE idx_t0 ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
ANALYZE TABLE idx_t0;
SELECT '== EXPLAIN ==' AS info;
EXPLAIN SELECT * FROM idx_t0 WHERE c7 = '' OR c2 = 843150457;
SELECT '== With index_merge ==' AS info;
SELECT COUNT(*) AS cnt_index_merge FROM idx_t0 WHERE c7 = '' OR c2 = 843150457;
SET SESSION optimizer_switch = 'index_merge_intersection=off,index_merge_sort_union=off,index_merge_union=off';
SELECT '== Without index_merge ==' AS info;
SELECT COUNT(*) AS cnt_no_index_merge FROM idx_t0 WHERE c7 = '' OR c2 = 843150457;
SET SESSION optimizer_switch = 'default';
SELECT COUNT(*) AS cnt_c7 FROM idx_t0 WHERE c7 = '';
SELECT COUNT(*) AS cnt_c2 FROM idx_t0 WHERE c2 = 843150457;
SELECT COUNT(*) AS cnt_both FROM idx_t0 WHERE c7 = '' AND c2 = 843150457;
SELECT 'BUG: If index_merge=67 and no_index_merge=66' AS info;
DROP DATABASE IF EXISTS test_db7_bug;
Suggested fix:
Investigate the deduplication logic in the index merge union algorithm. When merging row results from two index scans for an OR predicate, the algorithm must correctly identify and skip rows that appear in both index scans.
Key areas to investigate:
1. The duplicate detection in the index merge union likely uses primary key (row ID) comparison. The bug suggests that for certain overlapping rows, the primary key-based deduplication comparison fails to detect the duplicate -- possibly due to a comparison error when one of the index scans encounters the overlapping row in a specific position (e.g. at a boundary of the merge, or when NULL values in secondary key columns affect the scan ordering).
2. Investigate the index merge row retrieval loop in ha_innodb.cc / opt_range.cc where the union deduplication is performed, ensuring the primary key comparison covers all overlap cases correctly.
3. Verify that the "sort by row ID and deduplicate" step correctly handles the case where the same row appears in both index scans and should be output only once.
Description: In MySQL 9.6.0, when the optimizer chooses an Index Merge Union plan for a SELECT query with OR conditions (e.g., WHERE c7 = '' OR c2 = 843150457), the deduplication logic in the index merge union algorithm fails to correctly eliminate a row that satisfies both OR predicates. This results in the query returning 67 rows instead of the correct 66 rows (one duplicate row is produced). When the same query is executed with index merge disabled: SET SESSION optimizer_switch = 'index_merge_intersection=off,index_merge_sort_union=off,index_merge_union=off'; the correct result of 66 rows is returned. The root cause is that the index_merge access method scans two indexes separately and merges the results. For rows that satisfy BOTH OR conditions (i.e., rows where c7 = '' AND c2 = 843150457), the deduplication step in the union merge fails to recognize one such overlapping row as a duplicate, producing it twice in the final result set. Verification using inclusion-exclusion on the individual conditions: SELECT COUNT(*) FROM idx_t0 WHERE c7 = ''; -- N1 (65) SELECT COUNT(*) FROM idx_t0 WHERE c2 = 843150457; -- N2 (2) SELECT COUNT(*) FROM idx_t0 WHERE c7 = '' AND c2 = 843150457; -- N_overlap(1) Correct result = N1 + N2 - N_overlap = 66 Index merge result = 67 (one EXTRA duplicate) This is a correctness bug -- the optimizer produces a semantically wrong query result under default settings. Since index_merge_union is enabled by default, any workload that triggers index merge union on tables with multiple secondary indexes and OR predicates may silently return incorrect results. How to repeat: See the attached file min_mysql_index_merge_union_repro.sql or the script below for the complete standalone reproduction. Expected result: Both queries should return the same count. The inclusion-exclusion verification confirms the correct count is 66. Actual result: - With default optimizer +-----------------+ | cnt_index_merge | +-----------------+ | 67 | <- WRONG! +-----------------+ 1 row in set (0.00 sec) - With index_merge_intersection,index_merge_sort_union,index_merge_union off +--------------------+ | cnt_no_index_merge | +--------------------+ | 66 |<- CORRECT +--------------------+ 1 row in set (0.00 sec) Inclusion-exclusion verification: mysql> SELECT COUNT(*) AS cnt_c7 FROM idx_t0 WHERE c7 = ''; +--------+ | cnt_c7 | +--------+ | 65 | +--------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) AS cnt_c2 FROM idx_t0 WHERE c2 = 843150457; +--------+ | cnt_c2 | +--------+ | 2 | +--------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) AS cnt_both FROM idx_t0 WHERE c7 = '' AND c2 = 843150457; +----------+ | cnt_both | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) expected result = cnt_c7 + cnt_c2 - cnt_both = 65 + 2 - 1 = 66 (NOT 67 !) Below is the full script: DROP DATABASE IF EXISTS test_db7_bug; CREATE DATABASE test_db7_bug; USE test_db7_bug; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS idx_t0; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE idx_t0 ( `c0` int NOT NULL, `c2` int DEFAULT NULL, `c4` int NOT NULL, `c7` varchar(255) NOT NULL, UNIQUE KEY `i19` (`c0` DESC,`c4`) USING BTREE, UNIQUE KEY `i93` (`c7`,`c2`), UNIQUE KEY `i40` (`c2`,`c7`(4),`c4`), UNIQUE KEY `i72` (`c4` DESC,`c2`,`c0`,`c7`(3) DESC), KEY `idx_0_c0` (`c0`), KEY `idx_0_c0c1` (`c0`), KEY `loose_idx_3121` (`c4`), KEY `sq_in_3119` (`c2`), KEY `ordlim_idx_3119` (`c2`), KEY `sq_out_3121` (`c4`), KEY `cover_idx_3119` (`c2`,`c7`), KEY `prefix_idx_3124` (`c7`(5)), KEY `null_idx_3121` (`c4`), KEY `loose_idx_3124` (`c7`,`c0`), KEY `cover_idx_3124` (`c7`), KEY `null_idx_3119` (`c2`), KEY `range_idx_3117` (`c0`), KEY `cover_idx_3117` (`c0`,`c4`), KEY `null_idx_3117` (`c0`), KEY `null_idx_3124` (`c7`), KEY `hash_idx_3124` (`c7`), KEY `btree_idx_3124` (`c7`) USING BTREE, KEY `loose_idx_3117` (`c0`,`c4`), KEY `loose_idx_3119` (`c2`,`c4`), KEY `hash_idx_3119` (`c2`), KEY `btree_idx_3119` (`c2`) USING BTREE, KEY `range_idx_3121` (`c4`), KEY `cover_idx_3121` (`c4`,`c2`), KEY `sq_out_3117` (`c0`), KEY `hash_idx_3117` (`c0`), KEY `btree_idx_3117` (`c0`) USING BTREE, KEY `range_idx_3119` (`c2`), KEY `dropcol_idx_3124` (`c7`), KEY `imerge_b_3121` (`c4`), KEY `dropcol_idx_3119` (`c2`), KEY `sq_in_3117` (`c0`), KEY `imerge_a_3121` (`c4`), KEY `range_idx_3124` (`c7`), KEY `imerge_a_3119` (`c2`), KEY `ordlim_idx_3117` (`c0`), KEY `ordlim_idx_3124` (`c7`), KEY `loose_idx_3122` (`c0`), KEY `sq_out_3124` (`c7`), KEY `sq_in_3124` (`c7`), KEY `i32` (`c2`,`c4`), KEY `imerge_a_3124` (`c7`), KEY `imerge_b_3119` (`c2`), KEY `sq_out_3119` (`c2`), KEY `hash_idx_3121` (`c4`), KEY `btree_idx_3121` (`c4`) USING BTREE, KEY `sq_in_3121` (`c4`), KEY `inv_idx_3119` (`c2`), KEY `i28` (`c7`(2)), KEY `i23` (`c0`,`c7`(2)), KEY `i57` (`c2`), KEY `imerge_a_3117` (`c0`), KEY `ordlim_idx_3121` (`c4`), KEY `i66` (`c2`,`c7`) USING BTREE, KEY `imerge_b_3117` (`c0`), KEY `i15` (`c4`,`c0`) USING BTREE, KEY `imerge_b_3124` (`c7`), KEY `i5` (`c4`,`c0`,`c2` DESC,`c7`), KEY `i84` (`c7` DESC,`c0`), FULLTEXT KEY `ft_idx_3124` (`c7`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; LOCK TABLES idx_t0 WRITE; /*!40000 ALTER TABLE idx_t0 DISABLE KEYS */; INSERT INTO idx_t0 VALUES (2147045168,NULL,14841676,''),(2117345031,NULL,1667410790,''),(1904387696,NULL,2095835898,''),(1880758046,NULL,1358744737,''),(1779573111,NULL,1213338139,''),(1674945258,NULL,1132079235,''),(1548567565,NULL,1788669696,''),(1523759413,NULL,611431630,''),(1523759413,NULL,1057960958,''),(1371492984,NULL,118940865,''),(1370657502,NULL,549028794,''),(1359041638,NULL,1842778344,''),(1252668140,NULL,350075089,''),(974153138,NULL,1771363725,''),(934828754,NULL,642845854,''),(930734054,NULL,1255038522,''),(919550890,NULL,1519192346,''),(774856002,NULL,762286399,''),(771800201,NULL,145402466,''),(362390838,NULL,1797585080,''),(358336914,NULL,1414626667,''),(119560663,NULL,183402085,''),(54378892,NULL,1118891475,''),(16412241,NULL,225171780,''),(41413,3989,73076,''),(51613,8291,32413,''),(82183,10014,81431,''),(92346,13657,62414,''),(28526,14584,80601,''),(55680,16349,84786,''),(45450,18819,61109,''),(71612,27933,21235,''),(91287,30787,94399,''),(84038,43830,99895,''),(19921,46432,11719,''),(36914,53646,73882,''),(99613,53817,71114,''),(73723,54926,33555,''),(13000,58578,4459,''),(65426,63725,71361,''),(91352,72617,29648,''),(89343,73969,94321,''),(39176,74590,86895,''),(65656,75810,38446,''),(19968,80150,75358,''),(40796,81110,23614,''),(51333,99812,58046,''),(172449481,34503578,1218873856,''),(191280998,84272344,107637559,''),(1371492984,92057120,1489589624,''),(20721479,92364601,1567451520,''),(2116423639,322998246,1062740533,''),(587213560,372882233,1001284786,''),(949040915,642563902,970485927,''),(807711719,737677516,16412241,''),(890281334,764203018,1771363725,''),(561732697,843150457,279416460,''),(227747812,854268660,686609524,''),(1388399793,1254650565,1926222297,''),(2113474910,1317507866,1116759449,''),(1385262738,1495855544,1878058977,''),(2062108484,1512136435,1228987089,''),(105765466,1592983694,2001072104,''),(380252171,1741026444,1793181195,'֧'),(871471114,2103424995,1343680601,''),(95889847,1243573363,1470117261,' '),(1056762373,NULL,980801455,'\rB(l'),(443622876,NULL,1771363725,' ('),(1051874117,NULL,443622876,' 4/IN '),(1182273969,843150457,1879313000,' 4/in '),(333531928,1057960958,356297673,' Gik쩓h*z*'),(8284,80418,1,' P'),(326081289,1692107145,845770512,' s^U2\\z'),(3178,34310,11958,'_mcG6z'),(580438611,NULL,20721479,'_w'),(20706,18347,5184,'-❞2dbt/bR'),(87702,1,80942,'-1057960958'),(1469381110,480938097,561454693,'-1057960958'),(362390838,NULL,329942308,'-1191899812'),(1781077776,NULL,871471114,'-1241522003'),(1554534456,NULL,378916813,'-1241522003'),(1050662283,NULL,2487134,'-1343680601'),(408595227,NULL,1295682690,'-1685273882'),(92984,30132,48325,'-1832100748'),(57441,12272,81832,'-1e500'),(53105,38611,19548,'-2062108484'),(715942397,NULL,450714359,'-2116423639'),(453144095,661968304,906870547,'-2116423639'),(912139514,2001180561,1563785845,'-21404459'),(1182586366,NULL,1126139364,'-220249012'),(920681927,1424268161,708253706,'-220249012'),(807711719,2116423639,21404459,'-220249012'),(1005203368,869190947,2061995811,'-250196810'),(0,49528,902,'-408595227'),(358336914,NULL,16412241,'-423301552'),(924319116,1993390117,180429877,'-423301552'),(67621,82736,79450,'-443622876'),(40865,39514,16460,'-549028794'),(20721479,343558237,344805234,'-549028794'),(1182586366,617244655,16412241,'-586294399'),(85585,26667,81334,'-630961109'),(1865085218,965713763,1243573363,'-630961109'),(577774322,NULL,191280998,'-73191683'),(549028794,2014407528,27151559,'-862010229'),(630961109,1371492984,384112869,'-974153138'),(18306,79235,86366,'-e'),(53727,83285,70866,'-f'),(2375,48933,58931,'-MB'),(60271,7866,19293,', \'^zh(*'),(71642055,15842355,1735053105,', \'^zh(*'),(1160598670,NULL,1086683207,',4\\'),(279416460,1974943830,14841676,',4\\'),(367486838,1198112375,1426707971,',QFqF|7'),(93852,7145,84555,',z'),(23639,50890,35094,',\''),(91854,32436,34682,'!b5P'),(93923,38611,40865,'!郞'),(460149540,899783616,2018232057,'?r'),(1467972847,NULL,142330059,'?TYO\n)c'),(1504755073,839257345,1683980069,'?z}'),(58046,1607,36260,'\'2'),(17767,94399,62414,'('),(2044763791,551618347,2058250660,'(XW'),(362390838,NULL,221765426,')^i jX'),(22003,71114,68611,')^i jX'),(1371492984,1523759413,1371492984,')+5'),(71802,30245,19548,')a &0Es+'),(18733,10229,56548,'[ VF'),(99812,22297,72617,'[{9>oG#&'),(1623900117,1164472617,564280717,'[0TFF'),(61109,97218,20838,'[j'),(45298,79869,71114,'}ό'),(577774322,NULL,1525916804,'*'),(1054806299,1454526118,1771363725,'*)4eU{b'),(22876,16906,40347,'**'),(1589844072,NULL,806038463,'*d[mf'),(891129563,NULL,146280645,'\\]()'),(7621,63725,42131,'\\9KD鏑{A!2'),(20958,47327,37345,'&撲u,'),(361980816,NULL,1137793127,'֍әǵӈ'),(10908,22297,5717,'+<vO'),(1179,90301,78605,'+1'),(87583018,NULL,912139514,'<%m'),(1439691453,NULL,1235021869,'<x_m&]Ag'),(1879313000,1199587702,1904387696,'>) }HJ'),(229343591,NULL,1556253586,'>n'),(60958,40684,4123,'|'),(1618776903,NULL,1634162414,'|-'),(69380,80717,16460,'👏'),(15486,81110,91683,'💧'),(385272972,1795751975,756927950,'📵'),(1317507866,NULL,880545259,'🔗'),(48364,59972,49284,'🔤'),(9709,47443,87795,'🔮'),(1124145450,NULL,1712741501,'🕱'),(1243573363,1391341853,1244409266,'🗜'),(51240,51824,80601,'͟0'),(69765,53138,61099,'0.08063267171602795'),(1634162414,NULL,871471114,'0.13265588521057825'),(198744902,NULL,1235021869,'0.13265588521057825'),(1235021869,371614899,549028794,'0.13265588521057825'),(851511388,487719986,1419219921,'0.13265588521057825'),(923836642,549028794,26663531,'0.13265588521057825'),(546538079,1154830544,434418733,'0.13265588521057825'),(224294066,1300807824,2076579769,'0.13265588521057825'),(80791,21677,63596,'0.27010397262033736'),(63016,80998,99010,'0.6569566770221136'),(807711719,NULL,652656668,'0.7507612272572769'),(709364539,NULL,835842862,'0.7507612272572769'),(373855867,NULL,260612742,'0.7507612272572769'),(358336914,NULL,453404894,'0.7507612272572769'),(27151559,NULL,423301552,'0.7507612272572769'),(177631023,1574574306,179232384,'0.7507612272572769'),(1454733821,NULL,1271525728,'0.7649744287375929'),(1101796340,NULL,1741466624,'0.7649744287375929'),(561454693,NULL,1243573363,'0.7649744287375929'),(134260054,1500870195,1646709511,'0.7649744287375929'),(1559011559,1932766588,198503284,'0.7649744287375929'),(1775113177,NULL,409882188,'0.8153759212902829'),(552051858,164632809,1629985478,'0.8153759212902829'),(31630,80998,74292,'0.838836989722932'),(75003299,NULL,16412241,'0.9212992395527393'),(1948821569,837432783,556139,'0.9212992395527393'),(21819,31887,39514,'0.9399888989885958'),(73363,59682,7716,'0.9399888989885958'),(87215,7866,64,'0N'),(67931,66593,40066,'1)T'),(36775,10695,11719,'1001284786'),(1057960958,NULL,406219548,'1051874117'),(16460,14518,85927,'1051874117'),(47483,24517,68787,'1164472617'),(1566317696,NULL,1124145450,'1235021869'),(66849,4795,63725,'14'),(1051874117,1221761375,330479218,'14'),(85927,41464,90838,'1469381110'),(43108,87696,57237,'1519192346'),(75089,89405,57552,'1530304774'),(26667,60749,54693,'1692107145'),(94061,2375,88881,'1879313000'),(348767468,NULL,1802847867,'1e500'),(2118,7866,40067,'1w.嵜'),(15220,29648,91468,'20c(8'),(912536587,278687259,31963796,'236568492'),(83606,89703,61109,'2J'),(5862,2388,29678,'3'),(4459,7145,74443,'329414518'),(727281431,1832100748,1414626667,'329414518'),(24111,8301,93131,'350075089'),(41620,71293,4939,'362390838'),(70157,67457,30896,'3gI/\''),(1883956833,992051206,1101754451,'3zgz(\''),(561732697,350075089,1886137088,'3㶍'),(934828754,807711719,329984408,'3㶍'),(1369772720,NULL,221765426,'4∈'),(1583084524,NULL,789541909,'434418733'),(80601,82289,20267,'4C]'),(640875905,NULL,350075089,'551618347'),(28754,18426,50457,'551618347'),(1519192346,1640507708,359709906,'551618347'),(11680,17321,79235,'564280717'),(45356,66970,23639,'564280717'),(45264,96440,27203,'5TR'),(0,96810,49467,'5uJ| d,8>'),(1788220973,NULL,2013798175,'6J|\''),(1780399043,NULL,1197790371,'6S'),(24352,56281,93276,'727281431'),(1904387696,NULL,1566317696,'774856002'),(1585337499,513303545,1781077776,'8'),(1001399426,NULL,862010229,'807711719'),(261815325,NULL,577774322,'807711719'),(-2147483648,279416460,1247774623,'871471114'),(30504,21869,88294,'871507806'),(29454,84016,89677,'8I'),(87583018,NULL,20721479,'912139514'),(63790,75905,85284,'919550890'),(611431630,NULL,236568492,'A'),(85927,42863,89306,'am6'),(275744475,NULL,710967318,'BB>52>崏'),(1845834495,NULL,423301552,'dES準> g'),(564280717,NULL,1716900262,'DkoLp'),(83018,88249,67391,'DW<g'),(24147,92984,59413,'DZ'),(27151559,NULL,344447193,'D㤳/B\'ᔐF['),(1685273882,1524072166,953881024,'ȡ'),(1688408093,NULL,1131207608,'E'),(88044,72124,73969,'Ē'),(143820551,1643857588,807711719,'e\n'),(1565964175,NULL,32351243,'E<k'),(2133390919,704568624,496368985,'ễz5kI!'),(1288729236,868033548,970485927,'ᵌB,?w1'),(47559,51741,81832,'FKYV'),(8682202,87583018,2107303178,'FN'),(1432731230,NULL,161994208,'fO묤*3'),(1458577698,586294399,1243573363,'FtVi\r'),(15048,74385,42556,'fWNTJoqNI'),(90842,1106,95249,'G'),(19548,63725,4459,'g'),(75783,65567,75905,'g&j坳'),(98897,81110,61961,'g#'),(4774,96659,10229,'G>-3짮J'),(1999691333,NULL,1307716693,'GqqL<Z賢TQ'),(99812,83018,38611,'hz怂'),(807711719,NULL,845984469,'Ī'),(41676,51004,11719,'I'),(696272602,447530912,1126419150,'ȊƓ'),(83026,75905,95227,'Ɩ'),(350075089,2051328615,1694356334,'j7N樲껊BWN'),(1660699380,1912153622,561732697,'jTDG熛<'),(82835,63013,83566,'j'),(47520,5573,81832,'k2!1y)?'),(406219548,NULL,1519192346,'kp'),(1739583531,712254610,1164472617,'kZ+|EH?j\r'),(329414518,NULL,1523759413,'k'),(507877287,NULL,1236462242,'K'),(1350336388,NULL,2110421182,'L&H'),(20617,42653,63725,'Lj '),(51046,44902,60543,'lkRS\'Ob'),(777271438,NULL,807711719,'M6'),(53138,57601,56687,'MO&c6^ꖬ0v'),(75905,87696,73882,'N MeCm'),(62414,49939,61130,'Ƞ/'),(73363,51866,39374,'o8Scf -ﳾ'),(1951526747,NULL,919550890,'ow'),(50749,17582,88691,'P+'),(862154366,NULL,1376527744,'Q%'),(77757,55484,70907,'R⢦'),(1832100748,970485927,423301552,'rgY\n'),(86366,49232,34168,'RH'),(8104,63399,29617,'R䘰4O2 z,'),(165485230,1771363725,1164472617,'S'),(1382029738,NULL,1665319861,'s*,krr'),(8484,7866,35595,'s*,krr'),(1288838675,NULL,718735138,'S厧*'),(793851458,1919435095,871471114,'t^d'),(1241624496,NULL,730610614,'tFu_w#'),(43978,16460,71466,'t'),(1726720993,1738405153,1104307523,'T'),(748,74117,56786,'U忇P'),(54693,1419,11719,'Vc'),(577774322,848801641,987449479,'vhd2hw'),(274190074,NULL,685200280,'vy᯽M뼂臨J'),(1182586366,NULL,1904387696,'VZq8~࿕J'),(1601176533,329414518,1418069388,'w'),(2134406122,1193115426,1029440889,'W4'),(85927,1606,54306,'ֻX'),(2711,89771,30648,'Xk'),(68722,42055,16368,'YC'),(1247580230,NULL,20721479,'ys'),(853542900,1326765244,655949939,'ys'),(83531,15411,85799,'Y뢼'),(80537,49012,22715,'Y當'),(7595,75624,47251,'Źá'),(1138732822,NULL,2091722139,'Zq'),(2086382100,595650010,1006198310,'zW^IPnm\n~'),(47495,90493,80998,'z퐹Mj'),(582129112,NULL,1252934415,'Σ.'),(74322,76638,3945,'Ҙ.'),(655949939,NULL,71642055,'ԩ.'),(1735053105,231757348,1076688688,'ѽ.'),(7866,29888,42930,'Ҹ.'),(909691285,NULL,1969887323,'Ⴗ_نqྦྷV'),(81832,81110,12242,'טױ'),(1739583531,904181832,1634162414,'ޝ0'),(1869574126,18963449,1425581430,'뾪Ȿgl^r냺,b'),(1739583531,NULL,1942592986,'쒋'),(8329,62503,14783,'쒋'),(781468457,2062108484,406219548,'쒋'),(99283,7145,21869,'퍪t'),(419940846,NULL,261152598,'亳'),(18079,92984,76290,'勌ྦྷ|4m'),(2062108484,460880322,1904387696,'勌ྦྷ|4m'),(59245,3053,33185,'勢'),(54693,54936,92346,'妭葬'),(2375,66849,35405,'娕玱'),(81832,29009,80186,'旳38Zen '),(1597155469,1904387696,1132079235,'煴}ZJ'),(6692,43259,63376,'瘑'),(329414518,NULL,880593820,'蓀'),(45450,37629,27915,'褱'),(29648,36015,12132,'蹒狮'),(1260052930,NULL,1926222297,'遂'),(1343680601,727281431,693416001,'銐'),(85927,95841,47218,'雖*/點('),(21479,2117,43129,'雙'),(1574065109,NULL,1385865252,'ֱ֕A'),(44792,17696,14258,'⓺8G'),(73075,3178,53524,'5'),(1359676851,NULL,1221239486,'_'),(611431630,NULL,518541331,'l'),(14841676,1739583531,2147483647,'dm\\Hu%?,'); /*!40000 ALTER TABLE idx_t0 ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; ANALYZE TABLE idx_t0; SELECT '== EXPLAIN ==' AS info; EXPLAIN SELECT * FROM idx_t0 WHERE c7 = '' OR c2 = 843150457; SELECT '== With index_merge ==' AS info; SELECT COUNT(*) AS cnt_index_merge FROM idx_t0 WHERE c7 = '' OR c2 = 843150457; SET SESSION optimizer_switch = 'index_merge_intersection=off,index_merge_sort_union=off,index_merge_union=off'; SELECT '== Without index_merge ==' AS info; SELECT COUNT(*) AS cnt_no_index_merge FROM idx_t0 WHERE c7 = '' OR c2 = 843150457; SET SESSION optimizer_switch = 'default'; SELECT COUNT(*) AS cnt_c7 FROM idx_t0 WHERE c7 = ''; SELECT COUNT(*) AS cnt_c2 FROM idx_t0 WHERE c2 = 843150457; SELECT COUNT(*) AS cnt_both FROM idx_t0 WHERE c7 = '' AND c2 = 843150457; SELECT 'BUG: If index_merge=67 and no_index_merge=66' AS info; DROP DATABASE IF EXISTS test_db7_bug; Suggested fix: Investigate the deduplication logic in the index merge union algorithm. When merging row results from two index scans for an OR predicate, the algorithm must correctly identify and skip rows that appear in both index scans. Key areas to investigate: 1. The duplicate detection in the index merge union likely uses primary key (row ID) comparison. The bug suggests that for certain overlapping rows, the primary key-based deduplication comparison fails to detect the duplicate -- possibly due to a comparison error when one of the index scans encounters the overlapping row in a specific position (e.g. at a boundary of the merge, or when NULL values in secondary key columns affect the scan ordering). 2. Investigate the index merge row retrieval loop in ha_innodb.cc / opt_range.cc where the union deduplication is performed, ensuring the primary key comparison covers all overlap cases correctly. 3. Verify that the "sort by row ID and deduplicate" step correctly handles the case where the same row appears in both index scans and should be output only once.