/ Check-in [c93284cd]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add further tests to unionvtab.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: c93284cd65b6471bffc38696e14dc24a2170698faf566cf91b727ba272f88c25
User & Date: dan 2017-07-19 11:52:25
Context
2017-07-19
18:01
Remove some 'breakpoint' commands in test files. check-in: d14fc621 user: mistachkin tags: trunk
17:12
Create "pure" versions of the date/time functions that omit the 'now' feature and are therefore deterministic and usable in an index. Closed-Leaf check-in: 55791928 user: drh tags: pure-date-functions
11:52
Add further tests to unionvtab.test. check-in: c93284cd user: dan tags: trunk
2017-07-18
21:19
Fix a crash in unionvtab caused by a misconfiguration. Add other test cases for the same. check-in: 255d6b99 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to test/unionvtab.test.

86
87
88
89
90
91
92










93
94
95
96
97
98
99
...
120
121
122
123
124
125
126



127
128
129
130
131
132
133
134
135
136






137
138
139
140
141
142
143
...
367
368
369
370
371
372
373
374


























































375
376
  CREATE VIRTUAL TABLE temp.opp USING unionvtab(src);
  SELECT * FROM opp;
} {
  1 one 2 two 3 three
  10 ten 11 eleven 12 twelve
  20 twenty 21 twenty-one 22 twenty-two
}











#-------------------------------------------------------------------------
# Error conditions.
#
#   2.1.*: Attempt to create a unionvtab table outside of the TEMP schema.
#   2.2.*: Tables that do not exist.
#   2.3.*: Non rowid tables.
................................................................................
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('xua', 't555', 1, 100)");
} {1 {no such rowid table: xua.t555}}

do_execsql_test 2.3.0 {
  CREATE TABLE wr1(a, b, c PRIMARY KEY) WITHOUT ROWID;
  CREATE VIEW v1 AS SELECT * FROM t1;
  CREATE VIEW v2 AS SELECT _rowid_, * FROM t1;



}
do_catchsql_test 2.3.1 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('main', 'wr1', 1, 2)");
} {1 {no such rowid table: main.wr1}}
do_catchsql_test 2.3.2 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'v1', 1, 2)");
} {1 {no such rowid table: v1}}
do_catchsql_test 2.3.3 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'v2', 1, 2)");
} {1 {no such rowid table: v2}}







do_execsql_test 2.4.0 {
  CREATE TABLE x1(a BLOB, b);
  CREATE TABLE x2(a BLOB, b);
  CREATE TEMP TABLE x3(a BLOB, b);

  CREATE TABLE aux.y1(one, two, three INTEGER PRIMARY KEY);
................................................................................
  SELECT * FROM sl WHERE rowid>=9223372036854775807
} {
  9223372036854775807 six
}
do_execsql_test 4.4.4 {
  SELECT * FROM sl WHERE rowid>9223372036854775807
} { }



























































finish_test








>
>
>
>
>
>
>
>
>
>







 







>
>
>










>
>
>
>
>
>







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
...
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
...
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
  CREATE VIRTUAL TABLE temp.opp USING unionvtab(src);
  SELECT * FROM opp;
} {
  1 one 2 two 3 three
  10 ten 11 eleven 12 twelve
  20 twenty 21 twenty-one 22 twenty-two
}

do_execsql_test 1.9 {
  CREATE VIRTUAL TABLE temp.qll USING unionvtab(
    'SELECT * FROM src WHERE db!=''xyz'''
  );
  SELECT * FROM qll WHERE rowid BETWEEN 10 AND 21;
} {
  10 ten 11 eleven 12 twelve
  20 twenty 21 twenty-one
}

#-------------------------------------------------------------------------
# Error conditions.
#
#   2.1.*: Attempt to create a unionvtab table outside of the TEMP schema.
#   2.2.*: Tables that do not exist.
#   2.3.*: Non rowid tables.
................................................................................
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('xua', 't555', 1, 100)");
} {1 {no such rowid table: xua.t555}}

do_execsql_test 2.3.0 {
  CREATE TABLE wr1(a, b, c PRIMARY KEY) WITHOUT ROWID;
  CREATE VIEW v1 AS SELECT * FROM t1;
  CREATE VIEW v2 AS SELECT _rowid_, * FROM t1;

  CREATE TABLE wr2(a, _rowid_ INTEGER, c PRIMARY KEY) WITHOUT ROWID;
  CREATE TABLE wr3(a, b, _rowid_ PRIMARY KEY) WITHOUT ROWID;
}
do_catchsql_test 2.3.1 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('main', 'wr1', 1, 2)");
} {1 {no such rowid table: main.wr1}}
do_catchsql_test 2.3.2 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'v1', 1, 2)");
} {1 {no such rowid table: v1}}
do_catchsql_test 2.3.3 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'v2', 1, 2)");
} {1 {no such rowid table: v2}}
do_catchsql_test 2.3.4 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'wr2', 1, 2)");
} {1 {no such rowid table: wr2}}
do_catchsql_test 2.3.5 {
  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'wr3', 1, 2)");
} {1 {no such rowid table: wr3}}

do_execsql_test 2.4.0 {
  CREATE TABLE x1(a BLOB, b);
  CREATE TABLE x2(a BLOB, b);
  CREATE TEMP TABLE x3(a BLOB, b);

  CREATE TABLE aux.y1(one, two, three INTEGER PRIMARY KEY);
................................................................................
  SELECT * FROM sl WHERE rowid>=9223372036854775807
} {
  9223372036854775807 six
}
do_execsql_test 4.4.4 {
  SELECT * FROM sl WHERE rowid>9223372036854775807
} { }

#-------------------------------------------------------------------------
# More than 8 source tables.
#
do_execsql_test 5.0 {
  CREATE TABLE c0(one, two INTEGER PRIMARY KEY);
  CREATE TABLE c1(one, two INTEGER PRIMARY KEY);
  CREATE TABLE c2(one, two INTEGER PRIMARY KEY);
  CREATE TABLE c3(one, two INTEGER PRIMARY KEY);
  CREATE TABLE c4(one, two INTEGER PRIMARY KEY);
  CREATE TABLE c5(one, two INTEGER PRIMARY KEY);
  CREATE TABLE c6(one, two INTEGER PRIMARY KEY);
  CREATE TABLE c7(one, two INTEGER PRIMARY KEY);
  CREATE TABLE c8(one, two INTEGER PRIMARY KEY);
  CREATE TABLE c9(one, two INTEGER PRIMARY KEY);

  INSERT INTO c0 VALUES('zero', 0);
  INSERT INTO c1 VALUES('one', 1);
  INSERT INTO c2 VALUES('two', 2);
  INSERT INTO c3 VALUES('three', 3);
  INSERT INTO c4 VALUES('four', 4);
  INSERT INTO c5 VALUES('five', 5);
  INSERT INTO c6 VALUES('six', 6);
  INSERT INTO c7 VALUES('seven', 7);
  INSERT INTO c8 VALUES('eight', 8);
  INSERT INTO c9 VALUES('nine', 9);

  CREATE VIRTUAL TABLE temp.cc USING unionvtab([
    SELECT 'main', 'c9', 9, 9 UNION ALL
    SELECT 'main', 'c8', 8, 8 UNION ALL
    SELECT 'main', 'c7', 7, 7 UNION ALL
    SELECT 'main', 'c6', 6, 6 UNION ALL
    SELECT 'main', 'c5', 5, 5 UNION ALL
    SELECT 'main', 'c4', 4, 4 UNION ALL
    SELECT 'main', 'c3', 3, 3 UNION ALL
    SELECT 'main', 'c2', 2, 2 UNION ALL
    SELECT 'main', 'c1', 1, 1 UNION ALL
    SELECT 'main', 'c0', 0, 0
  ]);

  SELECT sum(two) FROM cc;
} {45}

do_execsql_test 5.1 {
  SELECT one FROM cc WHERE one>='seven'
} {zero two three six seven}

do_execsql_test 5.2 {
  SELECT y.one FROM cc AS x, cc AS y WHERE x.one=y.one AND x.rowid>5
} {six seven eight nine}

do_execsql_test 5.3 {
  SELECT cc.one FROM c4, cc WHERE cc.rowid>c4.rowid
} {five six seven eight nine}

do_execsql_test 5.4 {
  SELECT * FROM cc WHERE two LIKE '6'
} {six 6}

finish_test