MySQL Bugs: #27695: View should not be allowed to have all space columns

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	CPU Architecture:	Any

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)

Thank you for the report.

Verified as described.

All versions are affected.

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.

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.

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.

It is OK for me

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.

Pushed into 5.0.50

Pushed into 5.1.23-beta

A side effect of empty columns in views can be observed in Bug#31202.
(scripts created by mysqldump fail to load)

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)

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.

> 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)

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.

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.

Views should not be allowed to have empty column names either.
(See Bug#31202)

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).

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).

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).

Pushed into 5.0.52

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

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.

This bug fix can result in incompatibilities for replication or loading dump files. See Bug#36287.