wasCSharpSQLite – Blame information for rev 4

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # The author disclaims copyright to this source code. In place of
2 # a legal notice, here is a blessing:
3 #
4 # May you do good and not evil.
5 # May you find forgiveness for yourself and forgive others.
6 # May you share freely, never taking more than you give.
7 #
8 #***********************************************************************
9 # This file implements regression tests for SQLite library. The
10 # focus of this file is testing compute SELECT statements and nested
11 # views.
12 #
13 # $Id: select7.test,v 1.11 2007/09/12 17:01:45 danielk1977 Exp $
14  
15  
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18  
19 ifcapable compound {
20  
21 # A 3-way INTERSECT. Ticket #875
22 ifcapable tempdb {
23 do_test select7-1.1 {
24 execsql {
25 create temp table t1(x);
26 insert into t1 values('amx');
27 insert into t1 values('anx');
28 insert into t1 values('amy');
29 insert into t1 values('bmy');
30 select * from t1 where x like 'a__'
31 intersect select * from t1 where x like '_m_'
32 intersect select * from t1 where x like '__x';
33 }
34 } {amx}
35 }
36  
37  
38 # Nested views do not handle * properly. Ticket #826.
39 #
40 ifcapable view {
41 do_test select7-2.1 {
42 execsql {
43 CREATE TABLE x(id integer primary key, a TEXT NULL);
44 INSERT INTO x (a) VALUES ('first');
45 CREATE TABLE tempx(id integer primary key, a TEXT NULL);
46 INSERT INTO tempx (a) VALUES ('t-first');
47 CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id;
48 CREATE VIEW tv1b AS SELECT x.id, tx.id FROM x JOIN tempx tx on tx.id=x.id;
49 CREATE VIEW tv2 AS SELECT * FROM tv1 UNION SELECT * FROM tv1b;
50 SELECT * FROM tv2;
51 }
52 } {1 1}
53 } ;# ifcapable view
54  
55 } ;# ifcapable compound
56  
57 # Do not allow GROUP BY without an aggregate. Ticket #1039.
58 #
59 # Change: force any query with a GROUP BY clause to be processed as
60 # an aggregate query, whether it contains aggregates or not.
61 #
62 ifcapable subquery {
63 # do_test select7-3.1 {
64 # catchsql {
65 # SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name
66 # }
67 # } {1 {GROUP BY may only be used on aggregate queries}}
68 do_test select7-3.1 {
69 catchsql {
70 SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name
71 }
72 } [list 0 [execsql {SELECT * FROM sqlite_master ORDER BY name}]]
73 }
74  
75 # Ticket #2018 - Make sure names are resolved correctly on all
76 # SELECT statements of a compound subquery.
77 #
78 ifcapable {subquery && compound} {
79 do_test select7-4.1 {
80 execsql {
81 CREATE TABLE IF NOT EXISTS photo(pk integer primary key, x);
82 CREATE TABLE IF NOT EXISTS tag(pk integer primary key, fk int, name);
83  
84 SELECT P.pk from PHOTO P WHERE NOT EXISTS (
85 SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk
86 EXCEPT
87 SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%'
88 );
89 }
90 } {}
91 do_test select7-4.2 {
92 execsql {
93 INSERT INTO photo VALUES(1,1);
94 INSERT INTO photo VALUES(2,2);
95 INSERT INTO photo VALUES(3,3);
96 INSERT INTO tag VALUES(11,1,'one');
97 INSERT INTO tag VALUES(12,1,'two');
98 INSERT INTO tag VALUES(21,1,'one-b');
99 SELECT P.pk from PHOTO P WHERE NOT EXISTS (
100 SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk
101 EXCEPT
102 SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%'
103 );
104 }
105 } {2 3}
106 }
107  
108 # ticket #2347
109 #
110 ifcapable {subquery && compound} {
111 do_test select7-5.1 {
112 catchsql {
113 CREATE TABLE t2(a,b);
114 SELECT 5 IN (SELECT a,b FROM t2);
115 }
116 } [list 1 \
117 {only a single result allowed for a SELECT that is part of an expression}]
118 do_test select7-5.2 {
119 catchsql {
120 SELECT 5 IN (SELECT * FROM t2);
121 }
122 } [list 1 \
123 {only a single result allowed for a SELECT that is part of an expression}]
124 do_test select7-5.3 {
125 catchsql {
126 SELECT 5 IN (SELECT a,b FROM t2 UNION SELECT b,a FROM t2);
127 }
128 } [list 1 \
129 {only a single result allowed for a SELECT that is part of an expression}]
130 do_test select7-5.4 {
131 catchsql {
132 SELECT 5 IN (SELECT * FROM t2 UNION SELECT * FROM t2);
133 }
134 } [list 1 \
135 {only a single result allowed for a SELECT that is part of an expression}]
136 }
137  
138 # Verify that an error occurs if you have too many terms on a
139 # compound select statement.
140 #
141 ifcapable compound {
142 if {$SQLITE_MAX_COMPOUND_SELECT>0} {
143 set sql {SELECT 0}
144 set result 0
145 for {set i 1} {$i<$SQLITE_MAX_COMPOUND_SELECT} {incr i} {
146 append sql " UNION ALL SELECT $i"
147 lappend result $i
148 }
149 do_test select7-6.1 {
150 catchsql $sql
151 } [list 0 $result]
152 append sql { UNION ALL SELECT 99999999}
153 do_test select7-6.2 {
154 catchsql $sql
155 } {1 {too many terms in compound SELECT}}
156 }
157 }
158  
159 # This block of tests verifies that bug aa92c76cd4 is fixed.
160 #
161 do_test select7-7.1 {
162 execsql {
163 CREATE TABLE t3(a REAL);
164 INSERT INTO t3 VALUES(44.0);
165 INSERT INTO t3 VALUES(56.0);
166 }
167 } {}
168 do_test select7-7.2 {
169 execsql {
170 pragma vdbe_trace = 0;
171 SELECT (CASE WHEN a=0 THEN 0 ELSE (a + 25) / 50 END) AS categ, count(*)
172 FROM t3 GROUP BY categ
173 }
174 } {1.38 1 1.62 1}
175 do_test select7-7.3 {
176 execsql {
177 CREATE TABLE t4(a REAL);
178 INSERT INTO t4 VALUES( 2.0 );
179 INSERT INTO t4 VALUES( 3.0 );
180 }
181 } {}
182 do_test select7-7.4 {
183 execsql {
184 SELECT (CASE WHEN a=0 THEN 'zero' ELSE a/2 END) AS t FROM t4 GROUP BY t;
185 }
186 } {1.0 1.5}
187 do_test select7-7.5 {
188 execsql { SELECT a=0, typeof(a) FROM t4 }
189 } {0 real 0 real}
190 do_test select7-7.6 {
191 execsql { SELECT a=0, typeof(a) FROM t4 GROUP BY a }
192 } {0 real 0 real}
193  
194 do_test select7-7.7 {
195 execsql {
196 CREATE TABLE t5(a TEXT, b INT);
197 INSERT INTO t5 VALUES(123, 456);
198 SELECT typeof(a), a FROM t5 GROUP BY a HAVING a<b;
199 }
200 } {text 123}
201  
202 finish_test