Bug #83286 Invalid error for Subquery returns more than 1 row

Description:
I am receiving an error when running the below SQL in MySQL 5.7.  It runs cleanly in MySQL 5.6 with no errors.

The error is:
ERROR 1242 (21000) at line 27: Subquery returns more than 1 row

If I split the last insert into 2 separate inserts, then it runs cleanly in MySQL 5.7.

How to repeat:
DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;
DROP TABLE IF EXISTS table3;

CREATE TABLE table3 (
  table3_id int NOT NULL AUTO_INCREMENT,
  table3_name varchar(16) NOT NULL,
  PRIMARY KEY (table3_id)
) ENGINE=InnoDB;

CREATE TABLE table2 (
  table2_id int NOT NULL AUTO_INCREMENT,
  table3_id int NOT NULL,
  table2_name varchar(16) NOT NULL,
  PRIMARY KEY (table2_id)
) ENGINE=InnoDB;

CREATE TABLE table1 (
  table1_id int NOT NULL AUTO_INCREMENT,
  table2_id int NOT NULL,
  PRIMARY KEY (table1_id)
) ENGINE=InnoDB;

INSERT INTO table3 (table3_name) VALUES ('Test1'),('Test2');
INSERT INTO table2 (table3_id, table2_name) VALUES (1, 'Test3');

INSERT INTO table1 (table2_id) VALUES
  ((SELECT table2_id FROM table3 JOIN table2 USING (table3_id) WHERE table2_name = 'Test3')),
  ((SELECT table2_id FROM table3 JOIN table2 USING (table3_id) WHERE table2_name = 'Test3'));

Thank you for the bug report.

mysql 5.7 > INSERT INTO table1 (table2_id) VALUES
    ->   ((SELECT table2_id FROM table3 JOIN table2 USING (table3_id) WHERE table2_name = 'Test3')),
    ->   ((SELECT table2_id FROM table3 JOIN table2 USING (table3_id) WHERE table2_name = 'Test3'));
ERROR 1242 (21000): Subquery returns more than 1 row

mysql 5.7 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------------------------+
| Variable_name           | Value                                 |
+-------------------------+---------------------------------------+
| innodb_version          | 5.7.17                                |
| protocol_version        | 10                                    |
| slave_type_conversions  |                                       |
| tls_version             | TLSv1,TLSv1.1                         |
| version                 | 5.7.17                                |
| version_comment         | Source distribution PULL: 2016-OCT-03 |
| version_compile_machine | x86_64                                |
| version_compile_os      | Win64                                 |
+-------------------------+---------------------------------------+
8 rows in set (0.02 sec)

mysql 5.6 > INSERT INTO table1 (table2_id) VALUES
    ->   ((SELECT table2_id FROM table3 JOIN table2 USING (table3_id) WHERE table2_name = 'Test3')),
    ->   ((SELECT table2_id FROM table3 JOIN table2 USING (table3_id) WHERE table2_name = 'Test3'));
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.6 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------------------------+
| Variable_name           | Value                                 |
+-------------------------+---------------------------------------+
| innodb_version          | 5.6.35                                |
| protocol_version        | 10                                    |
| slave_type_conversions  |                                       |
| version                 | 5.6.35                                |
| version_comment         | Source distribution PULL: 2016-OCT-03 |
| version_compile_machine | x86_64                                |
| version_compile_os      | Win64                                 |
+-------------------------+---------------------------------------+
7 rows in set (0.05 sec)

mysql 5.5 > INSERT INTO table1 (table2_id) VALUES
    ->   ((SELECT table2_id FROM table3 JOIN table2 USING (table3_id) WHERE table2_name = 'Test3')),
    ->   ((SELECT table2_id FROM table3 JOIN table2 USING (table3_id) WHERE table2_name = 'Test3'));
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.5 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------------------------+
| Variable_name           | Value                                 |
+-------------------------+---------------------------------------+
| innodb_version          | 5.5.54                                |
| protocol_version        | 10                                    |
| slave_type_conversions  |                                       |
| version                 | 5.5.54                                |
| version_comment         | Source distribution PULL: 2016-OCT-03 |
| version_compile_machine | AMD64                                 |
| version_compile_os      | Win64                                 |
+-------------------------+---------------------------------------+
7 rows in set (0.00 sec)

Posted by developer:
 
The statements don't fail on trunk (8.0.1) after WL#5094 - Create SQL command classes for DML statements.

The regression was introduced in 5.7 by WL#5275 - Process subqueries in FROM clause in the same way as view.

Posted by developer:
 
Fixed in 5.7.29