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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
|
# name: test/sql/overflow/table_overflow.test
# description: Test handling of integer overflows in table
# group: [overflow]
# statement ok
# PRAGMA enable_verification
# tinyint
statement ok
CREATE TABLE tinyints(i TINYINT);
statement ok
INSERT INTO tinyints VALUES (1), (10);
# addition
query I
SELECT i+100::TINYINT FROM tinyints ORDER BY 1;
----
101
110
# overflow in addition
statement error
SELECT i+120::TINYINT FROM tinyints ORDER BY 1;
----
Out of Range Error: Overflow
# no overflow if we filter the offending element
query I
SELECT i+120::TINYINT FROM tinyints WHERE i=1 ORDER BY 1;
----
121
# subtraction
query I
SELECT -100::TINYINT-i FROM tinyints ORDER BY 1;
----
-110
-101
statement error
SELECT -120::TINYINT-i FROM tinyints ORDER BY 1;
----
Out of Range Error: Overflow
# no overflow if we filter the offending element
query I
SELECT -120::TINYINT-i FROM tinyints WHERE i=1 ORDER BY 1;
----
-121
# addition with negation
statement error
SELECT -120::TINYINT + (-i) FROM tinyints ORDER BY 1;
----
Out of Range Error: Overflow
# multiple negations
statement error
SELECT -120::TINYINT + (-(-(-i))) FROM tinyints ORDER BY 1;
----
Out of Range Error: Overflow
# multiplication
query I
SELECT i*10::TINYINT FROM tinyints ORDER BY 1;
----
10
100
statement error
SELECT i*15::TINYINT FROM tinyints ORDER BY 1;
----
Out of Range Error: Overflow
statement error
SELECT (i*10::TINYINT)*10::TINYINT FROM tinyints ORDER BY 1;
----
Out of Range Error: Overflow
# no overflow if we filter the offending element
query I
SELECT (i*10::TINYINT)*10::TINYINT FROM tinyints WHERE i=1 ORDER BY 1;
----
100
# what if we have a subquery?
statement error
SELECT (i*10::TINYINT)*10::TINYINT FROM (SELECT * FROM tinyints) tbl(i) ORDER BY 1;
----
Out of Range Error: Overflow
query I
SELECT (i*10::TINYINT)*10::TINYINT FROM (SELECT * FROM tinyints) tbl(i) WHERE i=1 ORDER BY 1;
----
100
# what if we have a UNION in the subquery
statement error
SELECT (i*10::TINYINT)*10::TINYINT FROM (SELECT * FROM tinyints WHERE i=1 UNION ALL SELECT * FROM tinyints WHERE i=10) tbl(i) ORDER BY 1;
----
Out of Range Error: Overflow
# smallints
statement ok
CREATE TABLE smallints(i SMALLINT);
statement ok
INSERT INTO smallints VALUES (1), (10);
# overflow in various smallint ops
statement error
SELECT i+32765::SMALLINT FROM smallints
----
Out of Range Error: Overflow
query I
SELECT i+32765::SMALLINT FROM smallints WHERE i=1
----
32766
statement error
SELECT -32765::SMALLINT-i FROM smallints
----
Out of Range Error: Overflow
query I
SELECT -32765::SMALLINT-i FROM smallints WHERE i=1
----
-32766
statement error
SELECT i*10000::SMALLINT FROM smallints
----
Out of Range Error: Overflow
query I
SELECT i*10000::SMALLINT FROM smallints WHERE i=1
----
10000
# including a cast to tinyint
statement error
SELECT i::TINYINT+120::TINYINT FROM smallints
----
Out of Range Error: Overflow
query I
SELECT i::TINYINT+120::TINYINT FROM smallints WHERE i=1
----
121
# what if the smallint doesn't fit?
statement ok
INSERT INTO smallints VALUES (1000)
statement error
SELECT i::TINYINT FROM smallints
----
Conversion Error
query I
SELECT i::TINYINT+120::TINYINT FROM smallints WHERE i=1
----
121
# include multiple projections...
# include joins (specifically left joins! they add nulls)
# What about adding column types that end up overflowing?
statement ok
INSERT INTO smallints VALUES (30000);
statement error
SELECT integers1.i::SMALLINT + integers2.i::SMALLINT from smallints integers1, smallints integers2 where integers1.i = integers2.i;
----
Out of Range Error: Overflow
statement ok
CREATE TABLE smallints2 (j SMALLINT);
statement ok
INSERT INTO smallints2 VALUES (1), (10);
# no overflow, just some null values
query I rowsort
SELECT i + j from smallints si_left LEFT JOIN smallints2 si_right ON (i = j);
----
2
20
NULL
NULL
# insert 30000 into smallints 2
statement ok
INSERT INTO smallints2 VALUES (30000);
statement error
SELECT i + j from smallints LEFT JOIN smallints2 ON (i = j);
----
Out of Range Error: Overflow
|
