/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/unionvtab.test.

    86     86     CREATE VIRTUAL TABLE temp.opp USING unionvtab(src);
    87     87     SELECT * FROM opp;
    88     88   } {
    89     89     1 one 2 two 3 three
    90     90     10 ten 11 eleven 12 twelve
    91     91     20 twenty 21 twenty-one 22 twenty-two
    92     92   }
           93  +
           94  +do_execsql_test 1.9 {
           95  +  CREATE VIRTUAL TABLE temp.qll USING unionvtab(
           96  +    'SELECT * FROM src WHERE db!=''xyz'''
           97  +  );
           98  +  SELECT * FROM qll WHERE rowid BETWEEN 10 AND 21;
           99  +} {
          100  +  10 ten 11 eleven 12 twelve
          101  +  20 twenty 21 twenty-one
          102  +}
    93    103   
    94    104   #-------------------------------------------------------------------------
    95    105   # Error conditions.
    96    106   #
    97    107   #   2.1.*: Attempt to create a unionvtab table outside of the TEMP schema.
    98    108   #   2.2.*: Tables that do not exist.
    99    109   #   2.3.*: Non rowid tables.
................................................................................
   120    130     CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('xua', 't555', 1, 100)");
   121    131   } {1 {no such rowid table: xua.t555}}
   122    132   
   123    133   do_execsql_test 2.3.0 {
   124    134     CREATE TABLE wr1(a, b, c PRIMARY KEY) WITHOUT ROWID;
   125    135     CREATE VIEW v1 AS SELECT * FROM t1;
   126    136     CREATE VIEW v2 AS SELECT _rowid_, * FROM t1;
          137  +
          138  +  CREATE TABLE wr2(a, _rowid_ INTEGER, c PRIMARY KEY) WITHOUT ROWID;
          139  +  CREATE TABLE wr3(a, b, _rowid_ PRIMARY KEY) WITHOUT ROWID;
   127    140   }
   128    141   do_catchsql_test 2.3.1 {
   129    142     CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('main', 'wr1', 1, 2)");
   130    143   } {1 {no such rowid table: main.wr1}}
   131    144   do_catchsql_test 2.3.2 {
   132    145     CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'v1', 1, 2)");
   133    146   } {1 {no such rowid table: v1}}
   134    147   do_catchsql_test 2.3.3 {
   135    148     CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'v2', 1, 2)");
   136    149   } {1 {no such rowid table: v2}}
          150  +do_catchsql_test 2.3.4 {
          151  +  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'wr2', 1, 2)");
          152  +} {1 {no such rowid table: wr2}}
          153  +do_catchsql_test 2.3.5 {
          154  +  CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 'wr3', 1, 2)");
          155  +} {1 {no such rowid table: wr3}}
   137    156   
   138    157   do_execsql_test 2.4.0 {
   139    158     CREATE TABLE x1(a BLOB, b);
   140    159     CREATE TABLE x2(a BLOB, b);
   141    160     CREATE TEMP TABLE x3(a BLOB, b);
   142    161   
   143    162     CREATE TABLE aux.y1(one, two, three INTEGER PRIMARY KEY);
................................................................................
   367    386     SELECT * FROM sl WHERE rowid>=9223372036854775807
   368    387   } {
   369    388     9223372036854775807 six
   370    389   }
   371    390   do_execsql_test 4.4.4 {
   372    391     SELECT * FROM sl WHERE rowid>9223372036854775807
   373    392   } { }
          393  +
          394  +#-------------------------------------------------------------------------
          395  +# More than 8 source tables.
          396  +#
          397  +do_execsql_test 5.0 {
          398  +  CREATE TABLE c0(one, two INTEGER PRIMARY KEY);
          399  +  CREATE TABLE c1(one, two INTEGER PRIMARY KEY);
          400  +  CREATE TABLE c2(one, two INTEGER PRIMARY KEY);
          401  +  CREATE TABLE c3(one, two INTEGER PRIMARY KEY);
          402  +  CREATE TABLE c4(one, two INTEGER PRIMARY KEY);
          403  +  CREATE TABLE c5(one, two INTEGER PRIMARY KEY);
          404  +  CREATE TABLE c6(one, two INTEGER PRIMARY KEY);
          405  +  CREATE TABLE c7(one, two INTEGER PRIMARY KEY);
          406  +  CREATE TABLE c8(one, two INTEGER PRIMARY KEY);
          407  +  CREATE TABLE c9(one, two INTEGER PRIMARY KEY);
          408  +
          409  +  INSERT INTO c0 VALUES('zero', 0);
          410  +  INSERT INTO c1 VALUES('one', 1);
          411  +  INSERT INTO c2 VALUES('two', 2);
          412  +  INSERT INTO c3 VALUES('three', 3);
          413  +  INSERT INTO c4 VALUES('four', 4);
          414  +  INSERT INTO c5 VALUES('five', 5);
          415  +  INSERT INTO c6 VALUES('six', 6);
          416  +  INSERT INTO c7 VALUES('seven', 7);
          417  +  INSERT INTO c8 VALUES('eight', 8);
          418  +  INSERT INTO c9 VALUES('nine', 9);
          419  +
          420  +  CREATE VIRTUAL TABLE temp.cc USING unionvtab([
          421  +    SELECT 'main', 'c9', 9, 9 UNION ALL
          422  +    SELECT 'main', 'c8', 8, 8 UNION ALL
          423  +    SELECT 'main', 'c7', 7, 7 UNION ALL
          424  +    SELECT 'main', 'c6', 6, 6 UNION ALL
          425  +    SELECT 'main', 'c5', 5, 5 UNION ALL
          426  +    SELECT 'main', 'c4', 4, 4 UNION ALL
          427  +    SELECT 'main', 'c3', 3, 3 UNION ALL
          428  +    SELECT 'main', 'c2', 2, 2 UNION ALL
          429  +    SELECT 'main', 'c1', 1, 1 UNION ALL
          430  +    SELECT 'main', 'c0', 0, 0
          431  +  ]);
          432  +
          433  +  SELECT sum(two) FROM cc;
          434  +} {45}
          435  +
          436  +do_execsql_test 5.1 {
          437  +  SELECT one FROM cc WHERE one>='seven'
          438  +} {zero two three six seven}
          439  +
          440  +do_execsql_test 5.2 {
          441  +  SELECT y.one FROM cc AS x, cc AS y WHERE x.one=y.one AND x.rowid>5
          442  +} {six seven eight nine}
          443  +
          444  +do_execsql_test 5.3 {
          445  +  SELECT cc.one FROM c4, cc WHERE cc.rowid>c4.rowid
          446  +} {five six seven eight nine}
          447  +
          448  +do_execsql_test 5.4 {
          449  +  SELECT * FROM cc WHERE two LIKE '6'
          450  +} {six 6}
   374    451   
   375    452   finish_test
   376    453