MySQL Bugs: #24766: MySQL not able to detect an impossible query

Bug #24766	MySQL not able to detect an impossible query
Submitted:	2 Dec 2006 0:02	Modified:	4 Dec 2006 13:07
Reporter:	Regi Guardialao	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	Linux (Linux (FC5))
Assigned to:		CPU Architecture:	Any

Description:
MySQL unable to detect an impossible where from a query when the other value is a number and the other is a string.

How to repeat:
Table:

CREATE TABLE test1 (
  ID mediumint(8) unsigned NOT NULL auto_increment,
  test smallint(6) NOT NULL default '0',
  PRIMARY KEY  (ID),
  KEY test (test)
)

Query (test1 contains only a few rows):

Explain Select * from test1 where test > 1 and test = ''

Result: Impossible WHERE noticed after reading const table

While Query:
Explain Select * from test1 where test > 1 and test = 0

Results in:
Impossible WHERE

When test1 contains a million rows, the query:
Explain Select * from test1 where test > 1 and test = ''

Results in: Using WHERE.

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with 5.0.32-BK on Linux:

mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `ID` mediumint(8) unsigned NOT NULL auto_increment,
  `test` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  KEY `test` (`test`)
) ENGINE=MyISAM AUTO_INCREMENT=1572865 DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> show table status like 'test1'\G
*************************** 1. row ***************************
           Name: test1
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 1572864
 Avg_row_length: 7
    Data_length: 11010048
Max_data_length: 1970324836974591
   Index_length: 30999552
      Data_free: 0
 Auto_increment: 1572865
    Create_time: 2006-12-03 10:28:41
    Update_time: 2006-12-03 10:30:56
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> Explain Select * from test1 where test > 1 and test = ''\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.01 sec)

mysql> Explain Select * from test1 where test > 1 and test = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.32-debug |
+--------------+
1 row in set (0.00 sec)

How exactly you got that "Using WHERE"?

Sorry I gave you a bad test case. I was quite in a hurry last time.
Anyway, my point was that the other query was able to detect an impossible
query while the other needed to read the constant table. At any case, it should
have been an 'impossible where' right away.

Here is the actual table where we discovered the problem of 'Using Where'
Hope you can recreate it.
Thank you very much for you very fast response.
More power to all of you.

CREATE TABLE `campaigndetails` (
  `campaigndetailid` int(9) unsigned NOT NULL auto_increment,
  `targetdetailid` int(9) unsigned NOT NULL default '0',
  `campaignid` int(9) unsigned NOT NULL default '0',
  `eventid` int(9) unsigned NOT NULL default '0',
  PRIMARY KEY  (`campaigndetailid`),
  KEY `campaignid` (`campaignid`),
  KEY `targetdetailid` (`targetdetailid`),
  KEY `eventid` (`eventid`)
) ENGINE=MyISAM AUTO_INCREMENT=9391989 DEFAULT CHARSET=latin1

show table status like 'campaigndetails'\G;
*************************** 1. row ***************************
           Name: campaigndetails
         Engine: MyISAM
        Version: 9
     Row_format: Fixed
           Rows: 4790857
 Avg_row_length: 17
    Data_length: 81444569
Max_data_length: 73014444031
   Index_length: 158735360
      Data_free: 0
 Auto_increment: 9391990
    Create_time: 2006-05-04 16:31:50
    Update_time: 2006-12-04 04:57:47
     Check_time: 2006-12-04 03:25:05
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:

explain select * from campaigndetails where eventid > 0 and eventid = ''\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: campaigndetails
         type: ref
possible_keys: eventid
          key: eventid
      key_len: 4
          ref: const
         rows: 2197412
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Sorry again. Forgot to add:

show index from campaigndetails\G
*************************** 1. row ***************************
       Table: campaigndetails
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: campaigndetailid
   Collation: A
 Cardinality: 4790859
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: campaigndetails
  Non_unique: 1
    Key_name: campaignid
Seq_in_index: 1
 Column_name: campaignid
   Collation: A
 Cardinality: 2211
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 3. row ***************************
       Table: campaigndetails
  Non_unique: 1
    Key_name: targetdetailid
Seq_in_index: 1
 Column_name: targetdetailid
   Collation: A
 Cardinality: 4790859
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 4. row ***************************
       Table: campaigndetails
  Non_unique: 1
    Key_name: eventid
Seq_in_index: 1
 Column_name: eventid
   Collation: A
 Cardinality: 2395429
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
4 rows in set (0.00 sec)

Thanks Again!