| Bug #62076 | Views with lower() function are re-written to use lcase() function. | ||
|---|---|---|---|
| Submitted: | 3 Aug 2011 19:16 | Modified: | 26 Sep 2012 13:57 |
| Reporter: | Brian Pontius | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Views | Severity: | S3 (Non-critical) |
| Version: | 5.5.8-log, 5.1.59 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | lcase, LOWER, Views | ||
[4 Aug 2011 3:44]
Valeriy Kravchuk
Thank you for the problem report. Verified with current mysql-5.1 from bzr on Mac OS X also:
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.59 Source distribution
Copyright (c) 2000, 2011, 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> drop table test;
ERROR 1051 (42S02): Unknown table 'test'
mysql> create table test (id int, name varchar(12));
Query OK, 0 rows affected (0.06 sec)
mysql> create view test_v as select lower(name) as name from test;
Query OK, 0 rows affected (0.48 sec)
mysql> show create view test_v\G
*************************** 1. row ***************************
View: test_v
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test_v` AS select lcase(`test`.`name`) AS `name` from `test`
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.06 sec)
[26 Sep 2012 13:57]
Jon Stephens
Fixed in trunk (currently tagged 5.7.0). Closed.
Description: Create a view on a table using the lower function, then show create view, it shows the select query was re-written to use the lcase function. Per the 5.5 documentation, lcase is a synonym for LOWER(), not the other way around. LOWER is the SQL standard. This makes it more difficult to dump the MySQL database and import it into another DB product. I have not tried it, but the same may be true for other string funtions, such as ucase and upper. How to repeat: create table test (id int, name varchar(12)); insert into test (1,"one"); create view test_v as select lower(name) as name from test; show create view test_v\G *************************** 1. row *************************** View: test_v Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`dbsys`@`localhost` SQL SECURITY DEFINER VIEW `test_v` AS select lcase(`test`.`name`) AS `name` from `test` character_set_client: latin1 collation_connection: latin1_swedish_ci Suggested fix: The query rewrite should adhere to SQL standards whenever possible.