Bug #25197 repeat function returns null when using table field directly as count
Submitted: 20 Dec 2006 6:16 Modified: 13 Mar 2007 19:26
Reporter: Ivan Yeung Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Microsoft Windows (Win XP Pro)
Assigned to: Magnus Blåudd CPU Architecture:Any

[20 Dec 2006 6:16] Ivan Yeung
Description:
If a table field is passed directly to REPEAT function as count parameter, a 1301 warning is generated and the function in effect returns null.

The warning says "Result of repeat() was larger than max_allowed_packet (1048576) - truncated"

Tested on MyISAM, InnoDB, and Memory, all yields same result

I tried both PHP 4 (3.23.49) and mysql console (dunno how to get version...)

How to repeat:
CREATE TABLE `test` (
  `id` varchar(20) NOT NULL,
  `tire` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`id`, `tire`) VALUES ('A', 0),('B', 1),('C', 2);

SELECT REPEAT( '#', tire ) AS A, REPEAT( '#', tire % 999 ) AS B, tire FROM `test`;

Result:

+------+------+------+
| A    | B    | tire |
+------+------+------+
| NULL |      |    0 |
| NULL | #    |    1 |
| NULL | ##   |    2 |
+------+------+------+
3 rows in set, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------+
| Level   | Code | Message                                                                     |
+---------+------+-----------------------------------------------------------------------------+
| Warning | 1301 | Result of repeat() was larger than max_allowed_packet (1048576) - truncated |
| Warning | 1301 | Result of repeat() was larger than max_allowed_packet (1048576) - truncated |
| Warning | 1301 | Result of repeat() was larger than max_allowed_packet (1048576) - truncated |
+---------+------+-----------------------------------------------------------------------------+

Suggested fix:
Workaround: Modifying the statement so that table field is not used directly as parameter, even after optimization.  See test case for example.
[20 Dec 2006 8:58] Hartmut Holzgraefe
mysqltest test case

Attachment: bug25197.tgz (application/x-gtar, text), 945 bytes.

[7 Feb 2007 9:50] Magnus Blåudd
Program received signal SIGFPE, Arithmetic exception.
0x08161ca2 in Item_func_repeat::val_str (this=0x95e0608, str=0xb7df2f5c)
    at item_strfunc.cc:2264
2264      if (length > current_thd->variables.max_allowed_packet / (uint) count)
(gdb) p count
$5 = 0
[7 Feb 2007 11:14] Magnus Blåudd
It seem like this only happens for unsigned fields with the value 0
[7 Feb 2007 14:04] 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/19487

ChangeSet@1.2407, 2007-02-07 15:03:56+01:00, msvensson@pilot.mysql.com +3 -0
  Bug#25197 repeat function returns null when using table field directly as count
   - Return empty string also if count is unsigned and value is 0
[23 Feb 2007 9: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/20443

ChangeSet@1.2407, 2007-02-23 10:28:50+01:00, msvensson@pilot.blaudden +3 -0
  Bug#25197 repeat function returns null when using table field directly as count
   - Return empty string also if count is unsigned and value is 0
[8 Mar 2007 22:04] Timothy Smith
pushed to 5.0.38, 5.1.17
[13 Mar 2007 19:26] Paul DuBois
Noted in 5.0.38, 5.1.17 changelogs.

The REPEAT() function did not allow a column name as the count
parameter.
[26 Mar 2007 18:32] Igor Babaev
The patch also has fixed bug#27073 (another manifestation of the same problem).
[28 Mar 2007 7:33] 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/23104

ChangeSet@1.2408, 2007-03-28 09:32:49+02:00, msvensson@pilot.blaudden +3 -0
  Bug#25197 repeat function returns null when using table field directly as count
   - Add extra test case from bug#27073
   - Change "if" to be optimized for count > 0
[29 Mar 2007 10:22] Valeriy Kravchuk
Bug 27514 was marked as a duplicate of this one.
[6 Apr 2007 17:21] Bugs System
Pushed into 5.0.40
[6 Apr 2007 17:24] Bugs System
Pushed into 5.1.18-beta