Bug #24947 REPEAT function returns NULL when passed a field as the count parameter
Submitted: 10 Dec 2006 21:01 Modified: 21 Dec 2006 19:47
Reporter: Ameet Shah Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.32-nightly-20061207/5.1BK OS:FreeBSD (FreeBSD 6.1)
Assigned to: Ramil Kalimullin

[10 Dec 2006 21:01] Ameet Shah
Description:
When calling the REPEAT function with a field for the count parameter (i.e. REPEAT('a',frequency)), the function returns a null value and the following warning is produced:

Result of repeat() was larger than max_allowed_packet (5241856) - truncated

The function returns the correct result if a numerical value is used directly within the function call rather than referring to an integer field. The problem is not present in the stable release of 5.0.27.

How to repeat:
CREATE TABLE test1(frequency tinyint(1) unsigned not null default 0);
INSERT INTO test1 VALUES (1);
SELECT REPEAT('a',frequency) FROM test1;
[10 Dec 2006 21:41] Miguel Solorzano
Thank you for the bug report.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.32-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE test1(frequency tinyint(1) unsigned not null default 0);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test1 VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT REPEAT('a',frequency) FROM test1;
+-----------------------+
| REPEAT('a',frequency) |
+-----------------------+
| NULL                  | 
+-----------------------+
1 row in set, 1 warning (0.00 sec)

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

mysql> 
---------------------------------------------------------------------------------------------

miguel@hegel:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.14-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE test1(frequency tinyint(1) unsigned not null default 0);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT REPEAT('a',frequency) FROM test1;
+-----------------------+
| REPEAT('a',frequency) |
+-----------------------+
| NULL                  | 
+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 
-----------------------------------------------------------------------------------------
miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE test1(frequency tinyint(1) unsigned not null default 0);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test1 VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT REPEAT('a',frequency) FROM test1;
+-----------------------+
| REPEAT('a',frequency) |
+-----------------------+
| a                     |
+-----------------------+
1 row in set (0.02 sec)

mysql> 
-------------------------------------------------------------------------------------------
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> CREATE TABLE test1(frequency tinyint(1) unsigned not null default 0);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO test1 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT REPEAT('a',frequency) FROM test1;
+-----------------------+
| REPEAT('a',frequency) |
+-----------------------+
| a                     |
+-----------------------+
1 row in set (0.01 sec)

mysql>
[19 Dec 2006 22:54] 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/17188

ChangeSet@1.2353, 2006-12-19 15:54:12-07:00, tsmith@siva.hindu.god +3 -0
  Bug #24947: REPEAT function returns NULL when passed a field as the count parameter
  
  Handling of large signed/unsigned values was not consistent, so some string functions could return bogus results.
  The current fix is to simply patch up the val_str() methods for those string items.
  It would be good clean this code up in general, to make similar problems much harder to make.  This is left as an exercise for the reader.
[20 Dec 2006 10:34] Joerg Bruehe
Fix is included in the 5.0.32 release build already,
but not yet merged to main trees - 
please reset status after mentioning it for 5.0.32.
[21 Dec 2006 14:37] Joerg Bruehe
Fix is also in the 5.0 and 5.1 main trees, will be in 5.1.15.
[21 Dec 2006 19:47] Paul Dubois
Noted in 5.0.32, 5.1.15 changelogs.

The REPEAT() function could return NULL when passed a column for the
count argument.