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 bestindexC
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" && ![info exists ::do_not_use_limit]} {
47 if {$a(op)=="offset" && ![info exists ::do_not_use_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] {
64 if {![info exists x(limit)]} { set x(limit) -1 }
65 if {![info exists x(offset)]} { set x(offset) -1 }
66 set LIMIT " LIMIT $x(limit) OFFSET $x(offset)"
70 lappend lRow "($idx, '$v')"
75 SELECT * FROM ( VALUES [join $lRow ,]) $LIMIT
84 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command "a b c d e f");
85 CREATE VIRTUAL TABLE x2 USING tcl(vtab_command "A B C D E F a b");
89 CREATE TEMP TABLE t_unionall AS
90 SELECT * FROM x1 UNION ALL SELECT * FROM x2;
92 CREATE TEMP TABLE t_intersect AS
93 SELECT * FROM x1 INTERSECT SELECT * FROM x2;
95 CREATE TEMP TABLE t_union AS
96 SELECT * FROM x1 UNION SELECT * FROM x2;
98 CREATE TEMP TABLE t_except AS
99 SELECT * FROM x1 EXCEPT SELECT * FROM x2;
110 "UNION ALL" t_unionall
112 "INTERSECT" t_intersect
116 set expect [execsql "SELECT * FROM $tbl $limit"]
117 do_execsql_test 1.2.$tbl.$tn "SELECT * FROM (
118 SELECT * FROM x1 $op SELECT * FROM x2
125 #-------------------------------------------------------------------------
127 register_tcl_module db
129 do_execsql_test 2.0 {
130 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command "a b c d e f");
131 CREATE VIRTUAL TABLE x2 USING tcl(vtab_command "a b e f");
134 do_execsql_test 2.1 {
141 #-------------------------------------------------------------------------
143 register_tcl_module db
144 do_execsql_test 3.0 {
145 CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "1 2 3 4 5 6 7 8 9 10");
148 do_execsql_test 3.1 {
149 SELECT * FROM y1 WHERE a = COALESCE('8', a) LIMIT 3
152 do_execsql_test 3.2 {
153 SELECT * FROM y1 WHERE a = '2' LIMIT 3
156 load_static_extension db series
157 do_execsql_test 3.3 {
158 SELECT * FROM generate_series(1, 5) WHERE value = (value & 14) LIMIT 3
161 do_execsql_test 3.4 {
162 SELECT value FROM generate_series(1,10) WHERE value>2 LIMIT 4 OFFSET 1;
165 set ::do_not_use_limit 1
166 do_execsql_test 3.5 {
167 SELECT * FROM y1 LIMIT 5 OFFSET 3
169 unset ::do_not_use_limit
170 set ::do_not_use_offset 1
171 do_execsql_test 3.6 {
172 SELECT * FROM y1 LIMIT 5 OFFSET 3
174 unset ::do_not_use_offset