Bug #100800 wrong result when select int column with range

• Submitted: 10 Sep 2020 12:51
• Modified: 15 Jul 2021 13:12
• Reporter: lou shuai (OCA)
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.21, 8.0.11
• OS: Any
• CPU Architecture: Any

[10 Sep 2020 12:51] lou shuai

Description:
wrong result when select int column with range.

How to repeat:
testcase like below:

```
create table t1(c1 int, index idx1(c1));
insert into t1 values(0), (0), (0);
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
select count(*) from t1;
count(*)
96
select count(*) from t1 where c1 not in (0.1, -0.1);
count(*)
0
drop table t1;
```

[10 Sep 2020 13:02] MySQL Verification Team

Hello!

Thank you for the report and feedback!

Thanks,
Umesh

[15 Jul 2021 13:12] Jon Stephens

Documented fix as follows in the MySQL8.0.27 changelog:

  For a nullable column, when adjacent ranges were rounded off to
  the same value by range optimizer, wrong results were returned.

Closed.

[2 Dec 2021 11:20] Erlend Dahl

Bug#102655 results from SELECT are different when use WHERE or not

was marked as a duplicate.