gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/nan.test (about) 1 # 2008 April 28 2 # 3 # The author disclaims copyright to this source code. In place of 4 # a legal notice, here is a blessing: 5 # 6 # May you do good and not evil. 7 # May you find forgiveness for yourself and forgive others. 8 # May you share freely, never taking more than you give. 9 # 10 #*********************************************************************** 11 # 12 # Ticket #3060 13 # 14 # Make sure IEEE floating point NaN values are handled properly. 15 # SQLite should always convert NaN into NULL. 16 # 17 # Also verify that the decimal to IEEE754 binary conversion routines 18 # correctly generate 0.0, +Inf, and -Inf as appropriate for numbers 19 # out of range. 20 # 21 # $Id: nan.test,v 1.5 2008/09/18 11:30:13 danielk1977 Exp $ 22 # 23 24 set testdir [file dirname $argv0] 25 source $testdir/tester.tcl 26 27 # Do not use a codec for tests in this file, as the database file is 28 # manipulated directly using tcl scripts (using the [hexio_write] command). 29 # 30 do_not_use_codec 31 32 do_test nan-1.1.1 { 33 db eval { 34 PRAGMA auto_vacuum=OFF; 35 PRAGMA page_size=1024; 36 CREATE TABLE t1(x FLOAT); 37 } 38 set ::STMT [sqlite3_prepare db "INSERT INTO t1 VALUES(?)" -1 TAIL] 39 sqlite3_bind_double $::STMT 1 NaN 40 sqlite3_step $::STMT 41 sqlite3_reset $::STMT 42 db eval {SELECT x, typeof(x) FROM t1} 43 } {{} null} 44 if {$tcl_platform(platform) != "symbian"} { 45 do_realnum_test nan-1.1.2 { 46 sqlite3_bind_double $::STMT 1 +Inf 47 sqlite3_step $::STMT 48 sqlite3_reset $::STMT 49 db eval {SELECT x, typeof(x) FROM t1} 50 } {{} null inf real} 51 do_realnum_test nan-1.1.3 { 52 sqlite3_bind_double $::STMT 1 -Inf 53 sqlite3_step $::STMT 54 sqlite3_reset $::STMT 55 db eval {SELECT x, typeof(x) FROM t1} 56 } {{} null inf real -inf real} 57 do_realnum_test nan-1.1.4 { 58 sqlite3_bind_double $::STMT 1 -NaN 59 sqlite3_step $::STMT 60 sqlite3_reset $::STMT 61 db eval {SELECT x, typeof(x) FROM t1} 62 } {{} null inf real -inf real {} null} 63 do_realnum_test nan-1.1.5 { 64 sqlite3_bind_double $::STMT 1 NaN0 65 sqlite3_step $::STMT 66 sqlite3_reset $::STMT 67 db eval {SELECT x, typeof(x) FROM t1} 68 } {{} null inf real -inf real {} null {} null} 69 do_realnum_test nan-1.1.6 { 70 sqlite3_bind_double $::STMT 1 -NaN0 71 sqlite3_step $::STMT 72 sqlite3_reset $::STMT 73 db eval {SELECT x, typeof(x) FROM t1} 74 } {{} null inf real -inf real {} null {} null {} null} 75 do_test nan-1.1.7 { 76 db eval { 77 UPDATE t1 SET x=x-x; 78 SELECT x, typeof(x) FROM t1; 79 } 80 } {{} null {} null {} null {} null {} null {} null} 81 } 82 83 # The following block of tests, nan-1.2.*, are the same as the nan-1.1.* 84 # tests above, except that the SELECT queries used to validate data 85 # convert floating point values to text internally before returning them 86 # to Tcl. This allows the tests to be run on platforms where Tcl has 87 # problems converting "inf" and "-inf" from floating point to text format. 88 # It also tests the internal float->text conversion routines a bit. 89 # 90 do_test nan-1.2.1 { 91 db eval { 92 DELETE FROM T1; 93 } 94 sqlite3_bind_double $::STMT 1 NaN 95 sqlite3_step $::STMT 96 sqlite3_reset $::STMT 97 db eval {SELECT CAST(x AS text), typeof(x) FROM t1} 98 } {{} null} 99 do_test nan-1.2.2 { 100 sqlite3_bind_double $::STMT 1 +Inf 101 sqlite3_step $::STMT 102 sqlite3_reset $::STMT 103 db eval {SELECT CAST(x AS text), typeof(x) FROM t1} 104 } {{} null Inf real} 105 do_test nan-1.2.3 { 106 sqlite3_bind_double $::STMT 1 -Inf 107 sqlite3_step $::STMT 108 sqlite3_reset $::STMT 109 db eval {SELECT CAST(x AS text), typeof(x) FROM t1} 110 } {{} null Inf real -Inf real} 111 do_test nan-1.2.4 { 112 sqlite3_bind_double $::STMT 1 -NaN 113 sqlite3_step $::STMT 114 sqlite3_reset $::STMT 115 db eval {SELECT CAST(x AS text), typeof(x) FROM t1} 116 } {{} null Inf real -Inf real {} null} 117 do_test nan-1.2.5 { 118 sqlite3_bind_double $::STMT 1 NaN0 119 sqlite3_step $::STMT 120 sqlite3_reset $::STMT 121 db eval {SELECT CAST(x AS text), typeof(x) FROM t1} 122 } {{} null Inf real -Inf real {} null {} null} 123 do_test nan-1.2.6 { 124 sqlite3_bind_double $::STMT 1 -NaN0 125 sqlite3_step $::STMT 126 sqlite3_reset $::STMT 127 db eval {SELECT CAST(x AS text), typeof(x) FROM t1} 128 } {{} null Inf real -Inf real {} null {} null {} null} 129 do_test nan-1.2.7 { 130 db eval { 131 UPDATE t1 SET x=x-x; 132 SELECT CAST(x AS text), typeof(x) FROM t1; 133 } 134 } {{} null {} null {} null {} null {} null {} null} 135 136 do_test nan-2.1 { 137 db eval { 138 DELETE FROM T1; 139 } 140 sqlite3_bind_double $::STMT 1 NaN 141 sqlite3_step $::STMT 142 sqlite3_reset $::STMT 143 db eval {SELECT x, typeof(x) FROM t1} 144 } {{} null} 145 sqlite3_finalize $::STMT 146 147 # SQLite always converts NaN into NULL so it is not possible to write 148 # a NaN value into the database file using SQLite. The following series 149 # of tests writes a normal floating point value (0.5) into the database, 150 # then writes directly into the database file to change the 0.5 into NaN. 151 # Then it reads the value of the database to verify it is converted into 152 # NULL. 153 # 154 if {![nonzero_reserved_bytes]} { 155 do_test nan-3.1 { 156 db eval { 157 DELETE FROM t1; 158 INSERT INTO t1 VALUES(0.5); 159 PRAGMA auto_vacuum=OFF; 160 PRAGMA page_size=1024; 161 VACUUM; 162 } 163 hexio_read test.db 2040 8 164 } {3FE0000000000000} 165 do_test nan-3.2 { 166 db eval { 167 SELECT x, typeof(x) FROM t1 168 } 169 } {0.5 real} 170 do_test nan-3.3 { 171 db close 172 hexio_write test.db 2040 FFF8000000000000 173 sqlite3 db test.db 174 db eval {SELECT x, typeof(x) FROM t1} 175 } {{} null} 176 do_test nan-3.4 { 177 db close 178 hexio_write test.db 2040 7FF8000000000000 179 sqlite3 db test.db 180 db eval {SELECT x, typeof(x) FROM t1} 181 } {{} null} 182 do_test nan-3.5 { 183 db close 184 hexio_write test.db 2040 FFFFFFFFFFFFFFFF 185 sqlite3 db test.db 186 db eval {SELECT x, typeof(x) FROM t1} 187 } {{} null} 188 do_test nan-3.6 { 189 db close 190 hexio_write test.db 2040 7FFFFFFFFFFFFFFF 191 sqlite3 db test.db 192 db eval {SELECT x, typeof(x) FROM t1} 193 } {{} null} 194 } 195 196 # Verify that the sqlite3AtoF routine is able to handle extreme 197 # numbers. 198 # 199 do_test nan-4.1 { 200 db eval {DELETE FROM t1} 201 db eval "INSERT INTO t1 VALUES([string repeat 9 307].0)" 202 db eval {SELECT x, typeof(x) FROM t1} 203 } {1e+307 real} 204 do_test nan-4.2 { 205 db eval {DELETE FROM t1} 206 db eval "INSERT INTO t1 VALUES([string repeat 9 308].0)" 207 db eval {SELECT x, typeof(x) FROM t1} 208 } {1e+308 real} 209 do_test nan-4.3 { 210 db eval {DELETE FROM t1} 211 db eval "INSERT INTO t1 VALUES(-[string repeat 9 307].0)" 212 db eval {SELECT x, typeof(x) FROM t1} 213 } {-1e+307 real} 214 do_test nan-4.4 { 215 db eval {DELETE FROM t1} 216 db eval "INSERT INTO t1 VALUES(-[string repeat 9 308].0)" 217 db eval {SELECT x, typeof(x) FROM t1} 218 } {-1e+308 real} 219 do_test nan-4.5 { 220 db eval {DELETE FROM t1} 221 set big -[string repeat 0 10000][string repeat 9 308].[string repeat 0 10000] 222 db eval "INSERT INTO t1 VALUES($big)" 223 db eval {SELECT x, typeof(x) FROM t1} 224 } {-1e+308 real} 225 do_test nan-4.6 { 226 db eval {DELETE FROM t1} 227 set big [string repeat 0 10000][string repeat 9 308].[string repeat 0 10000] 228 db eval "INSERT INTO t1 VALUES($big)" 229 db eval {SELECT x, typeof(x) FROM t1} 230 } {1e+308 real} 231 232 if {$tcl_platform(platform) != "symbian"} { 233 # Do not run these tests on Symbian, as the Tcl port doesn't like to 234 # convert from floating point value "-inf" to a string. 235 # 236 do_realnum_test nan-4.7 { 237 db eval {DELETE FROM t1} 238 db eval "INSERT INTO t1 VALUES([string repeat 9 309].0)" 239 db eval {SELECT x, typeof(x) FROM t1} 240 } {inf real} 241 do_realnum_test nan-4.8 { 242 db eval {DELETE FROM t1} 243 db eval "INSERT INTO t1 VALUES(-[string repeat 9 309].0)" 244 db eval {SELECT x, typeof(x) FROM t1} 245 } {-inf real} 246 } 247 do_test nan-4.9 { 248 db eval {DELETE FROM t1} 249 db eval "INSERT INTO t1 VALUES([string repeat 9 309].0)" 250 db eval {SELECT CAST(x AS text), typeof(x) FROM t1} 251 } {Inf real} 252 do_test nan-4.10 { 253 db eval {DELETE FROM t1} 254 db eval "INSERT INTO t1 VALUES(-[string repeat 9 309].0)" 255 db eval {SELECT CAST(x AS text), typeof(x) FROM t1} 256 } {-Inf real} 257 258 do_test nan-4.11 { 259 db eval {DELETE FROM t1} 260 db eval "INSERT INTO t1 VALUES(1234.5[string repeat 0 10000]12345)" 261 db eval {SELECT x, typeof(x) FROM t1} 262 } {1234.5 real} 263 do_test nan-4.12 { 264 db eval {DELETE FROM t1} 265 db eval "INSERT INTO t1 VALUES(-1234.5[string repeat 0 10000]12345)" 266 db eval {SELECT x, typeof(x) FROM t1} 267 } {-1234.5 real} 268 do_test nan-4.13 { 269 db eval {DELETE FROM t1} 270 set small [string repeat 0 10000].[string repeat 0 324][string repeat 9 10000] 271 db eval "INSERT INTO t1 VALUES($small)" 272 db eval {SELECT x, typeof(x) FROM t1} 273 } {0.0 real} 274 do_test nan-4.14 { 275 db eval {DELETE FROM t1} 276 set small \ 277 -[string repeat 0 10000].[string repeat 0 324][string repeat 9 10000] 278 db eval "INSERT INTO t1 VALUES($small)" 279 db eval {SELECT x, typeof(x) FROM t1} 280 } {0.0 real} 281 282 # These tests test some really, really small floating point numbers. 283 # 284 if {$tcl_platform(platform) != "symbian"} { 285 # These two are not run on symbian because tcl has trouble converting 286 # the very small numbers back to text form (probably due to a difference 287 # in the sprintf() implementation). 288 # 289 do_test nan-4.15 { 290 db eval {DELETE FROM t1} 291 set small \ 292 [string repeat 0 10000].[string repeat 0 323][string repeat 9 10000] 293 db eval "INSERT INTO t1 VALUES($small)" 294 db eval {SELECT x, typeof(x) FROM t1} 295 } {9.88131291682493e-324 real} 296 do_test nan-4.16 { 297 db eval {DELETE FROM t1} 298 set small \ 299 -[string repeat 0 10000].[string repeat 0 323][string repeat 9 10000] 300 db eval "INSERT INTO t1 VALUES($small)" 301 db eval {SELECT x, typeof(x) FROM t1} 302 } {-9.88131291682493e-324 real} 303 } 304 do_test nan-4.17 { 305 db eval {DELETE FROM t1} 306 set small [string repeat 0 10000].[string repeat 0 323][string repeat 9 10000] 307 db eval "INSERT INTO t1 VALUES($small)" 308 db eval {SELECT CAST(x AS text), typeof(x) FROM t1} 309 } {9.88131291682493e-324 real} 310 do_test nan-4.18 { 311 db eval {DELETE FROM t1} 312 set small \ 313 -[string repeat 0 10000].[string repeat 0 323][string repeat 9 10000] 314 db eval "INSERT INTO t1 VALUES($small)" 315 db eval {SELECT CAST(x AS text), typeof(x) FROM t1} 316 } {-9.88131291682493e-324 real} 317 318 do_realnum_test nan-4.20 { 319 db eval {DELETE FROM t1} 320 set big [string repeat 9 10000].0e-9000 321 db eval "INSERT INTO t1 VALUES($big)" 322 db eval {SELECT x, typeof(x) FROM t1} 323 } {inf real} 324 325 do_realnum_test nan-4.30 { 326 db eval { 327 DELETE FROM t1; 328 INSERT INTO t1 VALUES('2.5e+9999'); 329 SELECT x, typeof(x) FROM t1; 330 } 331 } {inf real} 332 do_realnum_test nan-4.31 { 333 db eval { 334 DELETE FROM t1; 335 INSERT INTO t1 VALUES('2.5e+10000'); 336 SELECT x, typeof(x) FROM t1; 337 } 338 } {inf real} 339 340 do_realnum_test nan-4.32 { 341 db eval { 342 DELETE FROM t1; 343 INSERT INTO t1 VALUES('2.5e-9999'); 344 SELECT x, typeof(x) FROM t1; 345 } 346 } {0.0 real} 347 do_realnum_test nan-4.33 { 348 db eval { 349 DELETE FROM t1; 350 INSERT INTO t1 VALUES('2.5e-10000'); 351 SELECT x, typeof(x) FROM t1; 352 } 353 } {0.0 real} 354 do_realnum_test nan-4.34 { 355 db eval { 356 DELETE FROM t1; 357 INSERT INTO t1 VALUES('2.5e2147483650'); 358 SELECT x, typeof(x) FROM t1; 359 } 360 } {inf real} 361 do_realnum_test nan-4.35 { 362 db eval { 363 DELETE FROM t1; 364 INSERT INTO t1 VALUES('2.5e-2147483650'); 365 SELECT x, typeof(x) FROM t1; 366 } 367 } {0.0 real} 368 369 do_realnum_test nan-4.40 { 370 db eval { 371 SELECT cast('-1e999' AS real); 372 } 373 } {-inf} 374 375 finish_test