Bug #82021 Difference in range behaviour between range analyzer and query execution engine

• Submitted: 27 Jun 2016 17:46
• Modified: 28 Jun 2016 6:11
• Reporter: Manuel Ung
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.6.31, 5.7.13
• OS: Any
• CPU Architecture: Any

[27 Jun 2016 17:46] Manuel Ung

Description:
There is a difference between how casts are done for the range analysis module and how it is done during query execution.

Therefore, depending on how the optimizer chooses to execute the query, you may get different results.

How to repeat:
create table t1 (a int primary key, b int not null, unique key bonly (b));
let $i= 0;

while ($i < 5000)
{
  --eval insert into t1 values ($i, $i);
  inc $i;
}

explain select a from t1 FORCE INDEX (primary) where b >= '2009.09.23';
select a from t1 FORCE INDEX (primary) where b >= '2009.09.23';

explain select a from t1 FORCE INDEX (bonly) where b >= '2009.09.23';
select a from t1 FORCE INDEX (bonly) where b >= '2009.09.23';

drop table t1;

Suggested fix:
Make the casting behaviour consistent.

[28 Jun 2016 6:11] MySQL Verification Team

Hello Manuel Ung,

Thank you for the report and test case.

Thanks,
Umesh