Bug #118838 A query containing a where subclause returnning true, the query result is empty.

• Submitted: 14 Aug 3:10
• Modified: 18 Aug 15:21
• Reporter: Alice Alice
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[14 Aug 3:10] Alice Alice

Description:
A query containing a where subclause returnning true, the query result is empty.

while (NOT ((t0.c1) >= (t1.c2))) returns 1 actually, the query return an empty result set. 

mysql> SELECT t0.c1,t1.c2,(t0.c1) >= (t1.c2),(NOT ((t0.c1) >= (t1.c2))) FROM t1, t0;
+-------------+-------+--------------------+----------------------------+
| c1          | c2    | (t0.c1) >= (t1.c2) | (NOT ((t0.c1) >= (t1.c2))) |
+-------------+-------+--------------------+----------------------------+
| -1496950000 | 1e500 |                  0 |                          1 |
|    0.485855 | 1e500 |                  0 |                          1 |
+-------------+-------+--------------------+----------------------------+
2 rows in set, 4 warnings (0.00 sec)

mysql> SELECT t0.c1,t1.c2 FROM t1, t0 WHERE (NOT ((t0.c1) >= (t1.c2)));
Empty set, 1 warning (0.00 sec)

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

USE `tlp202506054`;

DROP TABLE IF EXISTS `t0`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t0` (
  `c0` decimal(10,0) DEFAULT NULL,
  `c1` float NOT NULL,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `i1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t0` VALUES (1,-1496950000),(1,0.485855);

DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t1` (
  `c0` float NOT NULL,
  `c1` float /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL COMMENT 'asdf',
  `c2` varchar(500) /*!50606 STORAGE MEMORY */ DEFAULT NULL COMMENT 'asdf',
  PRIMARY KEY (`c0`),
  UNIQUE KEY `i2` (`c2`(3) DESC,`c1`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t1` VALUES (0.833634,0.19342,'1e500');

mysql> SELECT t0.c1,t1.c2,(t0.c1) >= (t1.c2),(NOT ((t0.c1) >= (t1.c2))) FROM t1, t0;
+-------------+-------+--------------------+----------------------------+
| c1          | c2    | (t0.c1) >= (t1.c2) | (NOT ((t0.c1) >= (t1.c2))) |
+-------------+-------+--------------------+----------------------------+
| -1496950000 | 1e500 |                  0 |                          1 |
|    0.485855 | 1e500 |                  0 |                          1 |
+-------------+-------+--------------------+----------------------------+
2 rows in set, 4 warnings (0.00 sec)

mysql> SELECT t0.c1,t1.c2 FROM t1, t0 WHERE (NOT ((t0.c1) >= (t1.c2)));
Empty set, 1 warning (0.00 sec)

[18 Aug 15:21] MySQL Verification Team

Hi,

Please upgrade

mysql> SELECT t0.c1,t1.c2,(t0.c1) >= (t1.c2),(NOT ((t0.c1) >= (t1.c2))) FROM t1, t0;
+-------------+-------+--------------------+----------------------------+
| c1          | c2    | (t0.c1) >= (t1.c2) | (NOT ((t0.c1) >= (t1.c2))) |
+-------------+-------+--------------------+----------------------------+
| -1496950000 | 1e500 |                  0 |                          1 |
|    0.485855 | 1e500 |                  0 |                          1 |
+-------------+-------+--------------------+----------------------------+
2 rows in set, 4 warnings (0.001 sec)

mysql> SELECT t0.c1,t1.c2 FROM t1, t0 WHERE (NOT ((t0.c1) >= (t1.c2)));
+-------------+-------+
| c1          | c2    |
+-------------+-------+
| -1496950000 | 1e500 |
|    0.485855 | 1e500 |
+-------------+-------+
2 rows in set, 4 warnings (0.001 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '1e500' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1e500' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1e500' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1e500' |
+---------+------+-------------------------------------------+
4 rows in set (0.000 sec)