AryaWu/sqlite

1# 2017-07-202#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library.  The12# focus of this file is testing date and time functions used in13# check constraints and index expressions.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19# Skip this whole file if date and time functions are omitted20# at compile-time21#22ifcapable {!datetime} {23  finish_test24  return25}26 27do_execsql_test date2-100 {28  CREATE TABLE t1(x, y, CHECK( date(x) BETWEEN '2017-07-01' AND '2017-07-31' ));29  INSERT INTO t1(x,y) VALUES('2017-07-20','one');30} {}31do_catchsql_test date2-110 {32  INSERT INTO t1(x,y) VALUES('now','two');33} {1 {non-deterministic use of date() in a CHECK constraint}}34do_execsql_test date2-120 {35  SELECT * FROM t1;36} {2017-07-20 one}37do_catchsql_test date2-130 {38  INSERT INTO t1(x,y) VALUES('2017-08-01','two');39} {1 {CHECK constraint failed: date(x) BETWEEN '2017-07-01' AND '2017-07-31'}}40 41# 2021-03-16 Forum post https://sqlite.org/forum/forumpost/464afd408642do_catchsql_test date2-140 {43  DROP TABLE t1;44  CREATE TABLE t1(x, y, z AS (date()));45  INSERT INTO t1(x,y) VALUES(1,2);46} {1 {non-deterministic use of date() in a generated column}}47 48do_execsql_test date2-200 {49  CREATE TABLE t2(x,y);50  INSERT INTO t2(x,y) VALUES(1, '2017-07-20'), (2, 'xyzzy');51  CREATE INDEX t2y ON t2(date(y));52}53do_catchsql_test date2-210 {54  INSERT INTO t2(x,y) VALUES(3, 'now');55} {1 {non-deterministic use of date() in an index}}56do_execsql_test date2-220 {57  SELECT x, y FROM t2 ORDER BY x;58} {1 2017-07-20 2 xyzzy}59 60do_execsql_test date2-300 {61  CREATE TABLE t3(a INTEGER PRIMARY KEY,b);62  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)63    INSERT INTO t3(a,b) SELECT x, julianday('2017-07-01')+x FROM c;64  UPDATE t3 SET b='now' WHERE a=500;65}66do_catchsql_test date2-310 {67  CREATE INDEX t3b1 ON t3(datetime(b));68} {1 {non-deterministic use of datetime() in an index}}69do_catchsql_test date2-320 {70  CREATE INDEX t3b1 ON t3(datetime(b)) WHERE typeof(b)='real';71} {0 {}}72do_execsql_test date2-330 {73  EXPLAIN QUERY PLAN74  SELECT a FROM t375   WHERE typeof(b)='real'76     AND datetime(b) BETWEEN '2017-07-04' AND '2017-07-08';77} {/USING INDEX t3b/}78do_execsql_test date2-331 {79  SELECT a FROM t380   WHERE typeof(b)='real'81     AND datetime(b) BETWEEN '2017-07-04' AND '2017-07-08'82  ORDER BY a;83} {3 4 5 6}84 85do_execsql_test date2-400 {86  CREATE TABLE t4(a INTEGER PRIMARY KEY,b);87  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)88    INSERT INTO t4(a,b) SELECT x, julianday('2017-07-01')+x FROM c;89  UPDATE t4 SET b='now' WHERE a=500;90}91do_catchsql_test date2-410 {92  CREATE INDEX t4b1 ON t4(b)93    WHERE date(b) BETWEEN '2017-06-01' AND '2017-08-31';94} {1 {non-deterministic use of date() in an index}}95do_execsql_test date2-420 {96  DELETE FROM t4 WHERE a=500;97  CREATE INDEX t4b1 ON t4(b)98    WHERE date(b) BETWEEN '2017-06-01' AND '2017-08-31';99}100do_catchsql_test date2-430 {101  INSERT INTO t4(a,b) VALUES(9999,'now');102} {1 {non-deterministic use of date() in an index}}103 104do_execsql_test date2-500 {105  CREATE TABLE mods(x);106  INSERT INTO mods(x) VALUES107    ('+10 days'),108    ('-10 days'),109    ('+10 hours'),110    ('-10 hours'),111    ('+10 minutes'),112    ('-10 minutes'),113    ('+10 seconds'),114    ('-10 seconds'),115    ('+10 months'),116    ('-10 months'),117    ('+10 years'),118    ('-10 years'),119    ('start of month'),120    ('start of year'),121    ('start of day'),122    ('weekday 1'),123    ('unixepoch');124  CREATE TABLE t5(y,m);125  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)126    INSERT INTO t5(y,m) SELECT julianday('2017-07-01')+c.x, mods.x FROM c, mods;127  CREATE INDEX t5x1 on t5(y) WHERE datetime(y,m) IS NOT NULL;128}129do_catchsql_test date2-510 {130  INSERT INTO t5(y,m) VALUES('2017-07-20','localtime');131} {1 {non-deterministic use of datetime() in an index}}132do_catchsql_test date2-520 {133  INSERT INTO t5(y,m) VALUES('2017-07-20','utc');134} {1 {non-deterministic use of datetime() in an index}}135 136# 2019-10-30 Ticket 830277d9db6c3ba1137#138do_catchsql_test date2-600 {139  CREATE TABLE t600(a REAL CHECK( a<julianday('now') ));140  INSERT INTO t600(a) VALUES(1.0);141} {1 {non-deterministic use of julianday() in a CHECK constraint}}142do_catchsql_test date2-601 {143  CREATE TABLE t601(a REAL, b TEXT, CHECK( a<julianday(b) ));144  INSERT INTO t601(a,b) VALUES(1.0, '1970-01-01');145} {0 {}}146do_catchsql_test date2-602 {147  INSERT INTO t601(a,b) VALUES(1e100, '1970-01-01');148} {1 {CHECK constraint failed: a<julianday(b)}}149do_catchsql_test date2-603 {150  INSERT INTO t601(a,b) VALUES(10, 'now');151} {1 {non-deterministic use of julianday() in a CHECK constraint}}152do_catchsql_test date2-604 {153  INSERT INTO t600(a) VALUES(julianday('now')+10);154} {1 {non-deterministic use of julianday() in a CHECK constraint}}155 156 157do_catchsql_test date2-610 {158  CREATE TABLE t610(a,b);159  CREATE INDEX t610x1 ON t610(julianday('now')+b);160  INSERT INTO t610(a,b) VALUES(123,456);161} {1 {non-deterministic use of julianday() in an index}}162do_catchsql_test date2-611 {163  CREATE TABLE t611(a,b);164  CREATE INDEX t611x1 ON t611(julianday(a)+b);165  INSERT INTO t611(a,b) VALUES('1970-01-01',10.0);166} {0 {}}167do_catchsql_test date2-612 {168  INSERT INTO t611(a,b) VALUES('now',10.0);169} {1 {non-deterministic use of julianday() in an index}}170 171do_catchsql_test date3-620 {172  CREATE TABLE t620(a, b AS (a+julianday('now')));173  INSERT INTO t620 VALUES(10);174} {1 {non-deterministic use of julianday() in a generated column}}175 176finish_test177