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_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_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_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_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_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 do_test nan-3.1 { 155 db eval { 156 DELETE FROM t1; 157 INSERT INTO t1 VALUES(0.5); 158 PRAGMA auto_vacuum=OFF; 159 PRAGMA page_size=1024; 160 VACUUM; 161 } 162 hexio_read test.db 2040 8 163 } {3FE0000000000000} 164 do_test nan-3.2 { 165 db eval { 166 SELECT x, typeof(x) FROM t1 167 } 168 } {0.5 real} 169 do_test nan-3.3 { 170 db close 171 hexio_write test.db 2040 FFF8000000000000 172 sqlite3 db test.db 173 db eval {SELECT x, typeof(x) FROM t1} 174 } {{} null} 175 do_test nan-3.4 { 176 db close 177 hexio_write test.db 2040 7FF8000000000000 178 sqlite3 db test.db 179 db eval {SELECT x, typeof(x) FROM t1} 180 } {{} null} 181 do_test nan-3.5 { 182 db close 183 hexio_write test.db 2040 FFFFFFFFFFFFFFFF 184 sqlite3 db test.db 185 db eval {SELECT x, typeof(x) FROM t1} 186 } {{} null} 187 do_test nan-3.6 { 188 db close 189 hexio_write test.db 2040 7FFFFFFFFFFFFFFF 190 sqlite3 db test.db 191 db eval {SELECT x, typeof(x) FROM t1} 192 } {{} null} 193 194 # Verify that the sqlite3AtoF routine is able to handle extreme 195 # numbers. 196 # 197 do_test nan-4.1 { 198 db eval {DELETE FROM t1} 199 db eval "INSERT INTO t1 VALUES([string repeat 9 307].0)" 200 db eval {SELECT x, typeof(x) FROM t1} 201 } {1e+307 real} 202 do_test nan-4.2 { 203 db eval {DELETE FROM t1} 204 db eval "INSERT INTO t1 VALUES([string repeat 9 308].0)" 205 db eval {SELECT x, typeof(x) FROM t1} 206 } {1e+308 real} 207 do_test nan-4.3 { 208 db eval {DELETE FROM t1} 209 db eval "INSERT INTO t1 VALUES(-[string repeat 9 307].0)" 210 db eval {SELECT x, typeof(x) FROM t1} 211 } {-1e+307 real} 212 do_test nan-4.4 { 213 db eval {DELETE FROM t1} 214 db eval "INSERT INTO t1 VALUES(-[string repeat 9 308].0)" 215 db eval {SELECT x, typeof(x) FROM t1} 216 } {-1e+308 real} 217 do_test nan-4.5 { 218 db eval {DELETE FROM t1} 219 set big -[string repeat 0 10000][string repeat 9 308].[string repeat 0 10000] 220 db eval "INSERT INTO t1 VALUES($big)" 221 db eval {SELECT x, typeof(x) FROM t1} 222 } {-1e+308 real} 223 do_test nan-4.6 { 224 db eval {DELETE FROM t1} 225 set big [string repeat 0 10000][string repeat 9 308].[string repeat 0 10000] 226 db eval "INSERT INTO t1 VALUES($big)" 227 db eval {SELECT x, typeof(x) FROM t1} 228 } {1e+308 real} 229 230 if {$tcl_platform(platform) != "symbian"} { 231 # Do not run these tests on Symbian, as the Tcl port doesn't like to 232 # convert from floating point value "-inf" to a string. 233 # 234 do_test nan-4.7 { 235 db eval {DELETE FROM t1} 236 db eval "INSERT INTO t1 VALUES([string repeat 9 309].0)" 237 db eval {SELECT x, typeof(x) FROM t1} 238 } {inf real} 239 do_test nan-4.8 { 240 db eval {DELETE FROM t1} 241 db eval "INSERT INTO t1 VALUES(-[string repeat 9 309].0)" 242 db eval {SELECT x, typeof(x) FROM t1} 243 } {-inf real} 244 } 245 do_test nan-4.9 { 246 db eval {DELETE FROM t1} 247 db eval "INSERT INTO t1 VALUES([string repeat 9 309].0)" 248 db eval {SELECT CAST(x AS text), typeof(x) FROM t1} 249 } {Inf real} 250 do_test nan-4.10 { 251 db eval {DELETE FROM t1} 252 db eval "INSERT INTO t1 VALUES(-[string repeat 9 309].0)" 253 db eval {SELECT CAST(x AS text), typeof(x) FROM t1} 254 } {-Inf real} 255 256 do_test nan-4.11 { 257 db eval {DELETE FROM t1} 258 db eval "INSERT INTO t1 VALUES(1234.5[string repeat 0 10000]12345)" 259 db eval {SELECT x, typeof(x) FROM t1} 260 } {1234.5 real} 261 do_test nan-4.12 { 262 db eval {DELETE FROM t1} 263 db eval "INSERT INTO t1 VALUES(-1234.5[string repeat 0 10000]12345)" 264 db eval {SELECT x, typeof(x) FROM t1} 265 } {-1234.5 real} 266 do_test nan-4.13 { 267 db eval {DELETE FROM t1} 268 set small [string repeat 0 10000].[string repeat 0 324][string repeat 9 10000] 269 db eval "INSERT INTO t1 VALUES($small)" 270 db eval {SELECT x, typeof(x) FROM t1} 271 } {0.0 real} 272 do_test nan-4.14 { 273 db eval {DELETE FROM t1} 274 set small \ 275 -[string repeat 0 10000].[string repeat 0 324][string repeat 9 10000] 276 db eval "INSERT INTO t1 VALUES($small)" 277 db eval {SELECT x, typeof(x) FROM t1} 278 } {0.0 real} 279 280 # These tests test some really, really small floating point numbers. 281 # 282 if {$tcl_platform(platform) != "symbian"} { 283 # These two are not run on symbian because tcl has trouble converting 284 # the very small numbers back to text form (probably due to a difference 285 # in the sprintf() implementation). 286 # 287 do_test nan-4.15 { 288 db eval {DELETE FROM t1} 289 set small \ 290 [string repeat 0 10000].[string repeat 0 323][string repeat 9 10000] 291 db eval "INSERT INTO t1 VALUES($small)" 292 db eval {SELECT x, typeof(x) FROM t1} 293 } {9.88131291682493e-324 real} 294 do_test nan-4.16 { 295 db eval {DELETE FROM t1} 296 set small \ 297 -[string repeat 0 10000].[string repeat 0 323][string repeat 9 10000] 298 db eval "INSERT INTO t1 VALUES($small)" 299 db eval {SELECT x, typeof(x) FROM t1} 300 } {-9.88131291682493e-324 real} 301 } 302 do_test nan-4.17 { 303 db eval {DELETE FROM t1} 304 set small [string repeat 0 10000].[string repeat 0 323][string repeat 9 10000] 305 db eval "INSERT INTO t1 VALUES($small)" 306 db eval {SELECT CAST(x AS text), typeof(x) FROM t1} 307 } {9.88131291682493e-324 real} 308 do_test nan-4.18 { 309 db eval {DELETE FROM t1} 310 set small \ 311 -[string repeat 0 10000].[string repeat 0 323][string repeat 9 10000] 312 db eval "INSERT INTO t1 VALUES($small)" 313 db eval {SELECT CAST(x AS text), typeof(x) FROM t1} 314 } {-9.88131291682493e-324 real} 315 316 do_test nan-4.20 { 317 db eval {DELETE FROM t1} 318 set big [string repeat 9 10000].0e-9000 319 db eval "INSERT INTO t1 VALUES($big)" 320 db eval {SELECT x, typeof(x) FROM t1} 321 } {inf real} 322 323 324 325 finish_test 326
