xref: /sqlite-3.40.0/test/date2.test (revision b0c4ef71) 
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: t1}}
40
41do_execsql_test date2-200 {
42  CREATE TABLE t2(x,y);
43  INSERT INTO t2(x,y) VALUES(1, '2017-07-20'), (2, 'xyzzy');
44  CREATE INDEX t2y ON t2(date(y));
45}
46do_catchsql_test date2-210 {
47  INSERT INTO t2(x,y) VALUES(3, 'now');
48} {1 {non-deterministic use of date() in an index}}
49do_execsql_test date2-220 {
50  SELECT x, y FROM t2 ORDER BY x;
51} {1 2017-07-20 2 xyzzy}
52
53do_execsql_test date2-300 {
54  CREATE TABLE t3(a INTEGER PRIMARY KEY,b);
55  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
56    INSERT INTO t3(a,b) SELECT x, julianday('2017-07-01')+x FROM c;
57  UPDATE t3 SET b='now' WHERE a=500;
58}
59do_catchsql_test date2-310 {
60  CREATE INDEX t3b1 ON t3(datetime(b));
61} {1 {non-deterministic use of datetime() in an index}}
62do_catchsql_test date2-320 {
63  CREATE INDEX t3b1 ON t3(datetime(b)) WHERE typeof(b)='real';
64} {0 {}}
65do_execsql_test date2-330 {
66  EXPLAIN QUERY PLAN
67  SELECT a FROM t3
68   WHERE typeof(b)='real'
69     AND datetime(b) BETWEEN '2017-07-04' AND '2017-07-08';
70} {/USING INDEX t3b/}
71do_execsql_test date2-331 {
72  SELECT a FROM t3
73   WHERE typeof(b)='real'
74     AND datetime(b) BETWEEN '2017-07-04' AND '2017-07-08'
75  ORDER BY a;
76} {3 4 5 6}
77
78do_execsql_test date2-400 {
79  CREATE TABLE t4(a INTEGER PRIMARY KEY,b);
80  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
81    INSERT INTO t4(a,b) SELECT x, julianday('2017-07-01')+x FROM c;
82  UPDATE t4 SET b='now' WHERE a=500;
83}
84do_catchsql_test date2-410 {
85  CREATE INDEX t4b1 ON t4(b)
86    WHERE date(b) BETWEEN '2017-06-01' AND '2017-08-31';
87} {1 {non-deterministic use of date() in an index}}
88do_execsql_test date2-420 {
89  DELETE FROM t4 WHERE a=500;
90  CREATE INDEX t4b1 ON t4(b)
91    WHERE date(b) BETWEEN '2017-06-01' AND '2017-08-31';
92}
93do_catchsql_test date2-430 {
94  INSERT INTO t4(a,b) VALUES(9999,'now');
95} {1 {non-deterministic use of date() in an index}}
96
97do_execsql_test date2-500 {
98  CREATE TABLE mods(x);
99  INSERT INTO mods(x) VALUES
100    ('+10 days'),
101    ('-10 days'),
102    ('+10 hours'),
103    ('-10 hours'),
104    ('+10 minutes'),
105    ('-10 minutes'),
106    ('+10 seconds'),
107    ('-10 seconds'),
108    ('+10 months'),
109    ('-10 months'),
110    ('+10 years'),
111    ('-10 years'),
112    ('start of month'),
113    ('start of year'),
114    ('start of day'),
115    ('weekday 1'),
116    ('unixepoch');
117  CREATE TABLE t5(y,m);
118  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
119    INSERT INTO t5(y,m) SELECT julianday('2017-07-01')+c.x, mods.x FROM c, mods;
120  CREATE INDEX t5x1 on t5(y) WHERE datetime(y,m) IS NOT NULL;
121}
122do_catchsql_test date2-510 {
123  INSERT INTO t5(y,m) VALUES('2017-07-20','localtime');
124} {1 {non-deterministic use of datetime() in an index}}
125do_catchsql_test date2-520 {
126  INSERT INTO t5(y,m) VALUES('2017-07-20','utc');
127} {1 {non-deterministic use of datetime() in an index}}
128
129# 2019-10-30 Ticket 830277d9db6c3ba1
130#
131do_catchsql_test date2-600 {
132  CREATE TABLE t600(a REAL CHECK( a<julianday('now') ));
133  INSERT INTO t600(a) VALUES(1.0);
134} {1 {non-deterministic use of julianday() in a CHECK constraint}}
135do_catchsql_test date2-601 {
136  CREATE TABLE t601(a REAL, b TEXT, CHECK( a<julianday(b) ));
137  INSERT INTO t601(a,b) VALUES(1.0, '1970-01-01');
138} {0 {}}
139do_catchsql_test date2-602 {
140  INSERT INTO t601(a,b) VALUES(1e100, '1970-01-01');
141} {1 {CHECK constraint failed: t601}}
142do_catchsql_test date2-603 {
143  INSERT INTO t601(a,b) VALUES(10, 'now');
144} {1 {non-deterministic use of julianday() in a CHECK constraint}}
145do_catchsql_test date2-604 {
146  INSERT INTO t600(a) VALUES(julianday('now')+10);
147} {1 {non-deterministic use of julianday() in a CHECK constraint}}
148
149
150do_catchsql_test date2-610 {
151  CREATE TABLE t610(a,b);
152  CREATE INDEX t610x1 ON t610(julianday('now')+b);
153  INSERT INTO t610(a,b) VALUES(123,456);
154} {1 {non-deterministic use of julianday() in an index}}
155do_catchsql_test date2-611 {
156  CREATE TABLE t611(a,b);
157  CREATE INDEX t611x1 ON t611(julianday(a)+b);
158  INSERT INTO t611(a,b) VALUES('1970-01-01',10.0);
159} {0 {}}
160do_catchsql_test date2-612 {
161  INSERT INTO t611(a,b) VALUES('now',10.0);
162} {1 {non-deterministic use of julianday() in an index}}
163
164do_catchsql_test date3-620 {
165  CREATE TABLE t620(a, b AS (a+julianday('now')));
166  INSERT INTO t620 VALUES(10);
167} {1 {non-deterministic use of julianday() in a generated column}}
168
169finish_test
170