Bug #2959 UTF8 charset breaks joins with mixed column/string constant

• Submitted: 25 Feb 2004 7:56
• Modified: 2 Mar 2004 23:23
• Reporter: Dean Ellis
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.1.2
• Assigned to: Alexander Barkov
• CPU Architecture: Any

[25 Feb 2004 7:56] Dean Ellis

Description:
When using utf8 charset (with both _ci and _bin collations) joins with a mix of column and string constant join criteria fail.

How to repeat:
CREATE TABLE test ( a varchar(10) ) CHARACTER SET utf8;
INSERT INTO test VALUES ( 'test' );
SELECT a.a, b.a FROM test a, test b WHERE a.a = b.a;
SELECT a.a, b.a FROM test a, test b WHERE a.a = 'test' and b.a = 'test';
SELECT a.a, b.a FROM test a, test b WHERE a.a = b.a and a.a = 'test';
DROP TABLE test;

Suggested fix:
n/a

[2 Mar 2004 12:31] Alexander Keremidarski

I have another test case which might be relevant to this bug:

create table x (a char(255));
insert into x values('b');

select * from x where a = 'b';
+------+
| a |
+------+
| b |
| b |
+------+
2 rows in set (0.00 sec)

select * from x where a = 'b' and a = 'b';

Empty set (0.00 sec)

mysql> select * from x where a = 'b' and a != 'b';
+------+
| a |
+------+
| b |
+------+

Hoewer I get same results even after changing table default character set to latin1 as well as NAMES and alos differrent combinations of both.

[2 Mar 2004 23:23] Alexander Barkov

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html