| # 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 use of date() in a 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: date(x) BETWEEN '2017-07-01' AND '2017-07-31'}} |
| |
| # 2021-03-16 Forum post https://sqlite.org/forum/forumpost/464afd4086 |
| do_catchsql_test date2-140 { |
| DROP TABLE t1; |
| CREATE TABLE t1(x, y, z AS (date())); |
| INSERT INTO t1(x,y) VALUES(1,2); |
| } {1 {non-deterministic use of date() in a generated column}} |
| |
| 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 use of date() in an index}} |
| 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 use of datetime() in an index}} |
| 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 use of date() in an index}} |
| 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 use of date() in an index}} |
| |
| 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 use of datetime() in an index}} |
| do_catchsql_test date2-520 { |
| INSERT INTO t5(y,m) VALUES('2017-07-20','utc'); |
| } {1 {non-deterministic use of datetime() in an index}} |
| |
| # 2019-10-30 Ticket 830277d9db6c3ba1 |
| # |
| do_catchsql_test date2-600 { |
| CREATE TABLE t600(a REAL CHECK( a<julianday('now') )); |
| INSERT INTO t600(a) VALUES(1.0); |
| } {1 {non-deterministic use of julianday() in a CHECK constraint}} |
| do_catchsql_test date2-601 { |
| CREATE TABLE t601(a REAL, b TEXT, CHECK( a<julianday(b) )); |
| INSERT INTO t601(a,b) VALUES(1.0, '1970-01-01'); |
| } {0 {}} |
| do_catchsql_test date2-602 { |
| INSERT INTO t601(a,b) VALUES(1e100, '1970-01-01'); |
| } {1 {CHECK constraint failed: a<julianday(b)}} |
| do_catchsql_test date2-603 { |
| INSERT INTO t601(a,b) VALUES(10, 'now'); |
| } {1 {non-deterministic use of julianday() in a CHECK constraint}} |
| do_catchsql_test date2-604 { |
| INSERT INTO t600(a) VALUES(julianday('now')+10); |
| } {1 {non-deterministic use of julianday() in a CHECK constraint}} |
| |
| |
| do_catchsql_test date2-610 { |
| CREATE TABLE t610(a,b); |
| CREATE INDEX t610x1 ON t610(julianday('now')+b); |
| INSERT INTO t610(a,b) VALUES(123,456); |
| } {1 {non-deterministic use of julianday() in an index}} |
| do_catchsql_test date2-611 { |
| CREATE TABLE t611(a,b); |
| CREATE INDEX t611x1 ON t611(julianday(a)+b); |
| INSERT INTO t611(a,b) VALUES('1970-01-01',10.0); |
| } {0 {}} |
| do_catchsql_test date2-612 { |
| INSERT INTO t611(a,b) VALUES('now',10.0); |
| } {1 {non-deterministic use of julianday() in an index}} |
| |
| do_catchsql_test date3-620 { |
| CREATE TABLE t620(a, b AS (a+julianday('now'))); |
| INSERT INTO t620 VALUES(10); |
| } {1 {non-deterministic use of julianday() in a generated column}} |
| |
| finish_test |