| Bug #21060 | Maximum value of MAX() on string is 9999 | ||
|---|---|---|---|
| Submitted: | 14 Jul 2006 14:03 | Modified: | 14 Jul 2006 15:06 |
| Reporter: | Damien Bezborodow | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.18 | OS: | Linux (CentOS Linux i686 2.6.9-34.ELsmp) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | max 9999 string | ||
[14 Jul 2006 15:06]
Valeriy Kravchuk
Thank you for a problem report. I was able to repeat something similar to the behaviour you described:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.25 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `astcdr` (
-> `uniqueid` varchar(32) default NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into astcdr (uniqueid) values ('32412421.2342'), ('2323321.0'),
-> ('55235111.232323'), ('233242.324'), ('2342345.9999'), ('234652.741'),
-> ('3241242234231.21115'), ('993247891.11');
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select max(substring_index(uniqueid, '.', -1)) from astcdr where
-> uniqueid regexp '^[0-9]+\.[0-9]+$';
+-----------------------------------------+
| max(substring_index(uniqueid, '.', -1)) |
+-----------------------------------------+
| 9999 |
+-----------------------------------------+
1 row in set (0.01 sec)
mysql> select max(convert(substring_index(uniqueid, '.', -1), unsigned)) from a
stcdr where uniqueid regexp '^[0-9]+\.[0-9]+$';
+------------------------------------------------------------+
| max(convert(substring_index(uniqueid, '.', -1), unsigned)) |
+------------------------------------------------------------+
| 232323 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
But it is not a bug. According to the manual (http://dev.mysql.com/doc/refman/5.0/en/string-functions.html):
"SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
This function is multi-byte safe."
SUBSTRING_INDEX returns string (!), and strings are compared character-by-character, not as numbers. Hence the results. When you converted result to a numeric type you got what you (erroniously) expected from the original select.
Description: I posted this on the user comment system and it was removed: "Your comment to 'group-by-functions' in the MySQL Reference Manual was removed. The user comment system in the MySQL manual is not the place to report bugs." So, since somebody thought it was a bug, I am submitting it as one... When working with MAX() on strings of digits, I found that the maximum value returned by MAX() was 9999. Using CONVERT() seemed to fix this problem: mysql> select max(substring_index(uniqueid, '.', -1)) from astcdr where uniqueid regexp '^[0-9]+\.[0-9]+$'; +-----------------------------------------+ | max(substring_index(uniqueid, '.', -1)) | +-----------------------------------------+ | 9999 | +-----------------------------------------+ 1 row in set (1.66 sec) mysql> select max(convert(substring_index(uniqueid, '.', -1), unsigned)) from astcdr where uniqueid regexp '^[0-9]+\.[0-9]+$'; +------------------------------------------------------------+ | max(convert(substring_index(uniqueid, '.', -1), unsigned)) | +------------------------------------------------------------+ | 82609 | +------------------------------------------------------------+ 1 row in set (1.96 sec) How to repeat: 1) Create the table. I have removed all columns except uniqueid: CREATE TABLE `astcdr` ( `uniqueid` varchar(32) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 2) Populate the table: insert into astcdr (uniqueid) values ('32412421.2342'), ('2323321.0'), ('55235111.232323'), ('233242.324'), ('2342345.9999'), ('234652.741'), ('3241242234231.21115'), ('993247891.11'); 3) Do the select query: select max(substring_index(uniqueid, '.', -1)) from astcdr where uniqueid regexp '^[0-9]+\.[0-9]+$'; Suggested fix: No idea. Like I said, I can get around the problem using convert(), but I am guessing a possible "fix" would be to remove the 9999 limit?