← Back to branch summary

~ubuntu-branches/ubuntu/hardy/sqlite3/hardy

« back to all changes in this revision

Viewing changes to test/vtab6.test

  • Committer: Bazaar Package Importer
  • Author(s): Laszlo Boszormenyi (GCS)
  • Date: 2006-10-12 21:55:37 UTC
  • mfrom: (1.1.5 upstream)
  • Revision ID: james.westby@ubuntu.com-20061012215537-mgvrxoq8ee4nqxzh
Tags: 3.3.8-1
http://bugs.debian.org/310603
http://bugs.debian.org/375352
* New upstream version.
* Create lang_* files for documentation (closes: #310603).
* Enable column metadata functions (closes: #375352).

Show diffs side-by-side

added added

removed removed

Lines of Context:
test/vtab6.test
 
1
# 2002 May 24
 
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
# This file implements regression tests for SQLite library.
 
12
#
 
13
# This file implements tests for joins, including outer joins involving
 
14
# virtual tables. The test cases in this file are copied from the file
 
15
# join.test, and some of the comments still reflect that.
 
16
#
 
17
# $Id: vtab6.test,v 1.2 2006/06/28 18:18:10 drh Exp $
 
18
 
 
19
set testdir [file dirname $argv0]
 
20
source $testdir/tester.tcl
 
21
 
 
22
ifcapable !vtab {
 
23
  finish_test
 
24
  return
 
25
}
 
26
 
 
27
register_echo_module [sqlite3_connection_pointer db]
 
28
 
 
29
execsql {
 
30
  CREATE TABLE real_t1(a,b,c);
 
31
  CREATE TABLE real_t2(b,c,d);
 
32
  CREATE TABLE real_t3(c,d,e);
 
33
  CREATE TABLE real_t4(d,e,f);
 
34
  CREATE TABLE real_t5(a INTEGER PRIMARY KEY);
 
35
  CREATE TABLE real_t6(a INTEGER);
 
36
  CREATE TABLE real_t7 (x, y);
 
37
  CREATE TABLE real_t8 (a integer primary key, b);
 
38
  CREATE TABLE real_t9(a INTEGER PRIMARY KEY, b);
 
39
  CREATE TABLE real_t10(x INTEGER PRIMARY KEY, y);
 
40
  CREATE TABLE real_t11(p INTEGER PRIMARY KEY, q);
 
41
  CREATE TABLE real_t12(a,b);
 
42
  CREATE TABLE real_t13(b,c);
 
43
  CREATE TABLE real_t21(a,b,c);
 
44
  CREATE TABLE real_t22(p,q);
 
45
}
 
46
foreach t [list t1 t2 t3 t4 t5 t6 t7 t8 t9 t10 t11 t12 t13 t21 t22] {
 
47
  execsql "CREATE VIRTUAL TABLE $t USING echo(real_$t)"
 
48
}
 
49
 
 
50
do_test vtab6-1.1 {
 
51
  execsql {
 
52
    INSERT INTO t1 VALUES(1,2,3);
 
53
    INSERT INTO t1 VALUES(2,3,4);
 
54
    INSERT INTO t1 VALUES(3,4,5);
 
55
    SELECT * FROM t1;
 
56
  }  
 
57
} {1 2 3 2 3 4 3 4 5}
 
58
do_test vtab6-1.2 {
 
59
  execsql {
 
60
    INSERT INTO t2 VALUES(1,2,3);
 
61
    INSERT INTO t2 VALUES(2,3,4);
 
62
    INSERT INTO t2 VALUES(3,4,5);
 
63
    SELECT * FROM t2;
 
64
  }  
 
65
} {1 2 3 2 3 4 3 4 5}
 
66
 
 
67
do_test vtab6-1.3 {
 
68
  execsql2 {
 
69
    SELECT * FROM t1 NATURAL JOIN t2;
 
70
  }
 
71
} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
 
72
do_test vtab6-1.3.1 {
 
73
  execsql2 {
 
74
    SELECT * FROM t2 NATURAL JOIN t1;
 
75
  }
 
76
} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
 
77
do_test vtab6-1.3.2 {
 
78
  execsql2 {
 
79
    SELECT * FROM t2 AS x NATURAL JOIN t1;
 
80
  }
 
81
} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
 
82
do_test vtab6-1.3.3 {
 
83
  execsql2 {
 
84
    SELECT * FROM t2 NATURAL JOIN t1 AS y;
 
85
  }
 
86
} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
 
87
do_test vtab6-1.3.4 {
 
88
  execsql {
 
89
    SELECT b FROM t1 NATURAL JOIN t2;
 
90
  }
 
91
} {2 3}
 
92
do_test vtab6-1.4.1 {
 
93
  execsql2 {
 
94
    SELECT * FROM t1 INNER JOIN t2 USING(b,c);
 
95
  }
 
96
} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
 
97
do_test vtab6-1.4.2 {
 
98
  execsql2 {
 
99
    SELECT * FROM t1 AS x INNER JOIN t2 USING(b,c);
 
100
  }
 
101
} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
 
102
do_test vtab6-1.4.3 {
 
103
  execsql2 {
 
104
    SELECT * FROM t1 INNER JOIN t2 AS y USING(b,c);
 
105
  }
 
106
} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
 
107
do_test vtab6-1.4.4 {
 
108
  execsql2 {
 
109
    SELECT * FROM t1 AS x INNER JOIN t2 AS y USING(b,c);
 
110
  }
 
111
} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
 
112
do_test vtab6-1.4.5 {
 
113
  execsql {
 
114
    SELECT b FROM t1 JOIN t2 USING(b);
 
115
  }
 
116
} {2 3}
 
117
do_test vtab6-1.5 {
 
118
  execsql2 {
 
119
    SELECT * FROM t1 INNER JOIN t2 USING(b);
 
120
  }
 
121
} {a 1 b 2 c 3 c 3 d 4 a 2 b 3 c 4 c 4 d 5}
 
122
do_test vtab6-1.6 {
 
123
  execsql2 {
 
124
    SELECT * FROM t1 INNER JOIN t2 USING(c);
 
125
  }
 
126
} {a 1 b 2 c 3 b 2 d 4 a 2 b 3 c 4 b 3 d 5}
 
127
do_test vtab6-1.7 {
 
128
  execsql2 {
 
129
    SELECT * FROM t1 INNER JOIN t2 USING(c,b);
 
130
  }
 
131
} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
 
132
 
 
133
do_test vtab6-1.8 {
 
134
  execsql {
 
135
    SELECT * FROM t1 NATURAL CROSS JOIN t2;
 
136
  }
 
137
} {1 2 3 4 2 3 4 5}
 
138
do_test vtab6-1.9 {
 
139
  execsql {
 
140
    SELECT * FROM t1 CROSS JOIN t2 USING(b,c);
 
141
  }
 
142
} {1 2 3 4 2 3 4 5}
 
143
do_test vtab6-1.10 {
 
144
  execsql {
 
145
    SELECT * FROM t1 NATURAL INNER JOIN t2;
 
146
  }
 
147
} {1 2 3 4 2 3 4 5}
 
148
do_test vtab6-1.11 {
 
149
  execsql {
 
150
    SELECT * FROM t1 INNER JOIN t2 USING(b,c);
 
151
  }
 
152
} {1 2 3 4 2 3 4 5}
 
153
do_test vtab6-1.12 {
 
154
  execsql {
 
155
    SELECT * FROM t1 natural inner join t2;
 
156
  }
 
157
} {1 2 3 4 2 3 4 5}
 
158
 
 
159
ifcapable subquery {
 
160
breakpoint
 
161
  do_test vtab6-1.13 {
 
162
    execsql2 {
 
163
      SELECT * FROM t1 NATURAL JOIN 
 
164
        (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3
 
165
    }
 
166
  } {a 1 b 2 c 3 d 4 e 5}
 
167
  do_test vtab6-1.14 {
 
168
    execsql2 {
 
169
      SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx'
 
170
          NATURAL JOIN t1
 
171
    }
 
172
  } {c 3 d 4 e 5 a 1 b 2}
 
173
}
 
174
 
 
175
do_test vtab6-1.15 {
 
176
  execsql {
 
177
    INSERT INTO t3 VALUES(2,3,4);
 
178
    INSERT INTO t3 VALUES(3,4,5);
 
179
    INSERT INTO t3 VALUES(4,5,6);
 
180
    SELECT * FROM t3;
 
181
  }  
 
182
} {2 3 4 3 4 5 4 5 6}
 
183
do_test vtab6-1.16 {
 
184
  execsql {
 
185
    SELECT * FROM t1 natural join t2 natural join t3;
 
186
  }
 
187
} {1 2 3 4 5 2 3 4 5 6}
 
188
do_test vtab6-1.17 {
 
189
  execsql2 {
 
190
    SELECT * FROM t1 natural join t2 natural join t3;
 
191
  }
 
192
} {a 1 b 2 c 3 d 4 e 5 a 2 b 3 c 4 d 5 e 6}
 
193
do_test vtab6-1.18 {
 
194
  execsql {
 
195
    INSERT INTO t4 VALUES(2,3,4);
 
196
    INSERT INTO t4 VALUES(3,4,5);
 
197
    INSERT INTO t4 VALUES(4,5,6);
 
198
    SELECT * FROM t4;
 
199
  }  
 
200
} {2 3 4 3 4 5 4 5 6}
 
201
do_test vtab6-1.19.1 {
 
202
  execsql {
 
203
    SELECT * FROM t1 natural join t2 natural join t4;
 
204
  }
 
205
} {1 2 3 4 5 6}
 
206
do_test vtab6-1.19.2 {
 
207
  execsql2 {
 
208
    SELECT * FROM t1 natural join t2 natural join t4;
 
209
  }
 
210
} {a 1 b 2 c 3 d 4 e 5 f 6}
 
211
do_test vtab6-1.20 {
 
212
  execsql {
 
213
    SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1
 
214
  }
 
215
} {1 2 3 4 5}
 
216
 
 
217
do_test vtab6-2.1 {
 
218
  execsql {
 
219
    SELECT * FROM t1 NATURAL LEFT JOIN t2;
 
220
  }
 
221
} {1 2 3 4 2 3 4 5 3 4 5 {}}
 
222
do_test vtab6-2.2 {
 
223
  execsql {
 
224
    SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
 
225
  }
 
226
} {1 2 3 {} 2 3 4 1 3 4 5 2}
 
227
do_test vtab6-2.3 {
 
228
  catchsql {
 
229
    SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
 
230
  }
 
231
} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
 
232
do_test vtab6-2.4 {
 
233
  execsql {
 
234
    SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
 
235
  }
 
236
} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
 
237
do_test vtab6-2.5 {
 
238
  execsql {
 
239
    SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1
 
240
  }
 
241
} {2 3 4 {} {} {} 3 4 5 1 2 3}
 
242
do_test vtab6-2.6 {
 
243
  execsql {
 
244
    SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1
 
245
  }
 
246
} {1 2 3 {} {} {} 2 3 4 {} {} {}}
 
247
 
 
248
do_test vtab6-3.1 {
 
249
  catchsql {
 
250
    SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b;
 
251
  }
 
252
} {1 {a NATURAL join may not have an ON or USING clause}}
 
253
do_test vtab6-3.2 {
 
254
  catchsql {
 
255
    SELECT * FROM t1 NATURAL JOIN t2 USING(b);
 
256
  }
 
257
} {1 {a NATURAL join may not have an ON or USING clause}}
 
258
do_test vtab6-3.3 {
 
259
  catchsql {
 
260
    SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b);
 
261
  }
 
262
} {1 {cannot have both ON and USING clauses in the same join}}
 
263
do_test vtab6-3.4 {
 
264
  catchsql {
 
265
    SELECT * FROM t1 JOIN t2 USING(a);
 
266
  }
 
267
} {1 {cannot join using column a - column not present in both tables}}
 
268
do_test vtab6-3.5 {
 
269
  catchsql {
 
270
    SELECT * FROM t1 USING(a);
 
271
  }
 
272
} {0 {1 2 3 2 3 4 3 4 5}}
 
273
do_test vtab6-3.6 {
 
274
  catchsql {
 
275
    SELECT * FROM t1 JOIN t2 ON t3.a=t2.b;
 
276
  }
 
277
} {1 {no such column: t3.a}}
 
278
do_test vtab6-3.7 {
 
279
  catchsql {
 
280
    SELECT * FROM t1 INNER OUTER JOIN t2;
 
281
  }
 
282
} {1 {unknown or unsupported join type: INNER OUTER}}
 
283
do_test vtab6-3.7 {
 
284
  catchsql {
 
285
    SELECT * FROM t1 LEFT BOGUS JOIN t2;
 
286
  }
 
287
} {1 {unknown or unsupported join type: LEFT BOGUS}}
 
288
 
 
289
do_test vtab6-4.1 {
 
290
  execsql {
 
291
    BEGIN;
 
292
    INSERT INTO t6 VALUES(NULL);
 
293
    INSERT INTO t6 VALUES(NULL);
 
294
    INSERT INTO t6 SELECT * FROM t6;
 
295
    INSERT INTO t6 SELECT * FROM t6;
 
296
    INSERT INTO t6 SELECT * FROM t6;
 
297
    INSERT INTO t6 SELECT * FROM t6;
 
298
    INSERT INTO t6 SELECT * FROM t6;
 
299
    INSERT INTO t6 SELECT * FROM t6;
 
300
    COMMIT;
 
301
  }
 
302
  execsql {
 
303
    SELECT * FROM t6 NATURAL JOIN t5;
 
304
  }
 
305
} {}
 
306
do_test vtab6-4.2 {
 
307
  execsql {
 
308
    SELECT * FROM t6, t5 WHERE t6.a<t5.a;
 
309
  }
 
310
} {}
 
311
do_test vtab6-4.3 {
 
312
  execsql {
 
313
    SELECT * FROM t6, t5 WHERE t6.a>t5.a;
 
314
  }
 
315
} {}
 
316
do_test vtab6-4.4 {
 
317
  execsql {
 
318
    UPDATE t6 SET a='xyz';
 
319
    SELECT * FROM t6 NATURAL JOIN t5;
 
320
  }
 
321
} {}
 
322
do_test vtab6-4.6 {
 
323
  execsql {
 
324
    SELECT * FROM t6, t5 WHERE t6.a<t5.a;
 
325
  }
 
326
} {}
 
327
do_test vtab6-4.7 {
 
328
  execsql {
 
329
    SELECT * FROM t6, t5 WHERE t6.a>t5.a;
 
330
  }
 
331
} {}
 
332
do_test vtab6-4.8 {
 
333
  execsql {
 
334
    UPDATE t6 SET a=1;
 
335
    SELECT * FROM t6 NATURAL JOIN t5;
 
336
  }
 
337
} {}
 
338
do_test vtab6-4.9 {
 
339
  execsql {
 
340
    SELECT * FROM t6, t5 WHERE t6.a<t5.a;
 
341
  }
 
342
} {}
 
343
do_test vtab6-4.10 {
 
344
  execsql {
 
345
    SELECT * FROM t6, t5 WHERE t6.a>t5.a;
 
346
  }
 
347
} {}
 
348
 
 
349
# A test for ticket #247.
 
350
#
 
351
do_test vtab6-7.1 {
 
352
  execsql {
 
353
    INSERT INTO t7 VALUES ("pa1", 1);
 
354
    INSERT INTO t7 VALUES ("pa2", NULL);
 
355
    INSERT INTO t7 VALUES ("pa3", NULL);
 
356
    INSERT INTO t7 VALUES ("pa4", 2);
 
357
    INSERT INTO t7 VALUES ("pa30", 131);
 
358
    INSERT INTO t7 VALUES ("pa31", 130);
 
359
    INSERT INTO t7 VALUES ("pa28", NULL);
 
360
 
 
361
    INSERT INTO t8 VALUES (1, "pa1");
 
362
    INSERT INTO t8 VALUES (2, "pa4");
 
363
    INSERT INTO t8 VALUES (3, NULL);
 
364
    INSERT INTO t8 VALUES (4, NULL);
 
365
    INSERT INTO t8 VALUES (130, "pa31");
 
366
    INSERT INTO t8 VALUES (131, "pa30");
 
367
 
 
368
    SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a;
 
369
  }
 
370
} {1 999 999 2 131 130 999}
 
371
 
 
372
# Make sure a left join where the right table is really a view that
 
373
# is itself a join works right.  Ticket #306.
 
374
#
 
375
ifcapable view {
 
376
do_test vtab6-8.1 {
 
377
  execsql {
 
378
    BEGIN;
 
379
    INSERT INTO t9 VALUES(1,11);
 
380
    INSERT INTO t9 VALUES(2,22);
 
381
    INSERT INTO t10 VALUES(1,2);
 
382
    INSERT INTO t10 VALUES(3,3);    
 
383
    INSERT INTO t11 VALUES(2,111);
 
384
    INSERT INTO t11 VALUES(3,333);    
 
385
    CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p;
 
386
    COMMIT;
 
387
    SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x );
 
388
  }
 
389
} {1 11 1 111 2 22 {} {}}
 
390
ifcapable subquery {
 
391
  do_test vtab6-8.2 {
 
392
    execsql {
 
393
      SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p)
 
394
           ON( a=x);
 
395
    }
 
396
  } {1 11 1 111 2 22 {} {}}
 
397
}
 
398
do_test vtab6-8.3 {
 
399
  execsql {
 
400
    SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x );
 
401
  }
 
402
} {1 111 1 11 3 333 {} {}}
 
403
} ;# ifcapable view
 
404
 
 
405
# Ticket #350 describes a scenario where LEFT OUTER JOIN does not
 
406
# function correctly if the right table in the join is really
 
407
# subquery.
 
408
#
 
409
# To test the problem, we generate the same LEFT OUTER JOIN in two
 
410
# separate selects but with on using a subquery and the other calling
 
411
# the table directly.  Then connect the two SELECTs using an EXCEPT.
 
412
# Both queries should generate the same results so the answer should
 
413
# be an empty set.
 
414
#
 
415
ifcapable compound {
 
416
do_test vtab6-9.1 {
 
417
  execsql {
 
418
    BEGIN;
 
419
    INSERT INTO t12 VALUES(1,11);
 
420
    INSERT INTO t12 VALUES(2,22);
 
421
    INSERT INTO t13 VALUES(22,222);
 
422
    COMMIT;
 
423
  }
 
424
} {}
 
425
 
 
426
ifcapable subquery {
 
427
  do_test vtab6-9.1.1 {
 
428
    execsql {
 
429
      SELECT * FROM t12 NATURAL LEFT JOIN t13
 
430
      EXCEPT
 
431
      SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0);
 
432
    }
 
433
  } {}
 
434
}
 
435
ifcapable view {
 
436
  do_test vtab6-9.2 {
 
437
    execsql {
 
438
      CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0;
 
439
      SELECT * FROM t12 NATURAL LEFT JOIN t13
 
440
        EXCEPT
 
441
        SELECT * FROM t12 NATURAL LEFT JOIN v13;
 
442
    }
 
443
  } {}
 
444
} ;# ifcapable view
 
445
} ;# ifcapable compound
 
446
 
 
447
ifcapable subquery {
 
448
do_test vtab6-10.1 {
 
449
  execsql {
 
450
    CREATE INDEX i22 ON real_t22(q);
 
451
    SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q=
 
452
       (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1);
 
453
  }  
 
454
} {}
 
455
} ;# ifcapable subquery
 
456
 
 
457
finish_test