wasCSharpSQLite – Blame information for rev 7
?pathlinks?
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 |