-- MySQL Index Merge Bug - MySQL 9.6.0 -- BUG: index_merge returns 67 rows, table scan returns 66 (correct) -- Root: Deduplicate in index merge union fails for overlap row 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,'?TY⵮O\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,'+) }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-3짮J'),(1999691333,NULL,1307716693,'GqqL