| 1 |
# 2024-04-26 |
| 2 |
# |
| 3 |
# The author disclaims copyright to this source code. In place of |
| 4 |
# 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 |
|
| 14 |
set testdir [file dirname $argv0] |
| 15 |
source $testdir/tester.tcl |
| 16 |
set testprefix bestindexC |
| 17 |
|
| 18 |
ifcapable !vtab { |
| 19 |
finish_test |
| 20 |
return |
| 21 |
} |
| 22 |
|
| 23 |
register_tcl_module db |
| 24 |
|
| 25 |
proc 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 0 |
| 40 |
foreach c $clist { |
| 41 |
array set a $c |
| 42 |
if {$a(usable)==0} continue |
| 43 |
if {$a(op)=="limit" && ![info exists ::do_not_use_limit]} { |
| 44 |
lappend idxstr limit |
| 45 |
lappend res omit $idx |
| 46 |
} |
| 47 |
if {$a(op)=="offset" && ![info exists ::do_not_use_offset]} { |
| 48 |
lappend idxstr offset |
| 49 |
lappend res omit $idx |
| 50 |
} |
| 51 |
incr idx |
| 52 |
} |
| 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) $b |
| 62 |
} |
| 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 1 |
| 69 |
foreach v $lVal { |
| 70 |
lappend lRow "($idx, '$v')" |
| 71 |
incr idx |
| 72 |
} |
| 73 |
|
| 74 |
return [list sql " |
| 75 |
SELECT * FROM ( VALUES [join $lRow ,]) $LIMIT |
| 76 |
"] |
| 77 |
} |
| 78 |
} |
| 79 |
|
| 80 |
return {} |
| 81 |
} |
| 82 |
|
| 83 |
do_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 |
|
| 88 |
do_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 |
|
| 102 |
foreach {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_unionall |
| 111 |
"UNION" t_union |
| 112 |
"INTERSECT" t_intersect |
| 113 |
"EXCEPT" t_except |
| 114 |
} { |
| 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 x2 |
| 119 |
) $limit" $expect |
| 120 |
|
| 121 |
} |
| 122 |
|
| 123 |
} |
| 124 |
|
| 125 |
#------------------------------------------------------------------------- |
| 126 |
reset_db |
| 127 |
register_tcl_module db |
| 128 |
|
| 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"); |
| 132 |
} {} |
| 133 |
|
| 134 |
do_execsql_test 2.1 { |
| 135 |
SELECT * FROM x1 |
| 136 |
EXCEPT |
| 137 |
SELECT * FROM x2 |
| 138 |
LIMIT 3 |
| 139 |
} {c d} |
| 140 |
|
| 141 |
#------------------------------------------------------------------------- |
| 142 |
reset_db |
| 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"); |
| 146 |
} {} |
| 147 |
|
| 148 |
do_execsql_test 3.1 { |
| 149 |
SELECT * FROM y1 WHERE a = COALESCE('8', a) LIMIT 3 |
| 150 |
} {8} |
| 151 |
|
| 152 |
do_execsql_test 3.2 { |
| 153 |
SELECT * FROM y1 WHERE a = '2' LIMIT 3 |
| 154 |
} {2} |
| 155 |
|
| 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 |
| 159 |
} {2 4} |
| 160 |
|
| 161 |
do_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 |
|
| 165 |
set ::do_not_use_limit 1 |
| 166 |
do_execsql_test 3.5 { |
| 167 |
SELECT * FROM y1 LIMIT 5 OFFSET 3 |
| 168 |
} {4 5 6 7 8} |
| 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 |
| 173 |
} {4 5 6 7 8} |
| 174 |
unset ::do_not_use_offset |
| 175 |
|
| 176 |
#------------------------------------------------------------------------- |
| 177 |
reset_db |
| 178 |
proc vtab_command {lVal method args} { |
| 179 |
switch -- $method { |
| 180 |
xConnect { error "not happy!" } |
| 181 |
} |
| 182 |
|
| 183 |
return {} |
| 184 |
} |
| 185 |
|
| 186 |
register_tcl_module db |
| 187 |
do_catchsql_test 4.0 { |
| 188 |
CREATE VIRTUAL TABLE y1 USING tcl(vtab_command 1); |
| 189 |
} {1 {not happy!}} |
| 190 |
do_test 4.1 { |
| 191 |
sqlite3_errcode db |
| 192 |
} SQLITE_ERROR |
| 193 |
|
| 194 |
proc vtab_command {lVal method args} { |
| 195 |
switch -- $method { |
| 196 |
xConnect { |
| 197 |
return $lVal |
| 198 |
} |
| 199 |
} |
| 200 |
return {} |
| 201 |
} |
| 202 |
|
| 203 |
do_catchsql_test 4.2 { |
| 204 |
CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "PRAGMA page_size=1024"); |
| 205 |
} {1 {declare_vtab: syntax error}} |
| 206 |
do_catchsql_test 4.3 { |
| 207 |
CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "CREATE TABLE x1("); |
| 208 |
} {1 {declare_vtab: incomplete input}} |
| 209 |
do_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 |
#------------------------------------------------------------------------- |
| 214 |
reset_db |
| 215 |
register_tcl_module db |
| 216 |
|
| 217 |
proc quote {str} { |
| 218 |
return "'[string map {' ''} $str]'" |
| 219 |
} |
| 220 |
|
| 221 |
proc 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 0 |
| 233 |
set idxnum 0 |
| 234 |
|
| 235 |
set cols(0) a |
| 236 |
set cols(1) b |
| 237 |
set cols(2) c |
| 238 |
|
| 239 |
set lCons [list] |
| 240 |
|
| 241 |
foreach c $clist { |
| 242 |
array set a $c |
| 243 |
if {$a(usable)==0} continue |
| 244 |
|
| 245 |
if {($a(op)=="eq" || $a(op)=="is") && [info exists cols($a(column))]} { |
| 246 |
lappend res omit $idx |
| 247 |
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 idx |
| 255 |
} |
| 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 0 |
| 273 |
set where $idxstr |
| 274 |
foreach a $lArg { |
| 275 |
set where [string map [list %$i% [quote $a]] $where] |
| 276 |
incr i |
| 277 |
} |
| 278 |
# puts $where |
| 279 |
return [list sql "SELECT rowid, * FROM $lVal WHERE $where"] |
| 280 |
} |
| 281 |
} |
| 282 |
|
| 283 |
return {} |
| 284 |
} |
| 285 |
|
| 286 |
do_execsql_test 5.1 { |
| 287 |
CREATE VIRTUAL TABLE x1 USING tcl(vtab_command t1); |
| 288 |
CREATE TABLE t1(a, b, c, d); |
| 289 |
} |
| 290 |
|
| 291 |
foreach {tn where ok} { |
| 292 |
0 "WHERE a=? AND b=? AND c=? AND c=?" 1 |
| 293 |
1 "WHERE a=? AND b=? AND c=?" 1 |
| 294 |
2 "WHERE a=? AND b=? AND (c=? OR c=?)" 1 |
| 295 |
3 "WHERE a=? AND b=? AND (c=? OR c=? OR c=?)" 1 |
| 296 |
4 "WHERE a=? AND b=? AND (c IS ? OR c IS ?)" 1 |
| 297 |
5 "WHERE a=? AND ((b=? AND c=?) OR (c=? AND b=?))" 1 |
| 298 |
6 "WHERE a=? AND ((b=? AND c=?) OR (c=?))" 0 |
| 299 |
} { |
| 300 |
do_test 5.2.$tn { |
| 301 |
catch { execsql "SELECT * FROM x1 $::where" } msg |
| 302 |
# if {$tn==0 || $tn==2 || $tn==3} { puts "MSG: $msg" } |
| 303 |
} [expr !$ok] |
| 304 |
} |
| 305 |
|
| 306 |
do_execsql_test 5.3 { |
| 307 |
SELECT * FROM x1 WHERE (a, b, c) = (?, ?, ?); |
| 308 |
} |
| 309 |
|
| 310 |
do_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} |
| 315 |
do_execsql_test 5.5 { |
| 316 |
SELECT * FROM x1 WHERE a='x' AND b='y' AND c='z'; |
| 317 |
} {x y z one} |
| 318 |
do_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 |
|
| 323 |
do_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 |
|
| 336 |
|
| 337 |
if {1} { |
| 338 |
# Since 2026-05-18, SQLite does not attempt the OR-optimization on |
| 339 |
# conjuncts that contain COLLATE operators, due to |
| 340 |
# bug report 2026-05-17T11:46:40Z. |
| 341 |
do_catchsql_test 5.8 { |
| 342 |
SELECT d FROM x1 |
| 343 |
WHERE a='x' AND ((b='y' AND c='z') OR (b='Y' AND c='z' COLLATE nocase)) |
| 344 |
} {1 {no query solution}} |
| 345 |
|
| 346 |
do_catchsql_test 5.9 { |
| 347 |
SELECT d FROM x1 |
| 348 |
WHERE a='x' COLLATE nocase |
| 349 |
AND ((b='y' AND c='z') OR (b='Y' AND c='z' COLLATE nocase)) |
| 350 |
} {1 {no query solution}} |
| 351 |
} else { |
| 352 |
# This case would work if SQLite knew how to deal with conjuncts that |
| 353 |
# contain COLLATE operators. |
| 354 |
do_execsql_test 5.8 { |
| 355 |
SELECT d FROM x1 |
| 356 |
WHERE a='x' AND ((b='y' AND c='z') OR (b='Y' AND c='z' COLLATE nocase)) |
| 357 |
} { |
| 358 |
zero two three |
| 359 |
} |
| 360 |
|
| 361 |
do_execsql_test 5.9 { |
| 362 |
SELECT d FROM x1 |
| 363 |
WHERE a='x' COLLATE nocase |
| 364 |
AND ((b='y' AND c='z') OR (b='Y' AND c='z' COLLATE nocase)) |
| 365 |
} { |
| 366 |
zero four two |
| 367 |
three six seven |
| 368 |
} |
| 369 |
} |
| 370 |
|
| 371 |
#-------------------------------------------------------------------------- |
| 372 |
|
| 373 |
reset_db |
| 374 |
register_tcl_module db |
| 375 |
|
| 376 |
proc quote {str} { |
| 377 |
return "'[string map {' ''} $str]'" |
| 378 |
} |
| 379 |
|
| 380 |
proc vtab_command {lVal method args} { |
| 381 |
switch -- $method { |
| 382 |
xConnect { |
| 383 |
return "CREATE TABLE t1(a, b, c, d)" |
| 384 |
} |
| 385 |
|
| 386 |
xBestIndex { |
| 387 |
set hdl [lindex $args 0] |
| 388 |
set clist [$hdl constraints] |
| 389 |
|
| 390 |
set idx 0 |
| 391 |
set idxnum 0 |
| 392 |
|
| 393 |
foreach c $clist { |
| 394 |
array set a $c |
| 395 |
if {$a(usable)==0} continue |
| 396 |
|
| 397 |
if {$a(op)=="limit"} { |
| 398 |
set idxnum [$hdl rhs_value $idx 555] |
| 399 |
} |
| 400 |
|
| 401 |
incr idx |
| 402 |
} |
| 403 |
|
| 404 |
return "cost 1000 rows 1000 idxnum $idxnum" |
| 405 |
|
| 406 |
} |
| 407 |
|
| 408 |
xFilter { |
| 409 |
foreach {idxnum idxstr lArg} $args {} |
| 410 |
return [list sql "SELECT 0, $idxnum, $idxnum, $idxnum, $idxnum"] |
| 411 |
} |
| 412 |
} |
| 413 |
|
| 414 |
return {} |
| 415 |
} |
| 416 |
|
| 417 |
do_execsql_test 6.0 { |
| 418 |
CREATE TABLE t1(x, y); |
| 419 |
INSERT INTO t1 VALUES(2, 2); |
| 420 |
CREATE VIRTUAL TABLE x1 USING tcl(vtab_command t1); |
| 421 |
} |
| 422 |
|
| 423 |
do_execsql_test 6.1 { SELECT * FROM x1 LIMIT 50 } {50 50 50 50} |
| 424 |
|
| 425 |
do_execsql_test 6.2 { SELECT * FROM x1 WHERE b=c LIMIT 5 } {0 0 0 0} |
| 426 |
|
| 427 |
do_execsql_test 6.3 { |
| 428 |
SELECT (SELECT a FROM x1 WHERE t1.x=t1.y LIMIT 10) FROM t1 |
| 429 |
} {0} |
| 430 |
|
| 431 |
do_execsql_test 6.4 { |
| 432 |
SELECT (SELECT a FROM x1 WHERE x1.a=1) FROM t1 |
| 433 |
} {1} |
| 434 |
|
| 435 |
do_execsql_test 6.5 { |
| 436 |
SELECT (SELECT a FROM x1 WHERE x1.a=1 LIMIT 1) FROM t1 |
| 437 |
} {1} |
| 438 |
|
| 439 |
do_execsql_test 6.6 { |
| 440 |
SELECT (SELECT a FROM x1 WHERE x1.a=555 LIMIT 2) FROM t1 |
| 441 |
} {555} |
| 442 |
|
| 443 |
finish_test |