Bug #118909 Inconsistent evaluation of WHERE predicate between table-scan and covering-index paths; result set changes with projecti
Submitted: 28 Aug 11:04 Modified: 28 Aug 12:26
Reporter: Alice Alice Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.41 OS:Linux
Assigned to: CPU Architecture:x86

[28 Aug 11:04] Alice Alice 
Description:
I’m going to modify the query to better illustrate the issue, as shown below. Meanwhile, I executed the second statement on two MySQL 8.0.41 instances and got different results: one machine returned an empty set, while the other returned 7 rows. I’m sure the MD5 checksums of the two MySQL configurations are identical, but I don’t know how to further investigate the differences. Could you tell me how to proceed?
mysql> SELECT /*+ NO_INDEX(t0) */ ALL
    ->    t0.c5 AS ref5
    -> FROM t0 
    -> WHERE (! ((t0.c5) NOT IN (IF("*}", 0.7192219863751933, 0.26253644614218086))));
Empty set, 2 warnings (0.00 sec)

mysql> SELECT  ALL
    -> t0.c5 AS ref5
    -> FROM t0 FORCE INDEX (i1)
    -> WHERE (! ((t0.c5) NOT IN (IF("*}", 0.7192219863751933, 0.26253644614218086))));
+----------+
| ref5     |
+----------+
| 00000000 |
| 00000000 |
| 00000000 |
| 00000000 |
| 00000000 |
| 00000000 |
| 00000000 |
+----------+
7 rows in set, 9 warnings (0.00 sec)

How to repeat:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `tlp20250827009911` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `tlp20250827009911`;

--
-- Table structure for table `t0`
--

DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` (
  `c0` tinyint(235) unsigned zerofill /*!50606 STORAGE DISK */ DEFAULT NULL,
  `c1` mediumint /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL,
  `c2` decimal(10,0) unsigned zerofill NOT NULL /*!50606 STORAGE MEMORY */,
  `c3` varchar(500) DEFAULT NULL COMMENT 'asdf',
  `c4` varchar(500) /*!50606 STORAGE DISK */ DEFAULT NULL COMMENT 'asdf',
  `c5` mediumint(8) unsigned zerofill /*!50606 STORAGE DISK */ DEFAULT NULL COMMENT 'asdf',
  `c6` float DEFAULT NULL,
  `c7` varchar(500) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL,
  `c9` float DEFAULT NULL COMMENT 'asdf',
  PRIMARY KEY (`c2`),
  UNIQUE KEY `i6` (`c2`),
  UNIQUE KEY `i0` (`c0`,`c1`,`c6`),
  UNIQUE KEY `i1` (`c5`,`c3` DESC,`c9`,`c0`,`c1`),
  UNIQUE KEY `i8` (`c6`,`c7`) USING BTREE,
  UNIQUE KEY `i2` (`c6`,`c9`,`c2`,`c4`(4),`c3`(3),`c0` DESC,`c1` DESC) USING BTREE,
  UNIQUE KEY `i5` (`c3`(4) DESC,`c4` DESC,`c9`,`c7`(3),`c6` DESC,`c0`,`c5`),
  UNIQUE KEY `i7` (`c4`,`c0`,`c5`,`c2` DESC,`c3`(4)),
  KEY `i3` (`c9`),
  KEY `i4` (`c9`,`c5`,`c1`,`c4`(3),`c6` DESC) USING BTREE,
  KEY `i57` (`c5`,`c6`,`c0`) USING BTREE
) ENGINE=Dstore DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t0`
--

LOCK TABLES `t0` WRITE;
/*!40000 ALTER TABLE `t0` DISABLE KEYS */;
INSERT INTO `t0` VALUES (0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,-19122,1346995316,NULL,'-1429912105',00000000,NULL,'',0.355662),(0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000255,-87,0524899866,'DE','833257720',00016547,NULL,'-365803936',0.671865),(0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000255,-8388608,0010459196,'EVປ','',00000000,1355340000,NULL,0.161569),(0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000255,-8388608,1266149129,'>vqez','',00000000,0.866822,NULL,0.62843),(NULL,-8388608,1314089236,']tw',NULL,00000117,0.583815,NULL,0.261962),(0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000094,-8388608,0188601801,'','1302294887',00000000,0.72628,NULL,0.869459),(NULL,NULL,0167450852,NULL,NULL,NULL,NULL,NULL,NULL),(NULL,8388607,2041909108,NULL,NULL,NULL,NULL,NULL,NULL),(0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000255,8388607,0209221684,'49GFS5Mr','0*',00018694,0.615255,'827484688',NULL),(0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,-17747,0531677929,'866488885','P}',00000000,0.213991,'tr',NULL),(0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,-8388608,1848409012,'188601801','<_鈵<X',00000000,0.734422,NULL,NULL),(NULL,NULL,0711055758,'뱗~>qB0낵','D_*',00000000,0.658023,'',0.619615),(NULL,42,2081684976,'',NULL,NULL,0.228763,NULL,NULL),(NULL,-21994,0305950228,NULL,NULL,NULL,NULL,'',NULL),(NULL,-104,0403233624,NULL,NULL,NULL,NULL,'539351677',NULL),(0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,NULL,1794266920,'屦Q','0.4477769220225428',00000052,0.494029,'~BÍ*>ֺ',0.27641),(0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000093,NULL,1100971039,'&fXT','',16777215,-1156610000,'',0.47552),(0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000255,NULL,0229697451,'qy7v컥P','4h璣D+i%v',00000015,0.741278,'-2035830262',0.249427),(0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,8388607,0220430973,NULL,'',NULL,0.964645,NULL,0.970386),(0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,-8388608,2135690977,NULL,'',NULL,0.535145,NULL,0.675675),(0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,8388607,1800340362,NULL,'',NULL,NULL,NULL,0.608139),(NULL,-8388608,1302294887,'ed?#&#+>','',NULL,NULL,'',NULL),(NULL,63,0000000001,'','',NULL,NULL,'	i콄J',NULL),(NULL,8388607,0098195385,'MF2','',NULL,NULL,'',NULL),(NULL,8388607,1355336946,'?t','闿B',NULL,NULL,'-310746992',NULL),(0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,NULL,0000000000,NULL,NULL,NULL,NULL,NULL,NULL);
/*!40000 ALTER TABLE `t0` ENABLE KEYS */;
UNLOCK TABLES;

SELECT /*+ NO_INDEX(t0) */ ALL
   t0.c5 AS ref5
FROM t0 
WHERE (! ((t0.c5) NOT IN (IF("*}", 0.7192219863751933, 0.26253644614218086))));

SELECT  ALL
t0.c5 AS ref5
FROM t0  FORCE INDEX (i1)
WHERE (! ((t0.c5) NOT IN (IF("*}", 0.7192219863751933, 0.26253644614218086))));
[28 Aug 12:26] Alice Alice 
"By using ANALYZE TABLE t0, we resolved the issue of inconsistent results between two machines."