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:
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:mysql-8.0.18, 8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[4 Mar 2020 8:04] yayun zhou
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 |
[4 Mar 2020 8:18] MySQL Verification Team
Hello yoga yoga,

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

[16 Aug 2020 17:35] Jon Stephens
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.