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

[30 May 2005 21:27] Peter Gulutzan
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)
[30 May 2005 21:41] MySQL Verification Team
Thank you for the bug report.
[28 Apr 2006 16:41] Peter Gulutzan
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.
[14 Jul 2006 22:56] 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/commits/9196
[17 Jul 2006 21:23] 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/commits/9245
[17 Jul 2006 21:23] 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/commits/9246
[17 Jul 2006 21:23] 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/commits/9247
[17 Jul 2006 21:23] 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/commits/9248
[18 Jul 2006 23:55] Evgeny Potemkin
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
[18 Jul 2006 23:57] Evgeny Potemkin
Sorry, 4.1 isn't affected
[20 Jul 2006 17:35] Paul DuBois
Noted in 5.0.25 changelog.

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