MySQL Bugs: #26550: LEFT JOIN conditions sometimes case sensitive

Bug #26550	LEFT JOIN conditions sometimes case sensitive
Submitted:	22 Feb 2007 0:40	Modified:	22 Feb 2007 2:55
Reporter:	Franz Hänel	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.33	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Under certain circumstances the conditional expression for a LEFT JOIN can be case sensitive. That is, 'EUR'='eur' can suddenly fail. The behavior is a bit random. Small changes to the involved tables, back and forth, can make the behavior go away. I have also only been able to reproduce the behavior using the MyISAM and the Memory engines. I couldn't reproduce it using InnoDB. Also, using INNER JOIN or using functions inside the expression also causes the behavior to go away.

And in case you're interested:

SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci | 
| collation_database   | latin1_swedish_ci | 
| collation_server     | latin1_swedish_ci | 
+----------------------+-------------------+

How to repeat:
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `t1` (`currency1` char(3) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES('eur');
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `t2` (`currency2` char(3) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t2 VALUES('EUR');
Query OK, 1 row affected (0.00 sec)

Here suddenly 'eur'!='EUR'

mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.currency1=t2.currency2) WHERE currency1='eur';
+-----------+-----------+
| currency1 | currency2 |
+-----------+-----------+
| eur       | NULL      | 
+-----------+-----------+
1 row in set (0.00 sec)

Using INNER JOIN gives us expected behavior

mysql> SELECT * FROM t1 INNER JOIN t2 ON (t1.currency1=t2.currency2) WHERE currency1='eur';
+-----------+-----------+
| currency1 | currency2 |
+-----------+-----------+
| eur       | EUR       | 
+-----------+-----------+
1 row in set (0.00 sec)

UPPER('eur')='EUR' of course.

mysql> SELECT * FROM t1 LEFT JOIN t2 ON (UPPER(t1.currency1)=t2.currency2) WHERE currency1='eur';
+-----------+-----------+
| currency1 | currency2 |
+-----------+-----------+
| eur       | EUR       | 
+-----------+-----------+
1 row in set (0.00 sec)

'eur'=UPPER('EUR') - This is a bit more confusing.

mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.currency1=UPPER(t2.currency2)) WHERE currency1='eur';
+-----------+-----------+
| currency1 | currency2 |
+-----------+-----------+
| eur       | EUR       | 
+-----------+-----------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 ENGINE=InnoDB;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

Using InnoDB it works as expected.

mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.currency1=t2.currency2) WHERE currency1='eur';
+-----------+-----------+
| currency1 | currency2 |
+-----------+-----------+
| eur       | EUR       | 
+-----------+-----------+
1 row in set (0.01 sec)

Suggested fix:
Find the case sensitive comparison that is supposed to be case insensitive.

Dropping the WHERE expression also makes it work

mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.currency1=t2.currency2);
+-----------+-----------+
| currency1 | currency2 |
+-----------+-----------+
| eur       | EUR       | 
+-----------+-----------+
1 row in set (0.00 sec)

Thank you for the bug report. Duplicate as Paul commented and already fixed:

[miguel@skybr 5.0]$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.36-debug Source distribution

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

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `t1` (`currency1` char(3) NOT NULL) ENGINE=MyISAM DEFAULT
    -> CHARSET=latin1;
Query OK, 0 rows affected (0.38 sec)

mysql> INSERT INTO t1 VALUES('eur');
Query OK, 1 row affected (0.01 sec)

mysql> CREATE TABLE `t2` (`currency2` char(3) NOT NULL) ENGINE=MyISAM DEFAULT
    -> CHARSET=latin1;
Query OK, 0 rows affected (0.49 sec)

mysql> INSERT INTO t2 VALUES('EUR');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.currency1=t2.currency2) WHERE
    -> currency1='eur';
+-----------+-----------+
| currency1 | currency2 |
+-----------+-----------+
| eur       | EUR       |
+-----------+-----------+
1 row in set (0.42 sec)

mysql>