Bug #13403 using(colName) has become case sensitive in V5

• Submitted: 22 Sep 2005 13:51
• Modified: 22 Sep 2005 13:58
• Reporter: Dave Pullin (Basic Quality Contributor)
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.12-beta-nt-log
• OS: Windows (Windows 2000)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[22 Sep 2005 13:51] Dave Pullin

Description:
as of V5, SELECT .... FROM A JOIN B USING(X) requires the X columns in A and B and the using() to be matched in case.

where as in
SELECT .... FROM A JOIN B on A.X = B.X
X is case insensitive

I cannot find this incompatible change documented, nor does like look like a good idea. (It also stops a number of my SELECTS working - but it isn't a big deal).

Console Log
mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.0.12-beta-nt-log |
+--------------------+
1 row in set (0.00 sec)

mysql> drop table if exists temp,tempUpper,tempLower;
Query OK, 0 rows affected (0.00 sec)

mysql> create table temp ( x int );
Query OK, 0 rows affected (0.01 sec)

mysql> create table tempUpper ( X int );
Query OK, 0 rows affected (0.00 sec)

mysql> create table tempLower ( X int );
Query OK, 0 rows affected (0.00 sec)

mysql> select * from temp join tempUpper using(X);
ERROR 1054 (42S22): Unknown column 'X' in 'from clause'
mysql> select * from temp join tempLower using(X);
ERROR 1054 (42S22): Unknown column 'X' in 'from clause'
mysql> select * from temp join tempUpper on temp.x=tempUpper.x;
Empty set (0.00 sec)

mysql> select * from temp join tempLower on temp.x=tempLower.X;
Empty set (0.00 sec)

How to repeat:
select version();
drop table if exists temp,tempUpper,tempLower;
create table temp ( x int );
create table tempUpper ( X int );
create table tempLower ( X int );
select * from temp join tempUpper using(X);
select * from temp join tempLower using(X);
select * from temp join tempUpper on temp.x=tempUpper.x;
select * from temp join tempLower on temp.x=tempLower.X;

Suggested fix:
make using(x) case insensitive on X (like it was in V4)

[22 Sep 2005 13:58] MySQL Verification Team

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

Additional info:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.14-rc-nt

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

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.14-rc-nt |
+--------------+
1 row in set (0.08 sec)

mysql> drop table if exists temp,tempUpper,tempLower;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> create table temp ( x int );
Query OK, 0 rows affected (0.06 sec)

mysql> create table tempUpper ( X int );
Query OK, 0 rows affected (0.05 sec)

mysql> create table tempLower ( X int );
Query OK, 0 rows affected (0.05 sec)

mysql> select * from temp join tempUpper using(X);
Empty set (0.02 sec)

mysql> select * from temp join tempLower using(X);
Empty set (0.00 sec)

mysql> select * from temp join tempUpper on temp.x=tempUpper.x;
Empty set (0.00 sec)

mysql> select * from temp join tempLower on temp.x=tempLower.X;
Empty set (0.00 sec)

mysql>