gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/default.test (about) 1 # 2005 August 18 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 corner cases of the DEFAULT syntax 13 # on table definitions. 14 # 15 # $Id: default.test,v 1.3 2009/02/19 14:39:25 danielk1977 Exp $ 16 # 17 18 set testdir [file dirname $argv0] 19 source $testdir/tester.tcl 20 21 ifcapable bloblit { 22 do_test default-1.1 { 23 execsql { 24 CREATE TABLE t1( 25 a INTEGER, 26 b BLOB DEFAULT x'6869' 27 ); 28 INSERT INTO t1(a) VALUES(1); 29 SELECT * from t1; 30 } 31 } {1 hi} 32 } 33 do_test default-1.2 { 34 execsql { 35 CREATE TABLE t2( 36 x INTEGER, 37 y INTEGER DEFAULT NULL 38 ); 39 INSERT INTO t2(x) VALUES(1); 40 SELECT * FROM t2; 41 } 42 } {1 {}} 43 do_test default-1.3 { 44 catchsql { 45 CREATE TABLE t3( 46 x INTEGER, 47 y INTEGER DEFAULT (max(x,5)) 48 ) 49 } 50 } {1 {default value of column [y] is not constant}} 51 52 ifcapable pragma { 53 do_test default-2.1 { 54 execsql { 55 CREATE TABLE t4(c DEFAULT 'abc'); 56 PRAGMA table_info(t4); 57 } 58 } {0 c {} 0 'abc' 0} 59 do_test default-2.2 { 60 execsql { 61 INSERT INTO t4 DEFAULT VALUES; 62 PRAGMA table_info(t4); 63 } 64 } {0 c {} 0 'abc' 0} 65 } 66 67 do_execsql_test default-3.1 { 68 CREATE TABLE t3( 69 a INTEGER PRIMARY KEY AUTOINCREMENT, 70 b INT DEFAULT 12345 UNIQUE NOT NULL CHECK( b>=0 AND b<99999 ), 71 c VARCHAR(123,456) DEFAULT 'hello' NOT NULL ON CONFLICT REPLACE, 72 d REAL, 73 e FLOATING POINT(5,10) DEFAULT 4.36, 74 f NATIONAL CHARACTER(15) COLLATE RTRIM, 75 g LONG INTEGER DEFAULT( 3600*12 ) 76 ); 77 INSERT INTO t3 VALUES(null, 5, 'row1', '5.25', 'xyz', 321, '432'); 78 SELECT a, typeof(a), b, typeof(b), c, typeof(c), 79 d, typeof(d), e, typeof(e), f, typeof(f), 80 g, typeof(g) FROM t3; 81 } {1 integer 5 integer row1 text 5.25 real xyz text 321 text 432 integer} 82 do_execsql_test default-3.2 { 83 DELETE FROM t3; 84 INSERT INTO t3 DEFAULT VALUES; 85 SELECT * FROM t3; 86 } {2 12345 hello {} 4.36 {} 43200} 87 do_execsql_test default-3.3 { 88 CREATE TABLE t300( 89 a INT DEFAULT 2147483647, 90 b INT DEFAULT 2147483648, 91 c INT DEFAULT +9223372036854775807, 92 d INT DEFAULT -2147483647, 93 e INT DEFAULT -2147483648, 94 f INT DEFAULT -9223372036854775808, 95 g INT DEFAULT (-(-9223372036854775808)), 96 h INT DEFAULT (-(-9223372036854775807)) 97 ); 98 INSERT INTO t300 DEFAULT VALUES; 99 SELECT * FROM t300; 100 } {2147483647 2147483648 9223372036854775807 -2147483647 -2147483648 -9223372036854775808 9.22337203685478e+18 9223372036854775807} 101 102 # Do now allow bound parameters in new DEFAULT values. 103 # Silently convert bound parameters to NULL in DEFAULT causes 104 # in the sqlite_master table, for backwards compatibility. 105 # 106 db close 107 forcedelete test.db 108 sqlite3 db test.db 109 sqlite3_db_config db DEFENSIVE 0 110 do_execsql_test default-4.0 { 111 CREATE TABLE t1(a TEXT, b TEXT DEFAULT(99)); 112 PRAGMA writable_schema=ON; 113 UPDATE sqlite_master SET sql='CREATE TABLE t1(a TEXT, b TEXT DEFAULT(:xyz))'; 114 } {} 115 db close 116 sqlite3 db test.db 117 do_execsql_test default-4.1 { 118 INSERT INTO t1(a) VALUES('xyzzy'); 119 SELECT a, quote(b) FROM t1; 120 } {xyzzy NULL} 121 do_catchsql_test default-4.2 { 122 CREATE TABLE t2(a TEXT, b TEXT DEFAULT(:xyz)); 123 } {1 {default value of column [b] is not constant}} 124 do_catchsql_test default-4.3 { 125 CREATE TABLE t2(a TEXT, b TEXT DEFAULT(abs(:xyz))); 126 } {1 {default value of column [b] is not constant}} 127 do_catchsql_test default-4.4 { 128 CREATE TABLE t2(a TEXT, b TEXT DEFAULT(98+coalesce(5,:xyz))); 129 } {1 {default value of column [b] is not constant}} 130 131 # 2020-03-09 out-of-bounds memory access discovered by "Eternal Sakura" 132 # and reported to chromium. 133 # 134 reset_db 135 do_catchsql_test default-5.1 { 136 CREATE TABLE t1 (a,b DEFAULT(random() NOTNULL IN (RAISE(IGNORE),2,3))); 137 INSERT INTO t1(a) VALUES(1); 138 } {1 {RAISE() may only be used within a trigger-program}} 139 140 finish_test