Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
-
None
Description
DELETE with nested subquery in HAVING that contains set function aggregated in outer subquery returns a bogus error message while SELECT and UPDATE with the same WHERE condition work fine:
MariaDB [test]> delete from t1
|
-> where t1.a in (select t3.a from t3 group by t3.a
|
-> having t3.a > any (select t2.b from t2
|
-> where t2.b*10 < sum(t3.b)));
|
ERROR 1111 (HY000): Invalid use of group function
|
|
|
MariaDB [test]> select * from t1
|
-> where t1.a in (select t3.a from t3 group by t3.a
|
-> having t3.a > any (select t2.b from t2
|
-> where t2.b*10 < sum(t3.b)));
|
+------+
|
| a |
|
+------+
|
| 7 |
|
+------+
|
1 row in set (0.003 sec)
|
|
|
MariaDB [test]> update t1 set t1.a=t1.a+10
|
-> where t1.a in (select t3.a from t3 group by t3.a
|
-> having t3.a > any (select t2.b from t2
|
-> where t2.b*10 < sum(t3.b)));
|
Query OK, 1 row affected (0.006 sec)
|
Rows matched: 1 Changed: 1 Warnings: 0
|
Here's a test case to reproduce the bug
create table t1 (a int); |
insert into t1 values (3), (7), (1); |
|
|
create table t2 (b int); |
insert into t2 values (2), (1), (4), (7); |
|
|
create table t3 (a int, b int); |
insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40); |
|
|
|
|
delete from t1 |
where t1.a in (select t3.a from t3 group by t3.a |
having t3.a > any (select t2.b from t2 |
where t2.b*10 < sum(t3.b))); |
|
|
select * from t1 |
where t1.a in (select t3.a from t3 group by t3.a |
having t3.a > any (select t2.b from t2 |
where t2.b*10 < sum(t3.b))); |
|
|
update t1 set t1.a=t1.a+10 |
where t1.a in (select t3.a from t3 group by t3.a |
having t3.a > any (select t2.b from t2 |
where t2.b*10 < sum(t3.b))); |
|
|
drop table t1,t2,t3; |
The bug is reproducible with any engine with version 10.4 and up.
Version 10.3 works as expected
MariaDB [test]> delete from t1
|
-> where t1.a in (select t3.a from t3 group by t3.a
|
-> having t3.a > any (select t2.b from t2
|
-> where t2.b*10 < sum(t3.b)));
|
Query OK, 1 row affected (0.005 sec)
|
|
|
MariaDB [test]> select * from t1
|
-> where t1.a in (select t3.a from t3 group by t3.a
|
-> having t3.a > any (select t2.b from t2
|
-> where t2.b*10 < sum(t3.b)));
|
Empty set (0.002 sec)
|
|
|
MariaDB [test]> update t1 set t1.a=t1.a+10
|
-> where t1.a in (select t3.a from t3 group by t3.a
|
-> having t3.a > any (select t2.b from t2
|
-> where t2.b*10 < sum(t3.b)));
|
Query OK, 0 rows affected (0.002 sec)
|
Rows matched: 0 Changed: 0 Warnings: 0
|
Attachments
Issue Links
- causes
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Igor Babaev [ igor ] | Oleksandr Byelkin [ sanja ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Link |
This issue causes |
| Assignee | Oleksandr Byelkin [ sanja ] | Igor Babaev [ igor ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.4.29 [ 28510 ] | |
| Fix Version/s | 10.5.20 [ 28512 ] | |
| Fix Version/s | 10.6.13 [ 28514 ] | |
| Fix Version/s | 10.8.8 [ 28518 ] | |
| Fix Version/s | 10.9.6 [ 28520 ] | |
| Fix Version/s | 10.10.4 [ 28522 ] | |
| Fix Version/s | 10.11.3 [ 28524 ] | |
| Fix Version/s | 11.0.2 [ 28706 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |