MySQL Bugs: #22166: Falcon: case insensitive table names

Bug #22166	Falcon: case insensitive table names
Submitted:	9 Sep 2006 12:06	Modified:	2 Oct 2007 8:26
Reporter:	Georg Richter
Status:	Verified
Category:	Server: Falcon	Severity:	S3 (Non-critical)
Version:	5.2	OS:	Linux (Linux)
Assigned to:		Target Version:
Triage:	D4 (Minor) / R4 (High) / E4 (High)

Description:
If I have a table named 'T', I can't create a table named 't'.
That's rational behaviour, but not MySQL behaviour.

How to repeat:
/* default engine = myisam */

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

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

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

mysql> drop table T,t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t (s1 int) engine=falcon;
Query OK, 0 rows affected (0.15 sec)

mysql> create table T (s1 int) engine=falcon;
ERROR 1005 (HY000): Can't create table 'jstar18.T' (errno: 156)

This behaviour breaks explicit standard requirements for delimited identifiers:

set sql_mode='ansi_quotes';
create table "t1" (a int) engine = Falcon;
create table "T1" (a int) engine = Falcon;
--> 1005: Can't create table 'test.T1' (errno: 156)

Another test case:

mysql> create table u (s1 int) engine=falcon;
Query OK, 0 rows affected (0.67 sec)

mysql> create table U (s1 int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table U engine=falcon;
ERROR 1025 (HY000): Error on rename of './d15/#sql-64d8_2' to './d15/U' (errno: -2)
mysql> select * from u;
ERROR 1146 (42S02): Table 'd15.u' doesn't exist

My test case above shows that this behaviour results in a direct violation of the SQL
standard.

Sergei is half right.  While in his example Falcon is not standard compliant, it is, in
fact, MyISAM compliant:

    mysql> set sql_mode='ansi_quotes';
    Query OK, 0 rows affected (0.06 sec)

    mysql> create table "t1" (a int) engine = MyISAM;
    Query OK, 0 rows affected (0.09 sec)

    mysql> create table "T1" (a int) engine = MyISAM;
    ERROR 1050 (42S01): Table 't1' already exists

I asked Jim Starkey some questions after reporting this bug
originally on mantis. They are visible in comments above.
It's possible that I have missed or forgotten some replies
(in which case, my apologies). But as far as I know they're
still open questions.

Suppose Falcon table names are case insensitive, InnoDB's are case sensitive.
Is it legal to have an InnoDB table 'T' and a Falcon table 't'?

If so, if I then say SELECT * FROM T, which table do I see?
If not, aren't you imposing Falcon's rules on InnoDB tables?

Suppose Falcon table name is (lower case) Turkish dotless i. Should it then be
impossible to create another Falcon table whose name is (upper case) Turkish
dotless i? If so, that's not currently working.

And I also suggested at the time: "convince Monty". Did it happen?

I supplied an additional test case on September 27:

"
mysql> create table u (s1 int) engine=falcon;
Query OK, 0 rows affected (0.67 sec)

mysql> create table U (s1 int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table U engine=falcon;
ERROR 1025 (HY000): Error on rename of './d15/#sql-64d8_2' to './d15/U' (errno: -2)
mysql> select * from u;
ERROR 1146 (42S02): Table 'd15.u' doesn't exist
"

This isn't an argument about goodness or badness,
but questions need answering, specific cases need
addressing.

Another test case:

mysql> create table t (s1 int) engine=falcon;
Query OK, 0 rows affected (0.01 sec)

mysql> create table T (s1 int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table T engine=falcon;
ERROR 1025 (HY000): Error on rename of './d7/#sql-4f7c_1' to './d7/T' (errno: -2)
mysql> alter table T engine=falcon;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

So now I have a Falcon table named "t" and
another Falcon table named "T".

Another test case:

mysql> create table t (s1 int) engine=falcon;
Query OK, 0 rows affected (0.03 sec)

mysql> create table T (s1 int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table T engine=falcon;
ERROR 1025 (HY000): Error on rename of './d7/#sql-4f7c_1' to './d7/T' (errno: -2)
mysql> alter table t engine=innodb;
ERROR 2013 (HY000): Lost connection to MySQL server during query

On Linux MySQL works currently in a mode that is easiest described as
'database and table names are always quoted'. (Note that this is
independent of how table names are stored on the file system and is a
mode that we need to preserve also for the future).  The current
Falcon version seriosly breaks this mode and is this not compatible
with the handler interface.

It's up to the MySQL laywer to define how table names are to be
compared, not up to the storage engine

Jim Starkey's example for MyISAM is only true for WINDOWS (which
normally runs in a mode 'quites are not significant'; It's not how
MySQL work on most other platforms and also doesn't take into
account the different table name modes that MySQL provides.

Jeffery's comment about InnoDB is only relevant for WINDOWS; On UNIX
innodb follows the MySQL handler interface rules and you can thus
create tables with different cases. It's not acceptable that different
engines interprets the setting of the lower-case-table-name
differently within the same server.  The comment in the manual about
setting lower_case_table_names to 1 if you are using InnoDB is ONLY
relevant if you plan to copy innodb files between windows and
Unix. The manual is wrong to suggest that the end user should always
use this (and as far as I know, very few users does this)

As Peter demonstrated, this is a serious issue in that by doing an
ALTER TABLE you can accidently loose an existing table, crash the
server etc.

In the architecture team Brian, Mikael, Sergei and I are of the
opinion that this needs to be changed. I don't see any reason to
discuss this again as it's self evident what will be the decision
would be if this is brought up.

Jim, please fix this bug ASAP.

The fix for solving the table name issue is, as far as I know, going
to be solved in 6.1 (Talked to Mikael about it today)

Other bugs related to this problem are http://bugs.mysql.com/bug.php?id=23997 and
http://bugs.mysql.com/bug.php?id=27425

As there is no accepted strategy to fix the non-portable table name case problem in the
server, Falcon blocks (and will continue) to block combinations of table names that both
violate the standard, will break the application is the tables are moved across platforms,
and may break in the future if MySQL ever decides that either platform portability or
standard conformance is important.

Until there is a strategy to address the problem, Falcon will protect Falcon users.

Gentlemen and ladies, all it takes is an agreement on how the problem will be solve so
that Falcon can guarantee forward and backward compatibility of Falcon applications.

I split out bugs 32829 and 32830 for Peter's earlier tests, and those are now fixed.

I can create a Falcon table named A and a falcon table named a:
  create table A (s1 int) engine=falcon;
  create table a (s1 int) engine=falcon partition by key(s1);
or
  create table ı (s1 int) engine=falcon;
  create table I (s1 int) engine=falcon;
or
  create table Σ (s1 int) engine=falcon;
  create table ς (s1 int) engine=falcon;
  create table σ (s1 int) engine=falcon;

I can create a Falcon tablespace named A and a Falcon tablespace named a:
  create tablespace a add datafile 'a' engine=falcon;
  create tablespace A add datafile 'A' engine=falcon;

These are anti-bugs, because they show that Falcon object
names are in fact case sensitive. Sometimes.