Description:
Consider the following statements:
How to repeat:
CREATE TABLE t0(c0 FLOAT UNIQUE) ;
INSERT INTO t0(c0) VALUES (0);
SELECT * FROM t0 WHERE t0.c0 BETWEEN 0 AND '1e500'; -- expected: {0}, actual: {}
Unexpectedly, the SELECT does not fetch any rows. When removing the UNIQUE constraint, the row is fetched, as expected. When negating the predicate, the row is also not fetched, which is expected:
SELECT * FROM t0 WHERE NOT (t0.c0 BETWEEN 0 AND '1e500');
However, a warning is printed in this case:
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '1e500' |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
This bug might be related to https://bugs.mysql.com/bug.php?id=99135. However, I believe that these are different, since in this case, only FLOAT columns are affected; when replacing the FLOAT type by DOUBLE or INT, the query works as expected.
I found this bug based on MySQL 8.0.19.
Description: Consider the following statements: How to repeat: CREATE TABLE t0(c0 FLOAT UNIQUE) ; INSERT INTO t0(c0) VALUES (0); SELECT * FROM t0 WHERE t0.c0 BETWEEN 0 AND '1e500'; -- expected: {0}, actual: {} Unexpectedly, the SELECT does not fetch any rows. When removing the UNIQUE constraint, the row is fetched, as expected. When negating the predicate, the row is also not fetched, which is expected: SELECT * FROM t0 WHERE NOT (t0.c0 BETWEEN 0 AND '1e500'); However, a warning is printed in this case: mysql> show warnings; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '1e500' | +---------+------+-------------------------------------------+ 1 row in set (0.00 sec) This bug might be related to https://bugs.mysql.com/bug.php?id=99135. However, I believe that these are different, since in this case, only FLOAT columns are affected; when replacing the FLOAT type by DOUBLE or INT, the query works as expected. I found this bug based on MySQL 8.0.19.