MySQL Bugs: #1736: Inconsistent handling of case for database name in an ON clause in Win32

Bug #1736	Inconsistent handling of case for database name in an ON clause in Win32
Submitted:	2 Nov 2003 17:47	Modified:	13 Dec 2003 5:01
Reporter:	Paul Coldrey	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.0.16-nt	OS:	Windows (Windows XP Pro)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
This one's been hanging around for a long time (all of 3.23 and 4.0 releases, as far as I know), and I ran across it again today and thought I should track it down and send in a report. I didn't check whether it applies to other table types, but I found it with MyISAM.

On Win32, queries seem to cope well with arbitrary captilisation of database, column and table names. However, if a database name is included in the ON clause then it always wants small letters regardless of the actual case in the database name. Hence a database called 'DB2' must be refered to as 'db2' in windows and 'DB2' in linux.... which is a pain for those of use who develop code for both :-)

How to repeat:
CREATE DATABASE DB1;
USE DB1;

CREATE TABLE t1 (
	Id Integer,
	Data VARCHAR(255)
);

INSERT INTO t1 VALUES(1, 'database 1 - 1');
INSERT INTO t1 VALUES(2, 'database 1 - 2');

CREATE DATABASE DB2;
USE DB2;

CREATE TABLE t2 (
	Id Integer,
	Data VARCHAR(255)
);

INSERT INTO t2 VALUES(1, 'database 2 - 1');
INSERT INTO t2 VALUES(2, 'database 2 - 2');

USE db1;

-- This should fail with the complaint: 'Unknown table 'DB2.t2' in on clause'

SELECT t1.*
FROM t1 LEFT JOIN DB2.t2 on t1.Id = DB2.t2.Id
WHERE t2.Id = 2

-- But this should work OK

SELECT t1.*
FROM t1 LEFT JOIN DB2.t2 on t1.Id = db2.t2.Id
WHERE t2.Id = 2

-- Note: In both examples, the first 'DB2' (the one after the LEFT JOIN) 
-- can have either case while the second DB2 must be in smalls
-- regardless of actual case of the database name (ie DB2 or db2)

This also appears to be the case if you specify a column in the selection clause using database.table.column syntax.. eg: 

  SELECT Intranet.Permission.Id FROM.....

will result in "Intranet SQL Error: Unknown table 'Intranet.Permission' in field list" on Win32 but works properly on Linux.

Similarly,

  SELECT intranet.Permission.Id FROM.....

Works on Win32 whether the database is called Intranet or intranet.

This occurs because lower_case_table_names is enabled (by default in Windows).

Thank you

Extending my previous comment: The issue is that the initial reference to the database name (FROM clause) is case insensitive due to lower_case_table_names being enabled.  All subsequent references to that database name resolved via Database.Table are forced to use lowercase.  (Table names remain insensitive.

If you want case to remain consistent across platforms you should disable lower_case_table_names.

Some clarifications here:

When you create the databases and tables, the lower_case_table_names is ON
by default on Windows, then the databases was created with lower case:

E:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.16-max-debug-log

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

mysql> show databases;
+----------+
| Database |
+----------+
| db1      |
| db2      |
| mysql    |
| test     |
+----------+
4 rows in set (0.01 sec)

mysql> show variables like "lower_case_table_names";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | ON    |
+------------------------+-------+
1 row in set (0.00 sec)

now I tested your query with a bug server:

mysql> USE db1;
Database changed
mysql> SELECT t1.*
    -> FROM t1 LEFT JOIN DB2.t2 on t1.Id = DB2.t2.Id
    -> WHERE t2.Id = 2;
ERROR 1109: Unknown table 'DB2.t2' in on clause

mysql> SELECT t1.*
    -> FROM t1 LEFT JOIN DB2.t2 on t1.Id = Db2.t2.Id
    -> WHERE t2.Id = 2;
ERROR 1109: Unknown table 'Db2.t2' in on clause

Notice that the bug consists in to type the query with upper case
and not how were built the database/tables, so IMHO is necessary
to make the server to convert the query or to test the query with
lower case when it is ON. I did a small change in a piece of code
where are qualified the fields and:

mysql> SELECT t1.*
    -> FROM t1 LEFT JOIN DB2.t2 on t1.Id = DB2.t2.Id
    -> WHERE t2.Id = 2;
+------+----------------+
| Id   | Data           |
+------+----------------+
|    2 | database 1 - 2 |
+------+----------------+
1 row in set (0.00 sec)

In another words is pertimited to create the database with upper case
and converted to lower case and not permited to query the server with
upper case. I will send for Sergei the diff files as start point for
a fix of this specify windows issue.

Thanks you for the bug report.

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:

Fix will be in MySQL 4.0.17

Hi
I am trying to update a field of a table with same field of another table, I have done it 3 times and it was successfull. But this time it is showing error.

UPDATE tblproposals SET proposalNumber = tblprojname.prjname WHERE proposalID = tblprojname.prjids 

MySQL said:  
#1109 - Unknown table 'tblprojname' in where clause 

Please any one help me. also you can send me via mail : 
ravikant282 at yahoo dot co dot in
Thanks