Bug #7448 Views: column name generation doesn't always work
Submitted: 21 Dec 2004 2:18 Modified: 22 Jun 2005 1:51
Reporter: Peter Gulutzan
Status: Closed
Category:Server Severity:S3 (Non-critical)
Version:5.0.3-alpha-debug OS:Linux (SUSE 9.2)
Assigned to: Bugs System Target Version:

[21 Dec 2004 2: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 20: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 23: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 10: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 7: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 18: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 19: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 20:48] Oleksandr Byelkin 
pushed to 5.0.8
[22 Jun 2005 1: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>