# 2008 April 28

 #

 # Portions Copyright (c) 2007-2008 Nokia Corporation and/or its subsidiaries. All rights reserved.

 #

 # The author disclaims copyright to this source code.  In place of

 # a legal notice, here is a blessing:

 #

 #    May you do good and not evil.

 #    May you find forgiveness for yourself and forgive others.

 #    May you share freely, never taking more than you give.

 #

 #***********************************************************************

 #

 # Ticket #3060

 #

 # Make sure IEEE floating point NaN values are handled properly.

 # SQLite should always convert NaN into NULL.

 #

 # Also verify that the decimal to IEEE754 binary conversion routines

 # correctly generate 0.0, +Inf, and -Inf as appropriate for numbers

 # out of range.

 #

 # $Id: nan.test,v 1.5 2008/09/18 11:30:13 danielk1977 Exp $

 #

 set testdir [file dirname $argv0]

 source $testdir/tester.tcl

 do_test nan-1.1.1 {

   db eval {

     PRAGMA auto_vacuum=OFF;

     PRAGMA page_size=1024;

     CREATE TABLE t1(x FLOAT);

   }

   set ::STMT [sqlite3_prepare db "INSERT INTO t1 VALUES(?)" -1 TAIL]

   sqlite3_bind_double $::STMT 1 NaN

   sqlite3_step $::STMT

   sqlite3_reset $::STMT

   db eval {SELECT x, typeof(x) FROM t1}

 } {{} null}

 if {$tcl_platform(platform) != "symbian"} {

   do_test nan-1.1.2 {

     sqlite3_bind_double $::STMT 1 +Inf

     sqlite3_step $::STMT

     sqlite3_reset $::STMT

     db eval {SELECT x, typeof(x) FROM t1}

   } {{} null inf real}

   do_test nan-1.1.3 {

     sqlite3_bind_double $::STMT 1 -Inf

     sqlite3_step $::STMT

     sqlite3_reset $::STMT

     db eval {SELECT x, typeof(x) FROM t1}

   } {{} null inf real -inf real}

   do_test nan-1.1.4 {

     sqlite3_bind_double $::STMT 1 -NaN

     sqlite3_step $::STMT

     sqlite3_reset $::STMT

     db eval {SELECT x, typeof(x) FROM t1}

   } {{} null inf real -inf real {} null}

   do_test nan-1.1.5 {

     sqlite3_bind_double $::STMT 1 NaN0

     sqlite3_step $::STMT

     sqlite3_reset $::STMT

     db eval {SELECT x, typeof(x) FROM t1}

   } {{} null inf real -inf real {} null {} null}

   do_test nan-1.1.5 {

     sqlite3_bind_double $::STMT 1 -NaN0

     sqlite3_step $::STMT

     sqlite3_reset $::STMT

     db eval {SELECT x, typeof(x) FROM t1}

   } {{} null inf real -inf real {} null {} null {} null}

   do_test nan-1.1.6 {

     db eval {

       UPDATE t1 SET x=x-x;

       SELECT x, typeof(x) FROM t1;

     }

   } {{} null {} null {} null {} null {} null {} null}

 }

 # The following block of tests, nan-1.2.*, are the same as the nan-1.1.*

 # tests above, except that the SELECT queries used to validate data 

 # convert floating point values to text internally before returning them

 # to Tcl. This allows the tests to be run on platforms where Tcl has

 # problems converting "inf" and "-inf" from floating point to text format.

 # It also tests the internal float->text conversion routines a bit.

 #

 do_test nan-1.2.1 {

   db eval {

     DELETE FROM T1;

   }

   sqlite3_bind_double $::STMT 1 NaN

   sqlite3_step $::STMT

   sqlite3_reset $::STMT

   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}

 } {{} null}

 do_test nan-1.2.2 {

   sqlite3_bind_double $::STMT 1 +Inf

   sqlite3_step $::STMT

   sqlite3_reset $::STMT

   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}

 } {{} null Inf real}

 do_test nan-1.2.3 {

   sqlite3_bind_double $::STMT 1 -Inf

   sqlite3_step $::STMT

   sqlite3_reset $::STMT

   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}

 } {{} null Inf real -Inf real}

 do_test nan-1.2.4 {

   sqlite3_bind_double $::STMT 1 -NaN

   sqlite3_step $::STMT

   sqlite3_reset $::STMT

   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}

 } {{} null Inf real -Inf real {} null}

 do_test nan-1.2.5 {

   sqlite3_bind_double $::STMT 1 NaN0

   sqlite3_step $::STMT

   sqlite3_reset $::STMT

   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}

 } {{} null Inf real -Inf real {} null {} null}

 do_test nan-1.2.5 {

   sqlite3_bind_double $::STMT 1 -NaN0

   sqlite3_step $::STMT

   sqlite3_reset $::STMT

   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}

 } {{} null Inf real -Inf real {} null {} null {} null}

 do_test nan-1.2.6 {

   db eval {

     UPDATE t1 SET x=x-x;

     SELECT CAST(x AS text), typeof(x) FROM t1;

   }

 } {{} null {} null {} null {} null {} null {} null}

 do_test nan-2.1 {

   db eval {

     DELETE FROM T1;

   }

   sqlite3_bind_double $::STMT 1 NaN

   sqlite3_step $::STMT

   sqlite3_reset $::STMT

   db eval {SELECT x, typeof(x) FROM t1}

 } {{} null}

 sqlite3_finalize $::STMT

 # SQLite always converts NaN into NULL so it is not possible to write

 # a NaN value into the database file using SQLite.  The following series

 # of tests writes a normal floating point value (0.5) into the database,

 # then writes directly into the database file to change the 0.5 into NaN.

 # Then it reads the value of the database to verify it is converted into

 # NULL.

 #

 do_test nan-3.1 {

   db eval {

     DELETE FROM t1;

     INSERT INTO t1 VALUES(0.5);

     PRAGMA auto_vacuum=OFF;

     PRAGMA page_size=1024;

     VACUUM;

   }

   hexio_read test.db 2040 8

 } {3FE0000000000000}

 do_test nan-3.2 {

   db eval {

     SELECT x, typeof(x) FROM t1

   }

 } {0.5 real}

 do_test nan-3.3 {

   db close

   hexio_write test.db 2040 FFF8000000000000

   sqlite3 db test.db

   db eval {SELECT x, typeof(x) FROM t1}

 } {{} null}

 do_test nan-3.4 {

   db close

   hexio_write test.db 2040 7FF8000000000000

   sqlite3 db test.db

   db eval {SELECT x, typeof(x) FROM t1}

 } {{} null}

 do_test nan-3.5 {

   db close

   hexio_write test.db 2040 FFFFFFFFFFFFFFFF

   sqlite3 db test.db

   db eval {SELECT x, typeof(x) FROM t1}

 } {{} null}

 do_test nan-3.6 {

   db close

   hexio_write test.db 2040 7FFFFFFFFFFFFFFF

   sqlite3 db test.db

   db eval {SELECT x, typeof(x) FROM t1}

 } {{} null}

 # Verify that the sqlite3AtoF routine is able to handle extreme

 # numbers.

 #

 do_test nan-4.1 {

   db eval {DELETE FROM t1}

   db eval "INSERT INTO t1 VALUES([string repeat 9 307].0)"

   db eval {SELECT x, typeof(x) FROM t1}

 } {1e+307 real}

 do_test nan-4.2 {

   db eval {DELETE FROM t1}

   db eval "INSERT INTO t1 VALUES([string repeat 9 308].0)"

   db eval {SELECT x, typeof(x) FROM t1}

 } {1e+308 real}

 do_test nan-4.3 {

   db eval {DELETE FROM t1}

   db eval "INSERT INTO t1 VALUES(-[string repeat 9 307].0)"

   db eval {SELECT x, typeof(x) FROM t1}

 } {-1e+307 real}

 do_test nan-4.4 {

   db eval {DELETE FROM t1}

   db eval "INSERT INTO t1 VALUES(-[string repeat 9 308].0)"

   db eval {SELECT x, typeof(x) FROM t1}

 } {-1e+308 real}

 do_test nan-4.5 {

   db eval {DELETE FROM t1}

   set big -[string repeat 0 10000][string repeat 9 308].[string repeat 0 10000]

   db eval "INSERT INTO t1 VALUES($big)"

   db eval {SELECT x, typeof(x) FROM t1}

 } {-1e+308 real}

 do_test nan-4.6 {

   db eval {DELETE FROM t1}

   set big [string repeat 0 10000][string repeat 9 308].[string repeat 0 10000]

   db eval "INSERT INTO t1 VALUES($big)"

   db eval {SELECT x, typeof(x) FROM t1}

 } {1e+308 real}

 if {$tcl_platform(platform) != "symbian"} {

   # Do not run these tests on Symbian, as the Tcl port doesn't like to

   # convert from floating point value "-inf" to a string.

   #

   do_test nan-4.7 {

     db eval {DELETE FROM t1}

     db eval "INSERT INTO t1 VALUES([string repeat 9 309].0)"

     db eval {SELECT x, typeof(x) FROM t1}

   } {inf real}

   do_test nan-4.8 {

     db eval {DELETE FROM t1}

     db eval "INSERT INTO t1 VALUES(-[string repeat 9 309].0)"

     db eval {SELECT x, typeof(x) FROM t1}

   } {-inf real}

 }

 do_test nan-4.9 {

   db eval {DELETE FROM t1}

   db eval "INSERT INTO t1 VALUES([string repeat 9 309].0)"

   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}

 } {Inf real}

 do_test nan-4.10 {

   db eval {DELETE FROM t1}

   db eval "INSERT INTO t1 VALUES(-[string repeat 9 309].0)"

   db eval {SELECT CAST(x AS text), typeof(x) FROM t1}

 } {-Inf real}

 do_test nan-4.10 {

   db eval {DELETE FROM t1}

   db eval "INSERT INTO t1 VALUES(1234.5[string repeat 0 10000]12345)"

   db eval {SELECT x, typeof(x) FROM t1}

 } {1234.5 real}

 do_test nan-4.11 {

   db eval {DELETE FROM t1}

   db eval "INSERT INTO t1 VALUES(-1234.5[string repeat 0 10000]12345)"

   db eval {SELECT x, typeof(x) FROM t1}

 } {-1234.5 real}

 do_test nan-4.12 {

   db eval {DELETE FROM t1}

   set small [string repeat 0 10000].[string repeat 0 324][string repeat 9 10000]

   db eval "INSERT INTO t1 VALUES($small)"

   db eval {SELECT x, typeof(x) FROM t1}

 } {0.0 real}

 do_test nan-4.13 {

   db eval {DELETE FROM t1}

   set small \

       -[string repeat 0 10000].[string repeat 0 324][string repeat 9 10000]

   db eval "INSERT INTO t1 VALUES($small)"

   db eval {SELECT x, typeof(x) FROM t1}

 } {0.0 real}

 # These tests test some really, really small floating point numbers.

 #

 if {$tcl_platform(platform) != "symbian"} {

   # These two are not run on symbian because tcl has trouble converting

   # the very small numbers back to text form (probably due to a difference

   # in the sprintf() implementation).

   #

   do_test nan-4.14 {

     db eval {DELETE FROM t1}

     set small \

         [string repeat 0 10000].[string repeat 0 323][string repeat 9 10000]

     db eval "INSERT INTO t1 VALUES($small)"

     db eval {SELECT x, typeof(x) FROM t1}

   } {9.88131291682493e-324 real}

   do_test nan-4.15 {

     db eval {DELETE FROM t1}

     set small \

         -[string repeat 0 10000].[string repeat 0 323][string repeat 9 10000]

     db eval "INSERT INTO t1 VALUES($small)"

     db eval {SELECT x, typeof(x) FROM t1}

   } {-9.88131291682493e-324 real}

 }

 #

 # Symbian OS: the next test fails due to problems in sprintf/printf formatting. 

 if {$::tcl_platform(platform)!="symbian"} {

   do_test nan-4.16 {

     db eval {DELETE FROM t1}

     set small [string repeat 0 10000].[string repeat 0 323][string repeat 9 10000]

     db eval "INSERT INTO t1 VALUES($small)"

     db eval {SELECT CAST(x AS text), typeof(x) FROM t1}

   } {9.88131291682493e-324 real}

 }

 #

 # Symbian OS: the next test fails due to problems in sprintf/printf formatting. 

 if {$::tcl_platform(platform)!="symbian"} {

 do_test nan-4.17 {

     db eval {DELETE FROM t1}

     set small \

         -[string repeat 0 10000].[string repeat 0 323][string repeat 9 10000]

     db eval "INSERT INTO t1 VALUES($small)"

     db eval {SELECT CAST(x AS text), typeof(x) FROM t1}

   } {-9.88131291682493e-324 real}

 }

 do_test nan-4.20 {

   db eval {DELETE FROM t1}

   set big [string repeat 9 10000].0e-9000

   db eval "INSERT INTO t1 VALUES($big)"

   db eval {SELECT x, typeof(x) FROM t1}

 } {{} null}

 finish_test