AryaWu/sqlite
0
1# 2008 April 282#3# The author disclaims copyright to this source code. In place of4# 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 #306013#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 routines18# correctly generate 0.0, +Inf, and -Inf as appropriate for numbers19# out of range.20#21# $Id: nan.test,v 1.5 2008/09/18 11:30:13 danielk1977 Exp $22#23 24set testdir [file dirname $argv0]25source $testdir/tester.tcl26 27# Do not use a codec for tests in this file, as the database file is28# manipulated directly using tcl scripts (using the [hexio_write] command).29#30do_not_use_codec31 32do_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 NaN40 sqlite3_step $::STMT41 sqlite3_reset $::STMT42 db eval {SELECT x, typeof(x) FROM t1}43} {{} null}44if {$tcl_platform(platform) != "symbian"} {45 do_realnum_test nan-1.1.2 {46 sqlite3_bind_double $::STMT 1 +Inf47 sqlite3_step $::STMT48 sqlite3_reset $::STMT49 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 -Inf53 sqlite3_step $::STMT54 sqlite3_reset $::STMT55 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 -NaN59 sqlite3_step $::STMT60 sqlite3_reset $::STMT61 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 NaN065 sqlite3_step $::STMT66 sqlite3_reset $::STMT67 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 -NaN071 sqlite3_step $::STMT72 sqlite3_reset $::STMT73 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 them86# to Tcl. This allows the tests to be run on platforms where Tcl has87# problems converting "inf" and "-inf" from floating point to text format.88# It also tests the internal float->text conversion routines a bit.89#90do_test nan-1.2.1 {91 db eval {92 DELETE FROM T1;93 }94 sqlite3_bind_double $::STMT 1 NaN95 sqlite3_step $::STMT96 sqlite3_reset $::STMT97 db eval {SELECT CAST(x AS text), typeof(x) FROM t1}98} {{} null}99do_test nan-1.2.2 {100 sqlite3_bind_double $::STMT 1 +Inf101 sqlite3_step $::STMT102 sqlite3_reset $::STMT103 db eval {SELECT CAST(x AS text), typeof(x) FROM t1}104} {{} null Inf real}105do_test nan-1.2.3 {106 sqlite3_bind_double $::STMT 1 -Inf107 sqlite3_step $::STMT108 sqlite3_reset $::STMT109 db eval {SELECT CAST(x AS text), typeof(x) FROM t1}110} {{} null Inf real -Inf real}111do_test nan-1.2.4 {112 sqlite3_bind_double $::STMT 1 -NaN113 sqlite3_step $::STMT114 sqlite3_reset $::STMT115 db eval {SELECT CAST(x AS text), typeof(x) FROM t1}116} {{} null Inf real -Inf real {} null}117do_test nan-1.2.5 {118 sqlite3_bind_double $::STMT 1 NaN0119 sqlite3_step $::STMT120 sqlite3_reset $::STMT121 db eval {SELECT CAST(x AS text), typeof(x) FROM t1}122} {{} null Inf real -Inf real {} null {} null}123do_test nan-1.2.6 {124 sqlite3_bind_double $::STMT 1 -NaN0125 sqlite3_step $::STMT126 sqlite3_reset $::STMT127 db eval {SELECT CAST(x AS text), typeof(x) FROM t1}128} {{} null Inf real -Inf real {} null {} null {} null}129do_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 136do_test nan-2.1 {137 db eval {138 DELETE FROM T1;139 }140 sqlite3_bind_double $::STMT 1 NaN141 sqlite3_step $::STMT142 sqlite3_reset $::STMT143 db eval {SELECT x, typeof(x) FROM t1}144} {{} null}145sqlite3_finalize $::STMT146 147# SQLite always converts NaN into NULL so it is not possible to write148# a NaN value into the database file using SQLite. The following series149# 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 into152# NULL.153#154if {![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 8164 } {3FE0000000000000}165 do_test nan-3.2 {166 db eval {167 SELECT x, typeof(x) FROM t1168 }169 } {0.5 real}170 do_test nan-3.3 {171 db close172 hexio_write test.db 2040 FFF8000000000000173 sqlite3 db test.db174 db eval {SELECT x, typeof(x) FROM t1}175 } {{} null}176 do_test nan-3.4 {177 db close178 hexio_write test.db 2040 7FF8000000000000179 sqlite3 db test.db180 db eval {SELECT x, typeof(x) FROM t1}181 } {{} null}182 do_test nan-3.5 {183 db close184 hexio_write test.db 2040 FFFFFFFFFFFFFFFF185 sqlite3 db test.db186 db eval {SELECT x, typeof(x) FROM t1}187 } {{} null}188 do_test nan-3.6 {189 db close190 hexio_write test.db 2040 7FFFFFFFFFFFFFFF191 sqlite3 db test.db192 db eval {SELECT x, typeof(x) FROM t1}193 } {{} null}194}195 196# Verify that the sqlite3AtoF routine is able to handle extreme197# numbers.198#199do_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}204do_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}209do_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}214do_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}219do_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}225do_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 232if {$tcl_platform(platform) != "symbian"} {233 # Do not run these tests on Symbian, as the Tcl port doesn't like to234 # 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}247do_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}252do_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 258do_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}263do_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}268do_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}274do_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#284load_static_extension db decimal285if {$tcl_platform(platform) != "symbian"} {286 # These two are not run on symbian because tcl has trouble converting287 # the very small numbers back to text form (probably due to a difference288 # in the sprintf() implementation).289 #290 do_test nan-4.15 {291 db eval {DELETE FROM t1}292 set small \293 [string repeat 0 10000].[string repeat 0 323][string repeat 9 10000]294 db eval "INSERT INTO t1 VALUES($small)"295 db eval {SELECT decimal_exp(x), typeof(x) FROM t1}296 } {/9\.88131291682493\d*e-324 real/}297 do_test nan-4.16 {298 db eval {DELETE FROM t1}299 set small \300 -[string repeat 0 10000].[string repeat 0 323][string repeat 9 10000]301 db eval "INSERT INTO t1 VALUES($small)"302 db eval {SELECT decimal_exp(x), typeof(x) FROM t1}303 } {/-9\.88131291682493\d*e-324 real/}304}305do_test nan-4.17 {306 db eval {DELETE FROM t1}307 set small [string repeat 0 10000].[string repeat 0 323][string repeat 9 10000]308 db eval "INSERT INTO t1 VALUES($small)"309 db eval {SELECT CAST(x AS text), typeof(x) FROM t1}310} {9.88131291682493e-324 real}311do_test nan-4.18 {312 db eval {DELETE FROM t1}313 set small \314 -[string repeat 0 10000].[string repeat 0 323][string repeat 9 10000]315 db eval "INSERT INTO t1 VALUES($small)"316 db eval {SELECT CAST(x AS text), typeof(x) FROM t1}317} {-9.88131291682493e-324 real}318 319do_realnum_test nan-4.20 {320 db eval {DELETE FROM t1}321 set big [string repeat 9 10000].0e-9000322 db eval "INSERT INTO t1 VALUES($big)"323 db eval {SELECT x, typeof(x) FROM t1}324} {inf real}325 326do_realnum_test nan-4.30 {327 db eval {328 DELETE FROM t1;329 INSERT INTO t1 VALUES('2.5e+9999');330 SELECT x, typeof(x) FROM t1;331 }332} {inf real}333do_realnum_test nan-4.31 {334 db eval {335 DELETE FROM t1;336 INSERT INTO t1 VALUES('2.5e+10000');337 SELECT x, typeof(x) FROM t1;338 }339} {inf real}340 341do_realnum_test nan-4.32 {342 db eval {343 DELETE FROM t1;344 INSERT INTO t1 VALUES('2.5e-9999');345 SELECT x, typeof(x) FROM t1;346 }347} {0.0 real}348do_realnum_test nan-4.33 {349 db eval {350 DELETE FROM t1;351 INSERT INTO t1 VALUES('2.5e-10000');352 SELECT x, typeof(x) FROM t1;353 }354} {0.0 real}355do_realnum_test nan-4.34 {356 db eval {357 DELETE FROM t1;358 INSERT INTO t1 VALUES('2.5e2147483650');359 SELECT x, typeof(x) FROM t1;360 }361} {inf real}362do_realnum_test nan-4.35 {363 db eval {364 DELETE FROM t1;365 INSERT INTO t1 VALUES('2.5e-2147483650');366 SELECT x, typeof(x) FROM t1;367 }368} {0.0 real}369 370do_realnum_test nan-4.40 {371 db eval {372 SELECT cast('-1e999' AS real);373 }374} {-inf}375 376finish_test377