Posting on the forum as requested. I seem to have encountered a bug. There are some conditions where round(sum(x)) returns the correct result, but sum(x) alone - it seems - incorrectly returns zero.

I've stripped this down as best I can. I'm sure it would be possible to construct a simpler example.

Platform: macOS 11.7.10, sqlite installed with homebrew.

Steps to reproduce:


matthew@air ~ % /usr/local/opt/sqlite/bin/sqlite3

SQLite version 3.44.0 2023-11-01 11:23:50
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> CREATE TABLE pollbypoll (
    "index" INTEGER,
    "year" INTEGER,
    ed_num INTEGER,
    ed_name TEXT,
    pd_num TEXT,
    affiliation TEXT,
    poll_votes INTEGER
);

sqlite> INSERT INTO pollbypoll ("index","year",ed_num,ed_name,pd_num,affiliation,poll_votes) VALUES
         (0,2021,10001,'Avalon','1','Conservative',28),
         (1,2021,10001,'Avalon','1','NDP',13),
         (2,2021,10001,'Avalon','1','Liberal',55),
         (3,2021,10001,'Avalon','1','People''s Party - PPC',4),
         (36,2021,10001,'Avalon','10','Conservative',35),
         (37,2021,10001,'Avalon','10','NDP',6),
         (38,2021,10001,'Avalon','10','Liberal',43),
         (39,2021,10001,'Avalon','10','People''s Party - PPC',2);

sqlite> CREATE VIEW results_by_fed AS
SELECT
    "year",
    ed_num,
    ed_name,
    affiliation,
    SUM(poll_votes) AS votes
FROM
    pollbypoll
GROUP BY
    "year",
    ed_num,
    affiliation;

sqlite> CREATE VIEW results_by_fed_ranked AS
SELECT
    *,
    DENSE_RANK() OVER (
        PARTITION BY
            "year",
            ed_num
        ORDER BY
            votes DESC
    ) AS vote_order
FROM
    results_by_fed;

sqlite> CREATE VIEW results_by_fed_tabulated AS
select
    "year",
    ed_num,
    ed_name,
    max(affiliation) filter (
        where
            vote_order = 1
    ) as winner,
    (
        sum(votes) filter (
            where
                vote_order = 1
        ) - sum(votes) filter (
            where
                vote_order = 2
        )
    ) as margin,
    round(
        (
            (
                sum(votes) filter (
                    where
                        vote_order = 1
                ) - sum(votes) filter (
                    where
                        vote_order = 2
                )
            ) / sum(votes)
        ) * 100,
        1
    ) as margin_pct
from
    results_by_fed_ranked
group by
    "year",
    ed_num;
sqlite> CREATE VIEW results_by_fed_tabulated_fix AS
select
    "year",
    ed_num,
    ed_name,
    max(affiliation) filter (
        where
            vote_order = 1
    ) as winner,
    (
        sum(votes) filter (
            where
                vote_order = 1
        ) - sum(votes) filter (
            where
                vote_order = 2
        )
    ) as margin,
    round(
        (
            (
                sum(votes) filter (
                    where
                        vote_order = 1
                ) - sum(votes) filter (
                    where
                        vote_order = 2
                )
            ) / round(sum(votes)) --round() should not make a difference here
        ) * 100,
        1
    ) as margin_pct
from
    results_by_fed_ranked
group by
    "year",
    ed_num;

sqlite> select * from pollbypoll;
0|2021|10001|Avalon|1|Conservative|28
1|2021|10001|Avalon|1|NDP|13
2|2021|10001|Avalon|1|Liberal|55
3|2021|10001|Avalon|1|People's Party - PPC|4
36|2021|10001|Avalon|10|Conservative|35
37|2021|10001|Avalon|10|NDP|6
38|2021|10001|Avalon|10|Liberal|43
39|2021|10001|Avalon|10|People's Party - PPC|2

sqlite> select * from results_by_fed;
2021|10001|Avalon|Conservative|63
2021|10001|Avalon|Liberal|98
2021|10001|Avalon|NDP|19
2021|10001|Avalon|People's Party - PPC|6

sqlite> select * from results_by_fed_ranked;
2021|10001|Avalon|Liberal|98|1
2021|10001|Avalon|Conservative|63|2
2021|10001|Avalon|NDP|19|3
2021|10001|Avalon|People's Party - PPC|6|4

sqlite> select * from results_by_fed_tabulated;
2021|10001|Avalon|Liberal|35|0.0

sqlite> select * from results_by_fed_tabulated_fix;
2021|10001|Avalon|Liberal|35|18.8


Expected result: margin_pct should evaluate to 18.8 in both views.

Actual result: margin_pct is zero unless round() is added.

If for some reason this is a misunderstanding on my part about the expected functionality here let me know. I'm new to using dense_rank() and filter(). It does seem like a bug to me though, as I had a different view (that went through different steps to get from different data to the same penultimate ranked structure) and that worked fine, without adding round().

Thanks,
Matthew