Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.4, 5.3.12, 5.5.33a
-
None
-
None
-
Windows Vista, 32 bit
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
|
Attachments
Activity
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`
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.