MySQL Bugs: #98826: select query with error result with "not in" where

Bug #98826	select query with error result with "not in" where_clause
Submitted:	4 Mar 2020 8:04	Modified:	16 Aug 2020 17:35
Reporter:	yayun zhou	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	mysql-8.0.18, 8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In some cases, we find the select query has the error result with "not in" where_clause.
please see the cases, Thanks!

How to repeat:
drop database if exists testdb123;
create database if not exists testdb123;
create table t2(id int key, id2 int,id3 int);
insert into t2 values(0,0,0);

--error result
select id,id2 from t2 where id not in(-0.1,0.1);
Empty set (0.00 sec)

select id from t2 where id not in(-0.1,0.1);
+----+
| id |
+----+
|  0 |
+----+

--error result
select id2 from t2 where id not in(-0.1,0.1);
Empty set (0.00 sec)

select id2 from t2 where id2 not in(-0.1,0.1);
+------+
| id2  |
+------+
|    0 |
+------+

Hello yoga yoga,

Thank you for the report and test case.
verified as described with 8.0.19 build.

regards,
Umesh

Documented fix as follows in the MySQL 8.0.22 changelog:

    When range values specified in a predicate are not compatible
    with the data type of the column with which the values are
    compared, the range optimizer rounds off the range values and
    assigns certain flags so that it does not exclude rows that
    qualify for the range because of rounding. In the specific query
    that triggered the reported issue, a column named id of type INT
    was tested using id NOT IN (-0.1, 0.1), and the values being
    tested are rounded to integers, with the predicate thus being
    treated as NOT IN (0,0). The optimizer then treats this as the
    intervals id < 0 and 0 < id < 0, but in this case it set a flag to
    a value that indicated that reads should begin following rows
    containing 0 for the value to be compared. Now in such cases,
    the flag is set in such a way that the values that were rounded
    are treated correctly.

    Regression of BUG#80244.

Closed.