MySQL Bugs: #26017: LEFT JOIN does not select's case insensitive

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

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

test case

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

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"

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

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.

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.

The fix has been pushed to 5.0.36 and 5.1.16-beta.

Noted in 5.0.36, 5.1.16 changelogs.

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