Bug #17152 Wrong result with BINARY comparison on aliased column
Submitted: 6 Feb 2006 12:09 Modified: 17 Apr 2006 14:17
Reporter: Raimund Jacob Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.1[89], 5.1.7 OS:Linux (Debian GNU/Linux)
Assigned to: Georgi Kodinov CPU Architecture:Any

[6 Feb 2006 12:09] Raimund Jacob
Description:
Create the tables below and find that a BINARY comparison works as a simple select statement but fails when aliasing the table in the statement. Two semantically equivalent statements return different results.

I did not come up with a shorter way to reproduce the problem. It seems that it needs both tables and the foo2 table needs to be filled with some values. the DDL is done using DbVisualizer (JDBC) but the offending statement fails in the CLI, too.

Everything takes place in a database that has utf-8 as the default charset.

How to repeat:
/* 5.0.18 from source, jdbc connect  */
drop table if exists foo2;
drop table if exists foo1;

CREATE TABLE `foo1` (
  `oid` bigint(20) NOT NULL,
   PRIMARY KEY  (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `foo2` (
  `oid` bigint(20) NOT NULL,
  `keycol` varchar(128) NOT NULL,
  `valcol` text NOT NULL,
  PRIMARY KEY  (`oid`,`keycol`),
  KEY `idx_foo_keycol_valcol` (`keycol`,`valcol`(200)),
  CONSTRAINT `foo_fk` FOREIGN KEY (`oid`) REFERENCES `foo1` (`oid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/* fill table with some values, need more than the last row */
insert into foo1 values (1);
insert into foo2 values (1, 'bar', 'vbar');
insert into foo2 values (1, 'BAR2', 'VBAR');
insert into foo2 values (1, 'bar_bar', 'bibi');
insert into foo2 values (1, 'customer_over', '1');

/* Works as expected - ci collation: */
select * from foo2 where keycol = 'customer_over';
/* Works as expected - case sensitive: */
select * from foo2 where BINARY keycol = 'customer_over';
/* Still works as expected: Returns 1 */
SELECT DISTINCT p0.oid FROM foo2 p0  WHERE  p0.keycol = 'customer_over';
/* Bang: Empty result set, above was expected: */
SELECT DISTINCT p0.oid FROM foo2 p0  WHERE  BINARY p0.keycol = 'customer_over';
[6 Feb 2006 12:20] Valeriy Kravchuk
Thank you for a bug report. Verified just as described on 5.0.19-BK (ChangeSet@1.2033, 2006-02-05 23:06:08+01:00):

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.19

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `foo1` (
    ->   `oid` bigint(20) NOT NULL,
    ->    PRIMARY KEY  (`oid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE `foo2` (
    ->   `oid` bigint(20) NOT NULL,
    ->   `keycol` varchar(128) NOT NULL,
    ->   `valcol` text NOT NULL,
    ->   PRIMARY KEY  (`oid`,`keycol`),
    ->   KEY `idx_foo_keycol_valcol` (`keycol`,`valcol`(200)),
    ->   CONSTRAINT `foo_fk` FOREIGN KEY (`oid`) REFERENCES `foo1` (`oid`) ON DELETE
    -> CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.03 sec)

mysql> insert into foo1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo2 values (1, 'bar', 'vbar');
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo2 values (1, 'BAR2', 'VBAR');
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo2 values (1, 'bar_bar', 'bibi');
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo2 values (1, 'customer_over', '1');
Query OK, 1 row affected (0.01 sec)

mysql> select * from foo2 where keycol = 'customer_over';
+-----+---------------+--------+
| oid | keycol        | valcol |
+-----+---------------+--------+
|   1 | customer_over | 1      |
+-----+---------------+--------+
1 row in set (0.03 sec)

mysql> select * from foo2 where BINARY keycol = 'customer_over';
+-----+---------------+--------+
| oid | keycol        | valcol |
+-----+---------------+--------+
E|   1 | customer_over | 1      |
+-----+---------------+--------+
1 row in set (0.01 sec)

mysql> SELECT DISTINCT p0.oid FROM foo2 p0  WHERE  p0.keycol = 'customer_over';
+-----+
| oid |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

mysql> /* Bang: Empty result set, above was expected: */
mysql> SELECT DISTINCT p0.oid FROM foo2 p0  WHERE  BINARY p0.keycol =
    -> 'customer_over';
Empty set (0.01 sec)

mysql> SELECT p0.oid FROM foo2 p0  WHERE  BINARY p0.keycol = 'customer_over';
+-----+
| oid |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

Works OK in 4.1.19-BK, by the way.
[27 Mar 2006 14:08] Raimund Jacob
I just wanted to note that i am still very interested in a solution to this bug. It's still present in 5.0.19-standard and in 5.1.7-beta (both binary downloaded from mysql, amd64). I am willing to experiment.
[17 Apr 2006 13:47] Bugs System
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/5011
[17 Apr 2006 14:17] Georgi Kodinov
Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

I have tried the bug in version 5.0.21-debug. The bug appears fixed in it. I have added a test case for the bug.