Small performance optimization in sqlite3VdbeRecordCompareWithSkip() for
[sqlite.git] / test / unionvtab.test
blobab0fb7474aeb9e0888f6e01083673f4fb8d53ccd
1 # 2017-07-15
3 # The author disclaims copyright to this source code.  In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.  The
12 # focus of this file is percentile.c extension
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix unionvtab
19 ifcapable !vtab {
20   finish_test
21   return
24 load_static_extension db unionvtab
26 #-------------------------------------------------------------------------
27 # Warm body tests.
29 forcedelete test.db2
30 do_execsql_test 1.0 {
31   ATTACH 'test.db2' AS aux;
33   CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
34   CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);
35   CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b TEXT);
38   INSERT INTO t1 VALUES(1, 'one'), (2, 'two'), (3, 'three');
39   INSERT INTO t2 VALUES(10, 'ten'), (11, 'eleven'), (12, 'twelve');
40   INSERT INTO t3 VALUES(20, 'twenty'), (21, 'twenty-one'), (22, 'twenty-two');
43 do_execsql_test 1.1 {
44   CREATE VIRTUAL TABLE temp.uuu USING unionvtab(
45     "VALUES(NULL, 't1', 1, 9),  ('main', 't2', 10, 19), ('aux', 't3', 20, 29)"
46   );
47   SELECT * FROM uuu;
48 } {
49   1 one 2 two 3 three
50   10 ten 11 eleven 12 twelve
51   20 twenty 21 twenty-one 22 twenty-two
54 do_execsql_test 1.2 {
55   PRAGMA table_info(uuu);
56 } {
57   0 a INTEGER 0 {} 0 
58   1 b TEXT 0 {} 0
61 do_execsql_test 1.3 {
62   SELECT * FROM uuu WHERE rowid = 3;
63   SELECT * FROM uuu WHERE rowid = 11;
64 } {3 three 11 eleven}
66 do_execsql_test 1.4 {
67   SELECT * FROM uuu WHERE rowid IN (12, 10, 2);
68 } {2 two 10 ten 12 twelve}
70 do_execsql_test 1.5 {
71   SELECT * FROM uuu WHERE rowid BETWEEN 3 AND 11;
72 } {3 three 10 ten 11 eleven}
74 do_execsql_test 1.6 {
75   SELECT * FROM uuu WHERE rowid BETWEEN 11 AND 15;
76 } {11 eleven 12 twelve}
78 do_execsql_test 1.7 {
79   SELECT * FROM uuu WHERE rowid BETWEEN -46 AND 1500;
80 } {
81   1 one 2 two 3 three
82   10 ten 11 eleven 12 twelve
83   20 twenty 21 twenty-one 22 twenty-two
86 do_execsql_test 1.8 {
87   CREATE TABLE src(db, tbl, min, max);
88   INSERT INTO src VALUES(NULL, 't1', 1, 9);
89   INSERT INTO src VALUES('main', 't2', 10, 19);
90   INSERT INTO src VALUES('aux', 't3', 20, 29);
91   CREATE VIRTUAL TABLE temp.opp USING unionvtab(src);
92   SELECT * FROM opp;
93 } {
94   1 one 2 two 3 three
95   10 ten 11 eleven 12 twelve
96   20 twenty 21 twenty-one 22 twenty-two
99 do_execsql_test 1.9 {
100   CREATE VIRTUAL TABLE temp.qll USING unionvtab(
101     'SELECT * FROM src WHERE db!=''xyz'''
102   );
103   SELECT * FROM qll WHERE rowid BETWEEN 10 AND 21;
104 } {
105   10 ten 11 eleven 12 twelve
106   20 twenty 21 twenty-one
109 #-------------------------------------------------------------------------
110 # Error conditions.
112 #   2.1.*: Attempt to create a unionvtab table outside of the TEMP schema.
113 #   2.2.*: Tables that do not exist.
114 #   2.3.*: Non rowid tables.
115 #   2.4.*: Tables with mismatched schemas.
116 #   2.5.*: A unionvtab table with zero source tables.
118 do_catchsql_test 2.1.1 {
119   CREATE VIRTUAL TABLE u1 USING unionvtab("VALUES(NULL, 't1', 1, 100)");
120 } {1 {unionvtab tables must be created in TEMP schema}}
121 do_catchsql_test 2.1.2 {
122   CREATE VIRTUAL TABLE main.u1 USING unionvtab("VALUES('', 't1', 1, 100)");
123 } {1 {unionvtab tables must be created in TEMP schema}}
124 do_catchsql_test 2.1.3 {
125   CREATE VIRTUAL TABLE aux.u1 USING unionvtab("VALUES('', 't1', 1, 100)");
126 } {1 {unionvtab tables must be created in TEMP schema}}
128 do_catchsql_test 2.2.1 {
129   CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 't555', 1, 100)");
130 } {1 {no such rowid table: t555}}
131 do_catchsql_test 2.2.2 {
132   CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('aux', 't555', 1, 100)");
133 } {1 {no such rowid table: aux.t555}}
134 do_catchsql_test 2.2.3 {
135   CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('xua', 't555', 1, 100)");
136 } {1 {no such rowid table: xua.t555}}
138 do_execsql_test 2.3.0 {
139   CREATE TABLE wr1(a, b, c PRIMARY KEY) WITHOUT ROWID;
140   CREATE VIEW v1 AS SELECT * FROM t1;
141   CREATE VIEW v2 AS SELECT _rowid_, * FROM t1;
143   CREATE TABLE wr2(a, _rowid_ INTEGER, c PRIMARY KEY) WITHOUT ROWID;
144   CREATE TABLE wr3(a, b, _rowid_ PRIMARY KEY) WITHOUT ROWID;
146 do_catchsql_test 2.3.1 {
147   CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('main', 'wr1', 1, 2)");
148 } {1 {no such rowid table: main.wr1}}
149 do_catchsql_test 2.3.2 {
150   CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'v1', 1, 2)");
151 } {1 {no such rowid table: v1}}
152 do_catchsql_test 2.3.3 {
153   CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'v2', 1, 2)");
154 } {1 {no such rowid table: v2}}
155 do_catchsql_test 2.3.4 {
156   CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'wr2', 1, 2)");
157 } {1 {no such rowid table: wr2}}
158 do_catchsql_test 2.3.5 {
159   CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'wr3', 1, 2)");
160 } {1 {no such rowid table: wr3}}
162 do_execsql_test 2.4.0 {
163   CREATE TABLE x1(a BLOB, b);
164   CREATE TABLE x2(a BLOB, b);
165   CREATE TEMP TABLE x3(a BLOB, b);
167   CREATE TABLE aux.y1(one, two, three INTEGER PRIMARY KEY);
168   CREATE TEMP TABLE y2(one, two, three INTEGER PRIMARY KEY);
169   CREATE TABLE y3(one, two, three INTEGER PRIMARY KEY);
172 foreach {tn dbs res} {
173   1 {x1 x2 x3} {0 {}}
174   2 {y1 y2 y3} {0 {}}
175   3 {x1 y2 y3} {1 {source table schema mismatch}}
176   4 {x1 y2 x3} {1 {source table schema mismatch}}
177   5 {x1 x2 y3} {1 {source table schema mismatch}}
178 } {
179   set L [list]
180   set iMin 0
181   foreach e $dbs {
182     set E [split $e .]
183     if {[llength $E]>1} {
184       lappend L "('[lindex $E 0]', '[lindex $E 1]', $iMin, $iMin)"
185     } else {
186       lappend L "(NULL, '$e', $iMin, $iMin)"
187     }
188     incr iMin
189   }
191   set sql "CREATE VIRTUAL TABLE temp.a1 USING unionvtab(\"VALUES [join $L ,]\")"
192   do_catchsql_test 2.4.$tn "
193     DROP TABLE IF EXISTS temp.a1;
194     CREATE VIRTUAL TABLE temp.a1 USING unionvtab(\"VALUES [join $L ,]\");
195   " $res
198 do_catchsql_test 2.5 {
199   CREATE VIRTUAL TABLE temp.b1 USING unionvtab(
200     [SELECT 'main', 'b1', 0, 100 WHERE 0]
201   )
202 } {1 {no source tables configured}}
204 foreach {tn sql} {
205   1 { VALUES('main', 't1', 10, 20), ('main', 't2', 30, 29) }
206   2 { VALUES('main', 't1', 10, 20), ('main', 't2', 15, 30) }
207 } {
208   do_catchsql_test 2.6.$tn "
209     CREATE VIRTUAL TABLE temp.a1 USING unionvtab(`$sql`)
210   " {1 {rowid range mismatch error}}
213 do_catchsql_test 2.7.1 {
214   CREATE VIRTUAL TABLE temp.b1 USING unionvtab(1, 2, 3, 4)
215 } {1 {wrong number of arguments for unionvtab}}
217 #-------------------------------------------------------------------------
219 reset_db
220 load_static_extension db unionvtab
221 do_execsql_test 3.0 {
222   CREATE TABLE tbl1(a INTEGER PRIMARY KEY, b);
223   CREATE TABLE tbl2(a INTEGER PRIMARY KEY, b);
224   CREATE TABLE tbl3(a INTEGER PRIMARY KEY, b);
226   WITH ss(ii) AS ( SELECT 1 UNION ALL SELECT ii+1 FROM ss WHERE ii<100 )
227   INSERT INTO tbl1 SELECT ii, '1.' || ii FROM ss;
229   WITH ss(ii) AS ( SELECT 1 UNION ALL SELECT ii+1 FROM ss WHERE ii<100 )
230   INSERT INTO tbl2 SELECT ii, '2.' || ii FROM ss;
232   WITH ss(ii) AS ( SELECT 1 UNION ALL SELECT ii+1 FROM ss WHERE ii<100 )
233   INSERT INTO tbl3 SELECT ii, '3.' || ii FROM ss;
235   CREATE VIRTUAL TABLE temp.uu USING unionvtab(
236     "VALUES(NULL,'tbl2', 26, 74), (NULL,'tbl3', 75, 100), (NULL,'tbl1', 1, 25)"
237   );
240 do_execsql_test 3.1 {
241   SELECT * FROM uu WHERE rowid = 10;
242 } {10 {1.10}}
243 do_execsql_test 3.2 {
244   SELECT * FROM uu WHERE rowid = 25;
245 } {25 {1.25}}
247 do_execsql_test 3.3 { SELECT count(*) FROM uu WHERE rowid <= 24 } {24}
249 # The following queries get the "wrong" answers. This is because the
250 # module assumes that each source table contains rowids from only within
251 # the range specified. For example, (rowid <= 25) matches 100 rows. This
252 # is because the module implements (rowid <= 25) as a full table scan
253 # of tbl1 only.
254 do_execsql_test 3.4.1 { SELECT count(*) FROM uu WHERE rowid <= 25 } {100}
255 do_execsql_test 3.4.2 { SELECT count(*) FROM uu WHERE rowid <= 26 } {126}
256 do_execsql_test 3.4.3 { SELECT count(*) FROM uu WHERE rowid <= 73 } {173}
257 do_execsql_test 3.4.4 { SELECT count(*) FROM uu WHERE rowid <= 74 } {200}
258 do_execsql_test 3.4.5 { SELECT count(*) FROM uu WHERE rowid <= 75 } {275}
259 do_execsql_test 3.4.6 { SELECT count(*) FROM uu WHERE rowid <= 99 } {299}
260 do_execsql_test 3.4.7 { SELECT count(*) FROM uu WHERE rowid <= 100 } {300}
261 do_execsql_test 3.4.8 { SELECT count(*) FROM uu WHERE rowid <= 101 } {300}
263 do_execsql_test 3.5.1 { SELECT count(*) FROM uu WHERE rowid < 25 } {24}
264 do_execsql_test 3.5.2 { SELECT count(*) FROM uu WHERE rowid < 26 } {100}
265 do_execsql_test 3.5.3 { SELECT count(*) FROM uu WHERE rowid < 27 } {126}
266 do_execsql_test 3.5.4 { SELECT count(*) FROM uu WHERE rowid < 73 } {172}
267 do_execsql_test 3.5.5 { SELECT count(*) FROM uu WHERE rowid < 74 } {173}
268 do_execsql_test 3.5.6 { SELECT count(*) FROM uu WHERE rowid < 75 } {200}
269 do_execsql_test 3.5.7 { SELECT count(*) FROM uu WHERE rowid < 76 } {275}
270 do_execsql_test 3.5.8 { SELECT count(*) FROM uu WHERE rowid < 99 } {298}
271 do_execsql_test 3.5.9 { SELECT count(*) FROM uu WHERE rowid < 100 } {299}
272 do_execsql_test 3.5.10 { SELECT count(*) FROM uu WHERE rowid < 101 } {300}
274 do_execsql_test 3.6.1 { SELECT count(*) FROM uu WHERE rowid > 24 } {276}
275 do_execsql_test 3.6.1 { SELECT count(*) FROM uu WHERE rowid > 25 } {200}
276 do_execsql_test 3.6.2 { SELECT count(*) FROM uu WHERE rowid > 26 } {174}
277 do_execsql_test 3.6.3 { SELECT count(*) FROM uu WHERE rowid > 27 } {173}
278 do_execsql_test 3.6.4 { SELECT count(*) FROM uu WHERE rowid > 73 } {127}
279 do_execsql_test 3.6.5 { SELECT count(*) FROM uu WHERE rowid > 74 } {100}
280 do_execsql_test 3.6.6 { SELECT count(*) FROM uu WHERE rowid > 75 } {25}
281 do_execsql_test 3.6.7 { SELECT count(*) FROM uu WHERE rowid > 76 } {24}
282 do_execsql_test 3.6.8 { SELECT count(*) FROM uu WHERE rowid > 99 } {1}
283 do_execsql_test 3.6.9 { SELECT count(*) FROM uu WHERE rowid > 100 } {0}
284 do_execsql_test 3.6.10 { SELECT count(*) FROM uu WHERE rowid > 101 } {0}
286 do_execsql_test 3.7.1 { SELECT count(*) FROM uu WHERE rowid >= 24 } {277}
287 do_execsql_test 3.7.1 { SELECT count(*) FROM uu WHERE rowid >= 25 } {276}
288 do_execsql_test 3.7.2 { SELECT count(*) FROM uu WHERE rowid >= 26 } {200}
289 do_execsql_test 3.7.3 { SELECT count(*) FROM uu WHERE rowid >= 27 } {174}
290 do_execsql_test 3.7.4 { SELECT count(*) FROM uu WHERE rowid >= 73 } {128}
291 do_execsql_test 3.7.5 { SELECT count(*) FROM uu WHERE rowid >= 74 } {127}
292 do_execsql_test 3.7.6 { SELECT count(*) FROM uu WHERE rowid >= 75 } {100}
293 do_execsql_test 3.7.7 { SELECT count(*) FROM uu WHERE rowid >= 76 } {25}
294 do_execsql_test 3.7.8 { SELECT count(*) FROM uu WHERE rowid >= 99 } {2}
295 do_execsql_test 3.7.9 { SELECT count(*) FROM uu WHERE rowid >= 100 } {1}
296 do_execsql_test 3.7.10 { SELECT count(*) FROM uu WHERE rowid >= 101 } {0}
298 set L [expr  9223372036854775807]
299 set S [expr -9223372036854775808]
301 do_execsql_test 3.8.1 { SELECT count(*) FROM uu WHERE rowid >= $S } {300}
302 do_execsql_test 3.8.2 { SELECT count(*) FROM uu WHERE rowid >  $S } {300}
303 do_execsql_test 3.8.3 { SELECT count(*) FROM uu WHERE rowid <= $S } {0}
304 do_execsql_test 3.8.4 { SELECT count(*) FROM uu WHERE rowid <  $S } {0}
306 do_execsql_test 3.9.1 { SELECT count(*) FROM uu WHERE rowid >= $L } {0}
307 do_execsql_test 3.9.2 { SELECT count(*) FROM uu WHERE rowid >  $L } {0}
308 do_execsql_test 3.9.3 { SELECT count(*) FROM uu WHERE rowid <= $L } {300}
309 do_execsql_test 3.9.4 { SELECT count(*) FROM uu WHERE rowid <  $L } {300}
311 do_execsql_test 3.10.1 { SELECT count(*) FROM uu WHERE a < 25 } {24}
312 do_execsql_test 3.10.2 { SELECT count(*) FROM uu WHERE a < 26 } {100}
313 do_execsql_test 3.10.3 { SELECT count(*) FROM uu WHERE a < 27 } {126}
314 do_execsql_test 3.10.4 { SELECT count(*) FROM uu WHERE a < 73 } {172}
315 do_execsql_test 3.10.5 { SELECT count(*) FROM uu WHERE a < 74 } {173}
316 do_execsql_test 3.10.6 { SELECT count(*) FROM uu WHERE a < 75 } {200}
317 do_execsql_test 3.10.7 { SELECT count(*) FROM uu WHERE a < 76 } {275}
318 do_execsql_test 3.10.8 { SELECT count(*) FROM uu WHERE a < 99 } {298}
319 do_execsql_test 3.10.9 { SELECT count(*) FROM uu WHERE a < 100 } {299}
320 do_execsql_test 3.10.10 { SELECT count(*) FROM uu WHERE a < 101 } {300}
323 #-------------------------------------------------------------------------
325 do_execsql_test 4.0 {
326   CREATE TABLE s1(k INTEGER PRIMARY KEY, v);
327   INSERT INTO s1 VALUES($S, 'one');
328   INSERT INTO s1 VALUES($S+1, 'two');
329   INSERT INTO s1 VALUES($S+2, 'three');
331   CREATE TABLE l1(k INTEGER PRIMARY KEY, v);
332   INSERT INTO l1 VALUES($L, 'six');
333   INSERT INTO l1 VALUES($L-1, 'five');
334   INSERT INTO l1 VALUES($L-2, 'four');
336   CREATE VIRTUAL TABLE temp.sl USING unionvtab(
337     "SELECT NULL, 'l1', 0, 9223372036854775807
338      UNION ALL
339      SELECT NULL, 's1', -9223372036854775808, -1"
340   );
343 do_execsql_test 4.1 {
344   SELECT * FROM sl;
345 } {
346   -9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
347    9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
350 foreach {k v} {
351   -9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
352    9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
353 } {
354   do_execsql_test 4.2.$v { SELECT * FROM sl WHERE rowid=$k } [list $k $v]
357 do_execsql_test 4.3.1 {
358   SELECT * FROM sl WHERE rowid>-9223372036854775808
359 } {
360   -9223372036854775807 two -9223372036854775806 three
361    9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
363 do_execsql_test 4.3.2 {
364   SELECT * FROM sl WHERE rowid>=-9223372036854775808
365 } {
366   -9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
367    9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
369 do_execsql_test 4.3.3 {
370   SELECT * FROM sl WHERE rowid<=-9223372036854775808
371 } {
372   -9223372036854775808 one
374 do_execsql_test 4.3.4 {
375   SELECT * FROM sl WHERE rowid<-9223372036854775808
376 } { }
378 do_execsql_test 4.4.1 {
379   SELECT * FROM sl WHERE rowid<9223372036854775807
380 } {
381   -9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
382    9223372036854775805 four 9223372036854775806 five
384 do_execsql_test 4.4.2 {
385   SELECT * FROM sl WHERE rowid<=9223372036854775807
386 } {
387   -9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
388    9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
390 do_execsql_test 4.4.3 {
391   SELECT * FROM sl WHERE rowid>=9223372036854775807
392 } {
393   9223372036854775807 six
395 do_execsql_test 4.4.4 {
396   SELECT * FROM sl WHERE rowid>9223372036854775807
397 } { }
399 #-------------------------------------------------------------------------
400 # More than 8 source tables.
402 do_execsql_test 5.0 {
403   CREATE TABLE c0(one, two INTEGER PRIMARY KEY);
404   CREATE TABLE c1(one, two INTEGER PRIMARY KEY);
405   CREATE TABLE c2(one, two INTEGER PRIMARY KEY);
406   CREATE TABLE c3(one, two INTEGER PRIMARY KEY);
407   CREATE TABLE c4(one, two INTEGER PRIMARY KEY);
408   CREATE TABLE c5(one, two INTEGER PRIMARY KEY);
409   CREATE TABLE c6(one, two INTEGER PRIMARY KEY);
410   CREATE TABLE c7(one, two INTEGER PRIMARY KEY);
411   CREATE TABLE c8(one, two INTEGER PRIMARY KEY);
412   CREATE TABLE c9(one, two INTEGER PRIMARY KEY);
414   INSERT INTO c0 VALUES('zero', 0);
415   INSERT INTO c1 VALUES('one', 1);
416   INSERT INTO c2 VALUES('two', 2);
417   INSERT INTO c3 VALUES('three', 3);
418   INSERT INTO c4 VALUES('four', 4);
419   INSERT INTO c5 VALUES('five', 5);
420   INSERT INTO c6 VALUES('six', 6);
421   INSERT INTO c7 VALUES('seven', 7);
422   INSERT INTO c8 VALUES('eight', 8);
423   INSERT INTO c9 VALUES('nine', 9);
425   CREATE VIRTUAL TABLE temp.cc USING unionvtab([
426     SELECT 'main', 'c9', 9, 9 UNION ALL
427     SELECT 'main', 'c8', 8, 8 UNION ALL
428     SELECT 'main', 'c7', 7, 7 UNION ALL
429     SELECT 'main', 'c6', 6, 6 UNION ALL
430     SELECT 'main', 'c5', 5, 5 UNION ALL
431     SELECT 'main', 'c4', 4, 4 UNION ALL
432     SELECT 'main', 'c3', 3, 3 UNION ALL
433     SELECT 'main', 'c2', 2, 2 UNION ALL
434     SELECT 'main', 'c1', 1, 1 UNION ALL
435     SELECT 'main', 'c0', 0, 0
436   ]);
438   SELECT sum(two) FROM cc;
439 } {45}
441 do_execsql_test 5.1 {
442   SELECT one FROM cc WHERE one>='seven'
443 } {zero two three six seven}
445 do_execsql_test 5.2 {
446   SELECT y.one FROM cc AS x, cc AS y WHERE x.one=y.one AND x.rowid>5
447 } {six seven eight nine}
449 do_execsql_test 5.3 {
450   SELECT cc.one FROM c4, cc WHERE cc.rowid>c4.rowid
451 } {five six seven eight nine}
453 do_execsql_test 5.4 {
454   SELECT * FROM cc WHERE two LIKE '6'
455 } {six 6}
457 finish_test