Bug #1939 Wrong case sensitivity for table names when in "ANSI" mode
Submitted: 24 Nov 2003 16:08 Modified: 19 May 2009 22:36
Reporter: Troels Arvin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.2 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[24 Nov 2003 16:08] Troels Arvin
Description:
When running MySQL in "ANSI" mode, MySQL's handling of table names (possibly also other identifiers) becomes very non-ISO/ANSI:

create table testtab(id int not null);
mysql> create table testtab(id int not null);
Query OK, 0 rows affected (0.00 sec)
                                                                                                               
mysql> INSERT INTO TESTTAB VALUES(4);
ERROR 1146: Table 'troels.TESTTAB' doesn't exist

According to [1] and [2], table names are to be handled case insensitively (implictly converted to upper case whenever used) as long as they are not delimited.

This error potentially breaks heaps of fine SQL and is very annoying. In my opinion, the ANSI-mode becomes impossible to use in practice because of this. That's why I classify it as Critical. But as very few people use the feature, I gave it "Low" as Priority.

References:

1:
Melton J, Simon A R:
"SQL 1999 - Understanding Relational Language Components"
ISBN 1-55860-456-1
URL: http://books.elsevier.com/uk/mk/uk/subindex.asp?isbn=1558604561

2:
http://dbazine.com/gulutzan5.html

How to repeat:
See description.
[26 Nov 2003 13:35] Sergei Golubchik
I would not classiy it as critical either as in most applications table is often referred to by the same name, I mean case-sensitively "same".

Anyway, this breaks the standard, agree.

You can use --lower-table-names switch to make table names case insenstive. But it will also cause tables name to be creates in lowercase only. Thus old tables with uppercase characters in names will be unaccessible :(

This is the reason why we didn't turn lower-table-names automatically in the ANSI mode.

Unfortunately we can fix it the safe way in the version 5.0 only :(
[30 Mar 2005 22:31] Troels Arvin
I never realized that the bug also existed in non-ansi mode; that changes things a bit (the bug is consistent). I have lowered severity, as the bug is not triggered by the ansi-mode, as I previously thought.

By the way: it doesn't look like v. 5.0 will fix the bug :-(
[3 Nov 2010 10:29] Vlad Safronov
test. ignore.
[27 May 2013 12:03] Justin Jacob
add to my.cnf->[mysqld]

lower_case_table_names=1

it will solve the problem