| Bug #90836 | CTE names are case-sensitive even without quotes | ||
|---|---|---|---|
| Submitted: | 11 May 2018 12:11 | Modified: | 11 May 2018 12:57 |
| Reporter: | Markus Winand | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.11 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | cte, with | ||
[11 May 2018 12:38]
Peter Laursen
On Windows and lower_case_table_names = 1, I get expected results: C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -uroot -p --port=3311 Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> WITH CamelCase AS (SELECT 1) SELECT * FROM camelcase; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> show variables like 'lower_case_table_names'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_table_names | 1 | +------------------------+-------+ 1 row in set (0.00 sec) mysql> .. I did not try to set l_c_t_n = 2 as it would require a freshly initialized database with MySQL 8. But it should yield the same. However with l_c_t_n = 0 (as default on Linux), I think the reported behavior looks correct. (Reference https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html) -- Peter -- not a MySQL/Oracle person
[11 May 2018 12:57]
Markus Winand
It actually DOES behave like for regular tables, but on Linux the default was unexpected to me. mysql> show variables like 'lower_case_table_names'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_table_names | 0 | +------------------------+-------+
Description: mysql> WITH CamelCase AS (SELECT 1) -> SELECT * -> FROM camelcase; ERROR 1146 (42S02): Table 'test.camelcase' doesn't exist Query names introduced with WITH should follow the same case-sensitivity rules are normal table names. Using the same capitalizations gives the expected result: mysql> WITH CamelCase AS (SELECT 1) -> SELECT * -> FROM CamelCase; +---+ | 1 | +---+ | 1 | +---+ How to repeat: Run the above queries.