| Bug #17526 | errors thrown by VIEW using TRIM("x" FROM some_field) | ||
|---|---|---|---|
| Submitted: | 17 Feb 2006 14:00 | Modified: | 7 Aug 2006 7:07 |
| Reporter: | Dan Kloke (Candidate Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Views | Severity: | S2 (Serious) |
| Version: | 5.0.19-BK, 5.0.18-nt-max, 5.1BK, 5.0.23BK, 4.1.21BK | OS: | Any (*) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[19 Feb 2006 12:15]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.19-BK (ChangeSet@1.2064, 2006-02-18 21:08:41+01:00) on Linux:
mysql> create table some_table (str varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into some_table (str) values ('yadda');
Query OK, 1 row affected (0.00 sec)
mysql> select trim('y' FROM s.str) as newstr from some_table s;
+--------+
| newstr |
+--------+
| adda |
+--------+
1 row in set (0.01 sec)
mysql> create or replace view some_table_view as select trim('y' FROM s.str) as newstr
-> from some_table s;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from some_table_view;
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'y') AS `newstr` from `test`.`some_table` `s`' at line 1
mysql> show create view some_table_view \G;
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'y') AS `newstr` from `test`.`some_table` `s`' at line 1
ERROR:
No query specified
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19 |
+-----------+
1 row in set (0.00 sec)
[4 Mar 2006 9:50]
Dorothea Dorsch
My tests revealed it's not the from, its whatever charakter you define to be trimmed. If you leave the default blank, everything is fine.
So I thought of replacing the charakter with blank, trim it, and (In my case I only needed to strip off a trailing ".", but wanted to keep all other occurances of "." in the string. If your string has natural occurances of blanks, this won't work) replace all remaining blanks to "." again.
Not beautiful, but works for me.
mysql> create table some_table (str varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into some_table (str) values ('yadda');
Query OK, 1 row affected (0.00 sec)
mysql> insert into some_table (str) values ('yaddaya');
Query OK, 1 row affected (0.00 sec)
mysql> insert into some_table (str) values ('yadday');
Query OK, 1 row affected (0.00 sec)
mysql> create or replace view some_table_view as select replace(trim(leading from replace(str,'y',' ')),' ','y') as newstr from some_table;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from some_table_view;
+--------+
| newstr |
+--------+
| adda |
| addaya |
| adday |
+--------+
3 rows in set (0.00 sec)
[17 Jun 2006 13:11]
Trent Lloyd
I can confirm this, I ran into it today on 5.1 beta Also Bug #20500 (http://bugs.mysql.com/bug.php?id=20500&edit=3) is a duplicate of this
[18 Jun 2006 19:38]
MySQL Verification Team
marked bug #20500 as duplicate of this.
[18 Jul 2006 8:12]
Stephen Martindale
I can also confirm this. Version: 5.0.19 (Debian Etch) Character Set: utf8 (utf8_general_ci) (The character set forms part of the message, so it may be significant.)
[19 Jul 2006 2:06]
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/9315
[19 Jul 2006 18:21]
Igor Babaev
With the EXPLAIN EXTENDED commmand this problem can be demonstrated for 4.1 as well:
mysql> SELECT VERSION();
+--------------+
| VERSION() |
+--------------+
| 4.1.21-debug |
+--------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t1 (s varchar(10));
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO t1 VALUES ('yadda'), ('yaddy');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> EXPLAIN EXTENDED SELECT s FROM t1 WHERE TRIM('y' FROM s) > 'ab';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+---------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------+
| Note | 1003 | select test.t1.s AS `s` from test.t1 where (trim(test.t1.s,_latin1'y') > _latin1'ab') |
+-------+------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
The syntax trim(test.t1.s,_latin1'y') that we observe in the warning is not a valid MySQL syntax.
That's why I've decided to apply the fix to 4.1 merging it into 5.0 after this with addition of the reported test cases for views.
[19 Jul 2006 19:35]
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/9349
[19 Jul 2006 19:44]
Sergey Petrunya
Ok to push the above commit
[19 Jul 2006 23:40]
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/9358
[2 Aug 2006 18:36]
Evgeny Potemkin
For 4.1 it caused wrong output for EXPLAIN EXTENDED commands
if expressions with the TRIM function of two arguments were used.
For 5.0 it caused an error message when trying to select
from a view with the TRIM function of two arguments.
This unexpected error message was due to the fact that the
print method for the class Item_func_trim was inherited from
the class Item_func. Yet the TRIM function does not take a list
of its arguments. Rather it takes the arguments in the form:
[{BOTH | LEADING | TRAILING} [remstr] FROM] str) |
[remstr FROM] str
Fixed in 4.1.22, 5.0.25, 5.1.12
[7 Aug 2006 7:07]
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://www.mysql.com/doc/en/Installing_source_tree.html
Documented bugfix in 4.1.22/5.0.25/5.1.12 changelogs.
Description: When creating a view where a field is defined using the extended form of TRIM() (TRIM("x" FROM "yadda")), the view is successfully created, without error. However, when applied the view generates an error like: 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'y') AS `newstr` from `test`.`some_table` `s`' at line 1 and SHOW CREATE VIEW returns this same error. How to repeat: use test; drop table if exists some_table; create table some_table (str varchar(20)); insert into some_table (str) values ('yadda'); select trim('y' FROM s.str) as newstr from some_table s; /*no errors creating the view*/ create or replace view some_table_view as select trim('y' FROM s.str) as newstr from some_table s; /*but..*/ select * from some_table_view; /*and..*/ show create view some_table_view \G; /*cleanup*/ drop view some_table_view; drop table some_table; Suggested fix: Looks like a parsing issue, maybe the FROM is setting it off? Or the attempt to assign a characterset to the substring to be trimmed? One for the debugger. This seems to me to be an awkward form for TRIM anyway. How about: TRIM( str [,remstr [BOTH | LEADING | TRAILING]] ) no FROM in that form.. btw, currently TRIM does a BINARY replacement, not case-insensitive.. might be nice to be case-insensitive by default, and maybe have the syntax be: TRIM(str [, [BINARY] remstr [BOTH | LEADING | TRAILING]] ) Thanks again for your excellent work!