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: 3 Sep 8:23
Reporter: Alice Alice Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.41, 9.4.0 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."
[1 Sep 10:07] MySQL Verification Team 
Hi,

So we can close this as not a bug as this was a problem with your table?

Thanks
[2 Sep 1:00] Alice Alice 
Sorry, I believe we should not close this issue yet. The inconsistency between results when using the index versus not using the index still exists. We need to analyze and verify why this problem is occurring.
[2 Sep 12:49] MySQL Verification Team 
Apologies, I understood the difference was gone after you did analyze.

I'm unable to reproduce this locally.
[3 Sep 8:22] Alice Alice 
It might be that my description wasn’t clear enough, which made it hard for you to reproduce. I can reproduce this issue on my machine. You can set all indexes to invisible, because I found that /*+ NO_INDEX(t0) */ sometimes doesn’t take effect.

ALTER TABLE t0 ALTER INDEX i5 INVISIBLE;
ALTER TABLE t0 ALTER INDEX i7 INVISIBLE;
ALTER TABLE t0 ALTER INDEX i4 INVISIBLE;
ALTER TABLE t0 ALTER INDEX i57 INVISIBLE;
ALTER TABLE t0 ALTER INDEX i1 INVISIBLE;

Do something like the above to ensure the following statement performs a full table scan:
SELECT /*+ NO_INDEX(t0) / ALL t0.c5 AS ref5 FROM t0 WHERE (! ((t0.c5) NOT IN (IF("}", 0.7192219863751933, 0.26253644614218086))));

Then set the indexes back to visible and force it to use index i1:
SELECT /*+ NO_INDEX(t0) / ALL t0.c5 AS ref5 FROM t0 FORCE INDEX (i1) WHERE (! ((t0.c5) NOT IN (IF("}", 0.7192219863751933, 0.26253644614218086))));

In addition, I need to correct the CREATE TABLE statement: the engine should be InnoDB rather than ENGINE=Dstore. I uploaded the wrong one earlier—my apologies.
[3 Sep 8:23] MySQL Verification Team 
verified with innodb (don't know what's dstore)

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.003 sec)

mysql> 
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, 5 warnings (0.001 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 9.4.0     |
+-----------+
1 row in set (0.000 sec)

mysql>