AryaWu/sqlite
0
1# 2024-04-262#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#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.9#10#***********************************************************************11# 12#13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set testprefix bestindexC17 18ifcapable !vtab {19 finish_test20 return21}22 23register_tcl_module db24 25proc vtab_command {lVal method args} {26 switch -- $method {27 xConnect {28 return "CREATE TABLE t1(a)"29 }30 31 xBestIndex {32 set hdl [lindex $args 0]33 set clist [$hdl constraints]34 set orderby [$hdl orderby]35 36 set idxstr [list]37 set res [list]38 39 set idx 040 foreach c $clist {41 array set a $c42 if {$a(usable)==0} continue43 if {$a(op)=="limit" && ![info exists ::do_not_use_limit]} { 44 lappend idxstr limit45 lappend res omit $idx46 }47 if {$a(op)=="offset" && ![info exists ::do_not_use_offset]} { 48 lappend idxstr offset49 lappend res omit $idx50 }51 incr idx52 }53 54 return "cost 1000000 rows 1000000 idxnum 0 idxstr {$idxstr} $res"55 }56 57 xFilter {58 set idxstr [lindex $args 1]59 set LIMIT ""60 foreach a $idxstr b [lindex $args 2] {61 set x($a) $b62 }63 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)"67 68 set idx 169 foreach v $lVal {70 lappend lRow "($idx, '$v')"71 incr idx72 }73 74 return [list sql "75 SELECT * FROM ( VALUES [join $lRow ,]) $LIMIT76 "]77 }78 }79 80 return {}81}82 83do_execsql_test 1.0 {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");86} {}87 88do_execsql_test 1.1 {89 CREATE TEMP TABLE t_unionall AS 90 SELECT * FROM x1 UNION ALL SELECT * FROM x2;91 92 CREATE TEMP TABLE t_intersect AS 93 SELECT * FROM x1 INTERSECT SELECT * FROM x2;94 95 CREATE TEMP TABLE t_union AS 96 SELECT * FROM x1 UNION SELECT * FROM x2;97 98 CREATE TEMP TABLE t_except AS 99 SELECT * FROM x1 EXCEPT SELECT * FROM x2;100}101 102foreach {tn limit} {103 1 "LIMIT 8" 104 2 "LIMIT 4" 105 3 "LIMIT 4 OFFSET 2" 106 4 "LIMIT 8 OFFSET 4" 107} {108 109 foreach {op tbl} {110 "UNION ALL" t_unionall111 "UNION" t_union112 "INTERSECT" t_intersect113 "EXCEPT" t_except114 } {115 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 x2119 ) $limit" $expect120 121 }122 123}124 125#-------------------------------------------------------------------------126reset_db127register_tcl_module db128 129do_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");132} {}133 134do_execsql_test 2.1 {135 SELECT * FROM x1 136 EXCEPT137 SELECT * FROM x2138 LIMIT 3139} {c d}140 141#-------------------------------------------------------------------------142reset_db143register_tcl_module db144do_execsql_test 3.0 {145 CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "1 2 3 4 5 6 7 8 9 10");146} {}147 148do_execsql_test 3.1 {149 SELECT * FROM y1 WHERE a = COALESCE('8', a) LIMIT 3150} {8}151 152do_execsql_test 3.2 {153 SELECT * FROM y1 WHERE a = '2' LIMIT 3154} {2}155 156load_static_extension db series157do_execsql_test 3.3 {158 SELECT * FROM generate_series(1, 5) WHERE value = (value & 14) LIMIT 3159} {2 4}160 161do_execsql_test 3.4 {162 SELECT value FROM generate_series(1,10) WHERE value>2 LIMIT 4 OFFSET 1;163} {4 5 6 7}164 165set ::do_not_use_limit 1166do_execsql_test 3.5 {167 SELECT * FROM y1 LIMIT 5 OFFSET 3168} {4 5 6 7 8}169unset ::do_not_use_limit170set ::do_not_use_offset 1171do_execsql_test 3.6 {172 SELECT * FROM y1 LIMIT 5 OFFSET 3173} {4 5 6 7 8}174unset ::do_not_use_offset175 176#-------------------------------------------------------------------------177reset_db178proc vtab_command {lVal method args} {179 switch -- $method {180 xConnect { error "not happy!" }181 }182 183 return {}184}185 186register_tcl_module db187do_catchsql_test 4.0 {188 CREATE VIRTUAL TABLE y1 USING tcl(vtab_command 1);189} {1 {not happy!}}190do_test 4.1 {191 sqlite3_errcode db192} SQLITE_ERROR193 194proc vtab_command {lVal method args} {195 switch -- $method {196 xConnect {197 return $lVal198 }199 }200 return {}201}202 203do_catchsql_test 4.2 {204 CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "PRAGMA page_size=1024");205} {1 {declare_vtab: syntax error}}206do_catchsql_test 4.3 {207 CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "CREATE TABLE x1(");208} {1 {declare_vtab: incomplete input}}209do_catchsql_test 4.4 {210 CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "CREATE TABLE x1(insert)");211} {1 {declare_vtab: near "insert": syntax error}}212 213#-------------------------------------------------------------------------214reset_db215register_tcl_module db216 217proc quote {str} {218 return "'[string map {' ''} $str]'"219}220 221proc vtab_command {lVal method args} {222 switch -- $method {223 xConnect {224 return "CREATE TABLE t1(a, b, c, d)"225 }226 227 xBestIndex {228 set hdl [lindex $args 0]229 set clist [$hdl constraints]230 231 set res [list]232 set idx 0233 set idxnum 0234 235 set cols(0) a236 set cols(1) b237 set cols(2) c238 239 set lCons [list]240 241 foreach c $clist {242 array set a $c243 if {$a(usable)==0} continue244 245 if {($a(op)=="eq" || $a(op)=="is") && [info exists cols($a(column))]} {246 lappend res omit $idx247 set coll [$hdl collation $idx]248 lappend lCons "$cols($a(column)) = %[llength $lCons]% COLLATE $coll"249 250 set idxnum [expr {$idx + (1 << $a(column))}]251 catch { unset cols($a(column)) }252 }253 254 incr idx255 }256 257 if {[llength [array names cols]]>0} {258 set missing [list]259 for {set i 0} {$i < 3} {incr i} {260 catch { lappend missing $cols($i) }261 }262 set msg "missing required constraints: [join $missing ,]"263 return [list constraint $msg]264 }265 266 set idxstr [join $lCons " AND "]267 return "cost 1000 rows 1000 idxnum $idxnum $res idxstr {$idxstr}"268 }269 270 xFilter {271 foreach {idxnum idxstr lArg} $args {}272 set i 0273 set where $idxstr274 foreach a $lArg {275 set where [string map [list %$i% [quote $a]] $where]276 incr i277 }278 # puts $where279 return [list sql "SELECT rowid, * FROM $lVal WHERE $where"]280 }281 }282 283 return {}284}285 286do_execsql_test 5.1 {287 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command t1);288 CREATE TABLE t1(a, b, c, d);289}290 291foreach {tn where ok} {292 0 "WHERE a=? AND b=? AND c=? AND c=?" 1293 1 "WHERE a=? AND b=? AND c=?" 1294 2 "WHERE a=? AND b=? AND (c=? OR c=?)" 1295 3 "WHERE a=? AND b=? AND (c=? OR c=? OR c=?)" 1296 4 "WHERE a=? AND b=? AND (c IS ? OR c IS ?)" 1297 5 "WHERE a=? AND ((b=? AND c=?) OR (c=? AND b=?))" 1298 6 "WHERE a=? AND ((b=? AND c=?) OR (c=?))" 0299} {300 do_test 5.2.$tn {301 catch { execsql "SELECT * FROM x1 $::where" } msg302# if {$tn==0 || $tn==2 || $tn==3} { puts "MSG: $msg" }303 } [expr !$ok]304}305 306do_execsql_test 5.3 {307 SELECT * FROM x1 WHERE (a, b, c) = (?, ?, ?);308}309 310do_execsql_test 5.4 {311 INSERT INTO t1(rowid, a, b, c, d) VALUES(1, 'x', 'y', 'z', 'one');312 INSERT INTO t1(rowid, a, b, c, d) VALUES(2, 'X', 'Y', 'Z', 'two');313 SELECT * FROM x1 WHERE (a, b, c) = ('X', 'Y', 'Z');314} {X Y Z two}315do_execsql_test 5.5 {316 SELECT * FROM x1 WHERE a='x' AND b='y' AND c='z';317} {x y z one}318do_execsql_test 5.6 {319 SELECT * FROM x1 320 WHERE a='x' COLLATE nocase AND b='y' COLLATE nocase AND c='z'COLLATE nocase;321} {x y z one X Y Z two}322 323do_execsql_test 5.7 {324 DELETE FROM t1;325 326 INSERT INTO t1(rowid, a, b, c, d) VALUES(0, 'x', 'y', 'z', 'zero');327 INSERT INTO t1(rowid, a, b, c, d) VALUES(1, 'x', 'y', 'Z', 'one');328 INSERT INTO t1(rowid, a, b, c, d) VALUES(2, 'x', 'Y', 'z', 'two');329 INSERT INTO t1(rowid, a, b, c, d) VALUES(3, 'x', 'Y', 'Z', 'three');330 INSERT INTO t1(rowid, a, b, c, d) VALUES(4, 'X', 'y', 'z', 'four');331 INSERT INTO t1(rowid, a, b, c, d) VALUES(5, 'X', 'y', 'Z', 'five');332 INSERT INTO t1(rowid, a, b, c, d) VALUES(6, 'X', 'Y', 'z', 'six');333 INSERT INTO t1(rowid, a, b, c, d) VALUES(7, 'X', 'Y', 'z', 'seven');334}335 336do_execsql_test 5.8 {337 SELECT d FROM x1 338 WHERE a='x' AND ((b='y' AND c='z') OR (b='Y' AND c='z' COLLATE nocase))339} {340 zero two three341}342 343do_execsql_test 5.9 {344 SELECT d FROM x1 345 WHERE a='x' COLLATE nocase 346 AND ((b='y' AND c='z') OR (b='Y' AND c='z' COLLATE nocase))347} {348 zero four two349 three six seven350}351 352#--------------------------------------------------------------------------353 354reset_db355register_tcl_module db356 357proc quote {str} {358 return "'[string map {' ''} $str]'"359}360 361proc vtab_command {lVal method args} {362 switch -- $method {363 xConnect {364 return "CREATE TABLE t1(a, b, c, d)"365 }366 367 xBestIndex {368 set hdl [lindex $args 0]369 set clist [$hdl constraints]370 371 set idx 0372 set idxnum 0373 374 foreach c $clist {375 array set a $c376 if {$a(usable)==0} continue377 378 if {$a(op)=="limit"} {379 set idxnum [$hdl rhs_value $idx 555]380 }381 382 incr idx383 }384 385 return "cost 1000 rows 1000 idxnum $idxnum"386 387 }388 389 xFilter {390 foreach {idxnum idxstr lArg} $args {}391 return [list sql "SELECT 0, $idxnum, $idxnum, $idxnum, $idxnum"]392 }393 }394 395 return {}396}397 398do_execsql_test 6.0 {399 CREATE TABLE t1(x, y);400 INSERT INTO t1 VALUES(2, 2);401 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command t1);402}403 404do_execsql_test 6.1 { SELECT * FROM x1 LIMIT 50 } {50 50 50 50}405 406do_execsql_test 6.2 { SELECT * FROM x1 WHERE b=c LIMIT 5 } {0 0 0 0}407 408do_execsql_test 6.3 { 409 SELECT (SELECT a FROM x1 WHERE t1.x=t1.y LIMIT 10) FROM t1410} {0}411 412do_execsql_test 6.4 { 413 SELECT (SELECT a FROM x1 WHERE x1.a=1) FROM t1414} {1}415 416do_execsql_test 6.5 { 417 SELECT (SELECT a FROM x1 WHERE x1.a=1 LIMIT 1) FROM t1418} {1}419 420do_execsql_test 6.6 { 421 SELECT (SELECT a FROM x1 WHERE x1.a=555 LIMIT 2) FROM t1422} {555}423 424finish_test425 426 427