Debian Codegraph @

test/date2.test

raw
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
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18
19 # Skip this whole file if date and time functions are omitted
20 # at compile-time
21 #
22 ifcapable {!datetime} {
23 finish_test
24 return
25 }
26
27 do_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 } {}
31 do_catchsql_test date2-110 {
32 INSERT INTO t1(x,y) VALUES('now','two');
33 } {1 {non-deterministic use of date() in a CHECK constraint}}
34 do_execsql_test date2-120 {
35 SELECT * FROM t1;
36 } {2017-07-20 one}
37 do_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
42 do_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
48 do_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 }
53 do_catchsql_test date2-210 {
54 INSERT INTO t2(x,y) VALUES(3, 'now');
55 } {1 {non-deterministic use of date() in an index}}
56 do_execsql_test date2-220 {
57 SELECT x, y FROM t2 ORDER BY x;
58 } {1 2017-07-20 2 xyzzy}
59
60 do_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 }
66 do_catchsql_test date2-310 {
67 CREATE INDEX t3b1 ON t3(datetime(b));
68 } {1 {non-deterministic use of datetime() in an index}}
69 do_catchsql_test date2-320 {
70 CREATE INDEX t3b1 ON t3(datetime(b)) WHERE typeof(b)='real';
71 } {0 {}}
72 do_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/}
78 do_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
85 do_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 }
91 do_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}}
95 do_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 }
100 do_catchsql_test date2-430 {
101 INSERT INTO t4(a,b) VALUES(9999,'now');
102 } {1 {non-deterministic use of date() in an index}}
103
104 do_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 }
129 do_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}}
132 do_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 #
138 do_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}}
142 do_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 {}}
146 do_catchsql_test date2-602 {
147 INSERT INTO t601(a,b) VALUES(1e100, '1970-01-01');
148 } {1 {CHECK constraint failed: a<julianday(b)}}
149 do_catchsql_test date2-603 {
150 INSERT INTO t601(a,b) VALUES(10, 'now');
151 } {1 {non-deterministic use of julianday() in a CHECK constraint}}
152 do_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
157 do_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}}
162 do_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 {}}
167 do_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
171 do_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
176 finish_test