Description:
http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html
last paragraph reads:
"Object names may be considered duplicates if their uppercase forms are equal according to a binary collation. That is true for names of cursors, conditions, functions, procedures, savepoints, and routine local variables. It is not true for names of columns, constraints, databases, partitions, statements prepared with PREPARE, tables, triggers, users, and user-defined variables."
It's not really clear what "That" in "That is true" refers too.
I mean, the way it reads now to me implies that I can take two identifiers, make them uppercase, and then compare them using a binary collation. If they are equal by that standard I should consider them as duplicates.
So, assuming that it is implied that the identifiers are different according to a binary comparison before taking the upper case form, my interpretation of the text is:
"names of cursors, conditions, functions, procedures, savepoints, and routine local variables" must be case insensitive because "That is true" that they "may be considered duplicates if their uppercase forms are equal according to a binary collation"
"names of columns, constraints, databases, partitions, statements prepared with PREPARE, tables, triggers, users, and user-defined variables" are case sensitive because "It is not true" that "names may be considered duplicates if their uppercase forms are equal according to a binary collation"
#1
Well the docs read that "It is not true" for column names. But if I really do apply this comparison to column names I would not be able to create the table because of duplicate column names.
#2
The docs read that "It is not true" for "constraints" too. However:
- UNIQUE constraints are in the same per-table namespace with indexes. Within one table, I cannot have two UNIQUE constraints (or indexes for that matter) with identifiers that are equal according to a case-insensitive BINARY comparison
- FOREIGN KEY constraints are in their own schema scoped namespace, and within that namespace there can be no two foreign keys with identifiers that are equal according to a case insensitive BINARY comparison.
#3
triggers are interesting in that they are not generically case sensitive. It depends on the case sensitivity of the operating system (file system?) but unlike tables, they are case sensitive regardless of the lower_case_table_names setting.
#4
As far as I can see, names of user variables are case insensitive too
How to repeat:
show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
#1
create table ttt(
c int -- lcase
,C int -- ucase
);
ERROR 1060 (42S21): Duplicate column name 'C'
So upper case form of c and C is C, binary comparison is equal, and MySQL says it's a duplicate - contrary to the text (as I read it)
#2
create table p(id int primary key) engine = innodb;
create table c(id1 int, id2 int) engine = innodb;
alter table c
add foreign key c1(id1) references p(id) -- lcase
,add foreign key C1(id2) references p(id) -- ucase
;
ERROR 1061 (42000): Duplicate key name 'C1'
so, upper case form of c1 and C1 is C1, binary comparision is equal and MySQL says it's a duplicate - contrary to the text (as I read it)
#3
create table t(c int);
create trigger t before insert on t for each row set @a := 1;
now, on windows:
drop trigger T; -- works
now, on Linux:
drop trigger T;
ERROR 1360 (HY000): Trigger does not exist
#4
user variables
set @a:=null, @A:=null;
set @a:=1;
select @A;
+------+
| @A |
+------+
| 1 |
+------+
So, @a and @A really are the same - therefore, not case sensitive.
Suggested fix:
I think that for the docs, two bulleted lists would work better:
"Names of these objects are always case sensitive (within their namespace):
- ....
-
-
"
"Names of these objects are never case sensitive (within their namespace):
- .....
-
-
"
And then a section to explain each object where "it depends"
I guess it might also be good to add something somewhere to explain all the namespaces in the server, because often I think it depends on the namespace whether names are case sensitive or not (not entirely sure this always holds)
