MySQL Bugs: #85106: "lower_case_table_names" not respected for views

Bug #85106	"lower_case_table_names" not respected for views
Submitted:	21 Feb 2017 14:40	Modified:	16 May 2018 13:50
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7.17	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I think we have a (somewhat recent) regression here. 

Neither I_S nor SHOW preserves the lettercae ("camel case" in case) for Views. This is bound to break applications.

Further this page https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html is not explicit about Views (I was told before that the term "table" here also includes views.

How to repeat:
SHOW VARIABLES LIKE 'lower_case_table_names'; -- returns "2"

CREATE VIEW `test`.`View1` AS (SELECT 1); 

SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'test' AND `TABLE_TYPE` = 'VIEW'; -- returns "view1" (note lowercase)

SHOW CREATE VIEW View1; -- returns "CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view1` AS (select 1 AS `1`)" (also note lowercase)

Suggested fix:
Preserve lettercase as created in the CREATE statement and update docs page to describe expected behavior of Views explictily with "lower_case_table_names".

I almost forgot this old one. https://bugs.mysql.com/bug.php?id=20356

Hi!

Our documentation states clearly that `lower_case_table_names` should be set to 1 on Windows. I tested your test case on macOS and I got the same result.

However, the explanation of this global variable does not mention views, hence, it needs to be cleared in the manual.

Where does the documentation state this?

On the opposite on the page https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html is is clearly disaviced to set it to "0", but for "2" it says "This works only on file systems that are not case sensitive!" What means that "2" is a valid setting on Windows/NTFS and Mac with default file system. If it was not, the option to set it to "2" would have no use cases whatsoever.

Peter, 

In chapter 5.1.5, manual says clearly:

"

If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases.

On Windows the default value is 1. On OS X, the default value is 2.

You should not set lower_case_table_names to 0 if you are running MySQL on a system where
the data directory resides on a case-insensitive file system (such as on Windows or OS X).

"

Hence, the names of tables AND views are stored in lower case.

Yes, but they are DISPLAYED/RETURNED in 'camelcase' if created as such with the setting of "2" - or they should. 

Views don't honour the "2" setting like tables and databases do. And that is what this report is about.

Hi!

Views behave like tables when it comes to that particular variables. If you have a version and release number which behaves differently then 5.7, we shall investigate further.

Our manual,  nor our code, does not mention anything about any "camelcase". It is possible that there was a change of behaviour between two major versions, but then it could be intended.

So far, in absence of any other proof, this stays as a verified "Documentation" bug.

"Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive."

"using the lettercase specified" means "camelcase" like in "CREATE TABLE TableName .." and "CREATE VIEW ViewName .. "

Chapter 5.1.5 is quite clear. It is stored in lowercase. 

Regarding your addition, one more reason to clear it out in the documentation.

For the setting of "2" (quote from same docs page): "Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup". This contradicts your statement. 

So it is NOT stored in lowercase according to documentation. And database- and tablenames are not (in thé .FRM file, I believe - so it could actually be interesting to check same behavior of both databases, tables and views with MySQL 8 on Windows/Mac with l_c_t_n = "2" where the .FRM file has finally been eliminated, as far as I have read somewhere).

I have the impression that you are not familiar with MySQL on Windows.

The passage I am quoting

Attachment: case.PNG (image/png, text), 53.14 KiB.

I have been told *so many times* here that whenever documentation uses the term "table" this includes views as well. But behavior with "lower_case_table_names = 2" is not the same for tables and views. At least not in 5.7. 

"lower_case_table_names = 2" is important for *readability* of queries, scripts and results. The setting  makes it possible to use 'camel case' on systems with a non-case-sensitive filesystem.

MySQL may not want to fix/change this and in that case this is a documentation request. But it should not be deemed a documentation request on false premises and lack of understanding of MySQL on Windows and Mac - and that is what you are doing here IMO.

All of your comments will be taken into account.

https://bugs.mysql.com/bug.php?id=88240 marked as duplicate of this one.

https://bugs.mysql.com/bug.php?id=89942 marked as duplicate of this one.

So, What is the final solution for Changing Views to UPPER CASE.

Hi,

Views can not overcome the limitation of the operating system, in this case Windows.

The rest will be explained when our manual is updated.

Posted by developer:
 
The description for lower_case_table_names=2 says:

InnoDB table names are stored in lowercase, as for lower_case_table_names=1.

Will change that to:

InnoDB table names and view names are stored in lowercase, as for lower_case_table_names=1.