| blob | df3f65b8e6229bcbe7744949ac34ef4f5df4c3a4 |
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 #
24 set testdir [file dirname $argv0]
25 source $testdir/tester.tcl
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
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 }
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}
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
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}
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}
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_realnum_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_realnum_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}
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}
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}
316 do_realnum_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}
323 do_realnum_test nan-4.30 {
324 db eval {
325 DELETE FROM t1;
326 INSERT INTO t1 VALUES('2.5e+9999');
327 SELECT x, typeof(x) FROM t1;
328 }
329 } {inf real}
330 do_realnum_test nan-4.31 {
331 db eval {
332 DELETE FROM t1;
333 INSERT INTO t1 VALUES('2.5e+10000');
334 SELECT x, typeof(x) FROM t1;
335 }
336 } {inf real}
338 do_realnum_test nan-4.32 {
339 db eval {
340 DELETE FROM t1;
341 INSERT INTO t1 VALUES('2.5e-9999');
342 SELECT x, typeof(x) FROM t1;
343 }
344 } {0.0 real}
345 do_realnum_test nan-4.33 {
346 db eval {
347 DELETE FROM t1;
348 INSERT INTO t1 VALUES('2.5e-10000');
349 SELECT x, typeof(x) FROM t1;
350 }
351 } {0.0 real}
352 do_realnum_test nan-4.34 {
353 db eval {
354 DELETE FROM t1;
355 INSERT INTO t1 VALUES('2.5e2147483650');
356 SELECT x, typeof(x) FROM t1;
357 }
358 } {inf real}
359 do_realnum_test nan-4.35 {
360 db eval {
361 DELETE FROM t1;
362 INSERT INTO t1 VALUES('2.5e-2147483650');
363 SELECT x, typeof(x) FROM t1;
364 }
365 } {0.0 real}
371 finish_test