feat: add product function

[vibhatha]

This PR includes aggregate function product.

[jvanstraten]

As with other PRs, why the promotion? For floats especially since all floats represent all real numbers (to some degree of accuracy), but likewise for integers.

See #251 (you're not the first to do it this way and probably won't be the last)

[vibhatha]

Thanks for pointing this out. I also had the same doubt and I just followed the promotion. Waiting to see how the discussion progress on the promotion conclusion.

[jvanstraten]

I'm not sure this should be solved in this PR immediately because it probably also applies to existing stuff, but something to think about for SATURATE in the context of these types of aggregates... What if the internal accumulator overflows but the net result would be within range? For example, sum:opt_i64(2^63-1, 2^63-1, -2^63-1, -2^63-1). Is that required to always yield 0? Or for ERROR, is it required to throw an error (after all, if the second and third element are swapped it wouldn't overflow)?

[vibhatha]

Yes this is a valid point. But I guess this is hard to determine. I would say it is better to leave room for overflows. But I could be wrong.

[jvanstraten]

Maybe we should wait with this until we get some more opinions in #251. I'm inclined to say the intermediate value should be the same type, if only because picking i64 feels like an arbitrary "eh, that's probably good enough" decision that doesn't actually solve the problem, but that just makes the overflow question all the more important.

[vibhatha]

Sure, let’s do that.

[vibhatha]

@jvanstraten I updated the PR with some modifications.

[julianhyde]

It would be clearer if you said 'arithmetic product'. Many people would assume cartesian product.

What value does this function return if the input is empty? The documentation should specify.

In the discussion for sum, the consensus seemed to be that sum should return 0 if it weren't for the SQL standard requiring that SUM returns null. In this case there is no such precedent. I would vote for 1.

[jvanstraten]

What value does this function return if the input is empty? The documentation should specify.

It does, we mimicked sum and had it return null.

IMO we should either embrace the SQL way and apply its null nonsense consistently across all similar arithmetic aggregations (and also define a product1 alongside sum0) or not at all.

@vibhatha, you'll need to rewrite your commit messages (or just rebase them all into one) to make CI pass.

[vibhatha]

@jvanstraten I will rebase with an accurate commit msg.

[julianhyde]

SQL seems to be getting smarter. For example ARRAG_AGG on an empty input returns an empty array, not NULL. Similarly LISTAGG. See Snowflake.

For statistical functions such as AVG and STDEV there are valid reasons to return NULL on empty input. So I think that SUM is the only one it got wrong.

[vibhatha]

@jvanstraten I squashed to a single commit, let's see the CIs.

[vibhatha]

@jvanstraten I updated the PR with nullability and return type updates.