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: | |
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
[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.