[MDEV-5078] Incorrect 'Subquery returns more than 1 row' Created: 2013-09-27  Updated: 2013-10-04  Resolved: 2013-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.4, 5.3.12, 5.5.33a
Fix Version/s: 5.5.34, 10.0.6, 5.3.13

Type: Bug Priority: Major
Reporter: Gijs van der Ent (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None
Environment:

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



 Comments   
Comment by Elena Stepanova [ 2013-10-01 ]

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.

Comment by Elena Stepanova [ 2013-10-01 ]

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`

Comment by Igor Babaev [ 2013-10-04 ]

The fix for the bug was pushed into the 5.3 tree.

Generated at Thu Feb 08 07:01:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.