Bug #74230 "Impossible WHERE" detection could be smarter

• Submitted: 5 Oct 2014 17:53
• Modified: 5 Oct 2014 18:03
• Reporter: Slava C
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.6.19-ndb-7.3.6
• OS: Any
• CPU Architecture: Any
• Tags: impossible where

[5 Oct 2014 17:53] Slava C

Description:
Optimizer doesn't recognize some of impossible where conditions with unsigned types.

How to repeat:
drop table if exists foo;
create table foo (
  id int unsigned not null primary key
  -- id is unsigned, so numbers less than zero are known to be not possible
) engine=ndb;

explain extended select 1 from foo where id<-1;
-- this is OK, the WHERE clause is impossible, which is confirmed by optimizer:
-- "Impossible WHERE noticed after reading const tables"

explain extended select 1 from foo where id<0;
-- this one is not OK, because WHERE clause is known to be impossible, but optimizer couldn't guess that:
-- "Using where with pushed condition (`database`.`foo`.`id` < 0); Using MRR"

[5 Oct 2014 18:03] Slava C

Similarly, it happens on InnoDB:

explain select 1 from foo where id<0;
-- Using where; Using index