Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5, 10.6, 10.11, 10.4(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
-
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
-