Cannot sum bigint columns with negative values on SQLite

[danconnell]

Because of the way bigint columns are encoded in SQLite here https://github.com/ponder-sh/ponder/blob/main/packages/core/src/utils/encoding.ts
trying to sum over that column is a meaningless operation if it contains negative values as the EVM_MIN_INT decoding is not accounted for.

Everything works fine with using sum on a bigint column with negative values on PostgreSQL.

I am largely just noting this for anyone else who experiences the same issue.

However, it would be helpful to note constraints like this with specific drivers on https://ponder.sh/docs/schema#column-types

[0xOlias]

Thanks for opening, definitely a limitation of the encoding format. In what query context did you run into this? Direct SQL queries against the SQLite file, or in an API function?

Will give this more thought re: including the limitation in the docs. As an aside, we're considering a migration from SQLite to PGlite (#1084) as the dev database. Curious for your take.

[danconnell]

It was in an API function.

I haven't had a chance to try PGlite yet, but doing some quick reading it seems to me like it would be an improvement over SQLite.

I have no preference for SQLite (I actually very strongly dislike it), it's just something we use in local dev to save the trouble of having to run a local PostgreSQL server when a dev has no other need for one. In our deployed environments we use PostgreSQL and everything is fine.

[ind-igo]

+1 just ran into this issue, was wondering wtf was going on.

I ran into this issue when reading off of the result of a create call, and using that for a batch job (aggregating swap events into candlestick chart data)

Fully support moving to pglite if it will help alleviate this small issues with encoding mismatches (along with im sure a ton of other issues). Some note in the docs in the meantime would help a lot.