Bug #118905 Inconsistent evaluation of WHERE predicate between table-scan and covering-index paths; result set changes with projecti
Submitted: 28 Aug 6:47 Modified: 28 Aug 10:44
Reporter: Alice Alice Email Updates:
Status: Not a Bug 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 6:47] Alice Alice 
Description:
mysql> SELECT t0.c2 AS ref2, t0.c3 AS ref3, t0.c4 AS ref4, t0.c5 AS ref5 FROM t0 WHERE (! ((t0.c5) NOT IN (IF("*}", 0.7192219863751933, 0.26253644614218086))));
Empty set, 7 warnings (0.00 sec)

mysql> SELECT t0.c5 AS ref5 FROM t0 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;

mysql> SELECT t0.c2 AS ref2, t0.c3 AS ref3, t0.c4 AS ref4, t0.c5 AS ref5 FROM t0 WHERE (! ((t0.c5) NOT IN (IF("*}", 0.7192219863751933, 0.26253644614218086))));
Empty set, 7 warnings (0.00 sec)

mysql> SELECT t0.c5 AS ref5 FROM t0 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)
[28 Aug 7:53] Alice Alice 
Double-check
[28 Aug 7:53] Alice Alice 
double check
[28 Aug 10:44] Alice Alice 
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?
SELECT /+ NO_INDEX(t0) */ ALL
t0.c0 AS ref0, t0.c1 AS ref1, t0.c2 AS ref2, t0.c3 AS ref3,
t0.c4 AS ref4, t0.c5 AS ref5, t0.c6 AS ref6, t0.c7 AS ref7, t0.c9 AS ref8
FROM t0
WHERE NOT (t0.c5 NOT IN (IF('}', 0.7192219863751933, 0.26253644614218086)));

SELECT ALL
t0.c0 AS ref0, t0.c1 AS ref1, t0.c2 AS ref2, t0.c3 AS ref3,
t0.c4 AS ref4, t0.c5 AS ref5, t0.c6 AS ref6, t0.c7 AS ref7, t0.c9 AS ref8
FROM t0 FORCE INDEX (i1)
WHERE NOT (t0.c5 NOT IN (IF('}', 0.7192219863751933, 0.26253644614218086)));