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
|
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
|
-
+
-
+
-
-
-
-
-
-
-
-
-
-
+
+
+
+
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-
-
+
+
-
+
-
+
-
+
-
+
+
+
+
+
+
+
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
|
# 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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.1 2002/02/18 03:21:47 drh Exp $
# $Id: select6.test,v 1.2 2002/02/18 13:35:33 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# Build some test data
#
set fd [open data1.txt w]
for {set i 1} {$i<32} {incr i} {
for {set j 0} {pow(2,$j)<$i} {incr j} {}
puts $fd "[expr {32-$i}]\t[expr {10-$j}]"
}
close $fd
execsql {
CREATE TABLE t1(x int, y int);
COPY t1 FROM 'data1.txt'
}
file delete data1.txt
do_test select6-1.0 {
execsql {SELECT DISTINCT y FROM t1 ORDER BY y}
} {5 6 7 8 9 10}
execsql2 {SELECT * FROM (SELECT x, y FROM t1 ORDER BY x LIMIT 1)}
} {x 31 y 10}
execsql2 {SELECT * FROM (SELECT x, y FROM t1 WHERE x<2)}
} {x 1 y 1}
do_test select6-1.2 {
execsql {SELECT count(*) FROM (SELECT y FROM t1)}
} {31}
} {20}
do_test select6-1.3 {
execsql {SELECT count(*) FROM (SELECT DISTINCT y FROM t1)}
} {6}
} {5}
do_test select6-1.4 {
execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT y FROM t1))}
} {6}
} {5}
do_test select6-1.5 {
execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT y FROM t1))}
} {6}
} {5}
do_test select6-1.6 {
execsql {
SELECT *
FROM (SELECT count(*),y FROM t1 GROUP BY y) AS a,
(SELECT max(x),y FROM t1 GROUP BY y) as b
WHERE a.y=b.y ORDER BY a.y
}
} {1 1 1 1 2 2 3 2 4 3 7 3 8 4 15 4 5 5 20 5}
do_test select6-1.7 {
execsql {
SELECT a.y, a.[count(*)], [max(x)], [count(*)]
FROM (SELECT count(*),y FROM t1 GROUP BY y) AS a,
(SELECT max(x),y FROM t1 GROUP BY y) as b
WHERE a.y=b.y ORDER BY a.y
}
} {1 1 1 1 2 2 3 2 3 4 7 4 4 8 15 8 5 5 20 5}
do_test select6-1.8 {
execsql {
SELECT q, p, r
FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a,
(SELECT max(x) as r, y as s FROM t1 GROUP BY y) as b
WHERE q=s ORDER BY s
}
} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
do_test select6-1.9 {
execsql {
SELECT q, p, r, b.[min(x)+y]
FROM (SELECT count(*) as p , y as q FROM t1 GROUP BY y) AS a,
(SELECT max(x) as r, y as s, min(x)+y FROM t1 GROUP BY y) as b
WHERE q=s ORDER BY s
}
} {1 1 1 2 2 2 3 4 3 4 7 7 4 8 15 12 5 5 20 21}
do_test select6-2.0 {
execsql {
CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
INSERT INTO t2 SELECT * FROM t1;
SELECT DISTINCT b FROM t2 ORDER BY b;
}
} {1 2 3 4 5}
do_test select6-2.1 {
execsql2 {SELECT * FROM (SELECT a, b FROM t2 WHERE a<2)}
} {a 1 b 1}
do_test select6-2.2 {
execsql {SELECT count(*) FROM (SELECT b FROM t2)}
} {20}
do_test select6-2.3 {
execsql {SELECT count(*) FROM (SELECT DISTINCT b FROM t2)}
} {5}
do_test select6-2.4 {
execsql {SELECT count(*) FROM (SELECT DISTINCT * FROM (SELECT b FROM t2))}
} {5}
do_test select6-2.5 {
execsql {SELECT count(*) FROM (SELECT * FROM (SELECT DISTINCT b FROM t2))}
} {5}
do_test select6-2.6 {
execsql {
SELECT *
FROM (SELECT count(*),b FROM t2 GROUP BY b) AS a,
(SELECT max(a),b FROM t2 GROUP BY b) as b
WHERE a.b=b.b ORDER BY a.b
}
} {1 1 1 1 2 2 3 2 4 3 7 3 8 4 15 4 5 5 20 5}
do_test select6-2.7 {
execsql {
SELECT a.b, a.[count(*)], [max(a)], [count(*)]
FROM (SELECT count(*),b FROM t2 GROUP BY b) AS a,
(SELECT max(a),b FROM t2 GROUP BY b) as b
WHERE a.b=b.b ORDER BY a.b
}
} {1 1 1 1 2 2 3 2 3 4 7 4 4 8 15 8 5 5 20 5}
do_test select6-2.8 {
execsql {
SELECT q, p, r
FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY b) AS a,
(SELECT max(a) as r, b as s FROM t2 GROUP BY b) as b
WHERE q=s ORDER BY s
}
} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
do_test select6-2.9 {
execsql {
SELECT a.q, a.p, b.r
FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
(SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
WHERE a.q=b.s ORDER BY a.q
}
} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
do_test sqlite6-3.1 {
execsql2 {
SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE x=3));
}
} {x 3 y 2}
do_test sqlite6-3.2 {
execsql {
SELECT * FROM
(SELECT a.q, a.p, b.r
FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
(SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
WHERE a.q=b.s ORDER BY a.q)
ORDER BY [a.q]
}
} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
finish_test
|