Bug #118833 Two queries that use the where clause with opposite semantics return the same result.

• Submitted: 14 Aug 2:43
• Modified: 19 Aug 0:32
• Reporter: Alice Alice
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any

[14 Aug 2:43] Alice Alice

Description:
Two queries that use the where clause with opposite semantics return the same result.

mysql> select (((+ (t0.c4))) = (0.025838568995426914)) && ((CAST(0.19401801347016734 AS SIGNED)) LIKE (COALESCE(t0.c4, 1087206934))) from t0;
+------------------------------------------------------------------------------------------------------------------------+
| (((+ (t0.c4))) = (0.025838568995426914)) && ((CAST(0.19401801347016734 AS SIGNED)) LIKE (COALESCE(t0.c4, 1087206934))) |
+------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                      0 |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select ! (((+ (t0.c4))) = (0.025838568995426914)) && ((CAST(0.19401801347016734 AS SIGNED)) LIKE (COALESCE(t0.c4, 1087206934))) from t0;
+--------------------------------------------------------------------------------------------------------------------------+
| ! (((+ (t0.c4))) = (0.025838568995426914)) && ((CAST(0.19401801347016734 AS SIGNED)) LIKE (COALESCE(t0.c4, 1087206934))) |
+--------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                        1 |
+--------------------------------------------------------------------------------------------------------------------------+

mysql> SELECT t0.c0 AS ref6, t0.c1 AS ref7, t0.c2 AS ref8, t0.c3 AS ref9, t0.c4 AS ref10, t0.c5 AS ref11 FROM t0 WHERE (((+ (t0.c4))) = (0.025838568995426914)) && ((CAST(0.19401801347016734 AS SIGNED)) LIKE (COALESCE(t0.c4, 1087206934)));
+------+------+------+----------+-------+-------+
| ref6 | ref7 | ref8 | ref9     | ref10 | ref11 |
+------+------+------+----------+-------+-------+
| NULL | NULL | NULL | 0.967586 |     0 |  NULL |
+------+------+------+----------+-------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT t0.c0 AS ref6, t0.c1 AS ref7, t0.c2 AS ref8, t0.c3 AS ref9, t0.c4 AS ref10, t0.c5 AS ref11 FROM t0 WHERE (!(((+ (t0.c4))) = (0.025838568995426914)) && ((CAST(0.19401801347016734 AS SIGNED)) LIKE (COALESCE(t0.c4, 1087206934))));
+------+------+------+----------+-------+-------+
| ref6 | ref7 | ref8 | ref9     | ref10 | ref11 |
+------+------+------+----------+-------+-------+
| NULL | NULL | NULL | 0.967586 |     0 |  NULL |
+------+------+------+----------+-------+-------+
1 row in set, 2 warnings (0.00 sec)

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

USE `tlp202505273`;

CREATE TABLE `t0` (
  `c0` varchar(500) /*!50606 STORAGE MEMORY */ DEFAULT NULL,
  `c1` varchar(500) /*!50606 STORAGE MEMORY */ DEFAULT NULL COMMENT 'asdf',
  `c2` double /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL COMMENT 'asdf',
  `c3` float /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL COMMENT 'asdf',
  `c4` decimal(10,0) DEFAULT NULL,
  `c5` float DEFAULT NULL,
  KEY `i0` (`c4`,`c5`,`c2`,`c1`(4) DESC),
  KEY `i1` (`c1` DESC,`c0`(2),`c4` DESC,`c5`,`c3`) /*!80000 INVISIBLE */,
  KEY `i2` (`c5`) USING BTREE /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t0` VALUES (NULL,NULL,NULL,0.967586,0,NULL);

SELECT t0.c0 AS ref6, t0.c1 AS ref7, t0.c2 AS ref8, t0.c3 AS ref9, t0.c4 AS ref10, t0.c5 AS ref11 FROM t0 WHERE (((+ (t0.c4))) = (0.025838568995426914)) && ((CAST(0.19401801347016734 AS SIGNED)) LIKE (COALESCE(t0.c4, 1087206934)));

SELECT t0.c0 AS ref6, t0.c1 AS ref7, t0.c2 AS ref8, t0.c3 AS ref9, t0.c4 AS ref10, t0.c5 AS ref11 FROM t0 WHERE (!(((+ (t0.c4))) = (0.025838568995426914)) && ((CAST(0.19401801347016734 AS SIGNED)) LIKE (COALESCE(t0.c4, 1087206934))));

[19 Aug 0:28] MySQL Verification Team

mysql> SELECT t0.c0 AS ref6, t0.c1 AS ref7, t0.c2 AS ref8, t0.c3 AS ref9, t0.c4 AS ref10, t0.c5 AS ref11 FROM t0 WHERE (((+ (t0.c4))) = (0.025838568995426914)) && ((CAST(0.19401801347016734 AS SIGNED)) LIKE (COALESCE(t0.c4, 1087206934)));
+------+------+------+----------+-------+-------+
| ref6 | ref7 | ref8 | ref9     | ref10 | ref11 |
+------+------+------+----------+-------+-------+
| NULL | NULL | NULL | 0.967586 |     0 |  NULL |
+------+------+------+----------+-------+-------+
1 row in set, 1 warning (0.000 sec)

mysql> 
mysql> SELECT t0.c0 AS ref6, t0.c1 AS ref7, t0.c2 AS ref8, t0.c3 AS ref9, t0.c4 AS ref10, t0.c5 AS ref11 FROM t0 WHERE (!(((+ (t0.c4))) = (0.025838568995426914)) && ((CAST(0.19401801347016734 AS SIGNED)) LIKE (COALESCE(t0.c4, 1087206934))));
+------+------+------+----------+-------+-------+
| ref6 | ref7 | ref8 | ref9     | ref10 | ref11 |
+------+------+------+----------+-------+-------+
| NULL | NULL | NULL | 0.967586 |     0 |  NULL |
+------+------+------+----------+-------+-------+
1 row in set, 2 warnings (0.001 sec)

[19 Aug 0:32] MySQL Verification Team

First query should Returns rows where t0.c4 == 0.025838568995426914 and 0.19401801347016734 LIKE t0.c4

mysql> select 0.19401801347016734 LIKE t0.c4 from t0;
+--------------------------------+
| 0.19401801347016734 LIKE t0.c4 |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.001 sec)

mysql> select * from t0 where 0.19401801347016734 LIKE t0.c4;
Empty set (0.000 sec)

second quiery should returns rows where not both conditions are true — in other words, if either the first or the second condition is not true. So they should not return same result.

Note that this syntax is deprecated:
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                            |
+---------+------+------------------------------------------------------------------------------------+
| Warning | 1287 | '!' is deprecated and will be removed in a future release. Please use NOT instead  |
| Warning | 1287 | '&&' is deprecated and will be removed in a future release. Please use AND instead |
+---------+------+------------------------------------------------------------------------------------+

in any way bug is verified.