AryaWu/sqlite
0
1# 2020-01-292#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 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15set testprefix bestindex816 17ifcapable !vtab {18 finish_test19 return20}21 22register_tcl_module db23 24proc vtab_command {src method args} {25 switch -- $method {26 xConnect {27 return "CREATE TABLE xxx(a, b)"28 }29 30 xBestIndex {31 set hdl [lindex $args 0]32 set clist [$hdl constraints]33 set orderby [$hdl orderby]34 lappend ::lBestIndexDistinct [$hdl distinct]35 36 #puts "ORDERBY: $orderby" 37 set iCons 038 set ret [list]39 foreach cons $clist {40 catch { array unset C }41 array set C $cons42 if {$C(usable)} {43 lappend ret use $iCons44 }45 incr iCons46 }47 if {$orderby=="{column 0 desc 0} {column 1 desc 0}"48 || $orderby=="{column 0 desc 0}"49 } {50 lappend ret orderby 151 lappend ret idxnum 152 set ::lOrderByConsumed 153 }54 return $ret55 }56 57 xFilter {58 set idxnum [lindex $args 0]59 if {$idxnum} {60 return [list sql "SELECT rowid, a, b FROM $src order by 2, 3"]61 }62 return [list sql "SELECT rowid, a, b FROM $src"]63 }64 65 }66 67 return {}68}69 70do_execsql_test 1.0 {71 CREATE TABLE t1(a, b);72 CREATE INDEX i1 ON t1(a, b);73 INSERT INTO t1 VALUES('a', 'b'), ('c', 'd');74 INSERT INTO t1 VALUES('a', 'b'), ('c', 'd');75 CREATE VIRTUAL TABLE vt1 USING tcl(vtab_command t1);76 77 CREATE TABLE t0(c0);78 INSERT INTO t0(c0) VALUES (1), (0);79}80 81foreach {tn sql bDistinct idxinsert bConsumed res} {82 1 "SELECT a, b FROM vt1" 0 0 0 {a b c d a b c d}83 2 "SELECT DISTINCT a, b FROM vt1" 2 1 1 {a b c d}84 3 "SELECT DISTINCT a FROM vt1" 2 1 1 {a c}85 4 "SELECT DISTINCT b FROM vt1" 2 1 0 {b d}86 5 "SELECT DISTINCT b FROM vt1 ORDER BY a" 0 1 1 {b d}87 6 "SELECT DISTINCT t0.c0 FROM vt1, t0 ORDER BY vt1.a" 0 1 1 {1 0}88 7 "SELECT DISTINCT a, b FROM vt1 ORDER BY a, b" 3 0 1 {a b c d}89 8 "SELECT DISTINCT a, b FROM vt1 ORDER BY a" 0 1 1 {a b c d}90 9 "SELECT DISTINCT a FROM vt1 ORDER BY a, b" 0 1 1 {a c}91 92 10 "SELECT DISTINCT a, b FROM vt1 WHERE b='b'" 2 1 1 {a b}93 11 "SELECT DISTINCT a, b FROM vt1 WHERE +b='b'" 2 1 1 {a b}94} {95 set ::lBestIndexDistinct ""96 set ::lOrderByConsumed 097 do_execsql_test 1.$tn.1 $sql $res98 do_test 1.$tn.2 {99 set ::lBestIndexDistinct100 } $bDistinct101 do_test 1.$tn.3 {102 expr {[lsearch [execsql "explain $sql"] IdxInsert]>=0}103 } $idxinsert104 do_test 1.$tn.4 {105 set ::lOrderByConsumed106 } $bConsumed107}108 109#-------------------------------------------------------------------------110reset_db111register_tcl_module db112 113proc vtab_command {src method args} {114 switch -- $method {115 xConnect {116 return "CREATE TABLE xxx(a, b)"117 }118 119 xBestIndex {120 set hdl [lindex $args 0]121 set ret [list]122 123 set iCons 0124 foreach cons [$hdl constraints] {125 array set C $cons126 if {($C(op)=="limit" || $C(op)=="offset") && $C(usable)} {127 lappend ret use $iCons128 }129 incr iCons130 }131 132 return $ret133 }134 135 xFilter {136 lappend ::lFilterArgs [lindex $args 2]137 return [list sql "SELECT rowid, a, b FROM $src"]138 }139 140 }141 142 return {}143}144 145do_execsql_test 2.0 {146 CREATE TABLE t1(a, b);147 CREATE INDEX i1 ON t1(a, b);148 CREATE VIRTUAL TABLE vt1 USING tcl(vtab_command t1);149}150 151do_test 2.1 {152 set ::lFilterArgs [list]153 execsql { SELECT * FROM vt1 LIMIT 10 }154 set ::lFilterArgs155} {10}156 157do_test 2.2 {158 set ::lFilterArgs [list]159 execsql { SELECT * FROM vt1 LIMIT 5 OFFSET 50 }160 set ::lFilterArgs161} {{50 5}}162 163do_test 2.3 {164 set ::lFilterArgs [list]165 execsql { SELECT * FROM vt1 ORDER BY a, b LIMIT 1 OFFSET 1 }166 set ::lFilterArgs167} {{1 1}}168 169do_test 2.4 {170 set ::lFilterArgs [list]171 execsql { SELECT * FROM vt1 ORDER BY a, +b LIMIT 1 OFFSET 1 }172 set ::lFilterArgs173} {{}}174 175#-------------------------------------------------------------------------176reset_db177register_tcl_module db178 179proc vtab_command {src method args} {180 switch -- $method {181 xConnect {182 return "CREATE TABLE xxx(a, b)"183 }184 185 xBestIndex {186 set hdl [lindex $args 0]187 set lCons [$hdl constraints]188 189 set ret [list]190 for {set i 0} {$i < [llength $lCons]} {incr i} {191 array set C [lindex $lCons $i]192 if {$C(usable)} {193 lappend ret use $i194 $hdl in $i 1195 }196 }197 return $ret198 }199 200 xFilter {201 set lArg [lindex $args 2]202 lappend ::lFilterArg {*}$lArg203 return [list sql "SELECT rowid, a, b FROM $src"]204 }205 206 }207 208 return {}209}210 211do_execsql_test 3.0 {212 CREATE TABLE t1(a, b);213 CREATE INDEX i1 ON t1(a, b);214 CREATE VIRTUAL TABLE vt1 USING tcl(vtab_command t1);215}216 217foreach {tn sql lfa} {218 1 "SELECT * FROM vt1 WHERE b IN (10, 20, 30)" {{10 20 30}}219 2 "SELECT * FROM vt1 WHERE b IN ('abc', 'def')" {{abc def}}220 3 "SELECT * FROM vt1 WHERE a IS NULL AND b IN ('abc', 'def')" {{} {abc def}}221 4 "SELECT * FROM vt1 WHERE a IN (1,2,3) AND b IN ('abc', 'def')" 222 {{1 2 3} {abc def}}223 224 5 "SELECT * FROM vt1 225 WHERE a IN (SELECT 1 UNION SELECT 2) AND b IN ('abc', 'def')"226 {{1 2} {abc def}}227 228 6 "SELECT * FROM vt1 229 WHERE b IN ('abc', 'def') AND a IN (SELECT 1 UNION SELECT 2)"230 {{abc def} {1 2}}231} {232 do_test 3.$tn {233 set ::lFilterArg [list]234 execsql $sql235 set ::lFilterArg236 } $lfa237}238 239#explain_i { SELECT * FROM vt1 WHERE b IN (10, 20, 30) }240 241#-------------------------------------------------------------------------242reset_db243register_tcl_module db244 245proc vtab_command {src method args} {246 switch -- $method {247 xConnect {248 return "CREATE TABLE xxx(a, b, c)"249 }250 251 xBestIndex {252 set hdl [lindex $args 0]253 set lCons [$hdl constraints]254 255 set ret [list]256 for {set i 0} {$i < [llength $lCons]} {incr i} {257 lappend ::lBestIndexRhs [$hdl rhs_value $i -]258 }259 return $ret260 }261 262 xFilter {263 return [list sql "SELECT rowid, a, b, c FROM $src"]264 }265 266 }267 268 return {}269}270 271do_execsql_test 4.0 {272 CREATE TABLE t1(a, b, c);273 CREATE VIRTUAL TABLE vt1 USING tcl(vtab_command t1);274}275 276foreach {tn sql lbir} {277 1 "SELECT * FROM vt1 WHERE b = 10" {10}278 2 "SELECT * FROM vt1 WHERE a = 'abc' AND b < 30" {abc 30}279 3 "SELECT * FROM vt1 WHERE a = 'abc' AND b < 30+2" {abc -}280 4 "SELECT * FROM vt1 WHERE a IN (1,2,3) AND b < 30+2" {- -}281 5 "SELECT * FROM vt1 WHERE a IS 111 AND b < 30+2" {111 -}282} {283 do_test 4.$tn {284 set ::lBestIndexRhs [list]285 execsql $sql286 set ::lBestIndexRhs287 } $lbir288}289 290#-------------------------------------------------------------------------291reset_db292db cache size 0293register_tcl_module db294 295set ::vtab_handle_in 1296proc vtab_command {src method args} {297 switch -- $method {298 xConnect {299 return "CREATE TABLE xxx(a, b, c)"300 }301 302 xBestIndex {303 set lCols [list a b c]304 305 set hdl [lindex $args 0]306 set lCons [$hdl constraints]307 set lOrder [$hdl order]308 309 set L ""310 set O ""311 set W [list]312 set a 0313 for {set i 0} {$i < [llength $lCons]} {incr i} {314 array set C [lindex $lCons $i]315 if {$C(usable)} {316 if { $C(op)=="eq" } {317 set bIn 0318 if {$::vtab_handle_in} { set bIn [$hdl in $i 1] }319 if {$bIn} {320 lappend W "[lindex $lCols $C(column)] IN (%I$a%)"321 } else {322 lappend W "[lindex $lCols $C(column)] = %$a%"323 }324 lappend ret omit $i325 }326 if { $C(op)=="limit" } { set L " LIMIT %$a%" ; lappend ret use $i }327 if { $C(op)=="offset" } { set O " OFFSET %$a%" ; lappend ret use $i }328 incr a329 }330 }331 332 set order ""333 set selectlist "rowid, a, b, c"334 if {[llength $lOrder]} {335 array set sl [list]336 set lO [list]337 foreach s $lOrder {338 array set C $s339 set ad ""340 if {$C(desc)} { set ad " DESC" }341 lappend lO "[lindex $lCols $C(column)]$ad"342 set sl($C(column)) 1343 }344 if {[$hdl distinct]==2} {345 set selectlist "DISTINCT 0"346 foreach i {0 1 2} {347 if {[info exists sl($i)]} {348 append selectlist ", [lindex $lCols $i]"349 } else {350 append selectlist ", 0"351 }352 }353 } else {354 set order " ORDER BY [join $lO ,]"355 }356 }357 358 set where ""359 if {[llength $W]} { set where " WHERE [join $W { AND }]" }360 set sql "SELECT $selectlist FROM $src$where$order$L$O"361 362 lappend ret idxStr $sql363 return $ret364 }365 366 xFilter {367 foreach {idxnum idxstr lArg} $args {}368 set ii 0369 set sql $idxstr370 foreach a $lArg {371 set sql [string map [list %$ii% $a] $sql]372 set sql [string map [list %I$ii% [join $a ,]] $sql]373 incr ii374 }375 lappend ::lFilterSql $sql376 377 if {[regexp {OFFSET (.*)$} $sql -> off]} {378 set real_sql "379 WITH c(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM c WHERE i<$off )380 SELECT 0,0,0,0 FROM c381 UNION ALL SELECT * FROM (382 $sql383 )384 "385 } else {386 set real_sql $sql387 }388 389 return [list sql $real_sql]390 }391 392 }393 394 return {}395}396 397do_execsql_test 5.0 {398 CREATE TABLE t1(a, b, c);399 CREATE VIRTUAL TABLE vt1 USING tcl(vtab_command t1);400 INSERT INTO t1 VALUES(1, 2, 3);401 INSERT INTO t1 VALUES(2, 3, 4);402 INSERT INTO t1 VALUES(3, 4, 5);403 INSERT INTO t1 VALUES(1, 5, 6);404 INSERT INTO t1 VALUES(2, 6, 7);405 INSERT INTO t1 VALUES(3, 7, 8);406 INSERT INTO t1 VALUES(1, 8, 9);407 INSERT INTO t1 VALUES(2, 9, 0);408}409 410proc do_vtab_test {tn sql vtsql {res {}}} {411 set ::lFilterSql [list]412 uplevel [list do_execsql_test $tn.1 $sql $res]413 uplevel [list do_test $tn.2 {set ::lFilterSql} [list {*}$vtsql]]414}415 416do_vtab_test 5.1.1 {417 SELECT DISTINCT a FROM vt1418} {419 {SELECT DISTINCT 0, a, 0, 0 FROM t1}420} {1 2 3}421 422do_vtab_test 5.1.2 {423 SELECT DISTINCT a FROM vt1 ORDER BY a424} {425 {SELECT rowid, a, b, c FROM t1 ORDER BY a}426} {1 2 3}427 428do_vtab_test 5.1.3 {429 SELECT DISTINCT a FROM vt1 WHERE c IN (4,5,6,7,8)430} {431 {SELECT DISTINCT 0, a, 0, 0 FROM t1 WHERE c IN (4,5,6,7,8)}432} {2 3 1}433 434set ::vtab_handle_in 0435do_vtab_test 5.1.4 {436 SELECT DISTINCT a FROM vt1 WHERE c IN (4,5,6,7,8)437} {438 {SELECT DISTINCT 0, a, 0, 0 FROM t1 WHERE c = 4}439 {SELECT DISTINCT 0, a, 0, 0 FROM t1 WHERE c = 5}440 {SELECT DISTINCT 0, a, 0, 0 FROM t1 WHERE c = 6}441 {SELECT DISTINCT 0, a, 0, 0 FROM t1 WHERE c = 7}442 {SELECT DISTINCT 0, a, 0, 0 FROM t1 WHERE c = 8}443} {2 3 1}444 445set ::vtab_handle_in 1446do_vtab_test 5.1.5a {447 SELECT a, b, c FROM vt1 WHERE c IN (4,5,6,7,8) LIMIT 2 OFFSET 2448} {449 {SELECT rowid, a, b, c FROM t1 WHERE c IN (4,5,6,7,8) LIMIT 2 OFFSET 2}450} {1 5 6 2 6 7}451 452set ::vtab_handle_in 0453do_vtab_test 5.1.5b {454 SELECT a, b, c FROM vt1 WHERE c IN (4,5,6,7,8) LIMIT 2 OFFSET 2455} {456 {SELECT rowid, a, b, c FROM t1 WHERE c = 4}457 {SELECT rowid, a, b, c FROM t1 WHERE c = 5}458 {SELECT rowid, a, b, c FROM t1 WHERE c = 6}459 {SELECT rowid, a, b, c FROM t1 WHERE c = 7}460} {1 5 6 2 6 7}461set ::vtab_handle_in 1462 463finish_test464