wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2007 Dec 4 |
2 | # |
||
3 | # The author disclaims copyright to this source code. In place of |
||
4 | # a legal notice, here is a blessing: |
||
5 | # |
||
6 | # May you do good and not evil. |
||
7 | # May you find forgiveness for yourself and forgive others. |
||
8 | # May you share freely, never taking more than you give. |
||
9 | # |
||
10 | #*********************************************************************** |
||
11 | # |
||
12 | # This file is to test that the issues surrounding expressions in |
||
13 | # ORDER BY clauses on compound SELECT statements raised by ticket |
||
14 | # #2822 have been dealt with. |
||
15 | # |
||
16 | # $Id: tkt2822.test,v 1.6 2008/08/20 16:35:10 drh Exp $ |
||
17 | # |
||
18 | |||
19 | set testdir [file dirname $argv0] |
||
20 | source $testdir/tester.tcl |
||
21 | |||
22 | ifcapable !compound { |
||
23 | finish_test |
||
24 | return |
||
25 | } |
||
26 | |||
27 | # The ORDER BY matching algorithm is three steps: |
||
28 | # |
||
29 | # (1) If the ORDER BY term is an integer constant i, then |
||
30 | # sort by the i-th column of the result set. |
||
31 | # |
||
32 | # (2) If the ORDER BY term is an identifier (not x.y or x.y.z |
||
33 | # but simply x) then look for a column alias with the same |
||
34 | # name. If found, then sort by that column. |
||
35 | # |
||
36 | # (3) Evaluate the term as an expression and sort by the |
||
37 | # value of the expression. |
||
38 | # |
||
39 | # For a compound SELECT the rules are modified slightly. |
||
40 | # In the third rule, the expression must exactly match one |
||
41 | # of the result columns. The sequences of three rules is |
||
42 | # attempted first on the left-most SELECT. If that doesn't |
||
43 | # work, we move to the right, one by one. |
||
44 | # |
||
45 | # Rule (3) is not in standard SQL - it is an SQLite extension, |
||
46 | # though one copied from PostgreSQL. The rule for compound |
||
47 | # queries where a search is made of SELECTs to the right |
||
48 | # if the left-most SELECT does not match is not a part of |
||
49 | # standard SQL either. This extension is unique to SQLite |
||
50 | # as far as we know. |
||
51 | # |
||
52 | # Rule (2) was added by the changes ticket #2822. Prior to |
||
53 | # that changes, SQLite did not support rule (2), making it |
||
54 | # technically in violation of standard SQL semantics. |
||
55 | # No body noticed because rule (3) has the same effect as |
||
56 | # rule (2) except in some obscure cases. |
||
57 | # |
||
58 | |||
59 | |||
60 | # Test plan: |
||
61 | # |
||
62 | # tkt2822-1.* - Simple identifier as ORDER BY expression. |
||
63 | # tkt2822-2.* - More complex ORDER BY expressions. |
||
64 | |||
65 | do_test tkt2822-0.1 { |
||
66 | execsql { |
||
67 | CREATE TABLE t1(a, b, c); |
||
68 | CREATE TABLE t2(a, b, c); |
||
69 | |||
70 | INSERT INTO t1 VALUES(1, 3, 9); |
||
71 | INSERT INTO t1 VALUES(3, 9, 27); |
||
72 | INSERT INTO t1 VALUES(5, 15, 45); |
||
73 | |||
74 | INSERT INTO t2 VALUES(2, 6, 18); |
||
75 | INSERT INTO t2 VALUES(4, 12, 36); |
||
76 | INSERT INTO t2 VALUES(6, 18, 54); |
||
77 | } |
||
78 | } {} |
||
79 | |||
80 | # Test the "ORDER BY <integer>" syntax. |
||
81 | # |
||
82 | do_test tkt2822-1.1 { |
||
83 | execsql { |
||
84 | SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY 1; |
||
85 | } |
||
86 | } {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54} |
||
87 | do_test tkt2822-1.2 { |
||
88 | execsql { |
||
89 | SELECT a, CAST (b AS TEXT), c FROM t1 |
||
90 | UNION ALL |
||
91 | SELECT a, b, c FROM t2 |
||
92 | ORDER BY 2; |
||
93 | } |
||
94 | } {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27} |
||
95 | |||
96 | # Test the "ORDER BY <identifier>" syntax. |
||
97 | # |
||
98 | do_test tkt2822-2.1 { |
||
99 | execsql { |
||
100 | SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY a; |
||
101 | } |
||
102 | } {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54} |
||
103 | |||
104 | do_test tkt2822-2.2 { |
||
105 | execsql { |
||
106 | SELECT a, CAST (b AS TEXT) AS x, c FROM t1 |
||
107 | UNION ALL |
||
108 | SELECT a, b, c FROM t2 |
||
109 | ORDER BY x; |
||
110 | } |
||
111 | } {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27} |
||
112 | do_test tkt2822-2.3 { |
||
113 | execsql { |
||
114 | SELECT t1.a, b, c FROM t1 UNION ALL SELECT t2.a, b, c FROM t2 ORDER BY a; |
||
115 | } |
||
116 | } {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54} |
||
117 | |||
118 | # Test the "ORDER BY <expression>" syntax. |
||
119 | # |
||
120 | do_test tkt2822-3.1 { |
||
121 | execsql { |
||
122 | SELECT a, CAST (b AS TEXT) AS x, c FROM t1 |
||
123 | UNION ALL |
||
124 | SELECT a, b, c FROM t2 |
||
125 | ORDER BY CAST (b AS TEXT); |
||
126 | } |
||
127 | } {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27} |
||
128 | do_test tkt2822-3.2 { |
||
129 | execsql { |
||
130 | SELECT t1.a, b, c FROM t1 UNION ALL SELECT t2.a, b, c FROM t2 ORDER BY t1.a; |
||
131 | } |
||
132 | } {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54} |
||
133 | |||
134 | # Test that if a match cannot be found in the leftmost SELECT, an |
||
135 | # attempt is made to find a match in subsequent SELECT statements. |
||
136 | # |
||
137 | do_test tkt2822-3.3 { |
||
138 | execsql { |
||
139 | SELECT a, b, c FROM t1 UNION ALL SELECT a AS x, b, c FROM t2 ORDER BY x; |
||
140 | } |
||
141 | } {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54} |
||
142 | do_test tkt2822-3.4 { |
||
143 | # But the leftmost SELECT takes precedence. |
||
144 | execsql { |
||
145 | SELECT a AS b, CAST (b AS TEXT) AS a, c FROM t1 |
||
146 | UNION ALL |
||
147 | SELECT a, b, c FROM t2 |
||
148 | ORDER BY a; |
||
149 | } |
||
150 | } {2 6 18 4 12 36 6 18 54 5 15 45 1 3 9 3 9 27} |
||
151 | do_test tkt2822-3.5 { |
||
152 | execsql { |
||
153 | SELECT a, b, c FROM t2 |
||
154 | UNION ALL |
||
155 | SELECT a AS b, CAST (b AS TEXT) AS a, c FROM t1 |
||
156 | ORDER BY a; |
||
157 | } |
||
158 | } {1 3 9 2 6 18 3 9 27 4 12 36 5 15 45 6 18 54} |
||
159 | |||
160 | # Test some error conditions (ORDER BY clauses that match no column). |
||
161 | # |
||
162 | do_test tkt2822-4.1 { |
||
163 | catchsql { |
||
164 | SELECT a, b, c FROM t1 UNION ALL SELECT a, b, c FROM t2 ORDER BY x |
||
165 | } |
||
166 | } {1 {1st ORDER BY term does not match any column in the result set}} |
||
167 | do_test tkt2822-4.2 { |
||
168 | catchsql { |
||
169 | SELECT a, CAST (b AS TEXT) AS x, c FROM t1 |
||
170 | UNION ALL |
||
171 | SELECT a, b, c FROM t2 |
||
172 | ORDER BY CAST (b AS INTEGER); |
||
173 | } |
||
174 | } {1 {1st ORDER BY term does not match any column in the result set}} |
||
175 | |||
176 | # Tests for rule (2). |
||
177 | # |
||
178 | # The "ORDER BY b" should match the column alias (rule 2), not the |
||
179 | # the t3.b value (rule 3). |
||
180 | # |
||
181 | do_test tkt2822-5.1 { |
||
182 | execsql { |
||
183 | CREATE TABLE t3(a,b); |
||
184 | INSERT INTO t3 VALUES(1,8); |
||
185 | INSERT INTO t3 VALUES(9,2); |
||
186 | |||
187 | SELECT a AS b FROM t3 ORDER BY b; |
||
188 | } |
||
189 | } {1 9} |
||
190 | do_test tkt2822-5.2 { |
||
191 | # Case does not matter. b should match B |
||
192 | execsql { |
||
193 | SELECT a AS b FROM t3 ORDER BY B; |
||
194 | } |
||
195 | } {1 9} |
||
196 | do_test tkt2822-5.3 { |
||
197 | # Quoting should not matter |
||
198 | execsql { |
||
199 | SELECT a AS 'b' FROM t3 ORDER BY "B"; |
||
200 | } |
||
201 | } {1 9} |
||
202 | do_test tkt2822-5.4 { |
||
203 | # Quoting should not matter |
||
204 | execsql { |
||
205 | SELECT a AS "b" FROM t3 ORDER BY [B]; |
||
206 | } |
||
207 | } {1 9} |
||
208 | |||
209 | # In "ORDER BY +b" the term is now an expression rather than |
||
210 | # a label. It therefore matches by rule (3) instead of rule (2). |
||
211 | # |
||
212 | do_test tkt2822-5.5 { |
||
213 | execsql { |
||
214 | SELECT a AS b FROM t3 ORDER BY +b; |
||
215 | } |
||
216 | } {9 1} |
||
217 | |||
218 | # Tests for rule 2 in compound queries |
||
219 | # |
||
220 | do_test tkt2822-6.1 { |
||
221 | execsql { |
||
222 | CREATE TABLE t6a(p,q); |
||
223 | INSERT INTO t6a VALUES(1,8); |
||
224 | INSERT INTO t6a VALUES(9,2); |
||
225 | CREATE TABLE t6b(x,y); |
||
226 | INSERT INTO t6b VALUES(1,7); |
||
227 | INSERT INTO t6b VALUES(7,2); |
||
228 | |||
229 | SELECT p, q FROM t6a UNION ALL SELECT x, y FROM t6b ORDER BY 1, 2 |
||
230 | } |
||
231 | } {1 7 1 8 7 2 9 2} |
||
232 | do_test tkt2822-6.2 { |
||
233 | execsql { |
||
234 | SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b |
||
235 | ORDER BY PX, YX |
||
236 | } |
||
237 | } {1 7 1 8 7 2 9 2} |
||
238 | do_test tkt2822-6.3 { |
||
239 | execsql { |
||
240 | SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b |
||
241 | ORDER BY XX, QX |
||
242 | } |
||
243 | } {1 7 1 8 7 2 9 2} |
||
244 | do_test tkt2822-6.4 { |
||
245 | execsql { |
||
246 | SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b |
||
247 | ORDER BY QX, XX |
||
248 | } |
||
249 | } {7 2 9 2 1 7 1 8} |
||
250 | do_test tkt2822-6.5 { |
||
251 | execsql { |
||
252 | SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b |
||
253 | ORDER BY t6b.x, QX |
||
254 | } |
||
255 | } {1 7 1 8 7 2 9 2} |
||
256 | do_test tkt2822-6.6 { |
||
257 | execsql { |
||
258 | SELECT p PX, q QX FROM t6a UNION ALL SELECT x XX, y YX FROM t6b |
||
259 | ORDER BY t6a.q, XX |
||
260 | } |
||
261 | } {7 2 9 2 1 7 1 8} |
||
262 | |||
263 | # More error message tests. This is really more of a test of the |
||
264 | # %r ordinal value formatting capablity added to sqlite3_snprintf() |
||
265 | # by ticket #2822. |
||
266 | # |
||
267 | do_test tkt2822-7.1 { |
||
268 | execsql { |
||
269 | CREATE TABLE t7(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14, |
||
270 | a15,a16,a17,a18,a19,a20,a21,a22,a23,a24,a25); |
||
271 | } |
||
272 | catchsql { |
||
273 | SELECT * FROM t7 ORDER BY 0; |
||
274 | } |
||
275 | } {1 {1st ORDER BY term out of range - should be between 1 and 25}} |
||
276 | do_test tkt2822-7.2 { |
||
277 | catchsql { |
||
278 | SELECT * FROM t7 ORDER BY 1, 0; |
||
279 | } |
||
280 | } {1 {2nd ORDER BY term out of range - should be between 1 and 25}} |
||
281 | do_test tkt2822-7.3 { |
||
282 | catchsql { |
||
283 | SELECT * FROM t7 ORDER BY 1, 2, 0; |
||
284 | } |
||
285 | } {1 {3rd ORDER BY term out of range - should be between 1 and 25}} |
||
286 | do_test tkt2822-7.4 { |
||
287 | catchsql { |
||
288 | SELECT * FROM t7 ORDER BY 1, 2, 3, 0; |
||
289 | } |
||
290 | } {1 {4th ORDER BY term out of range - should be between 1 and 25}} |
||
291 | do_test tkt2822-7.9 { |
||
292 | catchsql { |
||
293 | SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 0; |
||
294 | } |
||
295 | } {1 {9th ORDER BY term out of range - should be between 1 and 25}} |
||
296 | do_test tkt2822-7.10 { |
||
297 | catchsql { |
||
298 | SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 0; |
||
299 | } |
||
300 | } {1 {10th ORDER BY term out of range - should be between 1 and 25}} |
||
301 | do_test tkt2822-7.11 { |
||
302 | catchsql { |
||
303 | SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 0; |
||
304 | } |
||
305 | } {1 {11th ORDER BY term out of range - should be between 1 and 25}} |
||
306 | do_test tkt2822-7.12 { |
||
307 | catchsql { |
||
308 | SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 0; |
||
309 | } |
||
310 | } {1 {12th ORDER BY term out of range - should be between 1 and 25}} |
||
311 | do_test tkt2822-7.13 { |
||
312 | catchsql { |
||
313 | SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 13, 0; |
||
314 | } |
||
315 | } {1 {13th ORDER BY term out of range - should be between 1 and 25}} |
||
316 | do_test tkt2822-7.20 { |
||
317 | catchsql { |
||
318 | SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, |
||
319 | 11,12,13,14,15,16,17,18,19, 0 |
||
320 | } |
||
321 | } {1 {20th ORDER BY term out of range - should be between 1 and 25}} |
||
322 | do_test tkt2822-7.21 { |
||
323 | catchsql { |
||
324 | SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, |
||
325 | 11,12,13,14,15,16,17,18,19, 20, 0 |
||
326 | } |
||
327 | } {1 {21st ORDER BY term out of range - should be between 1 and 25}} |
||
328 | do_test tkt2822-7.22 { |
||
329 | catchsql { |
||
330 | SELECT * FROM t7 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, |
||
331 | 11,12,13,14,15,16,17,18,19, 20, 21, 0 |
||
332 | } |
||
333 | } {1 {22nd ORDER BY term out of range - should be between 1 and 25}} |
||
334 | |||
335 | |||
336 | finish_test |