Bug #10269 SUBSTRING() with negative index returns empty from within a subquery
Submitted: 29 Apr 2005 17:11 Modified: 21 Jul 2005 4:48
Reporter: Nicholas Leippe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.10-max, 4.1.11-max official binaries OS:Linux (gentoo linux)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[29 Apr 2005 17:11] Nicholas Leippe
Description:
SUBSTRING() with a negative index returns that many characters from the end of the string.
eg: SUBSTRING('abc', -1) returns 'c'

However, when this is done inside a subquery, it returns empty.

How to repeat:
create table b (b char(3));
insert b set b='abc';
> select *,substring(b,1),substring(b,-1) from b;
+------+----------------+-----------------+
| b    | substring(b,1) | substring(b,-1) |
+------+----------------+-----------------+
| abc  | abc            | c               |
+------+----------------+-----------------+
1 row in set (0.00 sec)

> select * from (select *,substring(b,1),substring(b,-1) from b) t;
+------+----------------+-----------------+
| b    | substring(b,1) | substring(b,-1) |
+------+----------------+-----------------+
| abc  | abc            |                 |
+------+----------------+-----------------+
1 row in set (0.00 sec)
[29 Apr 2005 17:27] Miguel Solorzano
Verified on Slackware 10.0
[28 Jun 2005 19: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/internals/26492
[7 Jul 2005 8:28] Oleksandr Byelkin
pushed to 4.1.13
merged to 5.0.10
[21 Jul 2005 4:48] Jon Stephens
Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

Documented fix in 4.1.13 and 5.0.10 changelogs. Closed.