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
|
Bug
Critical
