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