P1-[4.0 bug hunting]-[SQL Plan Management]-INSERT INTO with ON DUPLICATE clause results in an unexpected "key not exist" error

[mrigger]

Consider the following statements:

CREATE TABLE t0(c0 INT AS ('a') UNIQUE, c1 INT);
INSERT IGNORE INTO t0(c1) VALUES (0);
DELETE FROM t0;
INSERT IGNORE INTO t0(c1) VALUES (0) ON DUPLICATE KEY UPDATE c1=t0.c0; -- Error: key not exist

I would expect the statements to execute without errors. However, TiDB prints an error key not exist:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-TiDB- TiDB Server (Apache License 2.0), MySQL 5.7 compatible

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t0(c0 INT AS ('a') VIRTUAL UNIQUE, c1 INT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT IGNORE INTO t0(c1) VALUES (0);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> DELETE FROM t0;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO t0(c1) VALUES (0) ON DUPLICATE KEY UPDATE c1=t0.c0; -- Error: key not exist
ERROR 8021 (HY000): Error: key not exist

When using MySQL 8.0.19, the INSERT executes without an error:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t0(c0 INT AS ('a') VIRTUAL UNIQUE, c1 INT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT IGNORE INTO t0(c1) VALUES (0);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> DELETE FROM t0;
Query OK, 1 row affected (0.01 sec)

mysql> INSERT IGNORE INTO t0(c1) VALUES (0) ON DUPLICATE KEY UPDATE c1=t0.c0; -- Error: key not exist
Query OK, 1 row affected, 1 warning (0.00 sec)

I found this based on the latest master commit 187f225002b67daa47992816f6ef5ddb76b0f68a, and also checked that this reproduces on the 4.0 RC.

[shuke987]

/bug P1

[jackysp]

I think it is fixed by https://github.com/pingcap/tidb/pull/17217. I've just tested it.

mysql> CREATE TABLE t0(c0 INT AS ('a') UNIQUE, c1 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT IGNORE INTO t0(c1) VALUES (0);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> DELETE FROM t0;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO t0(c1) VALUES (0) ON DUPLICATE KEY UPDATE c1=t0.c0;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1292 | Truncated incorrect FLOAT value: 'a' |
+---------+------+--------------------------------------+
1 row in set (0.00 sec)

Could it be closed? @shuke987 @mrigger

[mrigger]

Thanks for fixing! Closing the issue works for me. Would you consider this bug report to be a duplicate bug report?

[jackysp]

Yes, it does duplicate bug report, it just behaves differently. However, I found that I couldn't change the label of the issue. Maybe @shuke987 can help tag it and close the issue.