| Bug #27130 | SUBSTR wrong result | ||
|---|---|---|---|
| Submitted: | 14 Mar 2007 13:08 | Modified: | 27 Jun 2007 13:42 |
| Reporter: | Alexander Kudinov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.0.38-BK, 5.0.37 | OS: | Linux (Linux, WinXP Pro) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
| Tags: | substr | ||
[14 Mar 2007 15:00]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with latest 5.0.38-BK on Linux:
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.38 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> delimiter //
mysql> CREATE FUNCTION sample1 ()
-> RETURNS VARCHAR(255)
-> LANGUAGE SQL
-> DETERMINISTIC
-> BEGIN
-> DECLARE p INT UNSIGNED DEFAULT 0;
-> RETURN SUBSTR( 'sample text', 1, p );
-> END;
-> //
Query OK, 0 rows affected (0.02 sec)
mysql> select sample1()//
+-------------+
| sample1() |
+-------------+
| sample text |
+-------------+
1 row in set (0.01 sec)
Note that with RETURN SUBSTR( 'sample text', 1, 0 ) even in stored function I've got expected result. So, it has something to do with local variable having default value...
[14 Mar 2007 16:38]
Alexander Kudinov
It seems like SUBSTR function doesn`t make difference between 0 and NULL when third argument is passed via variable or expression, so the entire string is returned.
[16 Jun 2007 22:01]
Igor Babaev
The bug can be demonstrated with a very simple query:
mysql> select substr('foo', 1, cast(0 as unsigned)) from dual;
+---------------------------------------+
| substr('foo', 1, cast(0 as unsigned)) |
+---------------------------------------+
| foo |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.44-debug |
+--------------+
1 row in set (0.00 sec)
At the same time we have:
mysql> select substr('foo', 1, cast(0 as signed)) from dual;
+-------------------------------------+
| substr('foo', 1, cast(0 as signed)) |
+-------------------------------------+
| |
+-------------------------------------+
1 row in set (0.00 sec)
[17 Jun 2007 1:09]
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/28935 ChangeSet@1.2522, 2007-06-16 18:12:29-07:00, igor@olga.mysql.com +3 -0 Fixed bug #27130. If the third argument of the function SUBSTR was represented by an expression of the type UNSIGNED INT and this expression was evaluated to 0 then the function erroneously returned the value of the first argument instead of an empty string. This problem was introduced by the patch for bug 10963. The problem has been resolved by a proper modification of the code of Item_func_substr::val_str.
[17 Jun 2007 18:19]
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/28952 ChangeSet@1.2522, 2007-06-17 11:23:19-07:00, igor@olga.mysql.com +3 -0 Fixed bug #27130. If the third argument of the function SUBSTR was represented by an expression of the type UNSIGNED INT and this expression was evaluated to 0 then the function erroneously returned the value of the first argument instead of an empty string. This problem was introduced by the patch for bug 10963. The problem has been resolved by a proper modification of the code of Item_func_substr::val_str.
[25 Jun 2007 21:49]
Bugs System
Pushed into 5.1.21-beta
[25 Jun 2007 21:51]
Bugs System
Pushed into 5.0.46
[27 Jun 2007 13:42]
Peter Lavin
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
Noted in the changelogs for versions 5.0.46 and 5.1.21.
Description: SUBSTRING(str,pos,len) function returns the entire string instead of empty string when parameter `len` is given by a variable with value 0. This bug appears when SUBSTRING is called from stored procedure. How to repeat: Try this code: CREATE FUNCTION sample1 () RETURNS VARCHAR(255) LANGUAGE SQL DETERMINISTIC BEGIN DECLARE p INT UNSIGNED DEFAULT 0; RETURN SUBSTR( 'sample text', 1, p ); END; Statement SELECT sample1(); returns 'sample text' instead of ''. At the same time SUBSTR( 'sample text', 1, 0 ) returns empty string.