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:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.7.17 OS:Windows
Assigned to: CPU Architecture:Any

[21 Feb 2017 14:40] Peter Laursen
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); 


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".
[22 Feb 2017 16:49] Peter Laursen
I almost forgot this old one. https://bugs.mysql.com/bug.php?id=20356
[24 Oct 2017 13:50] MySQL Verification Team

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.
[24 Oct 2017 13:58] Peter Laursen
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.
[24 Oct 2017 14:25] MySQL Verification Team

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.
[24 Oct 2017 14:44] Peter Laursen
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.
[24 Oct 2017 14:52] MySQL Verification Team

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.
[24 Oct 2017 14:57] Peter Laursen
"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 .. "
[24 Oct 2017 14:59] MySQL Verification Team
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.
[24 Oct 2017 15:09] Peter Laursen
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.
[24 Oct 2017 15:28] Peter Laursen
The passage I am quoting

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

[24 Oct 2017 15:42] Peter Laursen
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.
[25 Oct 2017 11:09] MySQL Verification Team
All of your comments will be taken into account.
[26 Oct 2017 23:09] MySQL Verification Team
https://bugs.mysql.com/bug.php?id=88240 marked as duplicate of this one.
[7 Mar 2018 10:37] MySQL Verification Team
https://bugs.mysql.com/bug.php?id=89942 marked as duplicate of this one.
[8 Mar 2018 11:43] Anil Reddy
So, What is the final solution for Changing Views to UPPER CASE.
[8 Mar 2018 13:33] MySQL Verification Team

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

The rest will be explained when our manual is updated.
[16 May 2018 13:50] Paul DuBois
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.