MySQL Bugs: #23842: Unsigned check on "ON DUPLICATE KEY" query

Bug #23842	Unsigned check on "ON DUPLICATE KEY" query
Submitted:	1 Nov 2006 15:33	Modified:	2 Nov 2006 3:52
Reporter:	winzou w	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.27-community-nt	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I have a table with 2 columns : 1 primary key (named "key") and 1 unsigned int (named "col").

For my example, suppose that y > 0, and y < (SELECT MIN(col) FROM table). So (col - y) is always positive.
When I send a query like "INSERT INTO table (key, col) VALUES (x, -y) ON DUPLICATE KEY UPDATE col = col + VALUES(col)", MySQL returns me an error : "#1264 - Out of range value adjusted for column 'col' at row 1".

But, as we have seen above, col + (-y) is positive, so the value is _not_ out of range !

If the second column is signed, then the query works perfectly.

How to repeat:
CREATE TABLE gp_unt (
  unt_id tinyint(3) unsigned NOT NULL,
  unt_nb smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (unt_id),
  KEY unt_nb (unt_nb)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO gp_unt (unt_id, unt_nb) VALUES (1, 6)

And then, try this query :

INSERT INTO gp_unt (unt_id, unt_nb) VALUES (1, -1)
ON DUPLICATE KEY UPDATE unt_nb = unt_nb + VALUES(unt_nb)

Suggested fix:
I think it is due to the order of operations.

Indeed, when MySQL try to insert the line, it sees a negative value for an unsigned column. But it should look the end of the query (which, here, replaces the negative value by a positive one) before returns the error.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.27 and inform about the results. In case of the same problem, please, send the results of:

SHOW VARIABLES LIKE 'sql_%'\G

Sorry I've just tried with MySQL 5.0.27-community-nt, but it's the same problem.

Here the result of your request :

mysql> SHOW VARIABLES LIKE 'sql_%'\G
*************************** 1. row ***************************
Variable_name: sql_big_selects
        Value: ON
*************************** 2. row ***************************
Variable_name: sql_mode
        Value: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
*************************** 3. row ***************************
Variable_name: sql_notes
        Value: ON
*************************** 4. row ***************************
Variable_name: sql_warnings
        Value: OFF
4 rows in set (0.00 sec)

Thank you

the order of operations performed on the SQL level is:

#1 check field values against field types
#2 perform insert (using handler interface)
#3 if insert failed with duplicate key error 
   perform ON DUPLICATE KEY part

there is no reason to go into steps #2 and #3 if #1 already failed