[Bug] Optimizer incorrectly push down aggregations to subqueries with distinct and union all.
(1) By sunyinqi0508 on 2025-03-10 04:44:39 [source]
Here is a simple step to reproduce the bug:
create table a(a int);
insert into a values (1), (1);
select count() from (select distinct * from a union all select * from a);
This happens since around version 3.42.0, where the optimizer is trying to incorrectly push the COUNT() down to the subquery. If we explian the query, we will find the following opcodes, which simply does distinct count(*) instead of count(distinct *):
5 OpenEphemeral 3 0 0 k(1,B) 8 nColumn=0
6 OpenRead 4 2 0 1 0 root=2 iDb=0
7 Count 4 6 0 0 r[6]=count()
8 Close 4 0 0 0
The correct opcode for this part (count(distinct a)) should be something like this:
2 OpenEphemeral 1 0 0 k(1,B) 0 nColumn=0
3 OpenRead 0 2 0 1 0 root=2 iDb=0; a
4 Rewind 0 11 0 0
5 Column 0 0 3 0 r[3]= cursor 0 column 0
6 Found 1 10 3 1 0 key=r[3]
7 MakeRecord 3 1 4 0 r[4]=mkrec(r[3])
8 IdxInsert 1 4 3 1 16 key=r[4]
9 AggStep 0 3 2 count(1) 1 accum=r[2] step(r[3])
10 Next 0 5 0 1
11 AggFinal 2 1 0 count(1) 0 accum=r[2] N=1
(2) By Richard Hipp (drh) on 2025-03-10 10:51:52 in reply to 1 [link] [source]
Thanks for the bug report.
This is due to an error in the count-of-view optimization that was introduced by check-in d1ba200234f40b84 on 2017-07-04. The first release containing that check-in was 3.20.0. However, that optimization was disabled by default until check-in a4aacdd323a854d7 (2023-02-22) which first appeared in version 3.42.0, which is why it seemed to appear in in that release. The problem is now fixed on trunk and also on branch-3.49, and the fix will appear in the next release, whether that is 3.49.2 or 3.50.0.