github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/edge (about) 1 # LogicTest: local-vec-off fakedist-vec-off 2 # TODO(yuzefovich): run with all default configs once #40354 is resolved. 3 4 # Test on-disk SQL semantics of edge cases and overflows. On-disk is 5 # important because it avoids any constant folding that could happen 6 # in the parse or normalization phases, forcing it to be handled by the 7 # execution engine itself. 8 9 # TODO(mjibson): Remove family definition when #41277 is fixed. 10 statement ok 11 CREATE TABLE t ( 12 key 13 STRING PRIMARY KEY, 14 _date 15 DATE, 16 _float4 17 FLOAT4, 18 _float8 19 FLOAT8, 20 _int2 21 INT2, 22 _int4 23 INT4, 24 _int8 25 INT8, 26 FAMILY "primary" (key, _date, _float4, _float8, _int2, _int4, _int8) 27 ) 28 29 statement ok 30 INSERT 31 INTO 32 t 33 VALUES 34 ( 35 'min', 36 '4714-11-24 BC', 37 -3.40282346638528859811704183484516925440e+38, 38 -1.7976931348623e+308, 39 -32768, 40 -2147483648, 41 -9223372036854775808 42 ), 43 ( 44 'max', 45 '5874897-12-31', 46 3.40282346638528859811704183484516925440e+38, 47 1.7976931348623e+308, 48 32767, 49 2147483647, 50 9223372036854775807 51 ) 52 53 statement ok 54 INSERT 55 INTO 56 t (key, _date) 57 VALUES 58 ('+inf', 'infinity'), ('-inf', '-infinity') 59 60 # min and min + 1 61 62 query TT 63 SELECT _date, _date + 1 FROM t WHERE key = 'min' 64 ---- 65 -4713-11-24 00:00:00 +0000 +0000 -4713-11-25 00:00:00 +0000 +0000 66 67 query IIIIIIRRRR 68 SELECT 69 _int2, 70 _int2 + 1:::INT2, 71 _int4, 72 _int4 + 1:::INT4, 73 _int8, 74 _int8 + 1:::INT8, 75 _float4, 76 _float4 + 1, 77 _float8, 78 _float8 + 1 79 FROM 80 t 81 WHERE 82 key = 'min' 83 ---- 84 -32768 -32767 -2147483648 -2147483647 -9223372036854775808 -9223372036854775807 -3.40282346638529e+38 -3.40282346638529e+38 -1.7976931348623e+308 -1.7976931348623e+308 85 86 # min - 1 87 88 statement error date is out of range 89 SELECT _date - 1 FROM t WHERE key = 'min' 90 91 # For now we incorrectly do type promotion int2 -> int. 92 query I 93 SELECT _int2 - 1:::INT2 FROM t WHERE key = 'min' 94 ---- 95 -32769 96 97 # Incorrect type promotion. 98 query I 99 SELECT _int4 - 1:::INT4 FROM t WHERE key = 'min' 100 ---- 101 -2147483649 102 103 statement error integer out of range 104 SELECT _int8 - 1:::INT8 FROM t WHERE key = 'min' 105 106 query R 107 SELECT _float8 - 1e300 FROM t WHERE key = 'min' 108 ---- 109 -Inf 110 111 # max and max - 1 112 113 query TT 114 SELECT _date, _date - 1 FROM t WHERE key = 'max' 115 ---- 116 5874897-12-31 00:00:00 +0000 +0000 5874897-12-30 00:00:00 +0000 +0000 117 118 query IIIIIIRRRR 119 SELECT 120 _int2, 121 _int2 - 1:::INT2, 122 _int4, 123 _int4 - 1:::INT4, 124 _int8, 125 _int8 - 1:::INT8, 126 _float4, 127 _float4 - 1, 128 _float8, 129 _float8 - 1 130 FROM 131 t 132 WHERE 133 key = 'max' 134 ---- 135 32767 32766 2147483647 2147483646 9223372036854775807 9223372036854775806 3.40282346638529e+38 3.40282346638529e+38 1.7976931348623e+308 1.7976931348623e+308 136 137 # max + 1 138 139 statement error date is out of range 140 SELECT _date + 1 FROM t WHERE key = 'max' 141 142 # For now we incorrectly do type promotion int2 -> int. 143 query I 144 SELECT _int2 + 1:::INT2 FROM t WHERE key = 'max' 145 ---- 146 32768 147 148 # Incorrect type promotion. 149 query I 150 SELECT _int4 + 1:::INT4 FROM t WHERE key = 'max' 151 ---- 152 2147483648 153 154 statement error integer out of range 155 SELECT _int8 + 1:::INT8 FROM t WHERE key = 'max' 156 157 query R 158 SELECT _float8 + 1e300 FROM t WHERE key = 'max' 159 ---- 160 +Inf 161 162 # infinity 163 164 query TTT 165 SELECT _date, _date + 1, _date - 1 FROM t WHERE key = '+inf' 166 ---- 167 infinity infinity infinity 168 169 query TTT 170 SELECT _date, _date + 1, _date - 1 FROM t WHERE key = '-inf' 171 ---- 172 -infinity -infinity -infinity 173 174 # aggregates 175 176 query RRRRRR 177 SELECT 178 sum(t._int2), 179 sum(t._int4), 180 sum(t._int8), 181 avg(t._int2), 182 avg(t._int4), 183 avg(t._int8) 184 FROM 185 t, t AS u 186 WHERE 187 t.key = 'max' 188 ---- 189 131068 8589934588 36893488147419103228 32767 2147483647 9223372036854775807 190 191 query II 192 SELECT 193 sum_int(t._int2), sum_int(t._int4) 194 FROM 195 t, t AS u 196 WHERE 197 t.key = 'max' 198 ---- 199 131068 8589934588 200 201 statement error integer out of range 202 SELECT sum_int(t._int8) FROM t, t AS u WHERE t.key = 'max' 203 204 query RRRRRRRRRR 205 SELECT 206 sum(t._int2), 207 sum(t._int4), 208 sum(t._int8), 209 sum(t._float4), 210 sum(t._float8), 211 avg(t._int2), 212 avg(t._int4), 213 avg(t._int8), 214 avg(t._float4), 215 avg(t._float8) 216 FROM 217 t, t AS u 218 WHERE 219 t.key = 'min' 220 ---- 221 -131072 -8589934592 -36893488147419103232 -1.36112938655412e+39 -Inf -32768 -2147483648 -9223372036854775808 -3.40282346638529e+38 -Inf 222 223 query II 224 SELECT 225 sum_int(t._int2), sum_int(t._int4) 226 FROM 227 t, t AS u 228 WHERE 229 t.key = 'min' 230 ---- 231 -131072 -8589934592 232 233 statement error integer out of range 234 SELECT sum_int(t._int8) FROM t, t AS u WHERE t.key = 'min' 235 236 query RRRRRRRRRR 237 SELECT 238 sum(t._int2), 239 sum(t._int4), 240 sum(t._int8), 241 sum(t._float4), 242 sum(t._float8), 243 avg(t._int2), 244 avg(t._int4), 245 avg(t._int8), 246 avg(t._float4), 247 avg(t._float8) 248 FROM 249 t 250 ---- 251 -1 -1 -1 0 0 -0.5 -0.5 -0.5 0 0 252 253 query III 254 SELECT 255 sum_int(t._int2), sum_int(t._int4), sum_int(t._int8) 256 FROM 257 t 258 ---- 259 -1 -1 -1