-- ============================================================ -- MySQL Index Merge Intersect Bug -- MySQL 9.6.0 - Real Bug Confirmed -- -- Bug: "Intersect rows sorted by row ID" in index_merge incorrectly -- reduces 2 matching rows to 1, causing SELECT with OR to miss rows. -- -- Correct: 3 rows | Buggy: 2 rows -- Missing: (c2=0.7559466140838738, c3=1776068260, c7=2084714166) -- ============================================================ DROP DATABASE IF EXISTS db1_min; CREATE DATABASE db1_min; USE db1_min; CREATE TABLE `t` ( `c2` double NOT NULL, `c3` int NOT NULL, `c7` int NOT NULL, UNIQUE KEY `i30` (`c3` DESC,`c2`), UNIQUE KEY `i64` (`c2` DESC,`c3`), UNIQUE KEY `i95` (`c7`,`c3`) USING BTREE, UNIQUE KEY `i61` (`c2`,`c7`) USING BTREE, UNIQUE KEY `i107` (`c3` DESC,`c7` DESC), UNIQUE KEY `i109` (`c3`,`c7`), UNIQUE KEY `i20` (`c7` DESC,`c2`), UNIQUE KEY `i114` (`c2`,`c7`,`c3`), UNIQUE KEY `i36` (`c7`,`c3`,`c2`), UNIQUE KEY `i67` (`c7`,`c2`), UNIQUE KEY `i83` (`c2`,`c3`), UNIQUE KEY `i68` (`c3` DESC,`c2` DESC), KEY `sq_in_3120` (`c3`), KEY `hash_idx_3124` (`c7`), KEY `btree_idx_3124` (`c7`) USING BTREE, KEY `sq_in_3124` (`c7`), KEY `sq_out_3120` (`c3`), KEY `cover_idx_3124` (`c7`), KEY `hash_idx_3119` (`c2`), KEY `btree_idx_3119` (`c2`) USING BTREE, KEY `ordlim_idx_3124` (`c7`), KEY `hash_idx_3120` (`c3`), KEY `btree_idx_3120` (`c3`) USING BTREE, KEY `range_idx_3124` (`c7`), KEY `range_idx_3120` (`c3`), KEY `loose_idx_3118` (`c7`), KEY `cover_idx_3120` (`c3`,`c7`), KEY `null_idx_3120` (`c3`), KEY `loose_idx_3124` (`c7`,`c3`), KEY `ordlim_idx_3120` (`c3`), KEY `sq_out_3119` (`c2`), KEY `cover_idx_3119` (`c2`,`c3`), KEY `range_idx_3119` (`c2`), KEY `inv_idx_3120` (`c3`), KEY `sq_out_3124` (`c7`), KEY `sq_in_3119` (`c2`), KEY `null_idx_3124` (`c7`), KEY `loose_idx_3119` (`c2`,`c7`), KEY `loose_idx_3120` (`c3`,`c2`), KEY `inv_idx_3124` (`c7`), KEY `dropcol_idx_3120` (`c3`), KEY `null_idx_3119` (`c2`), KEY `i22` (`c3`), KEY `ordlim_idx_3119` (`c2`), KEY `i35` (`c2`,`c7`), KEY `i6` (`c2`,`c3`), KEY `i103` (`c7`,`c2`,`c3` DESC), KEY `i111` (`c7`,`c3`), KEY `dropcol_idx_3124` (`c7`), KEY `i29` (`c2`,`c3` DESC), KEY `imerge_b_3124` (`c7`), KEY `i78` (`c2` DESC,`c7`,`c3`), KEY `imerge_a_3120` (`c3`), KEY `i24` (`c7`), KEY `i110` (`c7`) USING BTREE, KEY `i12` (`c3` DESC,`c7` DESC,`c2`), KEY `inv_idx_3119` (`c2`) /*!80000 INVISIBLE */, KEY `imerge_b_3119` (`c2`), KEY `imerge_a_3124` (`c7`), KEY `i86` (`c3` DESC,`c2`), KEY `i40` (`c7`), KEY `i82` (`c3`), KEY `i116` (`c7` DESC,`c2`,`c3`), KEY `i91` ((229101023)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `t` VALUES (0.4998089324061624,1994043477,-2147483648),(0.22424891399631985,1498223081,1),(0.26984210596099323,1468944374,1),(0.21241236285752363,84268,997),(0.5342239710482061,28630,1591),(0.01284254378432037,7950,1687),(0.9692035158721042,96252,2504),(0.8595225375460958,39884,2545),(695456549,88411,2705),(1073842890,28235,3565),(0.04118537991833382,71245,6575),(0.4724971680355845,97865,6684),(0.1862490696825585,62370,9662),(2140440292,71233,10583),(0.258600194126538,1,11282),(0.461403723300269,21488,11362),(0.7092880529181196,38673,11925),(0.6747295696156336,64840,11960),(0.058303145416156,99589,12084),(0.9147446210637671,66345,12084),(0.5966080569182266,27870,12084),(0.927727396099887,45479,13321),(0.002993500114817582,82262,13427),(0.5995653583402312,80404,14981),(989198737,51527,16306),(1257161369,44836,18151),(0.8865974318761695,30371,20467),(0.7094210617130057,45666,21196),(0.7936152297262115,53451,21351),(0.5368321070735913,82313,21877),(0.8500701145183085,72359,22190),(0.986519293649018,4914,23531),(0.622172931619783,27028,24448),(0.22902397177494838,29843,24904),(0.11025879200547206,11857,26239),(0.9147446210637671,297,28288),(0.5575906213427299,59032,29749),(0.2833137634331453,15683,29841),(0.6149858826597377,44812,30641),(1544249587,44988,30963),(1127173507,2800,31896),(0.978176188283728,55087,32118),(0.8801679626191279,34602,32497),(0.2876080910623674,40007,32655),(0.09459269869877307,61074,33889),(0.6723179516048515,73507,34602),(0.8885837572306411,37574,34854),(0.3936484162383814,21824,35684),(0.5466238581271089,78343,36424),(0.9813261463206263,27300,36773),(716311265,76426,36890),(0.48535957630867155,64232,37574),(0.3282679852113395,66193,37869),(0.8242913416582269,11265,39639),(1146891378,82262,41098),(0.23646602888722879,28630,41595),(0.29243274536146413,51768,41752),(0.7693932605354313,11173,42051),(1374282262,32597,42723),(1762129841,78343,42890),(0.3469860060325287,17108,42890),(0.4638177421370593,663,42890),(0.7739719892088961,77956,43637),(0.43577827666270086,17263,43637),(0.8580965898934745,60510,43974),(0.35185444219951656,8886,45495),(0.9400907623567843,88325,47807),(1762129841,95331,49360),(0.6340959783205914,47560,49834),(46096254,17363,50166),(0.8606912872318876,96457,50293),(0.6728783227649782,58340,50504),(0.5770415014007827,37574,50539),(0.2005718228275929,99589,51527),(886180397,92830,52832),(0.5222853146288194,3000,53255),(0.1728573110846483,92124,53451),(0.9929786273653537,45553,53531),(0.9127569696523203,1304,54016),(0.9328386948139226,39948,54026),(0.36942207179876374,78343,57001),(0.8702813747460819,408,57269),(1073842890,5616,58844),(2128699589,61369,59739),(0.7588045877696131,38144,59822),(0.5343969248587289,1687,59822),(93209744,7357,60538),(0.14597389899480706,89686,61369),(0.9120969595417223,21810,62879),(0.18767037871708514,98737,64929),(0.6537271262153053,22546,66328),(0.25025316960104194,84111,66592),(1585389244,56549,66661),(0.24426262601053783,79653,67192),(0.5161264292311385,60434,67192),(0.9439794565149955,2036,67192),(0.7950863497479659,38432,67466),(1762129841,75352,67991),(0.01141609289640133,86033,69276),(0.1678737186878414,37574,69333),(716311265,49360,70551),(0.8266480221309724,93768,70668),(0.1267217592040284,88537,71442),(0.689204404251611,16306,72016),(0.7081233058418752,37827,72363),(0.3075310567959614,29749,73103),(0.14061468622087214,96411,73107),(0.6097944935812867,59916,73127),(0.8828475310907044,97502,73507),(1374282262,65215,73710),(0.8104040766817787,43657,75098),(0.2461046297846048,51314,75306),(0.8189130957642977,79944,75858),(0.2359589454065154,23098,77924),(0.7603003436947985,43332,78254),(0.9522183083796103,45132,78343),(1013166153,50623,78520),(0.05522373026710903,32118,78749),(0.9512639550498442,57003,78830),(0.04944357205162997,27758,80214),(0.37766256442245627,30371,80322),(608012084,70551,80541),(0.5426126166257024,40292,81989),(0.9903172201689084,2437,82093),(0.41369410571855714,70551,82262),(0.08094339298674924,18415,82807),(84630371,33077,83346),(0.28251776117256877,84842,83798),(0.27435656301738887,97663,87760),(0.8565665351624688,84561,88325),(1073072363,45771,88605),(0.691215517522686,48937,89244),(0.5106373122034648,10742,89463),(642818415,24812,90167),(0.20232595934817577,84842,91378),(1073072363,13427,92830),(0.7636289563817749,66519,93369),(0.23515654850729328,80842,93480),(0.7094210617130057,64311,93480),(732786033,76748,93768),(0.9781439488890539,77958,93985),(0.40593077718057036,24499,94976),(0.3216066963569737,86942,95503),(0.7320428326107856,26727,96254),(0.9044072913664168,96279,96411),(93209744,45053,96411),(0.08507728692565752,18551,96411),(0.632133006532417,28630,96665),(0.9313058983853841,90068,96716),(0.24902284606292924,50565,96776),(0.3470342050307058,2800,97502),(0.5632494156368514,85690,97601),(0.2716738120572951,47607,98666),(0.258600194126538,554328031,15657971),(0.5256977030321953,2021909723,18585690),(0.6396097300064939,504659916,23761152),(320353310,1331497601,29753091),(1585389244,1585966989,46096254),(0.16642841199886726,1,51811084),(0.10687642741106895,2090134339,84630371),(0.9749208117234744,990627117,130113355),(0.22100756988160153,557107234,145444069),(0.061879298321044995,898537844,149051130),(0.5457828211860187,2054756024,155270619),(0.023568435108917263,310610448,160838665),(0.8588809279538935,629730555,173078343),(608012084,106080172,173078343),(1861266328,2147086931,179918666),(695456549,267060465,190096623),(0.5304117471267208,264121193,190096623),(84630371,545280341,225041321),(0.02867645957570164,880177163,252789592),(0.42396938510799054,1203805085,255383148),(0.18382451343466077,989198737,256344449),(1331497601,1279326159,268131165),(0.7559466140838738,939902875,281834758),(1044617263,2076213040,287496496),(0.5139031607238688,603504925,288170551),(0.11025879200547206,222952377,288170551),(0.2809065726346426,1434516098,292127132),(2021909723,347062344,295415866),(0.0796873602635948,558674999,302302845),(989198737,458239350,321991625),(0.5396058873110469,1704632118,322331654),(0.7153189686543491,1583640270,338373753),(0.2907946688973023,2090134339,376870279),(0.42254979310622354,675497467,385243974),(0.9692035158721042,1890596869,420533688),(0.6114222762088029,1510920966,420533688),(0.6501604050142049,943552775,420533688),(0.6489351297081303,915355190,423947207),(0.9837602460071858,969932010,442684710),(0.2659747349616206,244382690,454989806),(0.01218536816691973,1807631078,466209836),(0.4727157730278453,1442007211,467483785),(0.7185571811429025,238100151,473906662),(0.7309204998042859,2110800140,504808765),(0.23646602888722879,1052801687,527129120),(0.6011586980169157,190096623,527129120),(0.6152262265306022,138703893,527129120),(0.22115236089734513,1073072363,528727164),(0.9518539566914046,93964457,541024757),(0.8634056615471105,361740912,542444988),(0.6121063970609464,137201839,555176110),(0.7615097908155286,36451430,589347785),(0.39846415897097076,1331497601,590416949),(0.2557109919667039,2009371849,605803772),(0.8427485746892304,536829749,608012084),(0.5139031607238688,1604642115,642818415),(0.26686769049053016,1174578266,661252154),(0.22115236089734513,654242807,675582093),(0.8242665334647581,2074447706,680875999),(1756051527,518462688,686077268),(0,302302845,697029995),(0.8163454950191514,729791145,732786033),(0.21312527789522884,73796028,740028630),(0.5839807570760187,1814019436,744681433),(0.4507379489141029,1073072363,747872156),(0.21241236285752363,1747083798,775159822),(0.8252153687412229,1572988656,775159822),(0.23785855899974762,536829749,775159822),(0.4090630222464714,84630371,798267809),(0.6128091905989959,46096254,798754783),(0.9385046956449079,1472695806,799773964),(0.9692035158721042,2071784343,802727656),(0.4684403722895043,1472263510,809832620),(0.10677407614106948,1146891378,882420341),(0.461403723300269,749234249,905066758),(0.7588045877696131,542444988,926428122),(0.4228475922139521,1073072363,926868181),(0.5259520025025977,1072102800,928745751),(0.1678737186878414,1731600523,930293480),(0.13158752347658442,695456549,937316092),(0.07431013124962949,1561250419,943552775),(0.7745707730900239,343201591,943552775),(0.9127569696523203,1509258553,962016247),(0.2669183588966644,1474359739,963963599),(0.2851349118466826,1983575352,967300607),(0.6080124670290428,444024442,973398441),(1763166309,1502548732,973486329),(0.48294666126673635,420533688,989198737),(0.48196325349155966,1609867843,1000163636),(0.13717854913908634,504659916,1003415283),(0.8971075730871655,427788689,1005577435),(0.3930307913833686,1052801687,1013166153),(0.05838090521984507,1279503163,1025187154),(0.9452076780416647,1430448158,1057954798),(1721888325,127015767,1073072363),(0.31186114697490697,1699327703,1073842890),(0.22074060211139923,1921705166,1098421474),(0.8669238142166014,542444988,1100902037),(0.554273317998072,1635392151,1103171245),(0.38778642833287935,376839823,1105121403),(0.2801600890197179,1800627608,1132334602),(0.05769787417471306,1921705166,1137362371),(0.1915685313345259,2143526645,1146891378),(0.21268320378054428,1350025032,1146891378),(0.7547799085874994,1871343637,1146975098),(385243974,655595366,1146975098),(1472263510,1610649642,1184093768),(0.01825621188471016,981044343,1184093768),(0.8291206906790957,1551889730,1203405836),(0.6118975522780473,-2147483648,1205737574),(0.170155990726652,1607950969,1209193788),(0.0870267475416926,419858190,1223551367),(0.25783301781350443,2050472842,1257161369),(0.3004569650199075,450692830,1257607734),(0.10530630424929999,225952002,1349221924),(0.4975361402339722,137136592,1359331216),(0.0049392844982967254,608012084,1375279601),(0.4025332786062461,1774747728,1386723032),(0.9140456183474737,451992234,1390262278),(1890596869,1445641750,1401905777),(46096254,1986850320,1402087000),(0.45259430961446223,1954840164,1411842150),(0.4675922578465931,1013166153,1414513427),(0.865741083886783,1061884842,1417014858),(0.7615097908155286,266035926,1427319651),(0.5917789062330566,1257607734,1469748688),(0.23003250886657256,1418473403,1472263510),(0.22144935878598537,985298689,1473679411),(0.629887897730372,1890596869,1488041343),(0.0170528942837902,442749360,1494672252),(0.5929270522618482,856956805,1508934915),(0.34780684243886084,1011311672,1510920966),(0.9559856561279263,941271255,1516945449),(0.5692225002091047,1184093768,1559726944),(0.5342239710482061,136556066,1576906244),(0.5181942594920211,1583317613,1577909064),(967300607,1756051527,1580323499),(0.4507379489141029,1921705166,1585389244),(0.6427040162174528,550653686,1585389244),(0.43247064735627705,445281969,1585389244),(0.8864599272820137,962686565,1617331962),(0.835926169573257,837412118,1617331962),(0.2809065726346426,979004834,1634465607),(0.3612823393864022,155270619,1640257265),(0.6951073174029753,1994004148,1640943525),(0.6853955798991991,590626802,1642112139),(0.04944357205162997,46096254,1650078818),(0.6884348809164152,1375836083,1654319753),(0.6994598449689549,1799547881,1655227005),(0.8242913416582269,84630371,1673484874),(0.5381084899799604,1626171215,1691587141),(1472263510,1392059768,1700308451),(1585389244,1200804315,1704632118),(0.2862897715798667,44219486,1704632118),(0.5304117471267208,1705177117,1725367215),(0.17695760236801528,1069235877,1731600523),(0.6975313660146797,1013166153,1732753451),(0.1601842716730384,2142578336,1747083798),(0.21241236285752363,659195503,1747083798),(0.04944357205162997,1301952411,1751109976),(0.41369410571855714,1609745779,1774725198),(0.6407136643181445,956647809,1794288419),(0.9721854009340624,1431141191,1795434043),(0.23646602888722879,1350977662,1808824494),(0.5301412153669223,405818183,1845512421),(0.4273926374818471,2015545851,1859492574),(0.9559856561279263,746485457,1870626004),(0.986519293649018,1822630486,1871343637),(0.37127726714212994,670301089,1871343637),(0.4194266610562105,296157430,1871343637),(0.8012714587910563,542444988,1884794065),(2128699589,1132334602,1885642240),(0.8794924167533392,1132334602,1890321328),(0.8163454950191514,1453396401,1890596869),(0.7056347448773813,170715896,1900420450),(0.22145682738900385,186932235,1904554257),(0.45878175648690367,1820264514,1919181365),(0.7693932605354313,966695877,1919181365),(0.5632494156368514,1745058692,1950160524),(0.26420344791402994,450692830,1983575352),(0.20040790431375322,1792580886,1988251547),(0.5946262163192706,320353310,1999428808),(0.33203580809559885,1902966099,2006251265),(0.8113204532716609,667074488,2021909723),(0.7908225030128324,339059048,2021909723),(0.3440643813625559,1721888325,2029895401),(0.5028505304693309,1756051527,2036996505),(0.4628597113866858,378188801,2063756120),(0.7559466140838738,1776068260,2084714166),(0.9256471413970471,1472263510,2088744134),(0.5613281397163855,775159822,2090112602),(0.9169720485137952,1721888325,2090134339),(1414513427,943552775,2090134339),(0.7936152297262115,269727101,2091250417),(0.3494614301535858,836011381,2093941197),(0.1601842716730384,1424598182,2125008434),(0.6500386505719806,1355968767,2135644556); -- Update statistics ANALYZE TABLE t; ANALYZE TABLE t UPDATE HISTOGRAM ON c2, c7 WITH 1024 BUCKETS; -- ============================================================ -- MySQL 9.6.0 Index Merge Intersect Bug -- Bug: Intersect reduces 2 rows to 1, missing 1 row -- ============================================================ -- With default optimizer (index_merge_intersection=on), returns 2: SELECT 'With default optimizer, returns 2: WRONG - missing 1 row' AS ''; SELECT COUNT(*) AS cnt FROM t WHERE c2 = 0.7559466140838738 OR c7 = 287496496; -- With index_merge_intersection=off, returns 3: SELECT 'With index_merge_intersection=off, returns 3: CORRECT' AS ''; SET SESSION optimizer_switch = 'index_merge_intersection=off'; SELECT COUNT(*) AS cnt FROM t WHERE c2 = 0.7559466140838738 OR c7 = 287496496; SET SESSION optimizer_switch = 'default'; -- Buggy execution plan (notice "Intersect rows sorted by row ID") SELECT 'Buggy execution plan (notice Intersect):' AS ''; EXPLAIN SELECT * FROM t WHERE c2 = 0.7559466140838738 OR c7 = 287496496;