| Bug #24909 | Explicit UNSIGNED int does not play nice with SUBSTR (but does with LEFT) | ||
|---|---|---|---|
| Submitted: | 8 Dec 2006 13:13 | Modified: | 16 Oct 2007 11:09 |
| Reporter: | Roland Bouman | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.1.14,5.1.5, 5.0bk | OS: | Linux (linux) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | INT, lpad, substr, substring, UNSIGNED | ||
[8 Dec 2006 14:06]
Hartmut Holzgraefe
works fine in 4.1, broken in 5.0/5.1
[8 Dec 2006 14:19]
Hartmut Holzgraefe
mysqltest test case
Attachment: bug24909.tgz (application/x-gtar, text), 731 bytes.
[12 Dec 2006 22:24]
Ian Greenhoe
Dup of 10963
[23 Jan 2007 14:43]
Hartmut Holzgraefe
seems to be fixed in 5.0bk but still exists in 5.1.14, building current 5.1bk now for further testing ...
[23 Jan 2007 18:16]
Hartmut Holzgraefe
ok, the attached test case failes with 5.0.30 and 5.1.14, it passes with 5.0.33 and bk source builds for both 5.0 and 5.1 from today, so it seems to be fixed by now somehow ...
[16 Oct 2007 11:09]
Roland Bouman
Seems to be fixed at least in 5.1.22, prolly way before that.
Description: Explicit unsigned int values do not play nice with SUBSTR and SUBSTRING. Other string functions such as LEFT and RIGHT are not affected How to repeat: select substring('Int?', cast(1 as unsigned),1) `substring_u` , substring('Int?', cast(1 as signed),1) `substring_s` , substr('Int?',cast(1 as unsigned),1) `substr_u` , substr('Int?',cast(1 as signed),1) `substr_s` , version() ; +-------------+-------------+----------+----------+-----------------+ | substring_u | substring_s | substr_u | substr_s | version() | +-------------+-------------+----------+----------+-----------------+ | | I | | I | 5.1.15-beta-log | +-------------+-------------+----------+----------+-----------------+ 1 row in set (0.01 sec) delimiter $$ create procedure p() begin declare ui int unsigned default 1; declare si int default 1; select substring('Int?', ui,1) `substring_u` , substring('Int?', si,1) `substring_s` , substr('Int?',ui,1) `substr_u` , substr('Int?',si,1) `substr_s` , version() ; end; $$ call p()$$ +-------------+-------------+----------+----------+-----------------+ | substring_u | substring_s | substr_u | substr_s | version() | +-------------+-------------+----------+----------+-----------------+ | | I | | I | 5.1.15-beta-log | +-------------+-------------+----------+----------+-----------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) The sp example can also be repeated with TINYINT i/o INT. Suggested fix: fix the unsigned int please.