patched/test/unionvtab.test - chromium/src/third_party/sqlite - Git at Google

 # 2017-07-15
 #
 # The author disclaims copyright to this source code.  In place of
 # a legal notice, here is a blessing:
 #
 #    May you do good and not evil.
 #    May you find forgiveness for yourself and forgive others.
 #    May you share freely, never taking more than you give.
 #
 #***********************************************************************
 # This file implements regression tests for SQLite library.  The
 # focus of this file is percentile.c extension
 #

 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
 set testprefix unionvtab

 ifcapable !vtab {
   finish_test
   return
 }

 load_static_extension db unionvtab

 #-------------------------------------------------------------------------
 # Warm body tests.
 #
 forcedelete test.db2
 do_execsql_test 1.0 {
   ATTACH 'test.db2' AS aux;

   CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
   CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);
   CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b TEXT);


   INSERT INTO t1 VALUES(1, 'one'), (2, 'two'), (3, 'three');
   INSERT INTO t2 VALUES(10, 'ten'), (11, 'eleven'), (12, 'twelve');
   INSERT INTO t3 VALUES(20, 'twenty'), (21, 'twenty-one'), (22, 'twenty-two');
 }

 do_execsql_test 1.1 {
   CREATE VIRTUAL TABLE temp.uuu USING unionvtab(
     "VALUES(NULL, 't1', 1, 9),  ('main', 't2', 10, 19), ('aux', 't3', 20, 29)"
   );
   SELECT * FROM uuu;
 } {
   1 one 2 two 3 three
   10 ten 11 eleven 12 twelve
   20 twenty 21 twenty-one 22 twenty-two
 }

 do_execsql_test 1.2 {
   PRAGMA table_info(uuu);
 } {
   0 a INTEGER 0 {} 0
   1 b TEXT 0 {} 0
 }

 do_execsql_test 1.3 {
   SELECT * FROM uuu WHERE rowid = 3;
   SELECT * FROM uuu WHERE rowid = 11;
 } {3 three 11 eleven}

 do_execsql_test 1.4 {
   SELECT * FROM uuu WHERE rowid IN (12, 10, 2);
 } {2 two 10 ten 12 twelve}

 do_execsql_test 1.5 {
   SELECT * FROM uuu WHERE rowid BETWEEN 3 AND 11;
 } {3 three 10 ten 11 eleven}

 do_execsql_test 1.6 {
   SELECT * FROM uuu WHERE rowid BETWEEN 11 AND 15;
 } {11 eleven 12 twelve}

 do_execsql_test 1.7 {
   SELECT * FROM uuu WHERE rowid BETWEEN -46 AND 1500;
 } {
   1 one 2 two 3 three
   10 ten 11 eleven 12 twelve
   20 twenty 21 twenty-one 22 twenty-two
 }

 do_execsql_test 1.8 {
   CREATE TABLE src(db, tbl, min, max);
   INSERT INTO src VALUES(NULL, 't1', 1, 9);
   INSERT INTO src VALUES('main', 't2', 10, 19);
   INSERT INTO src VALUES('aux', 't3', 20, 29);
   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.
 #   2.4.*: Tables with mismatched schemas.
 #   2.5.*: A unionvtab table with zero source tables.
 #
 do_catchsql_test 2.1.1 {
   CREATE VIRTUAL TABLE u1 USING unionvtab("VALUES(NULL, 't1', 1, 100)");
 } {1 {unionvtab tables must be created in TEMP schema}}
 do_catchsql_test 2.1.2 {
   CREATE VIRTUAL TABLE main.u1 USING unionvtab("VALUES('', 't1', 1, 100)");
 } {1 {unionvtab tables must be created in TEMP schema}}
 do_catchsql_test 2.1.3 {
   CREATE VIRTUAL TABLE aux.u1 USING unionvtab("VALUES('', 't1', 1, 100)");
 } {1 {unionvtab tables must be created in TEMP schema}}

 do_catchsql_test 2.2.1 {
   CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 't555', 1, 100)");
 } {1 {no such rowid table: t555}}
 do_catchsql_test 2.2.2 {
   CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('aux', 't555', 1, 100)");
 } {1 {no such rowid table: aux.t555}}
 do_catchsql_test 2.2.3 {
   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);
   CREATE TEMP TABLE y2(one, two, three INTEGER PRIMARY KEY);
   CREATE TABLE y3(one, two, three INTEGER PRIMARY KEY);
 }

 foreach {tn dbs res} {
   1 {x1 x2 x3} {0 {}}
   2 {y1 y2 y3} {0 {}}
   3 {x1 y2 y3} {1 {source table schema mismatch}}
   4 {x1 y2 x3} {1 {source table schema mismatch}}
   5 {x1 x2 y3} {1 {source table schema mismatch}}
 } {
   set L [list]
   set iMin 0
   foreach e $dbs {
     set E [split $e .]
     if {[llength $E]>1} {
       lappend L "('[lindex $E 0]', '[lindex $E 1]', $iMin, $iMin)"
     } else {
       lappend L "(NULL, '$e', $iMin, $iMin)"
     }
     incr iMin
   }

   set sql "CREATE VIRTUAL TABLE temp.a1 USING unionvtab(\"VALUES [join $L ,]\")"
   do_catchsql_test 2.4.$tn "
     DROP TABLE IF EXISTS temp.a1;
     CREATE VIRTUAL TABLE temp.a1 USING unionvtab(\"VALUES [join $L ,]\");
   " $res
 }

 do_catchsql_test 2.5 {
   CREATE VIRTUAL TABLE temp.b1 USING unionvtab(
     [SELECT 'main', 'b1', 0, 100 WHERE 0]
   )
 } {1 {no source tables configured}}

 foreach {tn sql} {
   1 { VALUES('main', 't1', 10, 20), ('main', 't2', 30, 29) }
   2 { VALUES('main', 't1', 10, 20), ('main', 't2', 15, 30) }
 } {
   do_catchsql_test 2.6.$tn "
     CREATE VIRTUAL TABLE temp.a1 USING unionvtab(`$sql`)
   " {1 {rowid range mismatch error}}
 }

 do_catchsql_test 2.7.1 {
   CREATE VIRTUAL TABLE temp.b1 USING unionvtab(1, 2, 3, 4)
 } {1 {wrong number of arguments for unionvtab}}

 #-------------------------------------------------------------------------
 #
 reset_db
 load_static_extension db unionvtab
 do_execsql_test 3.0 {
   CREATE TABLE tbl1(a INTEGER PRIMARY KEY, b);
   CREATE TABLE tbl2(a INTEGER PRIMARY KEY, b);
   CREATE TABLE tbl3(a INTEGER PRIMARY KEY, b);

   WITH ss(ii) AS ( SELECT 1 UNION ALL SELECT ii+1 FROM ss WHERE ii<100 )
   INSERT INTO tbl1 SELECT ii, '1.' || ii FROM ss;

   WITH ss(ii) AS ( SELECT 1 UNION ALL SELECT ii+1 FROM ss WHERE ii<100 )
   INSERT INTO tbl2 SELECT ii, '2.' || ii FROM ss;

   WITH ss(ii) AS ( SELECT 1 UNION ALL SELECT ii+1 FROM ss WHERE ii<100 )
   INSERT INTO tbl3 SELECT ii, '3.' || ii FROM ss;

   CREATE VIRTUAL TABLE temp.uu USING unionvtab(
     "VALUES(NULL,'tbl2', 26, 74), (NULL,'tbl3', 75, 100), (NULL,'tbl1', 1, 25)"
   );
 }

 do_execsql_test 3.1 {
   SELECT * FROM uu WHERE rowid = 10;
 } {10 {1.10}}
 do_execsql_test 3.2 {
   SELECT * FROM uu WHERE rowid = 25;
 } {25 {1.25}}

 do_execsql_test 3.3 { SELECT count(*) FROM uu WHERE rowid <= 24 } {24}

 # The following queries get the "wrong" answers. This is because the
 # module assumes that each source table contains rowids from only within
 # the range specified. For example, (rowid <= 25) matches 100 rows. This
 # is because the module implements (rowid <= 25) as a full table scan
 # of tbl1 only.
 do_execsql_test 3.4.1 { SELECT count(*) FROM uu WHERE rowid <= 25 } {100}
 do_execsql_test 3.4.2 { SELECT count(*) FROM uu WHERE rowid <= 26 } {126}
 do_execsql_test 3.4.3 { SELECT count(*) FROM uu WHERE rowid <= 73 } {173}
 do_execsql_test 3.4.4 { SELECT count(*) FROM uu WHERE rowid <= 74 } {200}
 do_execsql_test 3.4.5 { SELECT count(*) FROM uu WHERE rowid <= 75 } {275}
 do_execsql_test 3.4.6 { SELECT count(*) FROM uu WHERE rowid <= 99 } {299}
 do_execsql_test 3.4.7 { SELECT count(*) FROM uu WHERE rowid <= 100 } {300}
 do_execsql_test 3.4.8 { SELECT count(*) FROM uu WHERE rowid <= 101 } {300}

 do_execsql_test 3.5.1 { SELECT count(*) FROM uu WHERE rowid < 25 } {24}
 do_execsql_test 3.5.2 { SELECT count(*) FROM uu WHERE rowid < 26 } {100}
 do_execsql_test 3.5.3 { SELECT count(*) FROM uu WHERE rowid < 27 } {126}
 do_execsql_test 3.5.4 { SELECT count(*) FROM uu WHERE rowid < 73 } {172}
 do_execsql_test 3.5.5 { SELECT count(*) FROM uu WHERE rowid < 74 } {173}
 do_execsql_test 3.5.6 { SELECT count(*) FROM uu WHERE rowid < 75 } {200}
 do_execsql_test 3.5.7 { SELECT count(*) FROM uu WHERE rowid < 76 } {275}
 do_execsql_test 3.5.8 { SELECT count(*) FROM uu WHERE rowid < 99 } {298}
 do_execsql_test 3.5.9 { SELECT count(*) FROM uu WHERE rowid < 100 } {299}
 do_execsql_test 3.5.10 { SELECT count(*) FROM uu WHERE rowid < 101 } {300}

 do_execsql_test 3.6.1 { SELECT count(*) FROM uu WHERE rowid > 24 } {276}
 do_execsql_test 3.6.1 { SELECT count(*) FROM uu WHERE rowid > 25 } {200}
 do_execsql_test 3.6.2 { SELECT count(*) FROM uu WHERE rowid > 26 } {174}
 do_execsql_test 3.6.3 { SELECT count(*) FROM uu WHERE rowid > 27 } {173}
 do_execsql_test 3.6.4 { SELECT count(*) FROM uu WHERE rowid > 73 } {127}
 do_execsql_test 3.6.5 { SELECT count(*) FROM uu WHERE rowid > 74 } {100}
 do_execsql_test 3.6.6 { SELECT count(*) FROM uu WHERE rowid > 75 } {25}
 do_execsql_test 3.6.7 { SELECT count(*) FROM uu WHERE rowid > 76 } {24}
 do_execsql_test 3.6.8 { SELECT count(*) FROM uu WHERE rowid > 99 } {1}
 do_execsql_test 3.6.9 { SELECT count(*) FROM uu WHERE rowid > 100 } {0}
 do_execsql_test 3.6.10 { SELECT count(*) FROM uu WHERE rowid > 101 } {0}

 do_execsql_test 3.7.1 { SELECT count(*) FROM uu WHERE rowid >= 24 } {277}
 do_execsql_test 3.7.1 { SELECT count(*) FROM uu WHERE rowid >= 25 } {276}
 do_execsql_test 3.7.2 { SELECT count(*) FROM uu WHERE rowid >= 26 } {200}
 do_execsql_test 3.7.3 { SELECT count(*) FROM uu WHERE rowid >= 27 } {174}
 do_execsql_test 3.7.4 { SELECT count(*) FROM uu WHERE rowid >= 73 } {128}
 do_execsql_test 3.7.5 { SELECT count(*) FROM uu WHERE rowid >= 74 } {127}
 do_execsql_test 3.7.6 { SELECT count(*) FROM uu WHERE rowid >= 75 } {100}
 do_execsql_test 3.7.7 { SELECT count(*) FROM uu WHERE rowid >= 76 } {25}
 do_execsql_test 3.7.8 { SELECT count(*) FROM uu WHERE rowid >= 99 } {2}
 do_execsql_test 3.7.9 { SELECT count(*) FROM uu WHERE rowid >= 100 } {1}
 do_execsql_test 3.7.10 { SELECT count(*) FROM uu WHERE rowid >= 101 } {0}

 set L [expr  9223372036854775807]
 set S [expr -9223372036854775808]

 do_execsql_test 3.8.1 { SELECT count(*) FROM uu WHERE rowid >= $S } {300}
 do_execsql_test 3.8.2 { SELECT count(*) FROM uu WHERE rowid >  $S } {300}
 do_execsql_test 3.8.3 { SELECT count(*) FROM uu WHERE rowid <= $S } {0}
 do_execsql_test 3.8.4 { SELECT count(*) FROM uu WHERE rowid <  $S } {0}

 do_execsql_test 3.9.1 { SELECT count(*) FROM uu WHERE rowid >= $L } {0}
 do_execsql_test 3.9.2 { SELECT count(*) FROM uu WHERE rowid >  $L } {0}
 do_execsql_test 3.9.3 { SELECT count(*) FROM uu WHERE rowid <= $L } {300}
 do_execsql_test 3.9.4 { SELECT count(*) FROM uu WHERE rowid <  $L } {300}

 do_execsql_test 3.10.1 { SELECT count(*) FROM uu WHERE a < 25 } {24}
 do_execsql_test 3.10.2 { SELECT count(*) FROM uu WHERE a < 26 } {100}
 do_execsql_test 3.10.3 { SELECT count(*) FROM uu WHERE a < 27 } {126}
 do_execsql_test 3.10.4 { SELECT count(*) FROM uu WHERE a < 73 } {172}
 do_execsql_test 3.10.5 { SELECT count(*) FROM uu WHERE a < 74 } {173}
 do_execsql_test 3.10.6 { SELECT count(*) FROM uu WHERE a < 75 } {200}
 do_execsql_test 3.10.7 { SELECT count(*) FROM uu WHERE a < 76 } {275}
 do_execsql_test 3.10.8 { SELECT count(*) FROM uu WHERE a < 99 } {298}
 do_execsql_test 3.10.9 { SELECT count(*) FROM uu WHERE a < 100 } {299}
 do_execsql_test 3.10.10 { SELECT count(*) FROM uu WHERE a < 101 } {300}


 #-------------------------------------------------------------------------
 #
 do_execsql_test 4.0 {
   CREATE TABLE s1(k INTEGER PRIMARY KEY, v);
   INSERT INTO s1 VALUES($S, 'one');
   INSERT INTO s1 VALUES($S+1, 'two');
   INSERT INTO s1 VALUES($S+2, 'three');

   CREATE TABLE l1(k INTEGER PRIMARY KEY, v);
   INSERT INTO l1 VALUES($L, 'six');
   INSERT INTO l1 VALUES($L-1, 'five');
   INSERT INTO l1 VALUES($L-2, 'four');

   CREATE VIRTUAL TABLE temp.sl USING unionvtab(
     "SELECT NULL, 'l1', 0, 9223372036854775807
      UNION ALL
      SELECT NULL, 's1', -9223372036854775808, -1"
   );
 }

 do_execsql_test 4.1 {
   SELECT * FROM sl;
 } {
   -9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
    9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
 }

 foreach {k v} {
   -9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
    9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
 } {
   do_execsql_test 4.2.$v { SELECT * FROM sl WHERE rowid=$k } [list $k $v]
 }

 do_execsql_test 4.3.1 {
   SELECT * FROM sl WHERE rowid>-9223372036854775808
 } {
   -9223372036854775807 two -9223372036854775806 three
    9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
 }
 do_execsql_test 4.3.2 {
   SELECT * FROM sl WHERE rowid>=-9223372036854775808
 } {
   -9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
    9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
 }
 do_execsql_test 4.3.3 {
   SELECT * FROM sl WHERE rowid<=-9223372036854775808
 } {
   -9223372036854775808 one
 }
 do_execsql_test 4.3.4 {
   SELECT * FROM sl WHERE rowid<-9223372036854775808
 } {}

 do_execsql_test 4.4.1 {
   SELECT * FROM sl WHERE rowid<9223372036854775807
 } {
   -9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
    9223372036854775805 four 9223372036854775806 five
 }
 do_execsql_test 4.4.2 {
   SELECT * FROM sl WHERE rowid<=9223372036854775807
 } {
   -9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
    9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
 }
 do_execsql_test 4.4.3 {
   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
	# 2017-07-15
	#
	# The author disclaims copyright to this source code. In place of
	# a legal notice, here is a blessing:
	#
	# May you do good and not evil.
	# May you find forgiveness for yourself and forgive others.
	# May you share freely, never taking more than you give.
	#
	#***********************************************************************
	# This file implements regression tests for SQLite library. The
	# focus of this file is percentile.c extension
	#

	set testdir [file dirname $argv0]
	source $testdir/tester.tcl
	set testprefix unionvtab

	ifcapable !vtab {
	finish_test
	return
	}

	load_static_extension db unionvtab

	#-------------------------------------------------------------------------
	# Warm body tests.
	#
	forcedelete test.db2
	do_execsql_test 1.0 {
	ATTACH 'test.db2' AS aux;

	CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
	CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);
	CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b TEXT);


	INSERT INTO t1 VALUES(1, 'one'), (2, 'two'), (3, 'three');
	INSERT INTO t2 VALUES(10, 'ten'), (11, 'eleven'), (12, 'twelve');
	INSERT INTO t3 VALUES(20, 'twenty'), (21, 'twenty-one'), (22, 'twenty-two');
	}

	do_execsql_test 1.1 {
	CREATE VIRTUAL TABLE temp.uuu USING unionvtab(
	"VALUES(NULL, 't1', 1, 9), ('main', 't2', 10, 19), ('aux', 't3', 20, 29)"
	);
	SELECT * FROM uuu;
	} {
	1 one 2 two 3 three
	10 ten 11 eleven 12 twelve
	20 twenty 21 twenty-one 22 twenty-two
	}

	do_execsql_test 1.2 {
	PRAGMA table_info(uuu);
	} {
	0 a INTEGER 0 {} 0
	1 b TEXT 0 {} 0
	}

	do_execsql_test 1.3 {
	SELECT * FROM uuu WHERE rowid = 3;
	SELECT * FROM uuu WHERE rowid = 11;
	} {3 three 11 eleven}

	do_execsql_test 1.4 {
	SELECT * FROM uuu WHERE rowid IN (12, 10, 2);
	} {2 two 10 ten 12 twelve}

	do_execsql_test 1.5 {
	SELECT * FROM uuu WHERE rowid BETWEEN 3 AND 11;
	} {3 three 10 ten 11 eleven}

	do_execsql_test 1.6 {
	SELECT * FROM uuu WHERE rowid BETWEEN 11 AND 15;
	} {11 eleven 12 twelve}

	do_execsql_test 1.7 {
	SELECT * FROM uuu WHERE rowid BETWEEN -46 AND 1500;
	} {
	1 one 2 two 3 three
	10 ten 11 eleven 12 twelve
	20 twenty 21 twenty-one 22 twenty-two
	}

	do_execsql_test 1.8 {
	CREATE TABLE src(db, tbl, min, max);
	INSERT INTO src VALUES(NULL, 't1', 1, 9);
	INSERT INTO src VALUES('main', 't2', 10, 19);
	INSERT INTO src VALUES('aux', 't3', 20, 29);
	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.
	# 2.4.*: Tables with mismatched schemas.
	# 2.5.*: A unionvtab table with zero source tables.
	#
	do_catchsql_test 2.1.1 {
	CREATE VIRTUAL TABLE u1 USING unionvtab("VALUES(NULL, 't1', 1, 100)");
	} {1 {unionvtab tables must be created in TEMP schema}}
	do_catchsql_test 2.1.2 {
	CREATE VIRTUAL TABLE main.u1 USING unionvtab("VALUES('', 't1', 1, 100)");
	} {1 {unionvtab tables must be created in TEMP schema}}
	do_catchsql_test 2.1.3 {
	CREATE VIRTUAL TABLE aux.u1 USING unionvtab("VALUES('', 't1', 1, 100)");
	} {1 {unionvtab tables must be created in TEMP schema}}

	do_catchsql_test 2.2.1 {
	CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES(NULL, 't555', 1, 100)");
	} {1 {no such rowid table: t555}}
	do_catchsql_test 2.2.2 {
	CREATE VIRTUAL TABLE temp.u1 USING unionvtab("VALUES('aux', 't555', 1, 100)");
	} {1 {no such rowid table: aux.t555}}
	do_catchsql_test 2.2.3 {
	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);
	CREATE TEMP TABLE y2(one, two, three INTEGER PRIMARY KEY);
	CREATE TABLE y3(one, two, three INTEGER PRIMARY KEY);
	}

	foreach {tn dbs res} {
	1 {x1 x2 x3} {0 {}}
	2 {y1 y2 y3} {0 {}}
	3 {x1 y2 y3} {1 {source table schema mismatch}}
	4 {x1 y2 x3} {1 {source table schema mismatch}}
	5 {x1 x2 y3} {1 {source table schema mismatch}}
	} {
	set L [list]
	set iMin 0
	foreach e $dbs {
	set E [split $e .]
	if {[llength $E]>1} {
	lappend L "('[lindex $E 0]', '[lindex $E 1]', $iMin, $iMin)"
	} else {
	lappend L "(NULL, '$e', $iMin, $iMin)"
	}
	incr iMin
	}

	set sql "CREATE VIRTUAL TABLE temp.a1 USING unionvtab(\"VALUES [join $L ,]\")"
	do_catchsql_test 2.4.$tn "
	DROP TABLE IF EXISTS temp.a1;
	CREATE VIRTUAL TABLE temp.a1 USING unionvtab(\"VALUES [join $L ,]\");
	" $res
	}

	do_catchsql_test 2.5 {
	CREATE VIRTUAL TABLE temp.b1 USING unionvtab(
	[SELECT 'main', 'b1', 0, 100 WHERE 0]
	)
	} {1 {no source tables configured}}

	foreach {tn sql} {
	1 { VALUES('main', 't1', 10, 20), ('main', 't2', 30, 29) }
	2 { VALUES('main', 't1', 10, 20), ('main', 't2', 15, 30) }
	} {
	do_catchsql_test 2.6.$tn "
	CREATE VIRTUAL TABLE temp.a1 USING unionvtab(`$sql`)
	" {1 {rowid range mismatch error}}
	}

	do_catchsql_test 2.7.1 {
	CREATE VIRTUAL TABLE temp.b1 USING unionvtab(1, 2, 3, 4)
	} {1 {wrong number of arguments for unionvtab}}

	#-------------------------------------------------------------------------
	#
	reset_db
	load_static_extension db unionvtab
	do_execsql_test 3.0 {
	CREATE TABLE tbl1(a INTEGER PRIMARY KEY, b);
	CREATE TABLE tbl2(a INTEGER PRIMARY KEY, b);
	CREATE TABLE tbl3(a INTEGER PRIMARY KEY, b);

	WITH ss(ii) AS ( SELECT 1 UNION ALL SELECT ii+1 FROM ss WHERE ii<100 )
	INSERT INTO tbl1 SELECT ii, '1.' \|\| ii FROM ss;

	WITH ss(ii) AS ( SELECT 1 UNION ALL SELECT ii+1 FROM ss WHERE ii<100 )
	INSERT INTO tbl2 SELECT ii, '2.' \|\| ii FROM ss;

	WITH ss(ii) AS ( SELECT 1 UNION ALL SELECT ii+1 FROM ss WHERE ii<100 )
	INSERT INTO tbl3 SELECT ii, '3.' \|\| ii FROM ss;

	CREATE VIRTUAL TABLE temp.uu USING unionvtab(
	"VALUES(NULL,'tbl2', 26, 74), (NULL,'tbl3', 75, 100), (NULL,'tbl1', 1, 25)"
	);
	}

	do_execsql_test 3.1 {
	SELECT * FROM uu WHERE rowid = 10;
	} {10 {1.10}}
	do_execsql_test 3.2 {
	SELECT * FROM uu WHERE rowid = 25;
	} {25 {1.25}}

	do_execsql_test 3.3 { SELECT count(*) FROM uu WHERE rowid <= 24 } {24}

	# The following queries get the "wrong" answers. This is because the
	# module assumes that each source table contains rowids from only within
	# the range specified. For example, (rowid <= 25) matches 100 rows. This
	# is because the module implements (rowid <= 25) as a full table scan
	# of tbl1 only.
	do_execsql_test 3.4.1 { SELECT count(*) FROM uu WHERE rowid <= 25 } {100}
	do_execsql_test 3.4.2 { SELECT count(*) FROM uu WHERE rowid <= 26 } {126}
	do_execsql_test 3.4.3 { SELECT count(*) FROM uu WHERE rowid <= 73 } {173}
	do_execsql_test 3.4.4 { SELECT count(*) FROM uu WHERE rowid <= 74 } {200}
	do_execsql_test 3.4.5 { SELECT count(*) FROM uu WHERE rowid <= 75 } {275}
	do_execsql_test 3.4.6 { SELECT count(*) FROM uu WHERE rowid <= 99 } {299}
	do_execsql_test 3.4.7 { SELECT count(*) FROM uu WHERE rowid <= 100 } {300}
	do_execsql_test 3.4.8 { SELECT count(*) FROM uu WHERE rowid <= 101 } {300}

	do_execsql_test 3.5.1 { SELECT count(*) FROM uu WHERE rowid < 25 } {24}
	do_execsql_test 3.5.2 { SELECT count(*) FROM uu WHERE rowid < 26 } {100}
	do_execsql_test 3.5.3 { SELECT count(*) FROM uu WHERE rowid < 27 } {126}
	do_execsql_test 3.5.4 { SELECT count(*) FROM uu WHERE rowid < 73 } {172}
	do_execsql_test 3.5.5 { SELECT count(*) FROM uu WHERE rowid < 74 } {173}
	do_execsql_test 3.5.6 { SELECT count(*) FROM uu WHERE rowid < 75 } {200}
	do_execsql_test 3.5.7 { SELECT count(*) FROM uu WHERE rowid < 76 } {275}
	do_execsql_test 3.5.8 { SELECT count(*) FROM uu WHERE rowid < 99 } {298}
	do_execsql_test 3.5.9 { SELECT count(*) FROM uu WHERE rowid < 100 } {299}
	do_execsql_test 3.5.10 { SELECT count(*) FROM uu WHERE rowid < 101 } {300}

	do_execsql_test 3.6.1 { SELECT count(*) FROM uu WHERE rowid > 24 } {276}
	do_execsql_test 3.6.1 { SELECT count(*) FROM uu WHERE rowid > 25 } {200}
	do_execsql_test 3.6.2 { SELECT count(*) FROM uu WHERE rowid > 26 } {174}
	do_execsql_test 3.6.3 { SELECT count(*) FROM uu WHERE rowid > 27 } {173}
	do_execsql_test 3.6.4 { SELECT count(*) FROM uu WHERE rowid > 73 } {127}
	do_execsql_test 3.6.5 { SELECT count(*) FROM uu WHERE rowid > 74 } {100}
	do_execsql_test 3.6.6 { SELECT count(*) FROM uu WHERE rowid > 75 } {25}
	do_execsql_test 3.6.7 { SELECT count(*) FROM uu WHERE rowid > 76 } {24}
	do_execsql_test 3.6.8 { SELECT count(*) FROM uu WHERE rowid > 99 } {1}
	do_execsql_test 3.6.9 { SELECT count(*) FROM uu WHERE rowid > 100 } {0}
	do_execsql_test 3.6.10 { SELECT count(*) FROM uu WHERE rowid > 101 } {0}

	do_execsql_test 3.7.1 { SELECT count(*) FROM uu WHERE rowid >= 24 } {277}
	do_execsql_test 3.7.1 { SELECT count(*) FROM uu WHERE rowid >= 25 } {276}
	do_execsql_test 3.7.2 { SELECT count(*) FROM uu WHERE rowid >= 26 } {200}
	do_execsql_test 3.7.3 { SELECT count(*) FROM uu WHERE rowid >= 27 } {174}
	do_execsql_test 3.7.4 { SELECT count(*) FROM uu WHERE rowid >= 73 } {128}
	do_execsql_test 3.7.5 { SELECT count(*) FROM uu WHERE rowid >= 74 } {127}
	do_execsql_test 3.7.6 { SELECT count(*) FROM uu WHERE rowid >= 75 } {100}
	do_execsql_test 3.7.7 { SELECT count(*) FROM uu WHERE rowid >= 76 } {25}
	do_execsql_test 3.7.8 { SELECT count(*) FROM uu WHERE rowid >= 99 } {2}
	do_execsql_test 3.7.9 { SELECT count(*) FROM uu WHERE rowid >= 100 } {1}
	do_execsql_test 3.7.10 { SELECT count(*) FROM uu WHERE rowid >= 101 } {0}

	set L [expr 9223372036854775807]
	set S [expr -9223372036854775808]

	do_execsql_test 3.8.1 { SELECT count(*) FROM uu WHERE rowid >= $S } {300}
	do_execsql_test 3.8.2 { SELECT count(*) FROM uu WHERE rowid > $S } {300}
	do_execsql_test 3.8.3 { SELECT count(*) FROM uu WHERE rowid <= $S } {0}
	do_execsql_test 3.8.4 { SELECT count(*) FROM uu WHERE rowid < $S } {0}

	do_execsql_test 3.9.1 { SELECT count(*) FROM uu WHERE rowid >= $L } {0}
	do_execsql_test 3.9.2 { SELECT count(*) FROM uu WHERE rowid > $L } {0}
	do_execsql_test 3.9.3 { SELECT count(*) FROM uu WHERE rowid <= $L } {300}
	do_execsql_test 3.9.4 { SELECT count(*) FROM uu WHERE rowid < $L } {300}

	do_execsql_test 3.10.1 { SELECT count(*) FROM uu WHERE a < 25 } {24}
	do_execsql_test 3.10.2 { SELECT count(*) FROM uu WHERE a < 26 } {100}
	do_execsql_test 3.10.3 { SELECT count(*) FROM uu WHERE a < 27 } {126}
	do_execsql_test 3.10.4 { SELECT count(*) FROM uu WHERE a < 73 } {172}
	do_execsql_test 3.10.5 { SELECT count(*) FROM uu WHERE a < 74 } {173}
	do_execsql_test 3.10.6 { SELECT count(*) FROM uu WHERE a < 75 } {200}
	do_execsql_test 3.10.7 { SELECT count(*) FROM uu WHERE a < 76 } {275}
	do_execsql_test 3.10.8 { SELECT count(*) FROM uu WHERE a < 99 } {298}
	do_execsql_test 3.10.9 { SELECT count(*) FROM uu WHERE a < 100 } {299}
	do_execsql_test 3.10.10 { SELECT count(*) FROM uu WHERE a < 101 } {300}


	#-------------------------------------------------------------------------
	#
	do_execsql_test 4.0 {
	CREATE TABLE s1(k INTEGER PRIMARY KEY, v);
	INSERT INTO s1 VALUES($S, 'one');
	INSERT INTO s1 VALUES($S+1, 'two');
	INSERT INTO s1 VALUES($S+2, 'three');

	CREATE TABLE l1(k INTEGER PRIMARY KEY, v);
	INSERT INTO l1 VALUES($L, 'six');
	INSERT INTO l1 VALUES($L-1, 'five');
	INSERT INTO l1 VALUES($L-2, 'four');

	CREATE VIRTUAL TABLE temp.sl USING unionvtab(
	"SELECT NULL, 'l1', 0, 9223372036854775807
	UNION ALL
	SELECT NULL, 's1', -9223372036854775808, -1"
	);
	}

	do_execsql_test 4.1 {
	SELECT * FROM sl;
	} {
	-9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
	9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
	}

	foreach {k v} {
	-9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
	9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
	} {
	do_execsql_test 4.2.$v { SELECT * FROM sl WHERE rowid=$k } [list $k $v]
	}

	do_execsql_test 4.3.1 {
	SELECT * FROM sl WHERE rowid>-9223372036854775808
	} {
	-9223372036854775807 two -9223372036854775806 three
	9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
	}
	do_execsql_test 4.3.2 {
	SELECT * FROM sl WHERE rowid>=-9223372036854775808
	} {
	-9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
	9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
	}
	do_execsql_test 4.3.3 {
	SELECT * FROM sl WHERE rowid<=-9223372036854775808
	} {
	-9223372036854775808 one
	}
	do_execsql_test 4.3.4 {
	SELECT * FROM sl WHERE rowid<-9223372036854775808
	} {}

	do_execsql_test 4.4.1 {
	SELECT * FROM sl WHERE rowid<9223372036854775807
	} {
	-9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
	9223372036854775805 four 9223372036854775806 five
	}
	do_execsql_test 4.4.2 {
	SELECT * FROM sl WHERE rowid<=9223372036854775807
	} {
	-9223372036854775808 one -9223372036854775807 two -9223372036854775806 three
	9223372036854775805 four 9223372036854775806 five 9223372036854775807 six
	}
	do_execsql_test 4.4.3 {
	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