`sum`, number or integer?

[NicoLaval]

See the reference manual.

The specification quotes : dataset { measure<number> _+ } | component<number>

But, if we have a dataset of INTEGER measures? What would be the result?

Mathematically we know that it will return INTEGER, so, should we preserve INTEGER, because we know, or do we say that the resulting component is a double?

[linardian]

Hi @NicoLaval.
We already discussed similar issues for the (very long) topic of the cast operator for arithmetic operators.
Since integer is a subtype of number, I would not care about the specification whether the component has number of integer type.
The problem could arise in case of overflow, but it is an issue that the final user should take care of...
Unless I misunderstood you point...

[NicoLaval]

I just think we need to distinguish, in the case of SUM, between columns of type INTEGER and those of type NUMBER.
The question doesn't arise for MEAN, for example; the result is always NUMBER.

Therefore, I think we should be more precise in the documentation to clarify these cases.

I'll take a closer look in the coming days and keep you posted.

[vpinna80]

The question doesn't arise for MEAN, for example; the result is always NUMBER.

I don't think this is written explictly anywhere, even for count.

In fact, I remember asking for the specification to be clearer in this regard, expecially since sum/avg/max/min/var/std may as well work with integers (just round/truncate the result), so if you have all INTEGER measures, the result could be INTEGER too.

Dropping from integer to number in order to avoid domain conversions and variable incompatibility may sound a good idea in principle, but when using floating point numbers, you must be wary that the conversion may not be as accurate as you can imagine.

Unless your engine uses some implementation of "BigDecimal" (which is slow and cumbersome to be honest), it may lead to surprising results:

jshell> double a = 1.0 - 0.9 - 0.1;
a ==> -2.7755575615628914E-17

jshell> double b = 1.0 - 0.1 - 0.1 - 0.1 - 0.1 - 0.1 - 0.1 - 0.1 - 0.1 - 0.1 - 0.1;
b ==> 1.3877787807814457E-16

/* different magnitudes cause errors */
jshell> a == b
$3 ==> false

/* inverting the subtraction won't give the exact operands back */
jshell> double c = a + 0.3 + 0.3 + 0.3 + 0.1;
c ==> 0.9999999999999999

jshell> a == c - 1
$5 ==> false

/* Commutativity doesn't work */
jshell> -1 + 0.7 + 0.3 == 0.7 + 0.3 - 1
$6 ==> false

[NicoLaval]

@vpinna80 Thanks — I agree with your point, and I think this is larger than sum.

Right now the spec often declares aggregate operands/results using the super-type number, which is fine for acceptability (integer included in number), but it does not state the nominal result type rule. This is where engines diverge, and where unnecessary widening to binary floating point can introduce avoidable surprises (as in your examples).

I’d suggest we make the reference manual / invocations table explicit about the nominal output type, with a simple rule:

• if the operator is closed on integers and all operand values are integer, the nominal result may remain integer (subject to an overflow policy);
• otherwise the nominal result is number.

Regarding BigDecimal: I agree it is not really performant. In Trevas we use it sparingly (mainly round / trunc / between), not on aggregate hot paths. Aggregates such as sum already go through integer or double paths depending on operand type. Once the spec clarifies nominal result types, we can align those few decimal-sensitive spots even better — without making BigDecimal the default representation for every numeric measure.

Proposed result-type rules

Operator	Operand measure type(s)	Proposed nominal result type when all operands are integer	When operands contain number
count()	any	integer	integer
min(x) / max(x)	any (ordered types)	same as operand type	same as operand type
sum(x)	integer / number	integer	number
avg(x)	integer / number	number	number
median(x)	integer / number	number	number
var_pop, var_samp	integer / number	number	number
stddev_pop, stddev_samp	integer / number	number	number
ratio_to_report(x)	integer / number	number	number
rank()	any	integer	integer

From an implementation perspective (Trevas), we already preserve integer sums when the operand is an integer measure. The open point is what the specification should require normatively versus what it should allow as implementation-defined.

Proposed PR (v2.1)

I can open a PR on v2.1 that:

1. Clarifies nominal result types in the Aggregate and Analytic chapter (index.rst): short normative text after invocationsTable.csv.
2. Updates invocationsTable.csv: sum result type number or integer (operand remains number).
3. Updates operator pages where useful:
   • Sum: declared number, with text on integer preservation and overflow;
   • avg / median: result always number;
   • min / max: result same type as operand for numeric types.
4. Aligns examples with fixtures:
   • Aggregate invocation/examples/ex_2 (sum on integer measures): Integer in ex_2.json, integer notation in ex_2.csv;

If we have consensus, I will extend the PR to v2.2 version.

[NicoLaval]

See #713

[vpinna80]

I would make avg and median behave like sum (keep integer if all data points are integers).
Also why target 2.1? I would make this a 2.2-only change; otherwise we will have to follow the "Operators changing the data type" behavior.

[NicoLaval]

Ok for v2.2, I will update asap.

avg and median?
Consider 5 and 8, avg and median are 6.5

[vpinna80]

Yes i was suggesting to use the "integer division" when all are integers. Easier than forcing a change to floating point, but obviously debatable if precision is required.
EDIT
for median you can take any value between the two values in the middle, taking their mean is only a convention; this works even for integers and non-numeric values too (qualitative median) and for percentiles.

[NicoLaval]

I'm not a big fan; I prefer it to be deterministic, regardless of the data and without "hidden" operations (let's leave the truncated or rounded operations to the users if they wish).

[antonio-olleros]

Agree with Nico, it is important that the structure does not chnage in different runtimes