1 # 2008 April 28

     2 #

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

     4 #

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

     6 # a legal notice, here is a blessing:

     7 #

     8 #    May you do good and not evil.

     9 #    May you find forgiveness for yourself and forgive others.

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

    11 #

    12 #***********************************************************************

    13 #

    14 # Ticket #3060

    15 #

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

    17 # SQLite should always convert NaN into NULL.

    18 #

    19 # Also verify that the decimal to IEEE754 binary conversion routines

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

    21 # out of range.

    22 #

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

    24 #

    25 

    26 set testdir [file dirname $argv0]

    27 source $testdir/tester.tcl

    28 

    29 do_test nan-1.1.1 {

    30   db eval {

    31     PRAGMA auto_vacuum=OFF;

    32     PRAGMA page_size=1024;

    33     CREATE TABLE t1(x FLOAT);

    34   }

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

    36   sqlite3_bind_double $::STMT 1 NaN

    37   sqlite3_step $::STMT

    38   sqlite3_reset $::STMT

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

    40 } {{} null}

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

    42   do_test nan-1.1.2 {

    43     sqlite3_bind_double $::STMT 1 +Inf

    44     sqlite3_step $::STMT

    45     sqlite3_reset $::STMT

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

    47   } {{} null inf real}

    48   do_test nan-1.1.3 {

    49     sqlite3_bind_double $::STMT 1 -Inf

    50     sqlite3_step $::STMT

    51     sqlite3_reset $::STMT

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

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

    54   do_test nan-1.1.4 {

    55     sqlite3_bind_double $::STMT 1 -NaN

    56     sqlite3_step $::STMT

    57     sqlite3_reset $::STMT

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

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

    60   do_test nan-1.1.5 {

    61     sqlite3_bind_double $::STMT 1 NaN0

    62     sqlite3_step $::STMT

    63     sqlite3_reset $::STMT

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

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

    66   do_test nan-1.1.5 {

    67     sqlite3_bind_double $::STMT 1 -NaN0

    68     sqlite3_step $::STMT

    69     sqlite3_reset $::STMT

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

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

    72   do_test nan-1.1.6 {

    73     db eval {

    74       UPDATE t1 SET x=x-x;

    75       SELECT x, typeof(x) FROM t1;

    76     }

    77   } {{} null {} null {} null {} null {} null {} null}

    78 }

    79 

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

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

    82 # convert floating point values to text internally before returning them

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

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

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

    86 #

    87 do_test nan-1.2.1 {

    88   db eval {

    89     DELETE FROM T1;

    90   }

    91   sqlite3_bind_double $::STMT 1 NaN

    92   sqlite3_step $::STMT

    93   sqlite3_reset $::STMT

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

    95 } {{} null}

    96 do_test nan-1.2.2 {

    97   sqlite3_bind_double $::STMT 1 +Inf

    98   sqlite3_step $::STMT

    99   sqlite3_reset $::STMT

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

   101 } {{} null Inf real}

   102 do_test nan-1.2.3 {

   103   sqlite3_bind_double $::STMT 1 -Inf

   104   sqlite3_step $::STMT

   105   sqlite3_reset $::STMT

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

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

   108 do_test nan-1.2.4 {

   109   sqlite3_bind_double $::STMT 1 -NaN

   110   sqlite3_step $::STMT

   111   sqlite3_reset $::STMT

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

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

   114 do_test nan-1.2.5 {

   115   sqlite3_bind_double $::STMT 1 NaN0

   116   sqlite3_step $::STMT

   117   sqlite3_reset $::STMT

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

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

   120 do_test nan-1.2.5 {

   121   sqlite3_bind_double $::STMT 1 -NaN0

   122   sqlite3_step $::STMT

   123   sqlite3_reset $::STMT

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

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

   126 do_test nan-1.2.6 {

   127   db eval {

   128     UPDATE t1 SET x=x-x;

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

   130   }

   131 } {{} null {} null {} null {} null {} null {} null}

   132 

   133 do_test nan-2.1 {

   134   db eval {

   135     DELETE FROM T1;

   136   }

   137   sqlite3_bind_double $::STMT 1 NaN

   138   sqlite3_step $::STMT

   139   sqlite3_reset $::STMT

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

   141 } {{} null}

   142 sqlite3_finalize $::STMT

   143 

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

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

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

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

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

   149 # NULL.

   150 #

   151 do_test nan-3.1 {

   152   db eval {

   153     DELETE FROM t1;

   154     INSERT INTO t1 VALUES(0.5);

   155     PRAGMA auto_vacuum=OFF;

   156     PRAGMA page_size=1024;

   157     VACUUM;

   158   }

   159   hexio_read test.db 2040 8

   160 } {3FE0000000000000}

   161 do_test nan-3.2 {

   162   db eval {

   163     SELECT x, typeof(x) FROM t1

   164   }

   165 } {0.5 real}

   166 do_test nan-3.3 {

   167   db close

   168   hexio_write test.db 2040 FFF8000000000000

   169   sqlite3 db test.db

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

   171 } {{} null}

   172 do_test nan-3.4 {

   173   db close

   174   hexio_write test.db 2040 7FF8000000000000

   175   sqlite3 db test.db

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

   177 } {{} null}

   178 do_test nan-3.5 {

   179   db close

   180   hexio_write test.db 2040 FFFFFFFFFFFFFFFF

   181   sqlite3 db test.db

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

   183 } {{} null}

   184 do_test nan-3.6 {

   185   db close

   186   hexio_write test.db 2040 7FFFFFFFFFFFFFFF

   187   sqlite3 db test.db

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

   189 } {{} null}

   190 

   191 # Verify that the sqlite3AtoF routine is able to handle extreme

   192 # numbers.

   193 #

   194 do_test nan-4.1 {

   195   db eval {DELETE FROM t1}

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

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

   198 } {1e+307 real}

   199 do_test nan-4.2 {

   200   db eval {DELETE FROM t1}

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

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

   203 } {1e+308 real}

   204 do_test nan-4.3 {

   205   db eval {DELETE FROM t1}

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

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

   208 } {-1e+307 real}

   209 do_test nan-4.4 {

   210   db eval {DELETE FROM t1}

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

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

   213 } {-1e+308 real}

   214 do_test nan-4.5 {

   215   db eval {DELETE FROM t1}

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

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

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

   219 } {-1e+308 real}

   220 do_test nan-4.6 {

   221   db eval {DELETE FROM t1}

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

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

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

   225 } {1e+308 real}

   226 

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

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

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

   230   #

   231   do_test nan-4.7 {

   232     db eval {DELETE FROM t1}

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

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

   235   } {inf real}

   236   do_test nan-4.8 {

   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 }

   242 do_test nan-4.9 {

   243   db eval {DELETE FROM t1}

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

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

   246 } {Inf real}

   247 do_test nan-4.10 {

   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}

   252 

   253 do_test nan-4.10 {

   254   db eval {DELETE FROM t1}

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

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

   257 } {1234.5 real}

   258 do_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}

   263 do_test nan-4.12 {

   264   db eval {DELETE FROM t1}

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

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

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

   268 } {0.0 real}

   269 do_test nan-4.13 {

   270   db eval {DELETE FROM t1}

   271   set small \

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

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

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

   275 } {0.0 real}

   276 

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

   278 #

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

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

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

   282   # in the sprintf() implementation).

   283   #

   284   do_test nan-4.14 {

   285     db eval {DELETE FROM t1}

   286     set small \

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

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

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

   290   } {9.88131291682493e-324 real}

   291   do_test nan-4.15 {

   292     db eval {DELETE FROM t1}

   293     set small \

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

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

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

   297   } {-9.88131291682493e-324 real}

   298 }

   299 #

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

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

   302   do_test nan-4.16 {

   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 }

   309 #

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

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

   312 do_test nan-4.17 {

   313     db eval {DELETE FROM t1}

   314     set small \

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

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

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

   318   } {-9.88131291682493e-324 real}

   319 }

   320 do_test nan-4.20 {

   321   db eval {DELETE FROM t1}

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

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

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

   325 } {{} null}

   326 

   327 

   328 

   329 finish_test