Description:
MySQL 8.0.42 incorrectly evaluates the condition NOT (+ (GREATEST(c0, c0))) for float unsigned zerofill columns, causing it to return all rows instead of just rows with c0 = 0. This is a critical logic error affecting query results.
How to repeat:
This also can be reproduced in the latest version of MySQL.
DROP DATABASE IF EXISTS database0;
CREATE DATABASE database0;
USE database0;
SET SESSION query_prealloc_size = 9223372036854775807;
SET GLOBAL optimizer_switch = 'use_invisible_indexes=on,derived_merge=off,index_merge_intersection=off,batched_key_access=off,duplicateweedout=on,firstmatch=on';
CREATE TABLE `t0` (
`c0` float unsigned zerofill NOT NULL /*!50606 STORAGE DISK */ COMMENT 'asdf',
PRIMARY KEY (`c0`),
UNIQUE KEY `c0` (`c0`),
KEY `i0` ((cast(greatest(`c0`,`c0`) as signed)))
);
INSERT INTO t0(c0) VALUES(0),(0.0288292);
SELECT ALL t0.c0 AS ref0 FROM t0;
+--------------+
| ref0 |
+--------------+
| 000000000000 |
| 0000.0288292 |
+--------------+
2 rows in set (0.00 sec)
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (+ (GREATEST(t0.c0, t0.c0)));
+--------------+
| ref0 |
+--------------+
| 0000.0288292 |
+--------------+
1 row in set (0.00 sec)
SELECT t0.c0 AS ref0 FROM t0 WHERE (NOT ((+ (GREATEST(t0.c0, t0.c0)))));
+--------------+
| ref0 |
+--------------+
| 000000000000 |
| 0000.0288292 |
+--------------+
2 rows in set (0.01 sec)
Expected to return row 0, but two rows were returned, which contradicts the previous query result.
