modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/date2.test (about)

     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 function in index expression or 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: t1}}
    40  
    41  do_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  }
    46  do_catchsql_test date2-210 {
    47    INSERT INTO t2(x,y) VALUES(3, 'now');
    48  } {1 {non-deterministic function in index expression or CHECK constraint}}
    49  do_execsql_test date2-220 {
    50    SELECT x, y FROM t2 ORDER BY x;
    51  } {1 2017-07-20 2 xyzzy}
    52  
    53  do_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  }
    59  do_catchsql_test date2-310 {
    60    CREATE INDEX t3b1 ON t3(datetime(b));
    61  } {1 {non-deterministic function in index expression or CHECK constraint}}
    62  do_catchsql_test date2-320 {
    63    CREATE INDEX t3b1 ON t3(datetime(b)) WHERE typeof(b)='real';
    64  } {0 {}}
    65  do_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/}
    71  do_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  
    78  do_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  }
    84  do_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 function in index expression or CHECK constraint}}
    88  do_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  }
    93  do_catchsql_test date2-430 {
    94    INSERT INTO t4(a,b) VALUES(9999,'now');
    95  } {1 {non-deterministic function in index expression or CHECK constraint}}
    96  
    97  do_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  }
   122  do_catchsql_test date2-510 {
   123    INSERT INTO t5(y,m) VALUES('2017-07-20','localtime');
   124  } {1 {non-deterministic function in index expression or CHECK constraint}}
   125  do_catchsql_test date2-520 {
   126    INSERT INTO t5(y,m) VALUES('2017-07-20','utc');
   127  } {1 {non-deterministic function in index expression or CHECK constraint}}
   128  
   129  
   130  
   131    
   132  
   133  
   134  finish_test