Bug #20500 trim(both ' ' from col) causes syntax error in a view

• Submitted: 16 Jun 2006 12:30
• Modified: 18 Jun 2006 19:35
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Duplicate
• Category: MySQL Server: Views
• Severity: S3 (Non-critical)
• Version: 5.0BK, 5.1.11
• OS: Any (*)
• CPU Architecture: Any

[16 Jun 2006 12:30] Shane Bester

Description:
SELECT TRIM(BOTH ' ' FROM `a`) FROM `tkv` is a valid statement when executed at the mysql client.  However a view cannot be queried if it's created based on it.

mysql> SELECT * FROM `vkv`;
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 '_utf8' ') AS `TRIM(BOTH ' ' FROM ``a``)` from `test`.`tkv`' at line 1
mysql> SELECT TRIM(BOTH ' ' FROM `a`) FROM `tkv`;
Empty set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS `tkv`;
CREATE TABLE IF NOT EXISTS `tkv`(`a` CHAR(10) CHARACTER SET utf8)ENGINE=MyISAM;
DROP VIEW IF EXISTS `vkv`;
CREATE VIEW `vkv` AS SELECT TRIM(BOTH ' ' FROM `a`) FROM `tkv`;
SELECT * FROM `vkv`;
SELECT TRIM(BOTH ' ' FROM `a`) FROM `tkv`;

Suggested fix:
not sure..

[16 Jun 2006 12:51] Valeriy Kravchuk

Even more simple test case:

mysql> select trim(both ' ' from '   edfdfd ') as a;
+--------+
| a      |
+--------+
| edfdfd |
+--------+
1 row in set (0.00 sec)

mysql> create view vvv2 as select trim(both ' ' from '   edfdfd ') as a;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from vvv2;
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 '_latin1' ') AS `a`' at line 1

[17 Jun 2006 13:12] Trent Lloyd

This is a duplicate of Bug #17526