MySQL Bugs: #59703: SHOW TABLE LIKE '%....%' is case sensitive, but SELECT WHERE LIKE '%..." is not

Bug #59703	SHOW TABLE LIKE '%....%' is case sensitive, but SELECT WHERE LIKE '%..." is not
Submitted:	24 Jan 2011 19:35	Modified:	12 Mar 2013 19:01
Reporter:	Daniel Lo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	5.1.52 & 5.5.8	OS:	Linux
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	case, like, Sensitve, SHOW TABLE

Description:
The like clause for 'SHOW TABLES' is not the same as the SELECT WHERE clause version.  The show tables copy is "case sensitive" while SELECT WHERE's like is case insensitive.

How to repeat:
mysql> create database daniel;
Query OK, 1 row affected (0.04 sec)

mysql> use daniel;
Database changed

mysql> create table test ( str varchar(255) ) ;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test set str = 'testing';
Query OK, 1 row affected (0.00 sec)

mysql> show tables like '%TEST%';
Empty set (0.00 sec)

mysql> show tables like '%test%';
+---------------------------+
| Tables_in_daniel (%test%) |
+---------------------------+
| test                      |
+---------------------------+
1 row in set (0.00 sec)

mysql> select * from test where str like '%test%';
+---------+
| str     |
+---------+
| testing |
+---------+
1 row in set (0.00 sec)

mysql> select * from test where str like '%TEST%';
+---------+
| str     |
+---------+
| testing |
+---------+
1 row in set (0.00 sec)

Please, send the results of:

show variables like 'lower%';

from your system.

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.00 sec)

Ah, I see.

I always thought of the table name issues as applying to how mysql interacted with the file system in terms of queries and such, and I did not realize that they also would apply to the "like" statement, which in regular sql is case insensitive.

I'm not sure if this should be resolved, but I think you can see from a SQL "like" perspective how one would think that the above "should" work as case insensitive and also from a OS point of view that tables are filenames and are case sensitive.

I'm not sure about the resolution of this; but I have no objection to this being closed.

Thank you!

-daniel

Perhaps the documentation can be amended to say:

http://dev.mysql.com/doc/refman/5.5/en/show-tables.html

The LIKE clause is dependent upon the [ link=http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_lower_case_tabl... ] lower_case_table_names [ /link ] setting for case sensitivity.

TIA

-daniel

Looks like a valid documentation request based on your last comments.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added text:

Matching performed by the LIKE clause is dependent on the setting of
the lower_case_table_names system variable.