Strange behavior of count(*) over ()

[brankoradovanovic-mcom]

Details

Certain SQL statements using count(*) over () return wrong results. Strangely, I couldn't reproduce this behavior in other SQLite environments. I've also tried it with:

• https://sqliteonline.com/ (3.47.2) - works as expected
• Python 3.12+sqlite3 (3.45.3) - works as expected

Steps to reproduce

create table test as select 1 as x union all select 2;

--Q1
select *, count(*) over () as cnt
from test;

--Q2
select *, count(*) over () as cnt
from (select * from test);

Q1 returns:

x cnt
1 2
2 2

...which is as expected, but Q2 returns:

x cnt
1 1
2 2

...which is wrong. The expected result of Q2 is the same as Q1, of course.

My first thought was that it had to be an SQLite bug, but I couldn't reproduce it anywhere else, as noted above. I'm not sure if the use of an SQL window function is relevant to the problem or not.

Operating system

Windows 10 Pro x64

SQLiteStudio version

3.4.13