Annotation of embedaddon/sqlite3/test/misc4.test, revision 1.1.1.1
1.1 misho 1: # 2004 Jun 27
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: misc4.test,v 1.23 2007/12/08 18:01:31 drh Exp $
17:
18: set testdir [file dirname $argv0]
19: source $testdir/tester.tcl
20:
21: # Prepare a statement that will create a temporary table. Then do
22: # a rollback. Then try to execute the prepared statement.
23: #
24: do_test misc4-1.1 {
25: set DB [sqlite3_connection_pointer db]
26: execsql {
27: CREATE TABLE t1(x);
28: INSERT INTO t1 VALUES(1);
29: }
30: } {}
31:
32: ifcapable tempdb {
33: do_test misc4-1.2 {
34: set sql {CREATE TEMP TABLE t2 AS SELECT * FROM t1}
35: set stmt [sqlite3_prepare $DB $sql -1 TAIL]
36: execsql {
37: BEGIN;
38: CREATE TABLE t3(a,b,c);
39: INSERT INTO t1 SELECT * FROM t1;
40: ROLLBACK;
41: }
42: } {}
43:
44: # Because the previous transaction included a DDL statement and
45: # was rolled back, statement $stmt was marked as expired. Executing it
46: # now returns SQLITE_SCHEMA.
47: do_test misc4-1.2.1 {
48: list [sqlite3_step $stmt] [sqlite3_finalize $stmt]
49: } {SQLITE_ERROR SQLITE_SCHEMA}
50: do_test misc4-1.2.2 {
51: set stmt [sqlite3_prepare $DB $sql -1 TAIL]
52: set TAIL
53: } {}
54:
55: do_test misc4-1.3 {
56: sqlite3_step $stmt
57: } SQLITE_DONE
58: do_test misc4-1.4 {
59: execsql {
60: SELECT * FROM temp.t2;
61: }
62: } {1}
63:
64: # Drop the temporary table, then rerun the prepared statement to
65: # recreate it again. This recreates ticket #807.
66: #
67: do_test misc4-1.5 {
68: execsql {DROP TABLE t2}
69: sqlite3_reset $stmt
70: sqlite3_step $stmt
71: } {SQLITE_ERROR}
72: do_test misc4-1.6 {
73: sqlite3_finalize $stmt
74: } {SQLITE_SCHEMA}
75: }
76:
77: # Prepare but do not execute various CREATE statements. Then before
78: # those statements are executed, try to use the tables, indices, views,
79: # are triggers that were created.
80: #
81: do_test misc4-2.1 {
82: set stmt [sqlite3_prepare $DB {CREATE TABLE t3(x);} -1 TAIL]
83: catchsql {
84: INSERT INTO t3 VALUES(1);
85: }
86: } {1 {no such table: t3}}
87: do_test misc4-2.2 {
88: sqlite3_step $stmt
89: } SQLITE_DONE
90: do_test misc4-2.3 {
91: sqlite3_finalize $stmt
92: } SQLITE_OK
93: do_test misc4-2.4 {
94: catchsql {
95: INSERT INTO t3 VALUES(1);
96: }
97: } {0 {}}
98:
99: # Ticket #966
100: #
101: do_test misc4-3.1 {
102: execsql {
103: CREATE TABLE Table1(ID integer primary key, Value TEXT);
104: INSERT INTO Table1 VALUES(1, 'x');
105: CREATE TABLE Table2(ID integer NOT NULL, Value TEXT);
106: INSERT INTO Table2 VALUES(1, 'z');
107: INSERT INTO Table2 VALUES (1, 'a');
108: }
109: catchsql {
110: SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2 ORDER BY 1, 2;
111: }
112: } {1 {aggregate functions are not allowed in the GROUP BY clause}}
113: ifcapable compound {
114: do_test misc4-3.2 {
115: execsql {
116: SELECT ID, Value FROM Table1
117: UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1
118: ORDER BY 1, 2;
119: }
120: } {1 x 1 z}
121: do_test misc4-3.3 {
122: catchsql {
123: SELECT ID, Value FROM Table1
124: UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
125: ORDER BY 1, 2;
126: }
127: } {1 {aggregate functions are not allowed in the GROUP BY clause}}
128: do_test misc4-3.4 {
129: catchsql {
130: SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
131: UNION SELECT ID, Value FROM Table1
132: ORDER BY 1, 2;
133: }
134: } {1 {aggregate functions are not allowed in the GROUP BY clause}}
135: } ;# ifcapable compound
136:
137: # Ticket #1047. Make sure column types are preserved in subqueries.
138: #
139: ifcapable subquery {
140: do_test misc4-4.1 {
141: execsql {
142: create table a(key varchar, data varchar);
143: create table b(key varchar, period integer);
144: insert into a values('01','data01');
145: insert into a values('+1','data+1');
146:
147: insert into b values ('01',1);
148: insert into b values ('01',2);
149: insert into b values ('+1',3);
150: insert into b values ('+1',4);
151:
152: select a.*, x.*
153: from a, (select key,sum(period) from b group by key) as x
154: where a.key=x.key order by 1 desc;
155: }
156: } {01 data01 01 3 +1 data+1 +1 7}
157:
158: # This test case tests the same property as misc4-4.1, but it is
159: # a bit smaller which makes it easier to work with while debugging.
160: do_test misc4-4.2 {
161: execsql {
162: CREATE TABLE ab(a TEXT, b TEXT);
163: INSERT INTO ab VALUES('01', '1');
164: }
165: execsql {
166: select * from ab, (select b from ab) as x where x.b = ab.a;
167: }
168: } {}
169: }
170:
171:
172: # Ticket #1036. When creating tables from a SELECT on a view, use the
173: # short names of columns.
174: #
175: ifcapable view {
176: do_test misc4-5.1 {
177: execsql {
178: create table t4(a,b);
179: create table t5(a,c);
180: insert into t4 values (1,2);
181: insert into t5 values (1,3);
182: create view myview as select t4.a a from t4 inner join t5 on t4.a=t5.a;
183: create table problem as select * from myview;
184: }
185: execsql2 {
186: select * FROM problem;
187: }
188: } {a 1}
189: do_test misc4-5.2 {
190: execsql2 {
191: create table t6 as select * from t4, t5;
192: select * from t6;
193: }
194: } {a 1 b 2 a:1 1 c 3}
195: }
196:
197: # Ticket #1086
198: do_test misc4-6.1 {
199: execsql {
200: CREATE TABLE abc(a);
201: INSERT INTO abc VALUES(1);
202: CREATE TABLE def(d, e, f, PRIMARY KEY(d, e));
203: }
204: } {}
205: do_test misc4-6.2 {
206: execsql {
207: SELECT a FROM abc LEFT JOIN def ON (abc.a=def.d);
208: }
209: } {1}
210:
211: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to misc4.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test