MySQL Bugs: #10977: Views: no warning if column name is truncated

Bug #10977	Views: no warning if column name is truncated
Submitted:	30 May 2005 21:27	Modified:	20 Jul 2006 17:35
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.7-beta-debug	OS:	Linux (SUSE 9.2)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
If say "AS `column_name`", and column_name ends with a space, MySQL
throws the space away. That's odd, but it's documented, so okay.

The problem is: there is no warning. So I can create a view thinking that
I'm using a column name which ends with a space, and be confused
when, as I try to use that column name, I get an error message.

I recognize that this should be a priority 'P5'.

How to repeat:
mysql> create table t50 (s1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> create view v50 as select s1 as ` ` from t50;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into v50 (` `) values (0);
ERROR 1054 (42S22): Unknown column ' ' in 'field list'

mysql> insert into v50 (``) values (0);
Query OK, 1 row affected (0.00 sec)

Thank you for the bug report.

Either something changed, or I analyzed wrong.
Leading spaces, not trailing spaces, are stripped.
Another example:

mysql> create view v as select ' a ';
Query OK, 0 rows affected (0.00 sec)

mysql> show create view v;
+------+--------------------------------+
| View | Create View                    |
+------+--------------------------------+
| v    | CREATE ALGORITHM=UNDEFINED
         DEFINER=`root`@`localhost`
         SQL SECURITY DEFINER VIEW `v`
         AS select _latin1' a ' AS `a ` |
+------+--------------------------------+
1 row in set (0.00 sec)

Unfortunately, although the manual says
column names "should not end with space",
it doesn't say they can't start with space.
So the fix is either a warning or a change
to not strip leading spaces.

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/commits/9196

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/commits/9245

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/commits/9246

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/commits/9247

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/commits/9248

When an alias is set to a column leading spaces are removed from the alias.
But when this is done on aliases set by user this can lead to confusion.

Fixed in 4.1.22, 5.0.25

Sorry, 4.1 isn't affected

Noted in 5.0.25 changelog.

The server now issues a warning if it removes leading spaces from an alias.