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

[8 Dec 2004 10:29] Peter Robinson
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
[17 Feb 2005 3:54] Igor Babaev
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)
[18 Feb 2005 1:57] Igor Babaev
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.
[22 Feb 2005 20:21] Sergei Golubchik
Fixed in 4.1.11
[22 Feb 2005 20:42] Paul Dubois
Noted in 4.1.11 changelog.
[23 Feb 2005 3:37] Paul Dubois
Noted in 5.0.3 changelog as well.