[sql] Import reference version of SQLite 3.17..

third_party/sqlite/sqlite-src-3170000/test/nan.test

+# 2008 April 28
+#
+# 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 not use a codec for tests in this file, as the database file is
+# manipulated directly using tcl scripts (using the [hexio_write] command).
+#
+do_not_use_codec
+
+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_realnum_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_realnum_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_realnum_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_realnum_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_realnum_test nan-1.1.6 {
+    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.7 {
+    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.6 {
+  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.7 {
+  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.
+#
+if {![nonzero_reserved_bytes]} {
+  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_realnum_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_realnum_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.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}
+  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.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}
+do_test nan-4.14 {
+  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.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}
+  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 x, typeof(x) FROM t1}
+  } {-9.88131291682493e-324 real}
+}
+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.18 {
+  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_realnum_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}
+} {inf real}
+
+do_realnum_test nan-4.30 {
+  db eval {
+    DELETE FROM t1;
+    INSERT INTO t1 VALUES('2.5e+9999');
+    SELECT x, typeof(x) FROM t1;
+  }
+} {inf real}
+do_realnum_test nan-4.31 {
+  db eval {
+    DELETE FROM t1;
+    INSERT INTO t1 VALUES('2.5e+10000');
+    SELECT x, typeof(x) FROM t1;
+  }
+} {inf real}
+
+do_realnum_test nan-4.32 {
+  db eval {
+    DELETE FROM t1;
+    INSERT INTO t1 VALUES('2.5e-9999');
+    SELECT x, typeof(x) FROM t1;
+  }
+} {0.0 real}
+do_realnum_test nan-4.33 {
+  db eval {
+    DELETE FROM t1;
+    INSERT INTO t1 VALUES('2.5e-10000');
+    SELECT x, typeof(x) FROM t1;
+  }
+} {0.0 real}
+do_realnum_test nan-4.34 {
+  db eval {
+    DELETE FROM t1;
+    INSERT INTO t1 VALUES('2.5e2147483650');
+    SELECT x, typeof(x) FROM t1;
+  }
+} {inf real}
+do_realnum_test nan-4.35 {
+  db eval {
+    DELETE FROM t1;
+    INSERT INTO t1 VALUES('2.5e-2147483650');
+    SELECT x, typeof(x) FROM t1;
+  }
+} {0.0 real}
+
+  
+
+
+
+finish_test