Bug #27695 View should not be allowed to have all space columns
Submitted: 7 Apr 2007 12:14 Modified: 16 Nov 2007 12:42
Reporter: Hakan Küçükyılmaz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0, 5.1, 5.2 OS:Any
Assigned to: Gleb Shchepa

[7 Apr 2007 12:14] Hakan Küçükyılmaz
Description:
Creating a VIEW with an all space identifier leads to misleading warning.

An one space identifier is truncated to empty string identifier. However, an one space identifier has no "leading spaces".

How to repeat:
[14:04] root@test>CREATE VIEW v1 AS SELECT 1 AS ` `;
Query OK, 0 rows affected, 1 warning (0.00 sec)

[14:04] root@test>SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1548 | Leading spaces are removed from name ' ' |
+---------+------+------------------------------------------+

[14:10] root@test>select * from v1 where ` ` = 1;
ERROR 1054 (42S22): Unknown column ' ' in 'where clause'

[14:10] root@test>select * from v1 where `` = 1;
+---+
|   |
+---+
| 1 |
+---+
1 row in set (0.01 sec)
[9 Apr 2007 8:47] Sveta Smirnova
Thank you for the report.

Verified as described.

All versions are affected.
[5 Sep 2007 12:11] 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/33707

ChangeSet@1.2509, 2007-09-05 17:14:11+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #27695.
  Declaring an all space column name in the SELECT FROM DUAL or in a view
  leads to truncation of that name to empty string identifier.
  
  (This bug doesn't affect table column names because all space names
  are incorrect table column names.)
  
  The Item::set_name method has been modified to truncate such identifiers to
  the last character of the original string.
[11 Sep 2007 10:52] Oleksandr Byelkin
Technically the patch is correct, but I can't say something about idealogic. IMHO it will be better to get OK from Trudy or Peter.
[13 Sep 2007 13:34] 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/34177

ChangeSet@1.2509, 2007-09-13 18:41:50+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #27695.
  Declaring an all space column name in the SELECT FROM DUAL or in a view
  leads to misleading warning message:
  "Leading spaces are removed from name ' '".
  
  The Item::set_name method has been modified to raise warnings like
  "Name ' ' has become ''" in case of the truncation of an all
  space identifier to an empty string identifier instead of the
  "Leading spaces are removed from name ' '" warning message.
[13 Sep 2007 14:22] Oleksandr Byelkin
It is OK for me
[15 Sep 2007 6:04] 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/34310

ChangeSet@1.2529, 2007-09-15 07:04:32+02:00, gshchepa@devsrv-b.mysql.com +1 -0
  select.test:
    Post-fix for bug#27695.
[24 Sep 2007 8:31] Bugs System
Pushed into 5.0.50
[24 Sep 2007 8:35] Bugs System
Pushed into 5.1.23-beta
[26 Sep 2007 9:49] Giuseppe Maxia
A side effect of empty columns in views can be observed in Bug#31202.
(scripts created by mysqldump fail to load)
[26 Sep 2007 9:53] Giuseppe Maxia
A side effect of empty column names in views can be observed in Bug#31202.
(scripts created by mysqldump fail to load when dealing with views containing an empty column name)
[8 Oct 2007 12:53] Jon Stephens
I'm reopening this bug.

1. Why should we allow view columns to be named using all spaces or empty string when we don't allow this for table columns?

2. The above notwithstanding, SELECT behaviour appears broken.

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.23-beta-debug |
+-------------------+
1 row in set (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE VIEW v1 AS SELECT 1 AS ` `;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------+
| Level   | Code | Message                |
+---------+------+------------------------+
| Warning | 1474 | Name ' ' has become '' |
+---------+------+------------------------+
1 row in set (0.00 sec)

mysql> show create view v1;
+------+--------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                            | character_set_client | collation_connection |
+------+--------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `` | latin1               | latin1_swedish_ci    |
+------+--------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
mysql> select * from v1\G
*************************** 1. row ***************************
: 1
1 row in set (0.00 sec)

mysql> select * from v1 where '' = 1\G
Empty set (0.00 sec)

mysql> select * from v1 where '' <> 1\G
*************************** 1. row ***************************
: 1
1 row in set (0.00 sec)

[WTF? This is a fresh pull from 5.1 tree, BTW.]

We should disallow altogether the use of all spaces or an empty string as a view column identifier.
[8 Oct 2007 14:33] Gleb Shchepa
> 2. The above notwithstanding, SELECT behaviour appears broken.
 
No, it is not broken:
 
> [...]
> mysql> select * from v1 where '' = 1\G
> Empty set (0.00 sec)
> 
> mysql> select * from v1 where '' <> 1\G
> *************************** 1. row ***************************
> : 1
> 1 row in set (0.00 sec)
> 
> [WTF? This is a fresh pull from 5.1 tree, BTW.]
 
Please use ` sign to enclose column names instead of ' sign
('' = 1 is always FALSE and '' <> 1 is always TRUE).
 
mysql> CREATE VIEW v1 AS SELECT 1 AS ` `;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> SELECT * FROM v1 WHERE `` = 1;
+---+
|   |
+---+
| 1 | 
+---+
1 row in set (0.00 sec)
 
mysql> SELECT * FROM v1 WHERE `` <> 1;
Empty set (0.00 sec)
[8 Oct 2007 14:35] Jon Stephens
Disregard the second item in my previous comment; I typed "select * from v1 where '' = 1" where I'd intended to use "select * from v1 where `` = 1".

Even so, I still think it's a bad idea to allow empty strings for view column names.
[24 Oct 2007 9:01] Lars Thalmann
A view should not have all-space column names.

Note that this bug has serious consequences, see BUG#31202
(it is not possible to make a correct mysqldump).
Therefore increasing the priority to P2.
[24 Oct 2007 21:37] Giuseppe Maxia
Views should not be allowed to have empty column names either.
(See Bug#31202)
[24 Oct 2007 21:55] 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/36308

ChangeSet@1.2549, 2007-10-25 03:07:42+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #27695: View should not be allowed to have empty or
  all space column names.
  
  The parser has been modified to check VIEW column names
  with the check_column_name function and to report an error
  on empty and all space column names (same as for TABLE
  column names).
[25 Oct 2007 5:21] 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/36319

ChangeSet@1.2549, 2007-10-25 10:32:52+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #27695: View should not be allowed to have empty or
  all space column names.
  
  The parser has been modified to check VIEW column names
  with the check_column_name function and to report an error
  on empty and all space column names (same as for TABLE
  column names).
[12 Nov 2007 18:59] 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/37602

ChangeSet@1.2683, 2007-11-12 23:00:43+04:00, gshchepa@gleb.loc +2 -0
  falcon_bugs.result, falcon_bugs.test:
    Updated test case (bug #27695).
[16 Nov 2007 9:30] Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:33] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:35] Bugs System
Pushed into 6.0.4-alpha
[16 Nov 2007 12:42] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.52, 5.1.23, 6.0.4 changelogs.
[7 Apr 2009 18:35] Paul Dubois
This bug fix can result in incompatibilities for replication or loading dump files. See Bug#36287.