SQLite

do_execsql_test 8.2 {
  SELECT 
    typeof(x1), typeof(x2), typeof(y1), typeof(y2), typeof(z1), typeof(z2)
  FROM rtI
} {integer integer integer integer integer integer}

ifcapable nan_inf {
  do_execsql_test 8.3a {
    DELETE FROM rtF;
    INSERT INTO rtF VALUES(
      1, 'hello world', X'616263', NULL, 44
    );
    SELECT * FROM rtF;
  } {
    1   0.0 0.0    NaN 44.0
  }
} else {
  do_execsql_test 8.3b {
    DELETE FROM rtF;
    INSERT INTO rtF VALUES(
      1, 'hello world', X'616263', NULL, 44
    );
    SELECT * FROM rtF;
  } {
    1   0.0 0.0    0.0 44.0
  }
}
do_execsql_test 8.4 {
  SELECT 
    typeof(x1), typeof(x2), typeof(y1), typeof(y2)
  FROM rtF
} {real real real real}

  # Show there are no UNIQUE constraints
  do_execsql_test 1.$tn.3 {
    INSERT INTO abc VALUES(1, 10.0, 20.0, 10.0, 20.0);
    INSERT INTO abc VALUES(2, 10.0, 20.0, 10.0, 20.0);
  }

  # Show the default values have not been modified
  ifcapable nan_inf {
    do_execsql_test 1.$tn.4a {
      INSERT INTO abc DEFAULT VALUES;
      SELECT * FROM abc WHERE rowid NOT IN (1,2)
    } {3 NaN NaN NaN NaN}

    # Show that there are no NOT NULL constraints
    do_execsql_test 1.$tn.5a {
      INSERT INTO abc VALUES(NULL, NULL, NULL, NULL, NULL);
      SELECT * FROM abc WHERE rowid NOT IN (1,2,3)
    } {4 NaN NaN NaN NaN}
  } else {
    do_execsql_test 1.$tn.4b {
      INSERT INTO abc DEFAULT VALUES;
      SELECT * FROM abc WHERE rowid NOT IN (1,2)
    } {3 0.0 0.0 0.0 0.0}

    # Show that there are no NOT NULL constraints
    do_execsql_test 1.$tn.5b {
      INSERT INTO abc VALUES(NULL, NULL, NULL, NULL, NULL);
      SELECT * FROM abc WHERE rowid NOT IN (1,2,3)
    } {4 0.0 0.0 0.0 0.0}
  }

# EVIDENCE-OF: R-06893-30579 In an RTREE virtual table, the first column
# always has a type affinity of INTEGER and all other data columns have
# a type affinity of REAL.
  do_execsql_test 1.$tn.5 {
    INSERT INTO abc VALUES('5', '5', '5', '5', '5');
    SELECT * FROM abc WHERE rowid NOT IN (1,2,3,4)

# as the 3rd parameter to the Tcl script.
#
do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE rt1 USING rtree(id, x1,x2);
  CREATE VIRTUAL TABLE rt2 USING rtree(id, x1,x2, y1,y2);
  CREATE VIRTUAL TABLE rt3 USING rtree(id, x1,x2, y1,y2, z1,z2);

  INSERT INTO rt1(id,x1,x2) VALUES(1,0,0);
  INSERT INTO rt2 DEFAULT VALUES;
  INSERT INTO rt3 DEFAULT VALUES;
}
foreach {tn tbl nCoord} {
  1 rt1 2     
  2 rt2 4
  3 rt3 6

    }
  }
  set ::box_calls
} {B L B L B L B L B L B L B L B L B L}


finish_test

#ifdef SQLITE_32BIT_ROWID
  "32BIT_ROWID",
#endif
#ifdef SQLITE_4_BYTE_ALIGNED_MALLOC
  "4_BYTE_ALIGNED_MALLOC",
#endif



#ifdef SQLITE_ALLOW_COVERING_INDEX_SCAN
# if SQLITE_ALLOW_COVERING_INDEX_SCAN != 1
  "ALLOW_COVERING_INDEX_SCAN=" CTIMEOPT_VAL(SQLITE_ALLOW_COVERING_INDEX_SCAN),
# endif
#endif
#ifdef SQLITE_ALLOW_URI_AUTHORITY
  "ALLOW_URI_AUTHORITY",

  "ENABLE_MEMSYS3",
#endif
#ifdef SQLITE_ENABLE_MEMSYS5
  "ENABLE_MEMSYS5",
#endif
#ifdef SQLITE_ENABLE_MULTIPLEX
  "ENABLE_MULTIPLEX",
#endif
#ifdef SQLITE_ENABLE_NAN_INF
  "ENABLE_NAN_INF",
#endif
#ifdef SQLITE_ENABLE_NORMALIZE
  "ENABLE_NORMALIZE",
#endif
#ifdef SQLITE_ENABLE_NULL_TRIM
  "ENABLE_NULL_TRIM",
#endif

#endif

#ifdef SQLITE_ENABLE_URI_00_ERROR
  Tcl_SetVar2(interp, "sqlite_options", "uri_00_error", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "uri_00_error", "0", TCL_GLOBAL_ONLY);
#endif

#if defined(SQLITE_ENABLE_NAN_INF)
  Tcl_SetVar2(interp, "sqlite_options", "nan_inf", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "nan_inf", "0", TCL_GLOBAL_ONLY);
#endif

#if defined(SQLITE_ENABLE_NORMALIZE)
  Tcl_SetVar2(interp, "sqlite_options", "normalize", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "normalize", "0", TCL_GLOBAL_ONLY);
#endif

  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {real real real}
do_test bind-4.3 {
  execsql {
    DELETE FROM t1;
  }
} {}
ifcapable nan_inf {
  do_test bind-4.4nan {
    sqlite3_bind_double $VM 1 NaN
    sqlite3_bind_double $VM 2 1e300
    sqlite3_bind_double $VM 3 -1e-300
    sqlite_step $VM N VALUES COLNAMES
    sqlite3_reset $VM
    set x [execsql {SELECT rowid, * FROM t1}]
    regsub {1e-005} $x {1e-05} y
    set y
  } {1 NaN(7ffffffffffff) 1e+300 -1e-300}
  do_test bind-4.5nan {
    execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
  } {real real real}
} else {
  do_test bind-4.4 {
    sqlite3_bind_double $VM 1 NaN
    sqlite3_bind_double $VM 2 1e300
    sqlite3_bind_double $VM 3 -1e-300
    sqlite_step $VM N VALUES COLNAMES
    sqlite3_reset $VM
    set x [execsql {SELECT rowid, * FROM t1}]
    regsub {1e-005} $x {1e-05} y
    set y
  } {1 {} 1e+300 -1e-300}
  do_test bind-4.5 {
    execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
  } {null real real}
}
do_test bind-4.6 {
  execsql {
    DELETE FROM t1;
  }
} {}

# NULL

do_test capi3-5.5 {
  sqlite3_step $STMT
} SQLITE_ROW

check_header $STMT capi3-5.6 {a b c} {VARINT BLOB VARCHAR(16)}
check_origin_header $STMT capi3-5.6 {main main main} {t1 t1 t1} {a b c}
ifcapable !nan_inf {
  check_data $STMT capi3-5.7 {TEXT TEXT NULL} {0 0 0} {0.0 0.0 0.0} {one two {}}
}

do_test capi3-5.8 {
  sqlite3_step $STMT
} SQLITE_ROW

check_header $STMT capi3-5.9 {a b c} {VARINT BLOB VARCHAR(16)}
check_origin_header $STMT capi3-5.9 {main main main} {t1 t1 t1} {a b c}

do_test capi3c-5.5 {
  sqlite3_step $STMT
} SQLITE_ROW

check_header $STMT capi3c-5.6 {a b c} {VARINT BLOB VARCHAR(16)}
check_origin_header $STMT capi3c-5.6 {main main main} {t1 t1 t1} {a b c}
ifcapable !nan_inf {
  check_data $STMT capi3c-5.7 {TEXT TEXT NULL} {0 0 0} {0.0 0.0 0.0} {one two {}}
}

do_test capi3c-5.8 {
  sqlite3_step $STMT
} SQLITE_ROW

check_header $STMT capi3c-5.9 {a b c} {VARINT BLOB VARCHAR(16)}
check_origin_header $STMT capi3c-5.9 {main main main} {t1 t1 t1} {a b c}

  test_expr expr-2.19 {r1=2.34, r2=2.34} {r2=r1} 1
  test_expr expr-2.20 {r1=2.34, r2=2.34} {r2<>r1} 0
  test_expr expr-2.21 {r1=2.34, r2=2.34} {r2==r1} 1
  test_expr expr-2.22 {r1=1.23, r2=2.34} {min(r1,r2,r1+r2,r1-r2)} {-1.11}
  test_expr expr-2.23 {r1=1.23, r2=2.34} {max(r1,r2,r1+r2,r1-r2)} {3.57}
  test_expr expr-2.24 {r1=25.0, r2=11.0} {r1%r2} 3.0
  test_expr expr-2.25 {r1=1.23, r2=NULL} {coalesce(r1+r2,99.0)} 99.0
  ifcapable nan_inf {
    test_expr expr-2.26a {r1=1e300, r2=1e300} {coalesce((r1*r2)*0.0,99.0)} -NaN
    test_expr expr-2.26b {r1=1e300, r2=-1e300} {coalesce((r1*r2)*0.0,99.0)} -NaN
  } else {
    test_expr expr-2.26c {r1=1e300, r2=1e300} {coalesce((r1*r2)*0.0,99.0)} 99.0
    test_expr expr-2.26d {r1=1e300, r2=-1e300} {coalesce((r1*r2)*0.0,99.0)} 99.0
  }
  test_expr expr-2.27 {r1=1.1, r2=0.0} {r1/r2} {{}}
  test_expr expr-2.28 {r1=1.1, r2=0.0} {r1%r2} {{}}
}

test_expr expr-3.1 {t1='abc', t2='xyz'} {t1<t2} 1
test_expr expr-3.2 {t1='xyz', t2='abc'} {t1<t2} 0
test_expr expr-3.3 {t1='abc', t2='abc'} {t1<t2} 0

  } {2.225073858507201e-308}
  do_execsql_test func4-6.3.15 {
    SELECT toreal(x'fff0000000000000');
  } {-Inf}
  do_execsql_test func4-6.3.16 {
    SELECT toreal(x'7ff0000000000000');
  } {Inf}
  ifcapable !nan_inf {
    do_execsql_test func4-6.3.17 {
      SELECT toreal(x'fff8000000000000');
    } {{}}
    do_execsql_test func4-6.3.18 {
      SELECT toreal(x'fff0000000000001');
    } {{}}
    do_execsql_test func4-6.3.19 {
      SELECT toreal(x'fff7ffffffffffff');
    } {{}}
    do_execsql_test func4-6.3.20 {
      SELECT toreal(x'7ff0000000000001');
    } {{}}
    do_execsql_test func4-6.3.21 {
      SELECT toreal(x'7ff7ffffffffffff');
    } {{}}
    do_execsql_test func4-6.3.22 {
      SELECT toreal(x'fff8000000000001');
    } {{}}
    do_execsql_test func4-6.3.23 {
      SELECT toreal(x'ffffffffffffffff');
    } {{}}
    do_execsql_test func4-6.3.24 {
      SELECT toreal(x'7ff8000000000000');
    } {{}}
    do_execsql_test func4-6.3.25 {
      SELECT toreal(x'7fffffffffffffff');
    } {{}}
  }
}

set tcl_precision $saved_tcl_precision
unset saved_tcl_precision
finish_test

} {0.5493061}

# Test cases derived from MySQL documentation
#
do_execsql_test func7-mysql-100 {
   SELECT acos(1);
} {0.0}
ifcapable nan_inf {
  do_execsql_test func7-mysql-110a {
     SELECT acos(1.0001);
  } {NaN}
} else {
  do_execsql_test func7-mysql-110b {
     SELECT acos(1.0001);
  } {{}}
}
do_execsql_test func7-mysql-120 {
   SELECT round( acos(0.0), 7);
} {1.5707963}
do_execsql_test func7-mysql-130 {
   SELECT round( asin(0.2), 7);
} {0.2013579}
do_execsql_test func7-mysql-140 {

} {1.5707963}
do_execsql_test func7-mysql-300 {
   SELECT sign(-32), sign(0), sign(234);
} {-1 0 1}
do_execsql_test func7-mysql-310 {
   SELECT sin(pi()) BETWEEN -1.0e-15 AND 1.0e-15;
} {1}
ifcapable nan_inf {
  do_execsql_test func7-mysql-320a {
     SELECT sqrt(4), round(sqrt(20),7), quote(sqrt(-16));
  } {2.0 4.472136 NaN}
} else {
  do_execsql_test func7-mysql-320b {
     SELECT sqrt(4), round(sqrt(20),7), quote(sqrt(-16));
  } {2.0 4.472136 NULL}
}
do_execsql_test func7-mysql-330 {
   SELECT tan(pi()) BETWEEN -1.0e-15 AND 1.0e-15;
} {1}
do_execsql_test func7-mysql-331 {
   SELECT round(tan(pi()+1),7);
} {1.5574077}


finish_test

  ]
} {inf 1.79769313486232e+308}
do_test ieee754-111 {
  string tolower [
    db eval {SELECT ieee754(-1,1024), ieee754(-4503599627370495,972);}
  ]
} {-inf -1.79769313486232e+308}
ifcapable !nan_inf {
  do_execsql_test ieee754-112 {
    SELECT ieee754(4503599627370495,973) is null;
  } {1}
}

finish_test

  do_test istrue-600.$tn.2 {
    set ::STMT [sqlite3_prepare db "INSERT INTO t1 VALUES(?)" -1 TAIL]
    sqlite3_bind_double $::STMT 1 $val
    sqlite3_step $::STMT
    sqlite3_reset $::STMT
    sqlite3_finalize $::STMT
  } {SQLITE_OK}
  ifcapable nan_inf {
    set res 1
  } else {
    set res [expr {$tn in [list 5 6] ? {1} : {0}}]
  }
  do_execsql_test istrue-600.$tn.3 {
    SELECT x IS TRUE FROM t1;

  } $res
  do_execsql_test istrue-600.$tn.4 {
    SELECT x IS FALSE FROM t1;
  } {0}
}

ifcapable altertable {
  do_execsql_test istrue-700 {

# 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

ifcapable nan_inf {
  finish_test
  return
}

# 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 {

  SQLITE_ENABLE_LOAD_EXTENSION
  SQLITE_ENABLE_LOCKING_STYLE
  SQLITE_ENABLE_MATH_FUNCTIONS
  SQLITE_ENABLE_MEMORY_MANAGEMENT
  SQLITE_ENABLE_MEMSYS3
  SQLITE_ENABLE_MEMSYS5
  SQLITE_ENABLE_MULTIPLEX
  SQLITE_ENABLE_NAN_INF
  SQLITE_ENABLE_NORMALIZE
  SQLITE_ENABLE_NULL_TRIM
  SQLITE_ENABLE_OFFSET_SQL_FUNC
  SQLITE_ENABLE_OVERSIZE_CELL_CHECK
  SQLITE_ENABLE_PREUPDATE_HOOK
  SQLITE_ENABLE_QPSG
  SQLITE_ENABLE_RBU