[MDEV-21710] No 'subquery returns more than one row' error returned but faulty result set Created: 2020-02-12  Updated: 2020-03-12  Resolved: 2020-03-12

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.4.11
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Michiel Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: need_feedback
Environment:

Mariadb 10.4.11 on CentOs 8


 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

 Comments   
Comment by Alice Sherepa [ 2020-02-12 ]

Could you please explain why you consider it as a bug.
In the case with (SELECT GROUP_CONCAT(DISTINCT rel.code) FROM rel WHERE rel.persn_id=persn.id GROUP BY persn.id) – subquery returns only one row for every persn.id,
in case with (SELECT GROUP_CONCAT(DISTINCT rel.code) FROM rel WHERE rel.persn_id=persn.id GROUP BY rel.id) – there are multiple rows for persn.id.

The results I get on 10.4.11/10.3.21:

MariaDB [test]> 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;
 
+----+-------+-------------------+
 
| id | name  | codes             |
 
+----+-------+-------------------+
 
|  1 | Frank | 01001,01003,01004 |
 
|  2 | Julie | 01002,01005       |
 
+----+-------+-------------------+
 
2 rows in set (0.002 sec)

Comment by Michiel [ 2020-02-12 ]

Retry the query a number of times on 10.4.11 ! It will return 2 or 3 or 4 records!

Comment by Sergei Golubchik [ 2020-02-12 ]

1. There can be no "Subquery returns more than 1 row" error. A subquery

select GROUP_CONCAT(DISTINCT rel.code) 
 from rel
 
 where rel.persn_id=persn.id
 
 group by persn.id

can not possibly return more than one row.

2. You are getting multiple rows with the same id and name. It's because a subquery — GROUP_CONCAT, in particular — returns different results on different invocations. It can do that, because you do not have an ORDER BY clause. So the order is unspecified and can as well be different for different rows. You have to use ORDER BY if you want a stable ordering of results.

Generated at Thu Feb 08 09:09:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.