Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5078

Incorrect 'Subquery returns more than 1 row'

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.4, 5.3.12, 5.5.33a
    • 5.5.34, 10.0.6, 5.3.13
    • 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

          Ascending order - Click to sort in descending order
          gvdent Gijs van der Ent (Inactive) created issue -
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          Assignee Elena Stepanova [ elenst ]
          elenst Elena Stepanova added a comment -

          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.

          elenst Elena Stepanova added a comment - 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.
          elenst Elena Stepanova made changes -
          Fix Version/s 10.0.6 [ 13202 ]
          Fix Version/s 5.5.34 [ 13700 ]
          Fix Version/s 5.3.13 [ 12602 ]
          Affects Version/s 5.3.12 [ 12000 ]
          Affects Version/s 10.0.4 [ 13101 ]
          elenst Elena Stepanova added a comment -

          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`

          elenst Elena Stepanova added a comment - 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`
          elenst Elena Stepanova made changes -
          Assignee Elena Stepanova [ elenst ] Igor Babaev [ igor ]
          igor Igor Babaev (Inactive) added a comment -

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

          igor Igor Babaev (Inactive) added a comment - The fix for the bug was pushed into the 5.3 tree.
          igor Igor Babaev (Inactive) made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 29127 ] MariaDB v2 [ 44128 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 44128 ] MariaDB v3 [ 63398 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 63398 ] MariaDB v4 [ 147078 ]

          People

            igor Igor Babaev (Inactive)
            gvdent Gijs van der Ent (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.