MySQL Bugs: #7098: False negative with both like and equals clauses for the same text column

Bug #7098	False negative with both like and equals clauses for the same text column
Submitted:	8 Dec 2004 10:29	Modified:	23 Feb 2005 3:37
Reporter:	Peter Robinson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10, 5.0.2	OS:	Any (all)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
When running a select with both a like clause and an equals ('=') clause on the same case insensitive text column, under some circumstances no rows are returned even though there should be matching rows.

For instance:
select * from bug_test where city like "%ondon%" and city = "london";

The equals clause should make the like clause redundant.  Either clause returns the same non-empty results set when run on its own, yet explain select... reports an Impossible WHERE.

How to repeat:
# Create the test table with one text column.
use test;
drop table if exists bug_test;
create table bug_test (
  city text
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into bug_test set city="London";
insert into bug_test set city="Paris";

# This select returns one row as expected ...
select * from bug_test where city like "%London%" and city = "London";

# ... as do these two.
select * from bug_test where city like "%london%" and city = "london";
select * from bug_test where city like "%lonDon%" and city = "lonDon";

# But these selects returns nothing ...
select * from bug_test where city like "%london%" and city = "London";
select * from bug_test where city like "%London%" and city = "london";
select * from bug_test where city like "%ondon%" and city = "London";
select * from bug_test where city like "%ondon%" and city = "london";

# ... because mysql thinks we have an 'Impossible WHERE':
explain select * from bug_test where city like "%ondon%" and city = "London"\G

The problem can be demonstrated with with very simple queries:

mysql> SELECT * FROM t1 WHERE city='London';
+--------+
| city   |
+--------+
| London |
+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE city='london';
+--------+
| city   |
+--------+
| London |
+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE city='London' AND city='london';
Empty set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london';
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL |    NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

ChangeSet
  1.2177 05/02/16 21:17:20 igor@rurik.mysql.com +3 -0
  select.result, select.test:
    Added a test case for bug #7098.
  sql_select.cc:
    Fixed bug #7098.
    When a string field was substituted for an equal constant
    the collation of the constant was changed by mistake for 
    the binary collation.

ChangeSet
  1.2177 05/02/16 22:51:32 igor@rurik.mysql.com +2 -0
  item.h:
    Revised the fix for bug #7098.
    Corrected the method Item_string::new_item.
  sql_select.cc:
    Revised the fix for bug #7098.
    Aborted the previous modifications.

I merged the patch into 5.0 as well.

Fixed in 4.1.11

Noted in 4.1.11 changelog.

Noted in 5.0.3 changelog as well.