MySQL Bugs: #26446: Need reasonable names for expressions in VIEW without column aliases

Bug #26446	Need reasonable names for expressions in VIEW without column aliases
Submitted:	16 Feb 2007 15:30	Modified:	3 Jan 2009 11:40
Reporter:	Alex Rytov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S4 (Feature request)
Version:	5.0.36-BK, 5.1.15	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	column alias, expression, VIEW

Description:

When you try to define a VIEW with expression column(s) in its SELECT but without explicit column alias(es) for that column(s),  the server should either report that as an error or assign a reasonable column alias automatically (like `col1`, `col2`, etc.)

As it is, the expression itself is used as the column name, which might lead to some rather weird situations. I haven't seen them cause any real trouble (like buffer overruns), but maybe I just haven't tried hard enough ;-)

How to repeat:

CREATE VIEW vvv AS
select (1+2), '
[paste here a screenful of any text]
';

now try this:

show create view vvv;

or this (note the column captions):

select * from vvv;

or this:

SELECT * FROM information_schema.`COLUMNS` C where table_name = 'vvv';

Suggested fix:

Either:

(a) Make column aliases mandatory for expression columns in views or

(b) Make the server name them `col1`, `col2`... or somthing like that

Thank you for a problem report. Verified just as described also with 5.0.36-BK on Linux. I'd call this a feature request, but as you can not create table with the same column name:

mysql> create view vweird as select 1 + 1, ' some
    '> long
    '> text';
Query OK, 0 rows affected (0.03 sec)

mysql> show create view vweird\G
*************************** 1. row ***************************
       View: vweird
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `vweird` AS select (1 + 1) AS `1 + 1`,_latin1' some\nlong\ntext' AS
 `some
long
text`
1 row in set (0.00 sec)

mysql> create table tweird2(`1 + 1` int, `some
long
text`);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at
 line 1

I think, it is a bug (because of inconsistency!). I am also not sure that any SQL standard allows column names with embedded new lines...

> create table tweird2(`1 + 1` int, `some
long
text`);

Well, to be fair the table in your example couldn't be created because you forgot to specify the column type. It does work this way: 

create table tweird2(`1 + 1` int, `some
long
text` int);

What is of more concern is that you can specify pretty long texts or binary data literals in a SELECT and it's anybody's guess what would happen when the server tries to use that as a column name... And it sure looks like a mess...

Sorry, it was my fault. As these king of identifiers works consistently in all cases:

mysql> create table tweird3(`some
really really
really really
long text - it` int);
Query OK, 0 rows affected (0.01 sec)

mysql> desc tweird3;
+--------------------------------------------------+---------+------+-----+-----
----+-------+
| Field                                            | Type    | Null | Key | Defa
ult | Extra |
+--------------------------------------------------+---------+------+-----+-----
----+-------+
| some
really really
really really
long text - it | int(11) | YES  |     | NULL    |       |
+--------------------------------------------------+---------+------+-----+-----
----+-------+
1 row in set (0.01 sec)

(and their maximum length is still checked), the only concern for me is new lines in names + weird look. So, I think, this is a reasonable feature request (to disallow new lines in identifiers).

Actually, I think, this is a bug. We can consider it as a duplicate of already escalated Bug #40277.