Bug #828 Case sensitivity in ON clause during JOIN queries
Submitted: 10 Jul 2003 9:53 Modified: 28 Aug 2003 17:48
Reporter: [ name withheld ] Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.0.13 OS:Windows (Windows)
Assigned to: MySQL Verification Team CPU Architecture:Any

[10 Jul 2003 9:53] [ name withheld ] 
Description:
The presentation of a table name (in terms of case-sensitivity)in the FROM clause must match exactly in any reference to it in the ON clause of a JOIN query, or the query will fail.

Since case-sensitivity isn't an issue when referencing a table name in any other query, it probably shouldn't matter in the ON clause either.

How to repeat:
perform any JOIN query and make the tablename reference all uppercase in the FROM clause, and any reference to that same table in lowercase in the ON clause.
(or mix cases. The important thing is to make the FROM and ON clauses table references look different)

Example:
This will fail:
SELECT name FROM webcompany INNER JOIN webcompanyproducts ON (WEBCOMPANY.primarykey = webcompanyproducts.primarykey)

This will succeed:
SELECT name FROM webcompany INNER JOIN webcompanyproducts ON (webcompany.primarykey = webcompanyproducts.primarykey)

Suggested fix:
Not familiar with MySQL's code base, but I'd assume the problem lies in the SQL parser.

I'd guess one would do the same case-insensitive functions against the ON clause that's performed on the FROM clause.
[11 Jul 2003 11:54] [ name withheld ] 
I have found another related bug...

If table names are specified in the SELECT clause, any reference to them in the WHERE/ORDER BY/GROUP BY clauses must match the case character by character or the query will fail.
[12 Jul 2003 4:12] MySQL Verification Team 
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html
[28 Aug 2003 17:28] Michael Widenius 
Hi!

Sorry, but I have to revert Sinisa's patch.
The way MySQL works was intentional and should not be changed (at least for now).

This is documented at:
http://www.mysql.com/doc/en/Name_case_sensitivity.html

The reason for this behaviour is to make it easy to move databases and queries between windows and unix.

We may change this in later MySQL versions, but we can't change behaviour like this in a stable branch like 4.0