| Bug #83286 | Invalid error for Subquery returns more than 1 row | ||
|---|---|---|---|
| Submitted: | 6 Oct 2016 12:54 | Modified: | 2 Jan 2020 22:57 |
| Reporter: | Ryan Brothers | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[6 Oct 2016 18:52]
MySQL Verification Team
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)
[7 Oct 2016 9:53]
Knut Anders Hatlen
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.
[2 Jan 2020 22:57]
Roy Lyseng
Posted by developer: Fixed in 5.7.29
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'));