xref: /sqlite-3.40.0/test/misc2.test (revision c023e03e) 
1# 2003 June 21
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.
12#
13# This file implements tests for miscellanous features that were
14# left out of other test files.
15#
16# $Id: misc2.test,v 1.8 2003/08/27 22:54:32 drh Exp $
17
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20
21# Test for ticket #360
22#
23do_test misc2-1.1 {
24  catchsql {
25    CREATE TABLE FOO(bar integer);
26    CREATE TRIGGER foo_insert BEFORE INSERT ON foo BEGIN
27      SELECT CASE WHEN (NOT new.bar BETWEEN 0 AND 20)
28             THEN raise(rollback, 'aiieee') END;
29    END;
30    INSERT INTO foo(bar) VALUES (1);
31  }
32} {0 {}}
33do_test misc2-1.2 {
34  catchsql {
35    INSERT INTO foo(bar) VALUES (111);
36  }
37} {1 aiieee}
38
39# Make sure ROWID works on a view and a subquery.  Ticket #364
40#
41do_test misc2-2.1 {
42  execsql {
43    CREATE TABLE t1(a,b,c);
44    INSERT INTO t1 VALUES(1,2,3);
45    CREATE TABLE t2(a,b,c);
46    INSERT INTO t2 VALUES(7,8,9);
47    SELECT rowid, * FROM (SELECT * FROM t1, t2);
48  }
49} {{} 1 2 3 7 8 9}
50do_test misc2-2.2 {
51  execsql {
52    CREATE VIEW v1 AS SELECT * FROM t1, t2;
53    SELECT rowid, * FROM v1;
54  }
55} {{} 1 2 3 7 8 9}
56
57# Check name binding precedence.  Ticket #387
58#
59do_test misc2-3.1 {
60  catchsql {
61    SELECT t1.b+t2.b AS a, t1.a, t2.a FROM t1, t2 WHERE a==10
62  }
63} {1 {ambiguous column name: a}}
64
65# Make sure 32-bit integer overflow is handled properly in queries.
66# ticket #408
67#
68do_test misc2-4.1 {
69  execsql {
70    INSERT INTO t1 VALUES(4000000000,'a','b');
71    SELECT a FROM t1 WHERE a>1;
72  }
73} {4000000000}
74do_test misc2-4.2 {
75  execsql {
76    INSERT INTO t1 VALUES(2147483648,'b2','c2');
77    INSERT INTO t1 VALUES(2147483647,'b3','c3');
78    SELECT a FROM t1 WHERE a>2147483647;
79  }
80} {4000000000 2147483648}
81do_test misc2-4.3 {
82  execsql {
83    SELECT a FROM t1 WHERE a<2147483648;
84  }
85} {1 2147483647}
86do_test misc2-4.4 {
87  execsql {
88    SELECT a FROM t1 WHERE a<=2147483648;
89  }
90} {1 2147483648 2147483647}
91do_test misc2-4.5 {
92  execsql {
93    SELECT a FROM t1 WHERE a<10000000000;
94  }
95} {1 4000000000 2147483648 2147483647}
96do_test misc2-4.6 {
97  execsql {
98    SELECT a FROM t1 WHERE a<1000000000000 ORDER BY 1;
99  }
100} {1 2147483647 2147483648 4000000000}
101
102# There were some issues with expanding a SrcList object using a call
103# to sqliteSrcListAppend() if the SrcList had previously been duplicated
104# using a call to sqliteSrcListDup().  Ticket #416.  The following test
105# makes sure the problem has been fixed.
106#
107do_test misc2-5.1 {
108  execsql {
109    CREATE TABLE x(a,b);
110    CREATE VIEW y AS
111      SELECT x1.b AS p, x2.b AS q FROM x AS x1, x AS x2 WHERE x1.a=x2.a;
112    CREATE VIEW z AS
113      SELECT y1.p, y2.p FROM y AS y1, y AS y2 WHERE y1.q=y2.q;
114    SELECT * from z;
115  }
116} {}
117
118# Make sure we can open a database with an empty filename.  What this
119# does is store the database in a temporary file that is deleted when
120# the database is closed.  Ticket #432.
121#
122do_test misc2-6.1 {
123  db close
124  sqlite db {}
125  execsql {
126    CREATE TABLE t1(a,b);
127    INSERT INTO t1 VALUES(1,2);
128    SELECT * FROM t1;
129  }
130} {1 2}
131
132# Make sure we get an error message (not a segfault) on an attempt to
133# update a table from within the callback of a select on that same
134# table.
135#
136do_test misc2-7.1 {
137  db close
138  file delete -force test.db
139  sqlite db test.db
140  execsql {
141    CREATE TABLE t1(x);
142    INSERT INTO t1 VALUES(1);
143  }
144  set rc [catch {
145    db eval {SELECT rowid FROM t1} {} {
146      db eval "DELETE FROM t1 WHERE rowid=$rowid"
147    }
148  } msg]
149  lappend rc $msg
150} {1 {database table is locked}}
151do_test misc2-7.2 {
152  set rc [catch {
153    db eval {SELECT rowid FROM t1} {} {
154      db eval "INSERT INTO t1 VALUES(3)"
155    }
156  } msg]
157  lappend rc $msg
158} {1 {database table is locked}}
159do_test misc2-7.3 {
160  db close
161  file delete -force test.db
162  sqlite db :memory:
163  execsql {
164    CREATE TABLE t1(x);
165    INSERT INTO t1 VALUES(1);
166  }
167  set rc [catch {
168    db eval {SELECT rowid FROM t1} {} {
169      db eval "DELETE FROM t1 WHERE rowid=$rowid"
170    }
171  } msg]
172  lappend rc $msg
173} {1 {database table is locked}}
174do_test misc2-7.4 {
175  set rc [catch {
176    db eval {SELECT rowid FROM t1} {} {
177      db eval "INSERT INTO t1 VALUES(3)"
178    }
179  } msg]
180  lappend rc $msg
181} {1 {database table is locked}}
182