Bug #26017 LEFT JOIN does not select's case insensitive
Submitted: 1 Feb 2007 16:14 Modified: 21 Feb 2007 3:36
Reporter: Quis NoName Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.33-log, 5.1 BK OS:FreeBSD (FreeBSD 6.2)
Assigned to: Igor Babaev CPU Architecture:Any
Tags: case, CI, left join, USING, where

[1 Feb 2007 16:14] Quis NoName
Description:
The LEFT JOIN does not work case insensitive.
I`m really bad in explainations see example

How to repeat:
SELECT * FROM table1 LEFT JOIN table2 USING(field) WHERE field='Bla';

table1.field: varchar(16) latin1_swedish_ci PKEY
table2.field: varchar(16) latin1_swedish_ci PKEY
table1.otherfield: varchar(16) latin1_swedish_ci
table2.anotherfield: varchar(16) latin1_swedish_ci

data:
INSERT INTO table1 (field, otherfield) VALUES ('bla', 'blah');
INSERT INTO table2 (field, anotherfield) VALUES ('bla', 'sheep');

Result:
A row with:
field: bla
otherfield: blah
anotherfield: NULL

Suggested fix:
change a strcmp into strcasecmp somewhere I think
[2 Feb 2007 9:37] Sveta Smirnova
test case

Attachment: bug26017.test (application/octet-stream, text), 582 bytes.

[2 Feb 2007 9:39] Sveta Smirnova
Thank you for the report.

Verified as described using last BK sources and attached test.

Version 4.1 outputs correct error "1052: Column 'f1' in where clause is ambiguous"
[2 Feb 2007 16:09] Quis NoName
In the previous MySQL version I was using it worked.
the USING(f1) ensures the field f1 in both tables are equal.

The result I had:
A row with:
field: bla
otherfield: blah
anotherfield: sheep
[8 Feb 2007 11:40] 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/19555

ChangeSet@1.2406, 2007-02-08 03:44:29-08:00, igor@olga.mysql.com +3 -0
  Fixed bug #26017.
  Objects of the class Item_equal contain an auxiliary member
  eval_item of the type cmp_item that is used only for direct 
  evaluation of multiple equalities. Currently a multiple equality
  is evaluated directly only in the cases when the equality holds
  at most for one row in the result set.
  The compare collation of eval_item was determined incorectly.
  It could lead to returning incorrect results for some queries.
[9 Feb 2007 20:51] 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/19641

ChangeSet@1.2406, 2007-02-09 12:54:50-08:00, igor@olga.mysql.com +4 -0
  Fixed bug #26017.
  Objects of the class Item_equal contain an auxiliary member
  eval_item of the type cmp_item that is used only for direct 
  evaluation of multiple equalities. Currently a multiple equality
  is evaluated directly only in the cases when the equality holds
  at most for one row in the result set.
  The compare collation of eval_item was determined incorectly.
  It could lead to returning incorrect results for some queries.
[14 Feb 2007 10:32] Igor Babaev
The fix has been pushed to 5.0.36 and 5.1.16-beta.
[21 Feb 2007 3:36] Paul Dubois
Noted in 5.0.36, 5.1.16 changelogs.

Collation for LEFT JOIN comparisons could be evaluated incorrectly,
leading to improper query results.