SQLite

  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