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 #***********************************************************************
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix bestindexB
23 register_tcl_module db
25 proc vtab_command {lVal method args} {
28 return "CREATE TABLE t1(a)"
32 set hdl [lindex $args 0]
33 set clist [$hdl constraints]
34 set orderby [$hdl orderby]
42 if {$a(usable)==0} continue
43 if {$a(op)=="limit"} {
47 if {$a(op)=="offset"} {
54 return "cost 1000000 rows 1000000 idxnum 0 idxstr {$idxstr} $res"
58 set idxstr [lindex $args 1]
60 foreach a $idxstr b [lindex $args 2] {
66 lappend lRow "($idx, '$v')"
71 SELECT * FROM ( VALUES [join $lRow ,]) $LIMIT
80 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command "a b c d e f");
81 CREATE VIRTUAL TABLE x2 USING tcl(vtab_command "A B C D E F a b");
85 CREATE TEMP TABLE t_unionall AS
86 SELECT * FROM x1 UNION ALL SELECT * FROM x2;
88 CREATE TEMP TABLE t_intersect AS
89 SELECT * FROM x1 INTERSECT SELECT * FROM x2;
91 CREATE TEMP TABLE t_union AS
92 SELECT * FROM x1 UNION SELECT * FROM x2;
94 CREATE TEMP TABLE t_except AS
95 SELECT * FROM x1 EXCEPT SELECT * FROM x2;
106 "UNION ALL" t_unionall
108 "INTERSECT" t_intersect
112 set expect [execsql "SELECT * FROM $tbl $limit"]
113 do_execsql_test 1.2.$tbl.$tn "SELECT * FROM (
114 SELECT * FROM x1 $op SELECT * FROM x2
121 #-------------------------------------------------------------------------
123 register_tcl_module db
125 do_execsql_test 2.0 {
126 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command "a b c d e f");
127 CREATE VIRTUAL TABLE x2 USING tcl(vtab_command "a b e f");
130 do_execsql_test 2.1 {
137 #-------------------------------------------------------------------------
139 register_tcl_module db
140 do_execsql_test 3.0 {
141 CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "1 2 3 4 5 6 7 8 9 10");
144 do_execsql_test 3.1 {
145 SELECT * FROM y1 WHERE a = COALESCE('8', a) LIMIT 3
148 do_execsql_test 3.2 {
149 SELECT * FROM y1 WHERE a = '2' LIMIT 3
152 load_static_extension db series
153 do_execsql_test 3.3 {
154 SELECT * FROM generate_series(1, 5) WHERE value = (value & 14) LIMIT 3