1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
|
# 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 function in index expression or 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: t1}}
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 function in index expression or CHECK constraint}}
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 function in index expression or CHECK constraint}}
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 function in index expression or CHECK constraint}}
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 function in index expression or CHECK constraint}}
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 function in index expression or CHECK constraint}}
do_catchsql_test date2-520 {
INSERT INTO t5(y,m) VALUES('2017-07-20','utc');
} {1 {non-deterministic function in index expression or CHECK constraint}}
finish_test
|
