Description:
MySQL 8.0.42 incorrectly evaluates the condition COALESCE(t2.c0, t2.c0) BETWEEN 1 AND BIT_COUNT(NULL) when the upper bound is NULL, causing it to return unexpected rows instead of 0 rows. This is a critical data integrity bug affecting query results.
How to repeat:
DROP DATABASE IF EXISTS database3;
CREATE DATABASE database3;
USE database3;
SET SESSION myisam_sort_buffer_size = 2096219124106939755;
SET SESSION parser_max_mem_size = 9118147316543418437;
CREATE TABLE `t2` (
`c0` float /*!50606 STORAGE MEMORY */ DEFAULT NULL COMMENT 'asdf',
UNIQUE KEY `c0` (`c0`),
KEY `i1` (`c0`),
KEY `i0` ((cast(cast((case `c0` when `c0` then `c0` else -(828275160) end) as signed) as signed))),
KEY `i2` (((((case `c0` when 1880093134 then 0.5125572011614843 else `c0` end) is not null) <> greatest(1300862580,2133614360))),(cast(coalesce(`c0`,`c0`) as signed)))
);
INSERT INTO t2(c0) VALUES(NULL),(0.1709602634007511),(0.8805633219207256),(0.9528465444826707),( -786913111);
SELECT t2.c0 AS ref0 FROM t2;
+------------+
| ref0 |
+------------+
| NULL |
| -786913000 |
| 0.17096 |
| 0.880563 |
| 0.952847 |
+------------+
5 rows in set (0.00 sec)
SELECT ALL t2.c0 AS ref0 FROM t2 WHERE (COALESCE(t2.c0, t2.c0) BETWEEN 1 AND BIT_COUNT(NULL));
Empty set (0.00 sec)
SELECT ALL t2.c0 AS ref0 FROM t2 WHERE (!(COALESCE(t2.c0, t2.c0) BETWEEN 1 AND BIT_COUNT(NULL)));
+------------+
| ref0 |
+------------+
| 0.17096 |
| -786913000 |
+------------+
2 rows in set, 1 warning (0.00 sec)
Expecting all non-null rows to be returned, but only two rows were returned.
