Error subquery returns more than 1 row for insert on dup

[jackysp]

Description

Bug Report
Please answer these questions before submitting your issue. Thanks!

What did you do?
If possible, provide a recipe for reproducing the error.
CREATE TABLE t1(a INTEGER);
CREATE TABLE t11(a INTEGER primary key);
CREATE TABLE t2(b INTEGER);
INSERT INTO t2 VALUES (1),(1);

INSERT INTO t1(a) VALUES (1)
ON DUPLICATE KEY UPDATE a= (SELECT b FROM t2);

INSERT INTO t11(a) VALUES (1) ON DUPLICATE KEY UPDATE a= (SELECT b FROM t2);

DROP TABLE t1, t2;
What did you expect to see
MySQL behavior:
for inserting into t1

INSERT INTO t1(a) VALUES (1) ON DUPLICATE KEY UPDATE a= (SELECT b FROM t2);
Query OK, 1 row affected (0.01 sec)
INSERT INTO t1(a) VALUES (1) ON DUPLICATE KEY UPDATE a= (SELECT b FROM t2);
Query OK, 1 row affected (0.01 sec)

for inserting into t11
INSERT INTO t11(a) VALUES (1) ON DUPLICATE KEY UPDATE a= (SELECT b FROM t2);
Query OK, 1 row affected (0.02 sec)

INSERT INTO t11(a) VALUES (1) ON DUPLICATE KEY UPDATE a= (SELECT b FROM t2);
ERROR 1105 (HY000): subquery returns more than 1 row

TiDB behvaior:
for inserting into t1
mysql> INSERT INTO t1(a) VALUES (1) ON DUPLICATE KEY UPDATE a= (SELECT b FROM t2);
ERROR 1105 (HY000): subquery returns more than 1 row
mysql> INSERT INTO t1(a) VALUES (1) ON DUPLICATE KEY UPDATE a= (SELECT b FROM t2);
ERROR 1105 (HY000): subquery returns more than 1 row

for inserting into t11
mysql> INSERT INTO t11(a) VALUES (1) ON DUPLICATE KEY UPDATE a= (SELECT b FROM t2);
ERROR 1105 (HY000): subquery returns more than 1 row
Create issue (#10151) from GitHub user jackysp at 16:26 PM, April 15 2019

SIG slack channel

#sig-planner

Score

• 300

Mentor

• @lzmhhh123

[ghost]

Confirming this still exists in master:

DROP TABLE IF EXISTS t1, t11, t2;
CREATE TABLE t1(a INTEGER);
CREATE TABLE t11(a INTEGER primary key);
CREATE TABLE t2(b INTEGER);
INSERT INTO t2 VALUES (1),(1);

INSERT INTO t1(a) VALUES (1)
ON DUPLICATE KEY UPDATE a= (SELECT b FROM t2);

INSERT INTO t11(a) VALUES (1) ON DUPLICATE KEY UPDATE a= (SELECT b FROM t2);


..

mysql> INSERT INTO t1(a) VALUES (1)
    -> ON DUPLICATE KEY UPDATE a= (SELECT b FROM t2);
ERROR 1105 (HY000): subquery returns more than 1 row
mysql> 
mysql> INSERT INTO t11(a) VALUES (1) ON DUPLICATE KEY UPDATE a= (SELECT b FROM t2);
ERROR 1105 (HY000): subquery returns more than 1 row

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-750-g8a661044c
Edition: Community
Git Commit Hash: 8a661044cedf8daad1de4fbf79a390962b6f6c3b
Git Branch: master
UTC Build Time: 2020-07-10 10:52:37
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[dyzsr]

/assign

[winoros]

should fixed by #29270

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.