MySQL Bugs: #33851: Passing UNSIGNED param to EXECUTE returns ERROR 1210

Bug #33851	Passing UNSIGNED param to EXECUTE returns ERROR 1210
Submitted:	13 Jan 2008 18:15	Modified:	15 Mar 2008 10:53
Reporter:	Ross Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.0.54, 5.0.45-community-nt-log	OS:	Windows (XP SP2)
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	1210, regression

Description:
Executing the following in 5.0.45:

DROP PROCEDURE IF EXISTS e1210;
DELIMITER //
CREATE PROCEDURE e1210(u_rows INT UNSIGNED)
BEGIN
  SET @u_rows  = IFNULL(u_rows, 10);
  PREPARE stmt FROM "SELECT * FROM mysql.user LIMIT ?";
  EXECUTE stmt USING @u_rows;
  DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;
CALL e1210(10);

produces

ERROR 1210 (HY000): Incorrect arguments to EXECUTE

This problem was not in 5.0.41.

How to repeat:
See above.

Suggested fix:
There are several workarounds:

1. Replace

  SET @u_rows = IFNULL(u_rows, 10);

with

  SET @u_rows = u_rows;
  IF @u_rows IS NULL THEN
    SET @u_rows = 10;
  END IF;

2. Replace

  SET @u_rows = IFNULL(u_rows, 10);

with

  SET @u_rows = CONVERT(IFNULL(u_rows, 10), SIGNED);

3. Change u_rows to an INT or INT SIGNED parameter.

Thank you for a bug report. Verified just as described with MySQL 5.0.54 also.

I think http://bugs.mysql.com/bug.php?id=33859 refers to the same bug

A reduced test case:

  SET @limit  = CONVERT(10, DECIMAL);
  PREPARE stmt FROM "SELECT 1 LIMIT ?";
  EXECUTE stmt USING @limit;
  DEALLOCATE PREPARE stmt;

Regression caused by a fix for Bug#28464

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/43125

ChangeSet@1.2591, 2008-02-27 23:59:18-03:00, davi@mysql.com +9 -0
  Bug#33851 Passing UNSIGNED param to EXECUTE returns ERROR 1210
  
  The problem is that passing anything other than a integer to a limit
  clause in a prepared statement would fail. This limitation was introduced
  to avoid replication problems (e.g: replicating the statement with a
  string argument would cause a parse failure in the slave).
  
  The solution is to convert arguments to the limit clause to a integer
  value and use this converted value when persisting the query to the log.

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/43167

ChangeSet@1.2591, 2008-02-28 11:34:08-03:00, davi@mysql.com +11 -0
  Bug#33851 Passing UNSIGNED param to EXECUTE returns ERROR 1210
  
  The problem is that passing anything other than a integer to a limit
  clause in a prepared statement would fail. This limitation was introduced
  to avoid replication problems (e.g: replicating the statement with a
  string argument would cause a parse failure in the slave).
  
  The solution is to convert arguments to the limit clause to a integer
  value and use this converted value when persisting the query to the log.

Queued in 5.0-runtime

Note that if the converted or the original value is negative, the error will be thrown (1210 - Incorrect arguments to EXECUTE).

Pushed into 5.0.58

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Documented bugfix in the 5.0.58, 5.1.24, and 6.0.5 changelogs as follows:

        Passing anything other than a integer to a LIMIT clause in a prepared
        statement would fail. (This limitation was introduced to avoid
        replication problems; for example, replicating the statement with a
        string argument would cause a parse failure in the slave). Now,
        arguments to the LIMIT clause are converted to integer values, and these
        converted values are used when logging the statement.

Changed category from SP to PS to reflect the actual circumstances of the bug.

Fix also noted in the 5.1.23-ndb-6.3.11 changelog.