Details
Major
Description
Subquery in result column complains about returning multiple rows even though an aggregation is done over the rows.
CREATE TABLE t1 ( a INT(11) ); INSERT INTO t1 (a) VALUES (1), (2); CREATE TABLE t2 ( b INT(11) ); INSERT INTO t2 (b) VALUES (1), (2); SELECT a, ( SELECT SUM(a + c) FROM ( SELECT b as c FROM t2 ) AS v1 ) FROM t1;
Result:
ERROR 1242 (21000): Subquery returns more than 1 row
Expected
a subquery 1 5 2 7
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`,sum((`test`.`t1`.`a` + `test`.`t2`.`b`)),sum((`test`.`t1`.`a` + `test`.`t2`.`b`))>((select sum((`test`.`t1`.`a` + `test`.`t2`.`b`)) from `test`.`t2`)) AS `(
SELECT
SUM(a + c)
FROM
(
SELECT
b as c
FROM
t2
) AS v1
)` from `test`.`t1`
The fix for the bug was pushed into the 5.3 tree.
Thank you.
As a workaround, you can temporarily set derived_merge=off in your optimizer_switch:
optimizer_switch=derived_merge=off'
^ in the config file for future server starts
and/or
SET GLOBAL optimizer_switch = 'derived_merge=off';
SET optimizer_switch = 'derived_merge=off';
^ from the client for the currently running server.