Bug #21555 incorrect behavior with INSERT ... ON DUPL KEY UPDATE and VALUES

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.

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

See also bug: http://bugs.mysql.com/bug.php?id=22026

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.

Pushed into 4.1.22/5.0.26/5.1.12-beta

Noted in 4.1.22, 5.0.26, 5.1.12 changelogs.

Hi,

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

Thanks,
  Jocelyn

> 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 ?