| Bug #15757 | The substring function does not work as expected | ||
|---|---|---|---|
| Submitted: | 14 Dec 2005 19:54 | Modified: | 15 Mar 2007 3:06 |
| Reporter: | Argun Türe | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.16 | OS: | Linux (linux) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[15 Dec 2005 12:22]
Aleksey Kishkin
Verified on slackware 10.2 against mysql 5.0.16-max:
mysql> select lm.id, lm.time_stamp_opening,
-> SUBTIME(TIMEDIFF('2005-12-15 22:22:22', lm.time_stamp_opening),l.timer) timer,
-> SUBSTRING(lm.description,1,15) description
-> from logs lm
-> left join locations l on lm.place=l.id
-> where lm.time_stamp_closing='0000-00-00 00:00:00' order by timer;
+------+---------------------+----------+-------------+
| id | time_stamp_opening | timer | description |
+------+---------------------+----------+-------------+
| 1552 | 2005-12-14 20:26:05 | 21:56:17 | |
+------+---------------------+----------+-------------+
1 row in set (0.00 sec)
mysql> select lm.id, lm.time_stamp_opening, SUBTIME(TIMEDIFF('2005-12-15 22:22:22', lm.time_stamp_opening),l.timer) timer, SUBSTRING(lm.description,1,15) description from logs lm left join locations l on lm.place=l.id where lm.time_stamp_closing='0000-00-00 00:00:00';
+------+---------------------+----------+----------------------------+
| id | time_stamp_opening | timer | description |
+------+---------------------+----------+----------------------------+
| 1552 | 2005-12-14 20:26:05 | 21:56:17 | üğişçöıÜĞİŞÇÖI |
+------+---------------------+----------+----------------------------+
1 row in set (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.0.16-max |
+------------+
1 row in set (0.00 sec)
[5 May 2006 6:32]
Grzegorz Drozd
Hi
I also found that some operation with negative pos don't work (even from manual)
SELECT SUBSTRING('Sakila', -5, 3);
SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
empty string is returned
mysql:
4.1.10a-standard on linux
[21 Jun 2006 12:53]
Dennys Hsieh
Hi, I got the same problem too and I think the reason is substring() doesn't support longtext type.
[1 Aug 2006 7:12]
Ian Greenhoe
Simpler test case:
drop table if exists test_15757_main;
create table test_15757_main
(id int(11), a longtext not null, b longtext, c varchar(255));
drop table if exists test_15757_second;
create table test_15757_second (id int(11), random_field int(11));
insert into test_15757_main values
(1, "one value", "value one", "first value"),
(2, "two value", "value two", "second value");
insert into test_15757_second values (1, 1), (2, 2);
select
main.id,
substring(a, 1, 5), a,
substring(b, 1, 5), b,
substring(c, 1, 5), c
from test_15757_main main
left join test_15757_second second on main.id = second.id
order by second.random_field;
select
main.id,
substring(a, 1, 5), a,
substring(b, 1, 5), b,
substring(c, 1, 5), c
from test_15757_main main
left join test_15757_second second on main.id = second.id;
Results:
mysql> select
-> main.id,
-> substring(a, 1, 5), a,
-> substring(b, 1, 5), b,
-> substring(c, 1, 5), c
-> from test_15757_main main
-> left join test_15757_second second on main.id = second.id
-> order by second.random_field;
+------+--------------------+-----------+--------------------+-----------+--------------------+--------------+
| id | substring(a, 1, 5) | a | substring(b, 1, 5) | b | substring(c, 1, 5) | c |
+------+--------------------+-----------+--------------------+-----------+--------------------+--------------+
| 1 | | one value | NULL | value one | first | first value |
| 2 | | two value | NULL | value two | secon | second value |
+------+--------------------+-----------+--------------------+-----------+--------------------+--------------+
2 rows in set (0.00 sec)
mysql>
mysql> select
-> main.id,
-> substring(a, 1, 5), a,
-> substring(b, 1, 5), b,
-> substring(c, 1, 5), c
-> from test_15757_main main
-> left join test_15757_second second on main.id = second.id;
+------+--------------------+-----------+--------------------+-----------+--------------------+--------------+
| id | substring(a, 1, 5) | a | substring(b, 1, 5) | b | substring(c, 1, 5) | c |
+------+--------------------+-----------+--------------------+-----------+--------------------+--------------+
| 1 | one v | one value | value | value one | first | first value |
| 2 | two v | two value | value | value two | secon | second value |
+------+--------------------+-----------+--------------------+-----------+--------------------+--------------+
2 rows in set (0.01 sec)
It appears that substring is returning NULL on longtext type when there is an order by clause that references the joined table. (Alloowing the field to contain a NULL makes this clear.)
[23 Oct 2006 18:33]
Ian Christian
DROP TABLE IF EXISTS `moo`;
CREATE TABLE `moo` (
`fish` varchar(32) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `moo` VALUES ('fish1'),('fish1'),('fish1'),('moo1'),('moo1'),('moo1'),('moo1');
mysql> SELECT substring(fish, -2, 1) FROM moo;
+------------------------+
| substring(fish, -2, 1) |
+------------------------+
| h |
| h |
| h |
| o |
| o |
| o |
| o |
+------------------------+
7 rows IN SET (0.00 sec)
mysql> SELECT count(1) AS npple, substring(fish, -2, 1) AS ra FROM moo GROUP BY ra ORDER BY npple;
+-------+------+
| npple | ra |
+-------+------+
| 7 | |
+-------+------+
[5 Nov 2006 3:28]
Donald Byrd
This bug seems really old, but I just came across it on 5.0.22 on a FreeBSD 6.0 system. I upgraded mysql to 5.0.27 and the problem remains. I can provide a test for it if you are interested. Just like the original reporter, the bug goes away when the ORDER BY clause is removed from the query. For those looking for a work around, I switched from using SUBSTRING(COL1, 1, 150) to LEFT(COL1, 150) and it worked fine (Maybe some combination of LEFT & RIGHT will work for more complicated expressions).
[9 Mar 2007 17:46]
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/21631 ChangeSet@1.2435, 2007-03-09 19:44:13+03:00, evgen@moonbone.local +3 -0 Bug#15757: Wrong SUBSTRING() result when a tmp table was employed. When the SUBSTRING() function used over a LONGTEXT field the max_length of the SUBSTRING() result was wrongly calculated and set to 0. Due to max_length parameter is used while tmp field creation it limits the length of the result field and leads to printing an empty string instead of the correct result. Now the Item_func_substr::fix_length_and_dec() function correctly calculates the max_length parameter.
[9 Mar 2007 21:29]
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/21653 ChangeSet@1.2435, 2007-03-10 00:28:22+03:00, evgen@moonbone.local +3 -0 Bug#15757: Wrong SUBSTRING() result when a tmp table was employed. When the SUBSTRING() function used over a LONGTEXT field the max_length of the SUBSTRING() result was wrongly calculated and set to 0. Due to max_length parameter is used while tmp field creation it limits the length of the result field and leads to printing an empty string instead of the correct result. Now the Item_func_substr::fix_length_and_dec() function correctly calculates the max_length parameter.
[12 Mar 2007 5:08]
Igor Babaev
Pushed 5.0.38, 5.1.17
[12 Mar 2007 13:29]
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/21724 ChangeSet@1.2435, 2007-03-10 19:55:34+03:00, evgen@moonbone.local +3 -0 Bug#15757: Wrong SUBSTRING() result when a tmp table was employed. When the SUBSTRING() function was used over a LONGTEXT field the max_length of the SUBSTRING() result was wrongly calculated and set to 0. As the max_length parameter is used while tmp field creation it limits the length of the result field and leads to printing an empty string instead of the correct result. Now the Item_func_substr::fix_length_and_dec() function correctly calculates the max_length parameter.
[15 Mar 2007 3:06]
Paul DuBois
Noted in 5.0.38, 5.1.17 changelog.
Description: When I execute the query below, nothing returns in the description field. select lm.id, lm.time_stamp_opening, SUBTIME(TIMEDIFF('2005-12-15 22:22:22', lm.time_stamp_opening),l.timer) timer, SUBSTRING(lm.description,1,15) description from logs lm left join locations l on lm.place=l.id where lm.time_stamp_closing='0000-00-00 00:00:00' order by timer But, when I omit the 'order by timer' part the result set is ok. The same query works fine on MySQL 4.1.10 with the same data. How to repeat: Create the tables below and execute the query in the description. DROP TABLE IF EXISTS `helpdesk`.`logs`; CREATE TABLE `helpdesk`.`logs` ( `id` int(11) NOT NULL auto_increment, `message_from` int(11) NOT NULL default '0', `message_to` int(11) NOT NULL default '0', `message_cc1` int(11) NOT NULL default '0', `message_cc2` int(11) NOT NULL default '0', `time_stamp` datetime NOT NULL default '0000-00-00 00:00:00', `time_stamp_trouble` datetime NOT NULL default '0000-00-00 00:00:00', `time_stamp_opening` datetime NOT NULL default '0000-00-00 00:00:00', `time_stamp_closing` datetime NOT NULL default '0000-00-00 00:00:00', `category_id` int(11) NOT NULL default '0', `human_error` enum('t','f') NOT NULL default 'f', `network_error` enum('t','f') NOT NULL default 'f', `message_number` varchar(64) NOT NULL default '', `contact_person1` int(11) NOT NULL default '0', `contact_person2` int(11) NOT NULL default '0', `place` int(11) NOT NULL default '0', `description` longtext NOT NULL, `actions_taken` longtext NOT NULL, `uid` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin5; DROP TABLE IF EXISTS `helpdesk`.`locations`; CREATE TABLE `helpdesk`.`locations` ( `id` int(11) NOT NULL auto_increment, `alternate_id` int(11) NOT NULL default '0', `name` varchar(255) NOT NULL default '', `alias` varchar(64) NOT NULL default '', `city` int(11) NOT NULL default '0', `province` varchar(32) NOT NULL default '', `parent` int(11) NOT NULL default '0', `timer` time NOT NULL default '00:00:00', `timer_close` time NOT NULL default '00:00:00', PRIMARY KEY (`id`), UNIQUE KEY `unq_center` (`name`,`city`), KEY `parent` (`parent`) ) ENGINE=MyISAM DEFAULT CHARSET=latin5; INSERT INTO `locations` (`id`,`alternate_id`,`name`,`alias`,`city`,`province`,`parent`,`timer`,`timer_close`) VALUES (1,1,'Bilkent University','Bilkent',7,'Merkez',0,'04:00:00','24:00:00'); INSERT INTO `logs` (`id`,`message_from`,`message_to`,`message_cc1`,`message_cc2`,`time_stamp`,`time_stamp_trouble`,`time_stamp_opening`,`time_stamp_closing`,`category_id`,`human_error`,`network_error`,`message_number`,`contact_person1`,`contact_person2`,`place`,`description`,`actions_taken`,`uid`) VALUES (1551,132,245,0,0,'2005-12-14 16:07:38','2005-12-14 11:03:29','2005-12-14 11:03:29','2005-12-14 16:07:43',117,'f','f','',264,0,1,'Test Error','',21), (1552,197,245,204,17,'2005-12-14 20:27:50','2005-12-14 20:26:05','2005-12-14 20:26:05','0000-00-00 00:00:00',0,'f','f','555',126,57,1,'üğişçöıÜĞİŞÇÖI','üğişçöıÜĞİŞÇÖI',1);