github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/decimal (about) 1 # The following tests have results equivalent to Postgres (differences 2 # in string representation and number of decimals returned, but otherwise 3 # the same). These do not pass using the inf package. The inf package 4 # (http://gopkg.in/inf.v0) is what we used to use, but it had various problems 5 # (for example, all the test cases below), and was replaced with apd. 6 7 # inf returns 0 8 query R 9 SELECT (1.4238790346995263e-40::DECIMAL / 6.011482313728436e+41::DECIMAL) 10 ---- 11 2.3685988919035999994E-82 12 13 # inf returns -108.4851126682386588 14 query R 15 SELECT ln(7.682705743584112e-48::DECIMAL) 16 ---- 17 -108.48511266823882051 18 19 # inf returns 0 20 query R 21 SELECT sqrt(9.789765531128956e-34::DECIMAL) 22 ---- 23 3.1288601009199749773E-17 24 25 # inf returns 0.1547300000000000 26 query R 27 SELECT pow(4.727998800941528e-14::DECIMAL, 0.06081860494226844::DECIMAL) 28 ---- 29 0.15472926640705911955 30 31 # inf returns 0, 0 32 query RR 33 SELECT pow(sqrt(1e-10::DECIMAL), 2), sqrt(pow(1e-5::DECIMAL, 2)) 34 ---- 35 1E-10 0.00001 36 37 # inf returns 1e-16, 0, 2e-16 38 query RRR 39 SELECT 1e-16::DECIMAL / 2, 1e-16::DECIMAL / 3, 1e-16::DECIMAL / 2 * 2 40 ---- 41 5E-17 3.3333333333333333333E-17 1.0E-16 42 43 # inf returns 1e-8, 0, 0, 0 44 query RRRR 45 SELECT pow(1e-4::DECIMAL, 2), pow(1e-5::DECIMAL, 2), pow(1e-8::DECIMAL, 2), pow(1e-9::DECIMAL, 2) 46 ---- 47 1E-8 1E-10 1E-16 1E-18 48 49 # inf returns argument too large 50 query R 51 SELECT pow(1e-10::DECIMAL, 2) 52 ---- 53 1E-20 54 55 # inf panics (#13051) 56 query RR 57 SELECT 'NaN'::FLOAT::DECIMAL, 'NaN'::DECIMAL 58 ---- 59 NaN NaN 60 61 # Ensure trailing zeros are kept for decimal types with no listed scale, 62 # and enforced when the scale is listed. 63 64 statement ok 65 CREATE TABLE t (d decimal, v decimal(3, 1)) 66 67 statement ok 68 INSERT INTO t VALUES (0.000::decimal, 0.00::decimal), (1.00::decimal, 1.00::decimal), (2.0::decimal, 2.0::decimal), (3::decimal, 3::decimal) 69 70 query RR 71 SELECT * FROM t ORDER BY d 72 ---- 73 0.000 0.0 74 1.00 1.0 75 2.0 2.0 76 3 3.0 77 78 # Ensure trailing zeros are kept in an index. 79 80 statement ok 81 CREATE TABLE t2 (d decimal, v decimal(3, 1), primary key (d, v)) 82 83 statement ok 84 INSERT INTO t2 VALUES 85 (1.00::decimal, 1.00::decimal), 86 (2.0::decimal, 2.0::decimal), 87 (3::decimal, 3::decimal), 88 ('NaN'::decimal, 'NaN'::decimal), 89 ('Inf'::decimal, 'Inf'::decimal), 90 ('-Inf'::decimal, '-Inf'::decimal), 91 ('-0.0000'::decimal, '-0.0000'::decimal) 92 93 query RR 94 SELECT * FROM t2 ORDER BY d 95 ---- 96 NaN NaN 97 -Infinity -Infinity 98 0.0000 0.0 99 1.00 1.0 100 2.0 2.0 101 3 3.0 102 Infinity Infinity 103 104 # Ensure uniqueness in PK columns with +/- NaN and 0. 105 106 statement error duplicate key value 107 INSERT INTO t2 VALUES ('-NaN'::decimal, '-NaN'::decimal) 108 109 statement error duplicate key value 110 INSERT INTO t2 VALUES (0, 0) 111 112 # Ensure NaN cannot be signaling or negative. 113 114 query RRRR 115 SELECT 'NaN'::decimal, '-NaN'::decimal, 'sNaN'::decimal, '-sNaN'::decimal 116 ---- 117 NaN NaN NaN NaN 118 119 query RR 120 SELECT * FROM t2 WHERE d IS NaN and v IS NaN 121 ---- 122 NaN NaN 123 124 query RR 125 SELECT * FROM t2 WHERE d = 'Infinity' and v = 'Infinity' 126 ---- 127 Infinity Infinity 128 129 query RR 130 SELECT * FROM t2 WHERE d = '-Infinity' and v = '-Infinity' 131 ---- 132 -Infinity -Infinity 133 134 # Ensure special values are handled correctly. 135 136 statement ok 137 CREATE TABLE s (d decimal null, index (d)) 138 139 statement ok 140 INSERT INTO s VALUES 141 (null), 142 ('NaN'::decimal), 143 ('-NaN'::decimal), 144 ('Inf'::decimal), 145 ('-Inf'::decimal), 146 ('0'::decimal), 147 (1), 148 (-1) 149 150 statement ok 151 INSERT INTO s VALUES 152 ('-0'::decimal), 153 ('-0.0'::decimal), 154 ('-0.00'::decimal), 155 ('-0.00E-1'::decimal), 156 ('-0.0E-3'::decimal) 157 158 query R rowsort 159 SELECT * FROM s WHERE d = 0 160 ---- 161 0 162 0 163 0.0 164 0.00 165 0.000 166 0.0000 167 168 query R 169 SELECT * FROM s WHERE d IS NAN 170 ---- 171 NaN 172 NaN 173 174 query R 175 SELECT * FROM s WHERE d = 'inf'::decimal 176 ---- 177 Infinity 178 179 query R 180 SELECT * FROM s WHERE d = 'NaN' 181 ---- 182 NaN 183 NaN 184 185 # In the following tests, the various zero values all compare equal to 186 # each other so we must use two ORDER BY clauses to obtain a stable result. 187 188 # Check the ordering of decimal values. 189 query R 190 SELECT d FROM s ORDER BY d, d::TEXT 191 ---- 192 NULL 193 NaN 194 NaN 195 -Infinity 196 -1 197 0 198 0 199 0.0 200 0.00 201 0.000 202 0.0000 203 1 204 Infinity 205 206 # Just test the NaN-ness of the values. 207 query RBBB 208 SELECT d, d IS NaN, d = 'NaN', isnan(d) FROM s@{FORCE_INDEX=primary} ORDER BY d, d::TEXT 209 ---- 210 NULL NULL NULL NULL 211 NaN true true true 212 NaN true true true 213 -Infinity false false false 214 -1 false false false 215 0 false false false 216 0 false false false 217 0.0 false false false 218 0.00 false false false 219 0.000 false false false 220 0.0000 false false false 221 1 false false false 222 Infinity false false false 223 224 # Just test the NaN-ness of the values in secondary index 225 query RBBB 226 SELECT d, d IS NaN, d = 'NaN', isnan(d) FROM s@{FORCE_INDEX=s_d_idx} ORDER BY d, d::TEXT 227 ---- 228 NULL NULL NULL NULL 229 NaN true true true 230 NaN true true true 231 -Infinity false false false 232 -1 false false false 233 0 false false false 234 0 false false false 235 0.0 false false false 236 0.00 false false false 237 0.000 false false false 238 0.0000 false false false 239 1 false false false 240 Infinity false false false 241 242 query RB 243 select d, d > 'NaN' from s@{FORCE_INDEX=primary} where d > 'NaN' ORDER BY d, d::TEXT 244 ---- 245 -Infinity true 246 -1 true 247 0 true 248 0 true 249 0.0 true 250 0.00 true 251 0.000 true 252 0.0000 true 253 1 true 254 Infinity true 255 256 query RB 257 select d, d > 'NaN' from s@{FORCE_INDEX=s_d_idx} where d > 'NaN' ORDER BY d, d::TEXT 258 ---- 259 -Infinity true 260 -1 true 261 0 true 262 0 true 263 0.0 true 264 0.00 true 265 0.000 true 266 0.0000 true 267 1 true 268 Infinity true 269 270 # Verify that decimals don't lose trailing 0s even when used for an index. 271 statement ok 272 CREATE INDEX idx ON s (d) 273 274 query R rowsort 275 SELECT * FROM s@idx WHERE d = 0 276 ---- 277 0 278 0 279 0.0 280 0.00 281 0.000 282 0.0000 283 284 statement ok 285 INSERT INTO s VALUES 286 ('10'::decimal), 287 ('10.0'::decimal), 288 ('10.00'::decimal), 289 ('10.000'::decimal), 290 ('100000E-4'::decimal), 291 ('1000000E-5'::decimal), 292 ('1.0000000E+1'::decimal) 293 294 query R rowsort 295 SELECT * FROM s@primary WHERE d = 10 296 ---- 297 10 298 10.0 299 10.00 300 10.000 301 10.0000 302 10.00000 303 10.000000 304 305 query R rowsort 306 SELECT * FROM s@idx WHERE d = 10 307 ---- 308 10 309 10.0 310 10.00 311 10.000 312 10.0000 313 10.00000 314 10.000000 315 316 query R 317 SELECT 1.00::decimal(6,4) 318 ---- 319 1.0000 320 321 statement error value with precision 6, scale 4 must round to an absolute value less than 10\^2 322 SELECT 101.00::decimal(6,4) 323 324 statement error scale \(6\) must be between 0 and precision \(4\) 325 SELECT 101.00::decimal(4,6) 326 327 statement error value with precision 2, scale 2 must round to an absolute value less than 1 328 SELECT 1::decimal(2, 2) 329 330 # Regression test for #16081 331 332 statement 333 CREATE TABLE a (b DECIMAL) 334 335 statement 336 INSERT INTO a VALUES (142378208485490985369999605144727062141206925976498256305323716858805588894693616552055968571135475510700810219028167653516982373238641332965927953273383572708760984694356069974208844865675206339235758647159337463780100273189720943242182911961627806424621091859596571173867825568394327041453823674373002756096) 337 338 query R 339 SELECT * FROM a 340 ---- 341 142378208485490985369999605144727062141206925976498256305323716858805588894693616552055968571135475510700810219028167653516982373238641332965927953273383572708760984694356069974208844865675206339235758647159337463780100273189720943242182911961627806424621091859596571173867825568394327041453823674373002756096 342 343 # Verify that NaNs are returned instead of invalid operation. 344 query R 345 SELECT 'inf'::decimal + '-inf'::decimal 346 ---- 347 NaN 348 349 # Regression test for #40327 350 query R 351 SELECT 1.0 / 'Infinity' + 2 FROM a; 352 ---- 353 2.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 354 355 query R 356 SELECT 2.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 357 ---- 358 2.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000