xref: /sqlite-3.40.0/test/default.test (revision 74bbd37d) 
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
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20
21ifcapable 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}
33do_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 {}}
43do_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
52ifcapable 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
67do_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}
82do_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}
87do_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#
106db close
107forcedelete test.db
108sqlite3 db test.db
109sqlite3_db_config db DEFENSIVE 0
110do_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} {}
115db close
116sqlite3 db test.db
117do_execsql_test default-4.1 {
118  INSERT INTO t1(a) VALUES('xyzzy');
119  SELECT a, quote(b) FROM t1;
120} {xyzzy NULL}
121do_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}}
124do_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}}
127do_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#
134reset_db
135do_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
140finish_test
141