MySQL Bugs: #94582: Primary key 1 causes ON DUPLICATE KEY UPDATE to use erroneous value

Bug #94582	Primary key 1 causes ON DUPLICATE KEY UPDATE to use erroneous value
Submitted:	6 Mar 2019 21:06	Modified:	6 Mar 2019 22:10
Reporter:	Michael Seydel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have a situation where I am trying to copy data from one table to another identical table. An initial insert was performed, however subsequent changes need to be copied over.

When testing my query for doing so, I found that when the int primary key was 1, that the updated value was incorrect. I observe the expected behavior if I change the id from 1 to 10. Here is the query:

INSERT INTO dst (id, status_id)
  SELECT
    s.id,
    s.status_id
  FROM src s
    LEFT JOIN dst d on s.id = d.id
  WHERE d.status_id is null or d.status_id != s.status_id
ON DUPLICATE KEY UPDATE status_id = s.status_id;

Please see "How to repeat" for a full reproducible example.

How to repeat:
-- create a couple simple tables

DROP TABLE IF EXISTS src;
CREATE TABLE src
(
  id        INT PRIMARY KEY,
  status_id INT DEFAULT '1'
)
  engine = InnoDB;

DROP TABLE IF EXISTS dst;
CREATE TABLE dst LIKE src;

-- initial population
INSERT INTO src VALUES
  (0, 0),
  (1, 1);

-- initial copy over
INSERT INTO dst
  SELECT
    id,
    status_id
  FROM src;

-- selects to sanity check data

SELECT
  id,
  status_id
FROM src
ORDER BY id;

SELECT *
FROM dst
ORDER BY id;

-- insert new values to add, and modify one row

INSERT INTO src VALUES
  (2, 2),
  (3, 3);

UPDATE src
SET status_id = -1
WHERE id = 1;

-- another sanity check

SELECT
  id,
  status_id
FROM src
ORDER BY id;

-- this should bring dst up to date with src, but is where the bug is
INSERT INTO dst (id, status_id)
  SELECT
    s.id,
    s.status_id
  FROM src s
    LEFT JOIN dst d on s.id = d.id
  WHERE d.status_id is null or d.status_id != s.status_id
ON DUPLICATE KEY UPDATE status_id = s.status_id;

SELECT *
FROM dst
ORDER BY id; -- expecting (0, -1, 2, 3), but getting (0, 3, 2, 3)

Thank you for the bug report. Below is what are you expecting?

mysql 8.0 > INSERT INTO dst (id, status_id)
    ->   SELECT
    ->     s.id,
    ->     s.status_id
    ->   FROM src s
    ->     LEFT JOIN dst d on s.id = d.id
    ->   WHERE d.status_id is null or d.status_id != s.status_id
    -> ON DUPLICATE KEY UPDATE status_id = s.status_id;
Query OK, 4 rows affected (0.01 sec)
Records: 3  Duplicates: 1  Warnings: 0

mysql 8.0 > SELECT *
    -> FROM dst
    -> ORDER BY id; -- expecting (0, -1, 2, 3), but getting (0, 3, 2, 3)
+----+-----------+
| id | status_id |
+----+-----------+
|  0 |         0 |
|  1 |        -1 |
|  2 |         2 |
|  3 |         3 |
+----+-----------+
4 rows in set (0.00 sec)

mysql 8.0 > SHOW VARIABLES LIKE "%VERSION%";
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| immediate_server_version | 999999                                 |
| innodb_version           | 8.0.16                                 |
| original_server_version  | 999999                                 |
| protocol_version         | 10                                     |
| slave_type_conversions   |                                        |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2                  |
| version                  | 8.0.16                                 |
| version_comment          | Source distribution BUILD: 2019-FEB-20 |
| version_compile_machine  | x86_64                                 |
| version_compile_os       | Win64                                  |
| version_compile_zlib     | 1.2.11                                 |
+--------------------------+----------------------------------------+
11 rows in set (0.01 sec)

mysql 8.0 >

Thanks for the quick reply! Yes, that's what I expected. It looks like this was fixed between your version and mine.

Thank you for the feedback. Indeed current released version with wrong result:
    ->   SELECT
    ->     s.id,
    ->     s.status_id
    ->   FROM src s
    ->     LEFT JOIN dst d on s.id = d.id
    ->   WHERE d.status_id is null or d.status_id != s.status_id
    -> ON DUPLICATE KEY UPDATE status_id = s.status_id;
Query OK, 4 rows affected (0.01 sec)
Records: 3  Duplicates: 1  Warnings: 0

mysql> SELECT *
    -> FROM dst
    -> ORDER BY id; -- expecting (0, -1, 2, 3), but getting (0, 3, 2, 3)
+----+-----------+
| id | status_id |
+----+-----------+
|  0 |         0 |
|  1 |         3 |
|  2 |         2 |
|  3 |         3 |
+----+-----------+
4 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE "%VERSION%";
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| immediate_server_version | 999999                       |
| innodb_version           | 8.0.15                       |
| original_server_version  | 999999                       |
| protocol_version         | 10                           |
| slave_type_conversions   |                              |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2        |
| version                  | 8.0.15                       |
| version_comment          | MySQL Community Server - GPL |
| version_compile_machine  | x86_64                       |
| version_compile_os       | Win64                        |
| version_compile_zlib     | 1.2.11                       |
+--------------------------+------------------------------+
11 rows in set (0.00 sec)

Closing however since expected result in most recent source server so it was fixed somewhat.