Bug #7448 Views: column name generation doesn't always work
Submitted: 21 Dec 2004 1:18 Modified: 21 Jun 2005 23:51
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3-alpha-debug OS:Linux (SUSE 9.2)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[21 Dec 2004 1:18] Peter Gulutzan 
Description:
When a view's select list contains 'literal' with no AS clause, MySQL generates a delimited 
identifier `literal`, that is, the same string but with backticks instead of ticks. This trick fails 
when two similar literals appear in the select list, or when the literal is the same as a column 
name,  or when the literal contains backslash '\'. I believe that the only way to avoid all 
failures is to generate unique valid column names.  

How to repeat:
mysql> create table t54 (s1 int); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create view v54 as select s1,'s1' from t54; 
ERROR 1060 (42S21): Duplicate column name 's1' 
 
... 
 
mysql> create view v as select '\','\shazam'; 
-------------- 
/usr/local/mysql/bin/mysql  Ver 14.7 Distrib 5.0.3-alpha, for pc-linux (i686) 
etc. 
 
... 
 
mysql> create view vk as select 'k','K'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select K from vk; 
+---+ 
| K | 
+---+ 
| k | 
+---+ 
1 row in set (0.00 sec)
[19 Jun 2005 18:26] Oleksandr Byelkin 
following is just incorrect suntax
create view v1 as select '\','\shazam';
correct query (according to MySQL special characters escaping rules) will be one of following:
+ create view v1 as select '\\','\\shazam';
+ select * from v1;
+ \     \shazam
+ \     \shazam
+ create view v2 as select '\'','\shazam';
+ select * from v2;
+ '     shazam
+ '     shazam
[19 Jun 2005 21:25] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26164
[20 Jun 2005 8:41] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26174
[21 Jun 2005 5:27] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26221
[21 Jun 2005 16:38] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26254
[21 Jun 2005 17:30] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26258
[21 Jun 2005 18:48] Oleksandr Byelkin 
pushed to 5.0.8
[21 Jun 2005 23:51] Mike Hillyer 
Documented in 5.0.8 changelog:

<listitem><para>Fixed column name generation in <literal>VIEW</literal>  creation to ensure there are no duplicate column names. (Bug #7448)</para></listitem>