1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
# name: test/optimizer/test_sum_rewrite.test
# description: Test SUM rewrite
# group: [optimizer]
statement ok
CREATE TABLE integers AS FROM range(10000) t(i);
query IIII
SELECT SUM(i + 1), SUM(i + 2), SUM(i + 3), SUM(i + 4) FROM integers
----
50005000 50015000 50025000 50035000
query IIII
SELECT SUM(i + 1), SUM(2 + i), SUM(i + 3), SUM(4 + i) FROM integers
----
50005000 50015000 50025000 50035000
query IIIII
SELECT i//100000 grp, SUM(i + 1), SUM(2 + i), SUM(i + 3), SUM(4 + i) FROM integers GROUP BY grp
----
0 50005000 50015000 50025000 50035000
# empty result
query IIII
SELECT SUM(i + 1), SUM(2 + i), SUM(i + 3), SUM(4 + i) FROM integers WHERE i > 100000
----
NULL NULL NULL NULL
# nulls
statement ok
CREATE TABLE integer_nulls AS SELECT CASE WHEN i%2=0 THEN NULL ELSE i END i FROM range(10000) t(i);
query IIII
SELECT SUM(i + 1), SUM(i + 2), SUM(i + 3), SUM(i + 4) FROM integer_nulls;
----
25005000 25010000 25015000 25020000
# check that we did the rewrite using explain
statement ok
pragma explain_output='optimized_only'
query II
EXPLAIN SELECT SUM(i + 1), SUM(i + 2), SUM(i + 3), SUM(i + 4) FROM integers
----
logical_opt <REGEX>:.*sum_no_overflow\(i\).*
query II
EXPLAIN SELECT SUM(i + 1), SUM(2 + i), SUM(i + 3), SUM(4 + i) FROM integers
----
logical_opt <REGEX>:.*sum_no_overflow\(i\).*
# test rewrite with HUGEINT
statement ok
CREATE TABLE T(a HUGEINT);
statement ok
INSERT INTO T VALUES ('-40282366920938463463374607431768211455'::HUGEINT),('-40282366920938463463374607431768211455'::HUGEINT),('-40282366920938463463374607431768211455'::HUGEINT),('-40282366920938463463374607431768211455'::HUGEINT),('-40282366920938463463374607431768211455'::HUGEINT),('-40282366920938463463374607431768211455'::HUGEINT);
query I
SELECT sum(a + '40282366920938463463374607431768211455'::HUGEINT) FROM T;
----
0
|
