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

No 'subquery returns more than one row' error returned but faulty result set

    XMLWordPrintable

    Details

      Description

      Mariadb 10.4.11 does not always detect a select subquery returning more than one row.
      With the included (admittedly weird) query either 4 or 3 or 2 records are returned.
      When substituting the group by field in the subquery by an other identifier an expected error is returned.

      In Mariadb 10.3.17 no error is returned but it seems that the order of the group_concat on strings is implicit and always the same. Adding GROUP_CONCAT(DISTINCT rel.code ORDER BY rel.code) will obtain that same result in 10.4.11.

       
      DROP TABLE IF EXISTS `persn`;
      CREATE TABLE `persn` (
        `id` int(10) unsigned NOT NULL,
        `name` varchar(64) NOT NULL,
        PRIMARY KEY (`id`) USING BTREE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
      INSERT INTO `persn` VALUES (1,'Frank'),(2,'Julie');
       
      DROP TABLE IF EXISTS `rel`;
      CREATE TABLE `rel` (
        `id` int(11) unsigned NOT NULL ,
        `persn_id` int(11) unsigned NOT NULL,
        `code` varchar(14) DEFAULT NULL,
        PRIMARY KEY (`id`) USING BTREE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
      INSERT INTO `rel` VALUES (1,1,'01001'),(2,2,'01002'),(3,1,'01003'),(4,1,'01004'),(5,2,'01005');
       
      DROP TABLE IF EXISTS `decl`;
      CREATE TABLE `decl` (
        `id` int(10) unsigned NOT NULL,
        `persn_id` int(10) unsigned NOT NULL,
        `crdate` timestamp NOT NULL DEFAULT current_timestamp(),
        PRIMARY KEY (`id`) USING BTREE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
      INSERT INTO `decl` VALUES 
      (1,1,'2020-01-07 19:52:35'),
      (2,1,'2020-01-07 19:53:00'),
      (3,1,'2020-01-21 16:45:41'),
      (4,1,'2020-01-21 16:45:50'),
      (5,2,'2020-01-27 21:10:57'),
      (6,2,'2020-01-27 21:11:09'),
      (7,2,'2020-01-27 21:11:21'),
      (8,2,'2020-01-27 21:11:31'),
      (9,1,'2020-02-04 15:04:12'),
      (10,1,'2020-02-04 15:04:20');
       
      select distinct
      persn.id,persn.name
      ,(select GROUP_CONCAT(DISTINCT rel.code) 
       from rel
       where rel.persn_id=persn.id
       group by persn.id) as codes
      from persn
      join decl on persn.id = decl.persn_id 
      group by persn.id,decl.crdate;
      

      /*
      Expected result:

      Error: Subquery returns more than 1 row

      Result (in 10.4.11):

      "id","name","codes"
      "1","Frank","01001,01003,01004"
      "1","Frank","01003,01004,01001"
      "2","Julie","01002,01005"
      "2","Julie","01005,01002"

      Or

      "id" "name" "codes"
      "1" "Frank" "01001,01003,01004"
      "1" "Frank" "01003,01004,01001"
      "2" "Julie" "01002,01005"
      */

       
      select distinct
      persn.id,persn.name
      ,(select GROUP_CONCAT(DISTINCT rel.code) 
      from rel
      where rel.persn_id=persn.id
      group by rel.id) as codes
      from persn
      join decl on persn.id = decl.persn_id 
      group by persn.id,decl.crdate;
      

      Returns correct (in 10.4.11) Error: Subquery returns more than 1 row

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            Betel Michiel
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.