MySQL Bugs: #13311: IN + binary lead to incorrect SELECT results

Bug #13311	IN + binary lead to incorrect SELECT results
Submitted:	19 Sep 2005 0:13	Modified:	20 Sep 2006 15:28
Reporter:	Oleksandr Byelkin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Any (All)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
depends on number elements in IN we get correct/incorrect result:
+ create table t1( firstname char(20), lastname char(20));
+ insert into t1 values ("john","doe"),("John","Doe");
+ select * from t1 where firstname='John' and firstname in ( binary 'john');
+ firstname     lastname
+ john  doe
+ select * from t1 where firstname='john' and firstname in ( binary 'John');
+ firstname     lastname
+ John  Doe
+ select * from t1 where firstname='John' and firstname in ( binary 'john', binary 'john');
+ firstname     lastname
+ select * from t1 where firstname='john' and firstname in ( binary 'John', binary 'John');
+ firstname     lastname
+ select * from t1 where firstname='John' and firstname in ( binary 'john', 'doe');
+ firstname     lastname
+ select * from t1 where firstname='john' and firstname in ( binary 'John', 'Doe');
+ firstname     lastname
+ select * from t1 where firstname='John' and binary 'john' in (firstname);
+ firstname     lastname
+ john  doe
+ select * from t1 where firstname='john' and binary 'John' in (firstname);
+ firstname     lastname
+ John  Doe
+ select * from t1 where firstname='John' and binary 'john' in (firstname,lastname);
+ firstname     lastname
+ select * from t1 where firstname='john' and binary 'John' in (firstname,lastname);
+ firstname     lastname
+ select * from t1 where firstname='John' and firstname in ('john', 'john');
+ firstname     lastname
+ john  doe
+ John  Doe
+ select * from t1 where firstname='John' and firstname in ('john', 'doe');
+ firstname     lastname
+ john  doe
+ John  Doe
+ drop table t1;      

How to repeat:
create table t1( firstname char(20), lastname char(20));
insert into t1 values ("john","doe"),("John","Doe");
select * from t1 where firstname='John' and firstname in ( binary 'john');
select * from t1 where firstname='john' and firstname in ( binary 'John');
select * from t1 where firstname='John' and firstname in ( binary 'john', binary 'john');
select * from t1 where firstname='john' and firstname in ( binary 'John', binary 'John');
select * from t1 where firstname='John' and firstname in ( binary 'john', 'doe');
select * from t1 where firstname='john' and firstname in ( binary 'John', 'Doe');
select * from t1 where firstname='John' and binary 'john' in (firstname);
select * from t1 where firstname='john' and binary 'John' in (firstname);
select * from t1 where firstname='John' and binary 'john' in (firstname,lastname);
select * from t1 where firstname='john' and binary 'John' in (firstname,lastname);

select * from t1 where firstname='John' and firstname in ('john', 'john');
select * from t1 where firstname='John' and firstname in ('john', 'doe');
drop table t1;

Suggested fix:
should be done something like this:
     /*
        As this is binary compassion, mark all fields that they can't be
        transformed. Otherwise we would get into trouble with comparisons
        like:
        WHERE col= 'j' AND col LIKE BINARY 'j'
        which would be transformed to:
        WHERE col= 'j'
      */
      (*a)->transform(&Item::set_no_const_sub, (byte*) 0);
      (*b)->transform(&Item::set_no_const_sub, (byte*) 0);

Verified on 5.0.12-nt - results as described. On 4.1.14 the results are different:

mysql> create table t1( firstname char(20), lastname char(20));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values ("john","doe"),("John","Doe");
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1 where firstname='John' and firstname in ( binary 'john')
;
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| john      | doe      |
+-----------+----------+
1 row in set (0.03 sec)

mysql> select * from t1 where firstname='john' and firstname in ( binary 'John')
;
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| John      | Doe      |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select * from t1 where firstname='John' and firstname in ( binary 'john',
 binary
    -> 'john');
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| john      | doe      |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select * from t1 where firstname='john' and firstname in ( binary 'John',
 binary
    -> 'John');
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| John      | Doe      |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select * from t1 where firstname='John' and firstname in ( binary 'john',

    -> 'doe');
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| john      | doe      |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select * from t1 where firstname='john' and firstname in ( binary 'John',

    -> 'Doe');
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| John      | Doe      |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select * from t1 where firstname='John' and binary 'john' in (firstname);

+-----------+----------+
| firstname | lastname |
+-----------+----------+
| john      | doe      |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select * from t1 where firstname='john' and binary 'John' in (firstname);

+-----------+----------+
| firstname | lastname |
+-----------+----------+
| John      | Doe      |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select * from t1 where firstname='John' and binary 'john' in
    -> (firstname,lastname);
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| john      | doe      |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select * from t1 where firstname='john' and binary 'John' in
    -> (firstname,lastname);
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| John      | Doe      |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select * from t1 where firstname='John' and firstname in ('john', 'john')
;
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| john      | doe      |
| John      | Doe      |
+-----------+----------+
2 rows in set (0.00 sec)

mysql> select * from t1 where firstname='John' and firstname in ('john', 'doe');

+-----------+----------+
| firstname | lastname |
+-----------+----------+
| john      | doe      |
| John      | Doe      |
+-----------+----------+
2 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.01 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9120

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9222

I'm marking this bug as a duplicate of bug #9509. The fix for bug #9509 will address this case (and many others) in more consistent and optimal manner.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11001

ChangeSet@1.2253, 2006-08-29 15:51:38+03:00, gkodinov@macbook.gmz +7 -0
  Bug #13311: IN + binary lead to incorrect SELECT results
   It is not correct to substitute column references with constants 
   in comparisons, IN and BETWEEN if the collations used in the 
   equality comparison and comparison/IN/BETWEEN don't match.
  
   Extended the compare context of an item to contain collation.
   Extended the check for different contexts in const propagation
   to check that collations match.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

This bug's fix is super-seeded by the fix for bug #21698.