sl@0: # 2008 April 28
sl@0: #
sl@0: # Portions Copyright (c) 2007-2008 Nokia Corporation and/or its subsidiaries. All rights reserved.
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: #
sl@0: # Ticket #3060
sl@0: #
sl@0: # Make sure IEEE floating point NaN values are handled properly.
sl@0: # SQLite should always convert NaN into NULL.
sl@0: #
sl@0: # Also verify that the decimal to IEEE754 binary conversion routines
sl@0: # correctly generate 0.0, +Inf, and -Inf as appropriate for numbers
sl@0: # out of range.
sl@0: #
sl@0: # $Id: nan.test,v 1.5 2008/09/18 11:30:13 danielk1977 Exp $
sl@0: #
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: do_test nan-1.1.1 {
sl@0:   db eval {
sl@0:     PRAGMA auto_vacuum=OFF;
sl@0:     PRAGMA page_size=1024;
sl@0:     CREATE TABLE t1(x FLOAT);
sl@0:   }
sl@0:   set ::STMT [sqlite3_prepare db "INSERT INTO t1 VALUES(?)" -1 TAIL]
sl@0:   sqlite3_bind_double $::STMT 1 NaN
sl@0:   sqlite3_step $::STMT
sl@0:   sqlite3_reset $::STMT
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {{} null}
sl@0: if {$tcl_platform(platform) != "symbian"} {
sl@0:   do_test nan-1.1.2 {
sl@0:     sqlite3_bind_double $::STMT 1 +Inf
sl@0:     sqlite3_step $::STMT
sl@0:     sqlite3_reset $::STMT
sl@0:     db eval {SELECT x, typeof(x) FROM t1}
sl@0:   } {{} null inf real}
sl@0:   do_test nan-1.1.3 {
sl@0:     sqlite3_bind_double $::STMT 1 -Inf
sl@0:     sqlite3_step $::STMT
sl@0:     sqlite3_reset $::STMT
sl@0:     db eval {SELECT x, typeof(x) FROM t1}
sl@0:   } {{} null inf real -inf real}
sl@0:   do_test nan-1.1.4 {
sl@0:     sqlite3_bind_double $::STMT 1 -NaN
sl@0:     sqlite3_step $::STMT
sl@0:     sqlite3_reset $::STMT
sl@0:     db eval {SELECT x, typeof(x) FROM t1}
sl@0:   } {{} null inf real -inf real {} null}
sl@0:   do_test nan-1.1.5 {
sl@0:     sqlite3_bind_double $::STMT 1 NaN0
sl@0:     sqlite3_step $::STMT
sl@0:     sqlite3_reset $::STMT
sl@0:     db eval {SELECT x, typeof(x) FROM t1}
sl@0:   } {{} null inf real -inf real {} null {} null}
sl@0:   do_test nan-1.1.5 {
sl@0:     sqlite3_bind_double $::STMT 1 -NaN0
sl@0:     sqlite3_step $::STMT
sl@0:     sqlite3_reset $::STMT
sl@0:     db eval {SELECT x, typeof(x) FROM t1}
sl@0:   } {{} null inf real -inf real {} null {} null {} null}
sl@0:   do_test nan-1.1.6 {
sl@0:     db eval {
sl@0:       UPDATE t1 SET x=x-x;
sl@0:       SELECT x, typeof(x) FROM t1;
sl@0:     }
sl@0:   } {{} null {} null {} null {} null {} null {} null}
sl@0: }
sl@0: 
sl@0: # The following block of tests, nan-1.2.*, are the same as the nan-1.1.*
sl@0: # tests above, except that the SELECT queries used to validate data 
sl@0: # convert floating point values to text internally before returning them
sl@0: # to Tcl. This allows the tests to be run on platforms where Tcl has
sl@0: # problems converting "inf" and "-inf" from floating point to text format.
sl@0: # It also tests the internal float->text conversion routines a bit.
sl@0: #
sl@0: do_test nan-1.2.1 {
sl@0:   db eval {
sl@0:     DELETE FROM T1;
sl@0:   }
sl@0:   sqlite3_bind_double $::STMT 1 NaN
sl@0:   sqlite3_step $::STMT
sl@0:   sqlite3_reset $::STMT
sl@0:   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}
sl@0: } {{} null}
sl@0: do_test nan-1.2.2 {
sl@0:   sqlite3_bind_double $::STMT 1 +Inf
sl@0:   sqlite3_step $::STMT
sl@0:   sqlite3_reset $::STMT
sl@0:   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}
sl@0: } {{} null Inf real}
sl@0: do_test nan-1.2.3 {
sl@0:   sqlite3_bind_double $::STMT 1 -Inf
sl@0:   sqlite3_step $::STMT
sl@0:   sqlite3_reset $::STMT
sl@0:   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}
sl@0: } {{} null Inf real -Inf real}
sl@0: do_test nan-1.2.4 {
sl@0:   sqlite3_bind_double $::STMT 1 -NaN
sl@0:   sqlite3_step $::STMT
sl@0:   sqlite3_reset $::STMT
sl@0:   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}
sl@0: } {{} null Inf real -Inf real {} null}
sl@0: do_test nan-1.2.5 {
sl@0:   sqlite3_bind_double $::STMT 1 NaN0
sl@0:   sqlite3_step $::STMT
sl@0:   sqlite3_reset $::STMT
sl@0:   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}
sl@0: } {{} null Inf real -Inf real {} null {} null}
sl@0: do_test nan-1.2.5 {
sl@0:   sqlite3_bind_double $::STMT 1 -NaN0
sl@0:   sqlite3_step $::STMT
sl@0:   sqlite3_reset $::STMT
sl@0:   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}
sl@0: } {{} null Inf real -Inf real {} null {} null {} null}
sl@0: do_test nan-1.2.6 {
sl@0:   db eval {
sl@0:     UPDATE t1 SET x=x-x;
sl@0:     SELECT CAST(x AS text), typeof(x) FROM t1;
sl@0:   }
sl@0: } {{} null {} null {} null {} null {} null {} null}
sl@0: 
sl@0: do_test nan-2.1 {
sl@0:   db eval {
sl@0:     DELETE FROM T1;
sl@0:   }
sl@0:   sqlite3_bind_double $::STMT 1 NaN
sl@0:   sqlite3_step $::STMT
sl@0:   sqlite3_reset $::STMT
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {{} null}
sl@0: sqlite3_finalize $::STMT
sl@0: 
sl@0: # SQLite always converts NaN into NULL so it is not possible to write
sl@0: # a NaN value into the database file using SQLite.  The following series
sl@0: # of tests writes a normal floating point value (0.5) into the database,
sl@0: # then writes directly into the database file to change the 0.5 into NaN.
sl@0: # Then it reads the value of the database to verify it is converted into
sl@0: # NULL.
sl@0: #
sl@0: do_test nan-3.1 {
sl@0:   db eval {
sl@0:     DELETE FROM t1;
sl@0:     INSERT INTO t1 VALUES(0.5);
sl@0:     PRAGMA auto_vacuum=OFF;
sl@0:     PRAGMA page_size=1024;
sl@0:     VACUUM;
sl@0:   }
sl@0:   hexio_read test.db 2040 8
sl@0: } {3FE0000000000000}
sl@0: do_test nan-3.2 {
sl@0:   db eval {
sl@0:     SELECT x, typeof(x) FROM t1
sl@0:   }
sl@0: } {0.5 real}
sl@0: do_test nan-3.3 {
sl@0:   db close
sl@0:   hexio_write test.db 2040 FFF8000000000000
sl@0:   sqlite3 db test.db
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {{} null}
sl@0: do_test nan-3.4 {
sl@0:   db close
sl@0:   hexio_write test.db 2040 7FF8000000000000
sl@0:   sqlite3 db test.db
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {{} null}
sl@0: do_test nan-3.5 {
sl@0:   db close
sl@0:   hexio_write test.db 2040 FFFFFFFFFFFFFFFF
sl@0:   sqlite3 db test.db
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {{} null}
sl@0: do_test nan-3.6 {
sl@0:   db close
sl@0:   hexio_write test.db 2040 7FFFFFFFFFFFFFFF
sl@0:   sqlite3 db test.db
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {{} null}
sl@0: 
sl@0: # Verify that the sqlite3AtoF routine is able to handle extreme
sl@0: # numbers.
sl@0: #
sl@0: do_test nan-4.1 {
sl@0:   db eval {DELETE FROM t1}
sl@0:   db eval "INSERT INTO t1 VALUES([string repeat 9 307].0)"
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {1e+307 real}
sl@0: do_test nan-4.2 {
sl@0:   db eval {DELETE FROM t1}
sl@0:   db eval "INSERT INTO t1 VALUES([string repeat 9 308].0)"
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {1e+308 real}
sl@0: do_test nan-4.3 {
sl@0:   db eval {DELETE FROM t1}
sl@0:   db eval "INSERT INTO t1 VALUES(-[string repeat 9 307].0)"
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {-1e+307 real}
sl@0: do_test nan-4.4 {
sl@0:   db eval {DELETE FROM t1}
sl@0:   db eval "INSERT INTO t1 VALUES(-[string repeat 9 308].0)"
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {-1e+308 real}
sl@0: do_test nan-4.5 {
sl@0:   db eval {DELETE FROM t1}
sl@0:   set big -[string repeat 0 10000][string repeat 9 308].[string repeat 0 10000]
sl@0:   db eval "INSERT INTO t1 VALUES($big)"
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {-1e+308 real}
sl@0: do_test nan-4.6 {
sl@0:   db eval {DELETE FROM t1}
sl@0:   set big [string repeat 0 10000][string repeat 9 308].[string repeat 0 10000]
sl@0:   db eval "INSERT INTO t1 VALUES($big)"
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {1e+308 real}
sl@0: 
sl@0: if {$tcl_platform(platform) != "symbian"} {
sl@0:   # Do not run these tests on Symbian, as the Tcl port doesn't like to
sl@0:   # convert from floating point value "-inf" to a string.
sl@0:   #
sl@0:   do_test nan-4.7 {
sl@0:     db eval {DELETE FROM t1}
sl@0:     db eval "INSERT INTO t1 VALUES([string repeat 9 309].0)"
sl@0:     db eval {SELECT x, typeof(x) FROM t1}
sl@0:   } {inf real}
sl@0:   do_test nan-4.8 {
sl@0:     db eval {DELETE FROM t1}
sl@0:     db eval "INSERT INTO t1 VALUES(-[string repeat 9 309].0)"
sl@0:     db eval {SELECT x, typeof(x) FROM t1}
sl@0:   } {-inf real}
sl@0: }
sl@0: do_test nan-4.9 {
sl@0:   db eval {DELETE FROM t1}
sl@0:   db eval "INSERT INTO t1 VALUES([string repeat 9 309].0)"
sl@0:   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}
sl@0: } {Inf real}
sl@0: do_test nan-4.10 {
sl@0:   db eval {DELETE FROM t1}
sl@0:   db eval "INSERT INTO t1 VALUES(-[string repeat 9 309].0)"
sl@0:   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}
sl@0: } {-Inf real}
sl@0: 
sl@0: do_test nan-4.10 {
sl@0:   db eval {DELETE FROM t1}
sl@0:   db eval "INSERT INTO t1 VALUES(1234.5[string repeat 0 10000]12345)"
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {1234.5 real}
sl@0: do_test nan-4.11 {
sl@0:   db eval {DELETE FROM t1}
sl@0:   db eval "INSERT INTO t1 VALUES(-1234.5[string repeat 0 10000]12345)"
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {-1234.5 real}
sl@0: do_test nan-4.12 {
sl@0:   db eval {DELETE FROM t1}
sl@0:   set small [string repeat 0 10000].[string repeat 0 324][string repeat 9 10000]
sl@0:   db eval "INSERT INTO t1 VALUES($small)"
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {0.0 real}
sl@0: do_test nan-4.13 {
sl@0:   db eval {DELETE FROM t1}
sl@0:   set small \
sl@0:       -[string repeat 0 10000].[string repeat 0 324][string repeat 9 10000]
sl@0:   db eval "INSERT INTO t1 VALUES($small)"
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {0.0 real}
sl@0: 
sl@0: # These tests test some really, really small floating point numbers.
sl@0: #
sl@0: if {$tcl_platform(platform) != "symbian"} {
sl@0:   # These two are not run on symbian because tcl has trouble converting
sl@0:   # the very small numbers back to text form (probably due to a difference
sl@0:   # in the sprintf() implementation).
sl@0:   #
sl@0:   do_test nan-4.14 {
sl@0:     db eval {DELETE FROM t1}
sl@0:     set small \
sl@0:         [string repeat 0 10000].[string repeat 0 323][string repeat 9 10000]
sl@0:     db eval "INSERT INTO t1 VALUES($small)"
sl@0:     db eval {SELECT x, typeof(x) FROM t1}
sl@0:   } {9.88131291682493e-324 real}
sl@0:   do_test nan-4.15 {
sl@0:     db eval {DELETE FROM t1}
sl@0:     set small \
sl@0:         -[string repeat 0 10000].[string repeat 0 323][string repeat 9 10000]
sl@0:     db eval "INSERT INTO t1 VALUES($small)"
sl@0:     db eval {SELECT x, typeof(x) FROM t1}
sl@0:   } {-9.88131291682493e-324 real}
sl@0: }
sl@0: #
sl@0: # Symbian OS: the next test fails due to problems in sprintf/printf formatting. 
sl@0: if {$::tcl_platform(platform)!="symbian"} {
sl@0:   do_test nan-4.16 {
sl@0:     db eval {DELETE FROM t1}
sl@0:     set small [string repeat 0 10000].[string repeat 0 323][string repeat 9 10000]
sl@0:     db eval "INSERT INTO t1 VALUES($small)"
sl@0:     db eval {SELECT CAST(x AS text), typeof(x) FROM t1}
sl@0:   } {9.88131291682493e-324 real}
sl@0: }
sl@0: #
sl@0: # Symbian OS: the next test fails due to problems in sprintf/printf formatting. 
sl@0: if {$::tcl_platform(platform)!="symbian"} {
sl@0: do_test nan-4.17 {
sl@0:     db eval {DELETE FROM t1}
sl@0:     set small \
sl@0:         -[string repeat 0 10000].[string repeat 0 323][string repeat 9 10000]
sl@0:     db eval "INSERT INTO t1 VALUES($small)"
sl@0:     db eval {SELECT CAST(x AS text), typeof(x) FROM t1}
sl@0:   } {-9.88131291682493e-324 real}
sl@0: }
sl@0: do_test nan-4.20 {
sl@0:   db eval {DELETE FROM t1}
sl@0:   set big [string repeat 9 10000].0e-9000
sl@0:   db eval "INSERT INTO t1 VALUES($big)"
sl@0:   db eval {SELECT x, typeof(x) FROM t1}
sl@0: } {{} null}
sl@0: 
sl@0: 
sl@0: 
sl@0: finish_test