On Tue, Jun 23, 2026 at 2:13 PM Dean Rasheed <[email protected]> wrote:
> On Tue, 23 Jun 2026 at 09:37, Dean Rasheed <[email protected]> > wrote: > > > > On Tue, 23 Jun 2026 at 08:49, Jeevan Chalke > > <[email protected]> wrote: > > > > > > PRODUCT() returns the product of all non-null input values. It is > defined for > > > int2, int4, int8, float4, float8 and numeric input, and always returns > numeric. > > > > I don't think that you need to define it for all those types. I > > suspect that you could just define it for numeric and float8, and let > > implicit casting do the rest. > Thank you, Dean, for looking at this. I appreciate the suggestion, but I don't think the implicit-casting approach works well here, for the following reasons: 1. The integer types (int2/int4/int8) have implicit casts to both float8 and numeric. Since float8 is the preferred type in the numeric type category, the function resolution machinery would select the product(float8) variant. That has two consequences: an extra cast function has to be executed per row, and, more importantly, integer inputs would be accumulated as float8. For a large product that yields a lossy result in exponent form, whereas accumulating in numeric gives an exact answer. 2. This approach is also consistent with the existing aggregates — sum(), avg(), min()/max(), etc. all define per-type variants rather than relying on implicit input casting. The patch follows that established pattern rather than introducing a new one. 3. There is also a small performance penalty to the casting approach: the per-row execution of the cast function itself, in addition to the resolution issue noted in (1). I ran a quick test to demonstrate point (1). For large products, the float8 implementation returns a lossy, exponential result like 9.9999970000003e+20, whereas numeric returns the exact value of 999999700000029999999. I would be happy to share the test script if it's helpful. > > ... and perhaps make the float8 version return float8. > Yes, I already noted this under "Open questions" in my first email. We can certainly make the float4 and float8 variants return float8 instead of numeric. Let's see what others think before making that adjustment. Thanks > > Regards, > Dean > -- *Jeevan Chalke* *Senior Principal Engineer, Engineering Manager* *Product Development* enterprisedb.com <https://www.enterprisedb.com>
