Bug #21555 incorrect behavior with INSERT ... ON DUPL KEY UPDATE and VALUES
Submitted: 10 Aug 2006 1:34 Modified: 26 Sep 2006 20:03
Reporter: Jason Novak Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1, 5.0, 5.1 OS:FreeBSD (FreeBSD 4.10, Linux)
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: values duplicate key bigint unsigned

[10 Aug 2006 1:34] Jason Novak
Description:
I'm trying to implement a conditional insert-or-update, but MySQL is not handling the query properly.

I have a simplified example that breaks for various values of sequence ID. bug2.sql (see "How to repeat") shows that the server will both ignore updates with a greater sequence ID, and accept updates with a lesser sequence ID. bug1.sql has the right behavior.

I believe it's an interaction between the VALUES() function and BIGINT UNSIGNED, because

  % sed 's/BIGINT/INT/g' bug2.sql | mysql -u root

does the right thing.

Is this a known bug? Is there a workaround, other than SELECT before REPLACE INTO?

% mysql -u root < bug1.sql # correct
campaign_id     sequence_id
45      1
campaign_id     sequence_id
45      2
campaign_id     sequence_id
45      2
campaign_id     sequence_id
45      19
campaign_id     sequence_id
45      19

% mysql -u root < bug2.sql # broken
campaign_id     sequence_id
45      1
campaign_id     sequence_id
45      2
campaign_id     sequence_id
45      2
campaign_id     sequence_id
45      2
campaign_id     sequence_id
45      15

How to repeat:
# bug2.sql (broken)
CREATE DATABASE IF NOT EXISTS bug;
USE bug;

CREATE TABLE IF NOT EXISTS t1
(
  the_key       BIGINT UNSIGNED NOT NULL,
  sequence_id   BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY   (the_key)
) TYPE = MyISAM;

TRUNCATE TABLE t1;

INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE sequence_id = IF(VALUES(sequence_id) > t1.sequence_id, VALUES(sequence_id), t1.sequence_id);
SELECT * FROM t1;
INSERT INTO t1 VALUES (45, 2) ON DUPLICATE KEY UPDATE sequence_id = IF(VALUES(sequence_id) > t1.sequence_id, VALUES(sequence_id), t1.sequence_id);
SELECT * FROM t1;INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE sequence_id = IF(VALUES(sequence_id) > t1.sequence_id, VALUES(sequence_id), t1.sequence_id);
SELECT * FROM t1;
INSERT INTO t1 VALUES (45, 19) ON DUPLICATE KEY UPDATE sequence_id = IF(VALUES(sequence_id) > t1.sequence_id, VALUES(sequence_id), t1.sequence_id);
SELECT * FROM t1;
INSERT INTO t1 VALUES (45, 15) ON DUPLICATE KEY UPDATE sequence_id = IF(VALUES(sequence_id) > t1.sequence_id, VALUES(sequence_id), t1.sequence_id);
SELECT * FROM t1;

DROP DATABASE bug;

# bug1.sql (correct)
CREATE DATABASE IF NOT EXISTS bug;
USE bug;

CREATE TABLE IF NOT EXISTS t1
(
  the_key       BIGINT UNSIGNED NOT NULL,  sequence_id   BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY   (the_key)
) TYPE = MyISAM;
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE sequence_id = IF(1 > t1.sequence_id, 1, t1.sequence_id);
SELECT * FROM t1;
INSERT INTO t1 VALUES (45, 2) ON DUPLICATE KEY UPDATE sequence_id = IF(2 > t1.sequence_id, 2, t1.sequence_id);
SELECT * FROM t1;
INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE sequence_id = IF(1 > t1.sequence_id, 1, t1.sequence_id);
SELECT * FROM t1;
INSERT INTO t1 VALUES (45, 19) ON DUPLICATE KEY UPDATE sequence_id = IF(19 > t1.sequence_id, 19, t1.sequence_id);
SELECT * FROM t1;
INSERT INTO t1 VALUES (45, 15) ON DUPLICATE KEY UPDATE sequence_id = IF(15 > t1.sequence_id, 4, t1.sequence_id);
SELECT * FROM t1;

DROP DATABASE bug;

Suggested fix:
Numeric values in INSERT statements should be parsed as strtoull (str, NULL, 0) would, if the corresponding column is BIGINT UNSIGNED.

The VALUES(field) function should interact properly with fields of type BIGINT UNSIGNED.

The behavior for less-than operator on BIGINT UNSIGNED should be identical to a comparison of two unsigned long long values in C.
[10 Aug 2006 8:56] Sveta Smirnova
Thank you for the report.

Verified on Linux using 4.1, 5.0 and 5.1 BK sources with different result:

$bin/mysql --socket=/tmp/mysql.sock -uroot < bug2.sql
the_key sequence_id
45      1
the_key sequence_id
45      1
the_key sequence_id
45      1
the_key sequence_id
45      1
the_key sequence_id
45      15

On Windows results same as on Linux.

On Solaris results as in original report:

% mysql -u root < bug2.sql
campaign_id     sequence_id
45      1
campaign_id     sequence_id
45      2
campaign_id     sequence_id
45      2
campaign_id     sequence_id
45      2
campaign_id     sequence_id
45      15
[5 Sep 2006 17:42] MySQL Verification Team
See also bug: http://bugs.mysql.com/bug.php?id=22026
[8 Sep 2006 7:24] 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/11596

ChangeSet@1.2566, 2006-09-08 10:24:14+03:00, gkodinov@macbook.gmz +3 -0
  Bug#21555: incorrect behavior with INSERT ... ON DUPL KEY UPDATE and VALUES
   VALUES() was considered a constant. This caused replacing 
   (or pre-calculating) it using uninitialized values before the actual
   execution takes place.
   Mark it as a non-constant (still not dependent of tables) to prevent
   the pre-calculation.
[19 Sep 2006 8:29] Georgi Kodinov
Pushed into 4.1.22/5.0.26/5.1.12-beta
[26 Sep 2006 20:03] Paul DuBois
Noted in 4.1.22, 5.0.26, 5.1.12 changelogs.
[30 Oct 2006 8:42] jocelyn fournier
Hi,

Could this bug also affect in the same way the OLD.field inside a trigger ?

Thanks,
  Jocelyn
[30 Oct 2006 11:55] Georgi Kodinov
> Could this bug also affect in the same way the OLD.field inside a trigger ?

I wasn't able to find similar error in handling "OLD.field" in triggers. Can you provide an example ?