Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
10.4.11
-
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
Attachments
Activity
Retry the query a number of times on 10.4.11 ! It will return 2 or 3 or 4 records!
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.
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)