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

[6 Mar 2019 21:06] Michael Seydel
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)
[6 Mar 2019 21:41] MySQL Verification Team
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 >
[6 Mar 2019 21:46] Michael Seydel
Thanks for the quick reply! Yes, that's what I expected. It looks like this was fixed between your version and mine.
[6 Mar 2019 22:10] MySQL Verification Team
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.