Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | New test cases for date/time functions in indexes on expressions, in the WHERE clause of partial indexes, and in CHECK constraints. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | index-on-date-func |
| Files: | files | file ages | folders |
| SHA3-256: |
b7f70c7fcabc10b8b3e62fe5ac68476c |
| User & Date: | drh 2017-07-20 14:36:26 |
Context
|
2017-07-20
| ||
| 15:08 | Enhance the built-in date/time functions so that they can be used in CHECK constraints, in the WHERE clause or partial indexes, and index expressions, provided that none of the non-deterministic keywords ("now", "localtime", "utc") are used as arguments. (check-in: a90c062d user: drh tags: trunk) | |
| 14:36 | New test cases for date/time functions in indexes on expressions, in the WHERE clause of partial indexes, and in CHECK constraints. (Closed-Leaf check-in: b7f70c7f user: drh tags: index-on-date-func) | |
| 13:17 | Combine the Parse.ckBase and Parse.iSelfTab fields into just Parse.iSelfTab. This fixes a problem with date/time functions in check-constraints. Add some test cases for date/time functions in index expressions and check constraints. (check-in: 22eda098 user: drh tags: index-on-date-func) | |
Changes
Changes to test/date2.test.
45 46 47 48 49 50 51 52 53 |
}
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}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
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 |
}
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
|