1 files changed, 176 insertions, 0 deletions
diff --git a/test/date2.test b/test/date2.test
new file mode 100644
index 0000000..a16e25c
--- /dev/null
+++ b/test/date2.test
@@ -0,0 +1,176 @@
+# 2017-07-20
+#
+# 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.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.  The
+# focus of this file is testing date and time functions used in
+# check constraints and index expressions.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# Skip this whole file if date and time functions are omitted
+# at compile-time
+#
+ifcapable {!datetime} {
+  finish_test
+  return
+}
+
+do_execsql_test date2-100 {
+  CREATE TABLE t1(x, y, CHECK( date(x) BETWEEN '2017-07-01' AND '2017-07-31' ));
+  INSERT INTO t1(x,y) VALUES('2017-07-20','one');
+} {}
+do_catchsql_test date2-110 {
+  INSERT INTO t1(x,y) VALUES('now','two');
+} {1 {non-deterministic use of date() in a CHECK constraint}}
+do_execsql_test date2-120 {
+  SELECT * FROM t1;
+} {2017-07-20 one}
+do_catchsql_test date2-130 {
+  INSERT INTO t1(x,y) VALUES('2017-08-01','two');
+} {1 {CHECK constraint failed: date(x) BETWEEN '2017-07-01' AND '2017-07-31'}}
+
+# 2021-03-16 Forum post https://sqlite.org/forum/forumpost/464afd4086
+do_catchsql_test date2-140 {
+  DROP TABLE t1;
+  CREATE TABLE t1(x, y, z AS (date()));
+  INSERT INTO t1(x,y) VALUES(1,2);
+} {1 {non-deterministic use of date() in a generated column}}
+
+do_execsql_test date2-200 {
+  CREATE TABLE t2(x,y);
+  INSERT INTO t2(x,y) VALUES(1, '2017-07-20'), (2, 'xyzzy');
+  CREATE INDEX t2y ON t2(date(y));
+}
+do_catchsql_test date2-210 {
+  INSERT INTO t2(x,y) VALUES(3, 'now');
+} {1 {non-deterministic use of date() in an index}}
+do_execsql_test date2-220 {
+  SELECT x, y FROM t2 ORDER BY x;
+} {1 2017-07-20 2 xyzzy}
+
+do_execsql_test date2-300 {
+  CREATE TABLE t3(a INTEGER PRIMARY KEY,b);
+  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
+    INSERT INTO t3(a,b) SELECT x, julianday('2017-07-01')+x FROM c;
+  UPDATE t3 SET b='now' WHERE a=500;
+}
+do_catchsql_test date2-310 {
+  CREATE INDEX t3b1 ON t3(datetime(b));
+} {1 {non-deterministic use of datetime() in an index}}
+do_catchsql_test date2-320 {
+  CREATE INDEX t3b1 ON t3(datetime(b)) WHERE typeof(b)='real';
+} {0 {}}
+do_execsql_test date2-330 {
+  EXPLAIN QUERY PLAN
+  SELECT a FROM t3
+   WHERE typeof(b)='real'
+     AND datetime(b) BETWEEN '2017-07-04' AND '2017-07-08';
+} {/USING INDEX t3b/}
+do_execsql_test date2-331 {
+  SELECT a FROM t3
+   WHERE typeof(b)='real'
+     AND datetime(b) BETWEEN '2017-07-04' AND '2017-07-08'
+  ORDER BY a;
+} {3 4 5 6}
+
+do_execsql_test date2-400 {
+  CREATE TABLE t4(a INTEGER PRIMARY KEY,b);
+  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
+    INSERT INTO t4(a,b) SELECT x, julianday('2017-07-01')+x FROM c;
+  UPDATE t4 SET b='now' WHERE a=500;
+}
+do_catchsql_test date2-410 {
+  CREATE INDEX t4b1 ON t4(b)
+    WHERE date(b) BETWEEN '2017-06-01' AND '2017-08-31';
+} {1 {non-deterministic use of date() in an index}}
+do_execsql_test date2-420 {
+  DELETE FROM t4 WHERE a=500;
+  CREATE INDEX t4b1 ON t4(b)
+    WHERE date(b) BETWEEN '2017-06-01' AND '2017-08-31';
+}
+do_catchsql_test date2-430 {
+  INSERT INTO t4(a,b) VALUES(9999,'now');
+} {1 {non-deterministic use of date() in an index}}
+
+do_execsql_test date2-500 {
+  CREATE TABLE mods(x);
+  INSERT INTO mods(x) VALUES
+    ('+10 days'),
+    ('-10 days'),
+    ('+10 hours'),
+    ('-10 hours'),
+    ('+10 minutes'),
+    ('-10 minutes'),
+    ('+10 seconds'),
+    ('-10 seconds'),
+    ('+10 months'),
+    ('-10 months'),
+    ('+10 years'),
+    ('-10 years'),
+    ('start of month'),
+    ('start of year'),
+    ('start of day'),
+    ('weekday 1'),
+    ('unixepoch');
+  CREATE TABLE t5(y,m);
+  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
+    INSERT INTO t5(y,m) SELECT julianday('2017-07-01')+c.x, mods.x FROM c, mods;
+  CREATE INDEX t5x1 on t5(y) WHERE datetime(y,m) IS NOT NULL;
+}
+do_catchsql_test date2-510 {
+  INSERT INTO t5(y,m) VALUES('2017-07-20','localtime');
+} {1 {non-deterministic use of datetime() in an index}}
+do_catchsql_test date2-520 {
+  INSERT INTO t5(y,m) VALUES('2017-07-20','utc');
+} {1 {non-deterministic use of datetime() in an index}}
+
+# 2019-10-30 Ticket 830277d9db6c3ba1
+#
+do_catchsql_test date2-600 {
+  CREATE TABLE t600(a REAL CHECK( a<julianday('now') ));
+  INSERT INTO t600(a) VALUES(1.0);
+} {1 {non-deterministic use of julianday() in a CHECK constraint}}
+do_catchsql_test date2-601 {
+  CREATE TABLE t601(a REAL, b TEXT, CHECK( a<julianday(b) ));
+  INSERT INTO t601(a,b) VALUES(1.0, '1970-01-01');
+} {0 {}}
+do_catchsql_test date2-602 {
+  INSERT INTO t601(a,b) VALUES(1e100, '1970-01-01');
+} {1 {CHECK constraint failed: a<julianday(b)}}
+do_catchsql_test date2-603 {
+  INSERT INTO t601(a,b) VALUES(10, 'now');
+} {1 {non-deterministic use of julianday() in a CHECK constraint}}
+do_catchsql_test date2-604 {
+  INSERT INTO t600(a) VALUES(julianday('now')+10);
+} {1 {non-deterministic use of julianday() in a CHECK constraint}}
+
+
+do_catchsql_test date2-610 {
+  CREATE TABLE t610(a,b);
+  CREATE INDEX t610x1 ON t610(julianday('now')+b);
+  INSERT INTO t610(a,b) VALUES(123,456);
+} {1 {non-deterministic use of julianday() in an index}}
+do_catchsql_test date2-611 {
+  CREATE TABLE t611(a,b);
+  CREATE INDEX t611x1 ON t611(julianday(a)+b);
+  INSERT INTO t611(a,b) VALUES('1970-01-01',10.0);
+} {0 {}}
+do_catchsql_test date2-612 {
+  INSERT INTO t611(a,b) VALUES('now',10.0);
+} {1 {non-deterministic use of julianday() in an index}}
+
+do_catchsql_test date3-620 {
+  CREATE TABLE t620(a, b AS (a+julianday('now')));
+  INSERT INTO t620 VALUES(10);
+} {1 {non-deterministic use of julianday() in a generated column}}
+
+finish_test