Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add tests for some syntax diagrams in lang_select.html. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
2254e93bd5f70af5d3b2021983e4b826 |
| User & Date: | dan 2010-09-17 19:04:19 |
Context
|
2010-09-17
| ||
| 19:45 | Updates to the documentation for sqlite3_create_collation(). (check-in: 3df7715a user: drh tags: trunk) | |
| 19:04 | Add tests for some syntax diagrams in lang_select.html. (check-in: 2254e93b user: dan tags: trunk) | |
| 17:10 | Remove additional occurrences of <ctype.h> from FTS2. (check-in: ee52589c user: drh tags: trunk) | |
Changes
Changes to test/e_select.test.
91
92
93
94
95
96
97
98
99
100
101
102
103
104
....
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
|
} {9}
do_catchsql_test e_select-0.1.4 {
SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a)
} {1 {cannot have both ON and USING clauses in the same join}}
do_catchsql_test e_select-0.1.5 {
SELECT count(*) FROM t1, t2 USING (a) ON (t1.a=t2.a)
} {1 {near "ON": syntax error}}
#-------------------------------------------------------------------------
# The following tests focus on FROM clause (join) processing.
#
# EVIDENCE-OF: R-16074-54196 If the FROM clause is omitted from a simple
# SELECT statement, then the input data is implicitly a single row zero
................................................................................
# EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate
# query, then each expression in the result expression list is evaluated
# for each row in the dataset filtered by the WHERE clause.
#
# By other definitions in lang_select.html, a non-aggregate query is
# any simple SELECT that has no GROUP BY clause and no aggregate expressions
# in the result expression list.
#
do_execsql_test e_select-4.4.1 {
SELECT a, b FROM z1
} {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born}
do_execsql_test e_select-4.4.2 {
SELECT a IS NULL, b+1, * FROM z1
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
|
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
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
....
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
|
} {9}
do_catchsql_test e_select-0.1.4 {
SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a)
} {1 {cannot have both ON and USING clauses in the same join}}
do_catchsql_test e_select-0.1.5 {
SELECT count(*) FROM t1, t2 USING (a) ON (t1.a=t2.a)
} {1 {near "ON": syntax error}}
# EVIDENCE-OF: R-44854-11739 -- syntax diagram select-core
#
# 0: SELECT ...
# 1: SELECT DISTINCT ...
# 2: SELECT ALL ...
#
# 0: No FROM clause
# 1: Has FROM clause
#
# 0: No WHERE clause
# 1: Has WHERE clause
#
# 0: No GROUP BY clause
# 1: Has GROUP BY clause
# 2: Has GROUP BY and HAVING clauses
#
foreach {tn select res} {
0000.1 "SELECT 1, 2, 3 " {1 2 3}
1000.1 "SELECT DISTINCT 1, 2, 3 " {1 2 3}
2000.1 "SELECT ALL 1, 2, 3 " {1 2 3}
0100.1 "SELECT a, b, a||b FROM t1 " {
a one aone b two btwo c three cthree
}
1100.1 "SELECT DISTINCT a, b, a||b FROM t1 " {
a one aone b two btwo c three cthree
}
1200.1 "SELECT ALL a, b, a||b FROM t1 " {
a one aone b two btwo c three cthree
}
0010.1 "SELECT 1, 2, 3 WHERE 1 " {1 2 3}
0010.2 "SELECT 1, 2, 3 WHERE 0 " {}
0010.3 "SELECT 1, 2, 3 WHERE NULL " {}
1010.1 "SELECT DISTINCT 1, 2, 3 WHERE 1 " {1 2 3}
2010.1 "SELECT ALL 1, 2, 3 WHERE 1 " {1 2 3}
0110.1 "SELECT a, b, a||b FROM t1 WHERE a!='x' " {
a one aone b two btwo c three cthree
}
0110.2 "SELECT a, b, a||b FROM t1 WHERE a=='x'" {}
1110.1 "SELECT DISTINCT a, b, a||b FROM t1 WHERE a!='x' " {
a one aone b two btwo c three cthree
}
2110.0 "SELECT ALL a, b, a||b FROM t1 WHERE a=='x'" {}
0001.1 "SELECT 1, 2, 3 GROUP BY 2" {1 2 3}
0002.1 "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
0002.2 "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}
1001.1 "SELECT DISTINCT 1, 2, 3 GROUP BY 2" {1 2 3}
1002.1 "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
1002.2 "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}
2001.1 "SELECT ALL 1, 2, 3 GROUP BY 2" {1 2 3}
2002.1 "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
2002.2 "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}
0101.1 "SELECT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
0102.1 "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=1" {
1 a 1 c 1 b
}
0102.2 "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=2" { }
1101.1 "SELECT DISTINCT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
1102.1 "SELECT DISTINCT count(*), max(a) FROM t1
GROUP BY b HAVING count(*)=1" {
1 a 1 c 1 b
}
1102.2 "SELECT DISTINCT count(*), max(a) FROM t1
GROUP BY b HAVING count(*)=2" {
}
2101.1 "SELECT ALL count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
2102.1 "SELECT ALL count(*), max(a) FROM t1
GROUP BY b HAVING count(*)=1" {
1 a 1 c 1 b
}
2102.2 "SELECT ALL count(*), max(a) FROM t1
GROUP BY b HAVING count(*)=2" {
}
0011.1 "SELECT 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}
0012.1 "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}
0012.2 "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)>1" {}
1011.1 "SELECT DISTINCT 1, 2, 3 WHERE 0 GROUP BY 2" {}
1012.1 "SELECT DISTINCT 1, 2, 3 WHERE 1 GROUP BY 2 HAVING count(*)=1"
{1 2 3}
1012.2 "SELECT DISTINCT 1, 2, 3 WHERE NULL GROUP BY 2 HAVING count(*)>1" {}
2011.1 "SELECT ALL 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}
2012.1 "SELECT ALL 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}
2012.2 "SELECT ALL 1, 2, 3 WHERE 'abc' GROUP BY 2 HAVING count(*)>1" {}
0111.1 "SELECT count(*), max(a) FROM t1 WHERE a='a' GROUP BY b" {1 a}
0112.1 "SELECT count(*), max(a) FROM t1
WHERE a='c' GROUP BY b HAVING count(*)=1" {1 c}
0112.2 "SELECT count(*), max(a) FROM t1
WHERE 0 GROUP BY b HAVING count(*)=2" { }
1111.1 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a<'c' GROUP BY b"
{1 a 1 b}
1112.1 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a>'a'
GROUP BY b HAVING count(*)=1" {
1 c 1 b
}
1112.2 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE 0
GROUP BY b HAVING count(*)=2" {
}
2111.1 "SELECT ALL count(*), max(a) FROM t1 WHERE b>'one' GROUP BY b"
{1 c 1 b}
2112.1 "SELECT ALL count(*), max(a) FROM t1 WHERE a!='b'
GROUP BY b HAVING count(*)=1" {
1 a 1 c
}
2112.2 "SELECT ALL count(*), max(a) FROM t1
WHERE 0 GROUP BY b HAVING count(*)=2" { }
} {
do_execsql_test e_select-0.2.$tn $select [list {*}$res]
}
#-------------------------------------------------------------------------
# The following tests focus on FROM clause (join) processing.
#
# EVIDENCE-OF: R-16074-54196 If the FROM clause is omitted from a simple
# SELECT statement, then the input data is implicitly a single row zero
................................................................................
# EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate
# query, then each expression in the result expression list is evaluated
# for each row in the dataset filtered by the WHERE clause.
#
# By other definitions in lang_select.html, a non-aggregate query is
# any simple SELECT that has no GROUP BY clause and no aggregate expressions
# in the result expression list. These tests also verify (in a way) that
# that definition is consistent:
#
do_execsql_test e_select-4.4.1 {
SELECT a, b FROM z1
} {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born}
do_execsql_test e_select-4.4.2 {
SELECT a IS NULL, b+1, * FROM z1
|