MySQL Bugs: #119053: Incorrect filtering for NOT BETWEEN on a FLOAT PRIMARY KEY

Bug #119053	Incorrect filtering for NOT BETWEEN on a FLOAT PRIMARY KEY
Submitted:	22 Sep 9:22	Modified:	23 Sep 2:38
Reporter:	zz z	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	9.4.0 8.4.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The query incorrectly returns an empty set instead of all rows. This bug is triggered by a specific combination of necessary conditions:
1. The predicate is applied to a column of type FLOAT that has an index 
2. The table must contain at least two rows. 
3. boundary overflow: (1E308) significantly exceeds the maximum valid range for the FLOAT data type 

The BETWEEN operator, when given an inverted range (min > max), is defined to always return FALSE. Consequently, the NOT BETWEEN operator with the same inverted range must always return TRUE. Therefore, the WHERE clause c1 NOT BETWEEN 1E308 AND 1 is logically equivalent to WHERE TRUE, and the query should return all rows in the table.

How to repeat:
CREATE TABLE t93 (c1 FLOAT PRIMARY KEY) ;
INSERT INTO t93 (c1) VALUES (-2);
INSERT INTO t93 (c1) VALUES (-3);
SELECT * FROM t93 WHERE (((c1) NOT BETWEEN (1E308) AND (1))) ; -- return null;
SELECT SUM(count) FROM (SELECT ((((c1) NOT BETWEEN (1E308) AND (1)))) IS TRUE AS count FROM t93 ) AS ta_norec; -- return 2;

add version

Hi,

There's something I'm not seeing here?!

This all look ok to me

mysql> SELECT * FROM t93 WHERE (((c1) NOT BETWEEN (1E308) AND (1))) ; 
+----+
| c1 |
+----+
| -3 |
| -2 |
+----+
2 rows in set (0.000 sec)

mysql> SELECT SUM(count) FROM (SELECT ((((c1) NOT BETWEEN (1E308) AND (1)))) IS TRUE AS count FROM t93 ) AS ta_norec;
+------------+
| SUM(count) |
+------------+
|          2 |
+------------+
1 row in set (0.001 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 9.4.0     |
+-----------+
1 row in set (0.001 sec)

mysql>

I'm running on Docker with mysql:9.4.0.
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 9.4.0     |
+-----------+
1 row in set (0.000 sec)

mysql> drop table if exists t93;
Query OK, 0 rows affected (0.016 sec)

mysql> CREATE TABLE t93 (c1 FLOAT PRIMARY KEY) ;
Query OK, 0 rows affected (0.026 sec)

mysql> INSERT INTO t93 (c1) VALUES (-2);
Query OK, 1 row affected (0.004 sec)

mysql> INSERT INTO t93 (c1) VALUES (-3);
Query OK, 1 row affected (0.005 sec)

mysql> SELECT * FROM t93 WHERE (((c1) NOT BETWEEN (1E308) AND (1))) ; -- return null;
Empty set (0.001 sec)

mysql> SELECT SUM(count) FROM (SELECT ((((c1) NOT BETWEEN (1E308) AND (1)))) IS TRUE AS count FROM t93 ) AS ta_norec; -- retu
+------------+
| SUM(count) |
+------------+
|          2 |
+------------+
1 row in set (0.001 sec)

mysql> CREATE TABLE t93 (c1 FLOAT PRIMARY KEY) ;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t93 (c1) VALUES (-2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t93 (c1) VALUES (-3);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t93 WHERE (((c1) NOT BETWEEN (1E308) AND (1))) ; -- return null;
Empty set (0.00 sec)

mysql> SELECT SUM(count) FROM (SELECT ((((c1) NOT BETWEEN (1E308) AND (1)))) IS TRUE AS count FROM t93 ) AS ta_norec; -- return 2;
+------------+
| SUM(count) |
+------------+
|          2 |
+------------+
1 row in set (0.01 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.4.6     |
+-----------+
1 row in set (0.00 sec)

My CPU architecture is x86_64.
mysql> EXPLAIN SELECT * FROM t93 WHERE (((c1) NOT BETWEEN (1E308) AND (1))) ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t93   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.001 sec)

Hi,

With ARM64 it was ok but now tested on x86_64 and it is not the same. Verifying this, thanks for your report

mysql [localhost:9400] {msandbox} (test) > SELECT * FROM t93 WHERE (((c1) NOT BETWEEN (1E308) AND (1))) ; -- return null;
Empty set (0.000 sec)

mysql [localhost:9400] {msandbox} (test) > SELECT SUM(count) FROM (SELECT ((((c1) NOT BETWEEN (1E308) AND (1)))) IS TRUE AS count FROM t93 ) AS ta_norec;
+------------+
| SUM(count) |
+------------+
|          2 |
+------------+
1 row in set (0.000 sec)