deterministic aggregate functions

(1) By anonymous on 2022-07-28 00:50:39 [link] [source]

How do I know if my user defined aggregate function is deterministic?

When making a user defined function/aggregate function, one can flag it as deterministic. The docs and examples make it clear what a deterministic function is. It's a function that always returns the same output give the same input. It doesn't explain what a deterministic aggregate function is.

I would think an aggregate function is deterministic if given the same underlying data, it produces the same output. With that definition, I would expect that all the built in aggregate functions are deterministic. Is this correct?

(2) By Richard Hipp (drh) on 2022-07-28 13:44:42 in reply to 1 [link] [source]

I don't think it matters. SQLite will process aggregate and window functions the same way regardless of the SQLITE_DETERMINISTIC flag.

SQLITE_DETERMINISTIC does matter for scalar functions. A deterministic scalar function with contant arguments can be factored out of loops so that it is only called once per statement rather than once per row. But no such optimizations are possible with aggregate and window functions, as far as I am aware.

(3) By anonymous on 2022-07-28 15:58:45 in reply to 2 [source]

That makes sense. Thanks for the quick response and clarification!