1# 2017-07-20 2# 3# The author disclaims copyright to this source code. In place of 4# 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. The 12# focus of this file is testing date and time functions used in 13# check constraints and index expressions. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19# Skip this whole file if date and time functions are omitted 20# at compile-time 21# 22ifcapable {!datetime} { 23 finish_test 24 return 25} 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/464afd4086 42do_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 PLAN 74 SELECT a FROM t3 75 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 t3 80 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) VALUES 107 ('+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 830277d9db6c3ba1 137# 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_test 177