gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/like.test (about) 1 # 2005 August 13 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 # This file implements regression tests for SQLite library. The 12 # focus of this file is testing the LIKE and GLOB operators and 13 # in particular the optimizations that occur to help those operators 14 # run faster. 15 # 16 # $Id: like.test,v 1.13 2009/06/07 23:45:11 drh Exp $ 17 18 set testdir [file dirname $argv0] 19 source $testdir/tester.tcl 20 set testprefix like 21 22 # Create some sample data to work with. 23 # 24 do_test like-1.0 { 25 execsql { 26 CREATE TABLE t1(x TEXT); 27 } 28 foreach str { 29 a 30 ab 31 abc 32 abcd 33 34 acd 35 abd 36 bc 37 bcd 38 39 xyz 40 ABC 41 CDE 42 {ABC abc xyz} 43 } { 44 db eval {INSERT INTO t1 VALUES(:str)} 45 } 46 execsql { 47 SELECT count(*) FROM t1; 48 } 49 } {12} 50 51 # Test that both case sensitive and insensitive version of LIKE work. 52 # 53 do_test like-1.1 { 54 execsql { 55 SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; 56 } 57 } {ABC abc} 58 do_test like-1.2 { 59 execsql { 60 SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1; 61 } 62 } {abc} 63 do_test like-1.3 { 64 execsql { 65 SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1; 66 } 67 } {ABC abc} 68 do_test like-1.4 { 69 execsql { 70 SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1; 71 } 72 } {ABC abc} 73 do_test like-1.5.1 { 74 # Use sqlite3_exec() to verify fix for ticket [25ee81271091] 2011-06-26 75 sqlite3_exec db {PRAGMA case_sensitive_like=on} 76 } {0 {}} 77 do_test like-1.5.2 { 78 execsql { 79 SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; 80 } 81 } {abc} 82 do_test like-1.5.3 { 83 execsql { 84 PRAGMA case_sensitive_like; -- no argument; does not change setting 85 SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; 86 } 87 } {abc} 88 do_test like-1.6 { 89 execsql { 90 SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1; 91 } 92 } {abc} 93 do_test like-1.7 { 94 execsql { 95 SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1; 96 } 97 } {ABC} 98 do_test like-1.8 { 99 execsql { 100 SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1; 101 } 102 } {} 103 do_test like-1.9 { 104 execsql { 105 PRAGMA case_sensitive_like=off; 106 SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; 107 } 108 } {ABC abc} 109 do_test like-1.10 { 110 execsql { 111 PRAGMA case_sensitive_like; -- No argument, does not change setting. 112 SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; 113 } 114 } {ABC abc} 115 116 # Tests of the REGEXP operator 117 # 118 do_test like-2.1 { 119 proc test_regexp {a b} { 120 return [regexp $a $b] 121 } 122 db function regexp -argcount 2 test_regexp 123 execsql { 124 SELECT x FROM t1 WHERE x REGEXP 'abc' ORDER BY 1; 125 } 126 } {{ABC abc xyz} abc abcd} 127 do_test like-2.2 { 128 execsql { 129 SELECT x FROM t1 WHERE x REGEXP '^abc' ORDER BY 1; 130 } 131 } {abc abcd} 132 133 # Tests of the MATCH operator 134 # 135 do_test like-2.3 { 136 proc test_match {a b} { 137 return [string match $a $b] 138 } 139 db function match -argcount 2 test_match 140 execsql { 141 SELECT x FROM t1 WHERE x MATCH '*abc*' ORDER BY 1; 142 } 143 } {{ABC abc xyz} abc abcd} 144 do_test like-2.4 { 145 execsql { 146 SELECT x FROM t1 WHERE x MATCH 'abc*' ORDER BY 1; 147 } 148 } {abc abcd} 149 150 # For the remaining tests, we need to have the like optimizations 151 # enabled. 152 # 153 ifcapable !like_opt { 154 finish_test 155 return 156 } 157 158 # This procedure executes the SQL. Then it appends to the result the 159 # "sort" or "nosort" keyword (as in the cksort procedure above) then 160 # it appends the names of the table and index used. 161 # 162 proc queryplan {sql} { 163 set ::sqlite_sort_count 0 164 db cache flush 165 set data [execsql $sql] 166 if {$::sqlite_sort_count} {set x sort} {set x nosort} 167 lappend data $x 168 set eqp [execsql "EXPLAIN QUERY PLAN $sql"] 169 # puts eqp=$eqp 170 foreach {a b c x} $eqp { 171 if {[regexp {(SCAN|SEARCH) (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\y} \ 172 $x all ss as tab idx]} { 173 lappend data {} $idx 174 } elseif {[regexp {(SCAN|SEARCH) (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \ 175 $x all ss as tab idx]} { 176 lappend data $tab $idx 177 } elseif {[regexp {(SCAN|SEARCH) (\w+ AS )?(\w+)\y} $x all ss as tab]} { 178 lappend data $tab * 179 } 180 } 181 return $data 182 } 183 184 # Perform tests on the like optimization. 185 # 186 # With no index on t1.x and with case sensitivity turned off, no optimization 187 # is performed. 188 # 189 do_test like-3.1 { 190 set sqlite_like_count 0 191 queryplan { 192 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; 193 } 194 } {ABC {ABC abc xyz} abc abcd sort t1 *} 195 do_test like-3.2 { 196 set sqlite_like_count 197 } {12} 198 199 # With an index on t1.x and case sensitivity on, optimize completely. 200 # 201 do_test like-3.3.100 { 202 set sqlite_like_count 0 203 execsql { 204 PRAGMA case_sensitive_like=on; 205 CREATE INDEX i1 ON t1(x); 206 } 207 queryplan { 208 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; 209 } 210 } {abc abcd nosort {} i1} 211 do_test like-3.3.100.cnt { 212 set sqlite_like_count 213 } 0 214 215 # The like optimization works even when the pattern is a bound parameter 216 # 217 # Exception: It does not work if sqlite3_prepare() is used instead of 218 # sqlite3_prepare_v2(), as in that case the statement cannot be reprepared 219 # after the parameter is bound. 220 # 221 unset -nocomplain ::likepat 222 set ::likepat abc% 223 if {[permutation]!="prepare"} { 224 do_test like-3.3.102 { 225 set sqlite_like_count 0 226 queryplan { 227 SELECT x FROM t1 WHERE x LIKE $::likepat ORDER BY 1; 228 } 229 } {abc abcd nosort {} i1} 230 do_test like-3.3.103 { 231 set sqlite_like_count 232 } 0 233 } 234 235 # Except, the like optimization does not work for bound parameters if 236 # the query planner stability guarantee is active. 237 # 238 do_test like-3.3.104 { 239 set sqlite_like_count 0 240 sqlite3_db_config db QPSG 1 241 queryplan { 242 SELECT x FROM t1 WHERE x LIKE $::likepat ORDER BY 1; 243 } 244 } {abc abcd nosort {} i1} 245 do_test like-3.3.105 { 246 set sqlite_like_count 247 } 12 248 249 # The query planner stability guarantee does not disrupt explicit patterns 250 # 251 do_test like-3.3.105 { 252 set sqlite_like_count 0 253 queryplan { 254 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; 255 } 256 } {abc abcd nosort {} i1} 257 do_test like-3.3.106 { 258 set sqlite_like_count 259 } 0 260 sqlite3_db_config db QPSG 0 261 262 # The LIKE optimization still works when the RHS is a string with no 263 # wildcard. Ticket [e090183531fc2747] 264 # 265 do_test like-3.4.2 { 266 queryplan { 267 SELECT x FROM t1 WHERE x LIKE 'a' ORDER BY 1; 268 } 269 } {a nosort {} i1} 270 do_test like-3.4.3 { 271 queryplan { 272 SELECT x FROM t1 WHERE x LIKE 'ab' ORDER BY 1; 273 } 274 } {ab nosort {} i1} 275 do_test like-3.4.4 { 276 queryplan { 277 SELECT x FROM t1 WHERE x LIKE 'abcd' ORDER BY 1; 278 } 279 } {abcd nosort {} i1} 280 do_test like-3.4.5 { 281 queryplan { 282 SELECT x FROM t1 WHERE x LIKE 'abcde' ORDER BY 1; 283 } 284 } {nosort {} i1} 285 286 287 # Partial optimization when the pattern does not end in '%' 288 # 289 do_test like-3.5 { 290 set sqlite_like_count 0 291 queryplan { 292 SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1; 293 } 294 } {abc nosort {} i1} 295 do_test like-3.6 { 296 set sqlite_like_count 297 } 6 298 do_test like-3.7 { 299 set sqlite_like_count 0 300 queryplan { 301 SELECT x FROM t1 WHERE x LIKE 'ab%d' ORDER BY 1; 302 } 303 } {abcd abd nosort {} i1} 304 do_test like-3.8 { 305 set sqlite_like_count 306 } 4 307 do_test like-3.9 { 308 set sqlite_like_count 0 309 queryplan { 310 SELECT x FROM t1 WHERE x LIKE 'a_c%' ORDER BY 1; 311 } 312 } {abc abcd nosort {} i1} 313 do_test like-3.10 { 314 set sqlite_like_count 315 } 6 316 317 # No optimization when the pattern begins with a wildcard. 318 # Note that the index is still used but only for sorting. 319 # 320 do_test like-3.11 { 321 set sqlite_like_count 0 322 queryplan { 323 SELECT x FROM t1 WHERE x LIKE '%bcd' ORDER BY 1; 324 } 325 } {abcd bcd nosort {} i1} 326 do_test like-3.12 { 327 set sqlite_like_count 328 } 12 329 330 # No optimization for case insensitive LIKE 331 # 332 do_test like-3.13 { 333 set sqlite_like_count 0 334 db eval {PRAGMA case_sensitive_like=off;} 335 queryplan { 336 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; 337 } 338 } {ABC {ABC abc xyz} abc abcd nosort {} i1} 339 do_test like-3.14 { 340 set sqlite_like_count 341 } 12 342 343 # No optimization without an index. 344 # 345 do_test like-3.15 { 346 set sqlite_like_count 0 347 db eval { 348 PRAGMA case_sensitive_like=on; 349 DROP INDEX i1; 350 } 351 queryplan { 352 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; 353 } 354 } {abc abcd sort t1 *} 355 do_test like-3.16 { 356 set sqlite_like_count 357 } 12 358 359 # No GLOB optimization without an index. 360 # 361 do_test like-3.17 { 362 set sqlite_like_count 0 363 queryplan { 364 SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; 365 } 366 } {abc abcd sort t1 *} 367 do_test like-3.18 { 368 set sqlite_like_count 369 } 12 370 371 # GLOB is optimized regardless of the case_sensitive_like setting. 372 # 373 do_test like-3.19 { 374 set sqlite_like_count 0 375 db eval {CREATE INDEX i1 ON t1(x);} 376 queryplan { 377 SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; 378 } 379 } {abc abcd nosort {} i1} 380 do_test like-3.20 { 381 set sqlite_like_count 382 } 0 383 do_test like-3.21 { 384 set sqlite_like_count 0 385 db eval {PRAGMA case_sensitive_like=on;} 386 queryplan { 387 SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; 388 } 389 } {abc abcd nosort {} i1} 390 do_test like-3.22 { 391 set sqlite_like_count 392 } 0 393 do_test like-3.23 { 394 set sqlite_like_count 0 395 db eval {PRAGMA case_sensitive_like=off;} 396 queryplan { 397 SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1; 398 } 399 } {abd acd nosort {} i1} 400 do_test like-3.24 { 401 set sqlite_like_count 402 } 6 403 404 # GLOB optimization when there is no wildcard. Ticket [e090183531fc2747] 405 # 406 do_test like-3.25 { 407 queryplan { 408 SELECT x FROM t1 WHERE x GLOB 'a' ORDER BY 1; 409 } 410 } {a nosort {} i1} 411 do_test like-3.26 { 412 queryplan { 413 SELECT x FROM t1 WHERE x GLOB 'abcd' ORDER BY 1; 414 } 415 } {abcd nosort {} i1} 416 do_test like-3.27 { 417 queryplan { 418 SELECT x FROM t1 WHERE x GLOB 'abcde' ORDER BY 1; 419 } 420 } {nosort {} i1} 421 422 423 424 # No optimization if the LHS of the LIKE is not a column name or 425 # if the RHS is not a string. 426 # 427 do_test like-4.1 { 428 execsql {PRAGMA case_sensitive_like=on} 429 set sqlite_like_count 0 430 queryplan { 431 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1 432 } 433 } {abc abcd nosort {} i1} 434 do_test like-4.2 { 435 set sqlite_like_count 436 } 0 437 do_test like-4.3 { 438 set sqlite_like_count 0 439 queryplan { 440 SELECT x FROM t1 WHERE +x LIKE 'abc%' ORDER BY 1 441 } 442 } {abc abcd nosort {} i1} 443 do_test like-4.4 { 444 set sqlite_like_count 445 } 12 446 do_test like-4.5 { 447 set sqlite_like_count 0 448 queryplan { 449 SELECT x FROM t1 WHERE x LIKE ('ab' || 'c%') ORDER BY 1 450 } 451 } {abc abcd nosort {} i1} 452 do_test like-4.6 { 453 set sqlite_like_count 454 } 12 455 456 # Collating sequences on the index disable the LIKE optimization. 457 # Or if the NOCASE collating sequence is used, the LIKE optimization 458 # is enabled when case_sensitive_like is OFF. 459 # 460 do_test like-5.1 { 461 execsql {PRAGMA case_sensitive_like=off} 462 set sqlite_like_count 0 463 queryplan { 464 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1 465 } 466 } {ABC {ABC abc xyz} abc abcd nosort {} i1} 467 do_test like-5.2 { 468 set sqlite_like_count 469 } 12 470 do_test like-5.3 { 471 execsql { 472 CREATE TABLE t2(x TEXT COLLATE NOCASE); 473 INSERT INTO t2 SELECT * FROM t1 ORDER BY rowid; 474 CREATE INDEX i2 ON t2(x COLLATE NOCASE); 475 } 476 set sqlite_like_count 0 477 queryplan { 478 SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1 479 } 480 } {abc ABC {ABC abc xyz} abcd nosort {} i2} 481 do_test like-5.4 { 482 set sqlite_like_count 483 } 0 484 do_test like-5.5 { 485 execsql { 486 PRAGMA case_sensitive_like=on; 487 } 488 set sqlite_like_count 0 489 queryplan { 490 SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1 491 } 492 } {abc abcd nosort {} i2} 493 do_test like-5.6 { 494 set sqlite_like_count 495 } 12 496 do_test like-5.7 { 497 execsql { 498 PRAGMA case_sensitive_like=off; 499 } 500 set sqlite_like_count 0 501 queryplan { 502 SELECT x FROM t2 WHERE x GLOB 'abc*' ORDER BY 1 503 } 504 } {abc abcd nosort {} i2} 505 do_test like-5.8 { 506 set sqlite_like_count 507 } 12 508 do_test like-5.11 { 509 execsql {PRAGMA case_sensitive_like=off} 510 set sqlite_like_count 0 511 queryplan { 512 SELECT x FROM t1 WHERE x LIKE 'ABC%' ORDER BY 1 513 } 514 } {ABC {ABC abc xyz} abc abcd nosort {} i1} 515 do_test like-5.12 { 516 set sqlite_like_count 517 } 12 518 do_test like-5.13 { 519 set sqlite_like_count 0 520 queryplan { 521 SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1 522 } 523 } {abc ABC {ABC abc xyz} abcd nosort {} i2} 524 do_test like-5.14 { 525 set sqlite_like_count 526 } 0 527 do_test like-5.15 { 528 execsql { 529 PRAGMA case_sensitive_like=on; 530 } 531 set sqlite_like_count 0 532 queryplan { 533 SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1 534 } 535 } {ABC {ABC abc xyz} nosort {} i2} 536 do_test like-5.16 { 537 set sqlite_like_count 538 } 12 539 do_test like-5.17 { 540 execsql { 541 PRAGMA case_sensitive_like=off; 542 } 543 set sqlite_like_count 0 544 queryplan { 545 SELECT x FROM t2 WHERE x GLOB 'ABC*' ORDER BY 1 546 } 547 } {ABC {ABC abc xyz} nosort {} i2} 548 do_test like-5.18 { 549 set sqlite_like_count 550 } 12 551 552 # Boundary case. The prefix for a LIKE comparison is rounded up 553 # when constructing the comparison. Example: "ab" becomes "ac". 554 # In other words, the last character is increased by one. 555 # 556 # Make sure this happens correctly when the last character is a 557 # "z" and we are doing case-insensitive comparisons. 558 # 559 # Ticket #2959 560 # 561 do_test like-5.21 { 562 execsql { 563 PRAGMA case_sensitive_like=off; 564 INSERT INTO t2 VALUES('ZZ-upper-upper'); 565 INSERT INTO t2 VALUES('zZ-lower-upper'); 566 INSERT INTO t2 VALUES('Zz-upper-lower'); 567 INSERT INTO t2 VALUES('zz-lower-lower'); 568 } 569 queryplan { 570 SELECT x FROM t2 WHERE x LIKE 'zz%'; 571 } 572 } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2} 573 do_test like-5.22 { 574 queryplan { 575 SELECT x FROM t2 WHERE x LIKE 'zZ%'; 576 } 577 } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2} 578 do_test like-5.23 { 579 queryplan { 580 SELECT x FROM t2 WHERE x LIKE 'Zz%'; 581 } 582 } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2} 583 do_test like-5.24 { 584 queryplan { 585 SELECT x FROM t2 WHERE x LIKE 'ZZ%'; 586 } 587 } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2} 588 do_test like-5.25 { 589 db eval { 590 PRAGMA case_sensitive_like=on; 591 CREATE TABLE t3(x TEXT); 592 CREATE INDEX i3 ON t3(x); 593 INSERT INTO t3 VALUES('ZZ-upper-upper'); 594 INSERT INTO t3 VALUES('zZ-lower-upper'); 595 INSERT INTO t3 VALUES('Zz-upper-lower'); 596 INSERT INTO t3 VALUES('zz-lower-lower'); 597 } 598 queryplan { 599 SELECT x FROM t3 WHERE x LIKE 'zz%'; 600 } 601 } {zz-lower-lower nosort {} i3} 602 do_test like-5.26 { 603 queryplan { 604 SELECT x FROM t3 WHERE x LIKE 'zZ%'; 605 } 606 } {zZ-lower-upper nosort {} i3} 607 do_test like-5.27 { 608 queryplan { 609 SELECT x FROM t3 WHERE x LIKE 'Zz%'; 610 } 611 } {Zz-upper-lower nosort {} i3} 612 do_test like-5.28 { 613 queryplan { 614 SELECT x FROM t3 WHERE x LIKE 'ZZ%'; 615 } 616 } {ZZ-upper-upper nosort {} i3} 617 618 619 # ticket #2407 620 # 621 # Make sure the LIKE prefix optimization does not strip off leading 622 # characters of the like pattern that happen to be quote characters. 623 # 624 do_test like-6.1 { 625 foreach x { 'abc 'bcd 'def 'ax } { 626 set x2 '[string map {' ''} $x]' 627 db eval "INSERT INTO t2 VALUES($x2)" 628 } 629 execsql { 630 SELECT * FROM t2 WHERE x LIKE '''a%' 631 } 632 } {'abc 'ax} 633 634 do_test like-7.1 { 635 execsql { 636 SELECT rowid, * FROM t1 WHERE rowid GLOB '1*' ORDER BY rowid; 637 } 638 } {1 a 10 ABC 11 CDE 12 {ABC abc xyz}} 639 640 # ticket #3345. 641 # 642 # Overloading the LIKE function with -1 for the number of arguments 643 # will overload both the 2-argument and the 3-argument LIKE. 644 # 645 do_test like-8.1 { 646 db eval { 647 CREATE TABLE t8(x); 648 INSERT INTO t8 VALUES('abcdef'); 649 INSERT INTO t8 VALUES('ghijkl'); 650 INSERT INTO t8 VALUES('mnopqr'); 651 SELECT 1, x FROM t8 WHERE x LIKE '%h%'; 652 SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x'; 653 } 654 } {1 ghijkl 2 ghijkl} 655 do_test like-8.2 { 656 proc newlike {args} {return 1} ;# Alternative LIKE is always return TRUE 657 db function like newlike ;# Uses -1 for nArg in sqlite3_create_function 658 db cache flush 659 db eval { 660 SELECT 1, x FROM t8 WHERE x LIKE '%h%'; 661 SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x'; 662 } 663 } {1 ghijkl 2 ghijkl} 664 do_test like-8.3 { 665 db function like -argcount 2 newlike 666 db eval { 667 SELECT 1, x FROM t8 WHERE x LIKE '%h%'; 668 SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x'; 669 } 670 } {1 abcdef 1 ghijkl 1 mnopqr 2 ghijkl} 671 do_test like-8.4 { 672 db function like -argcount 3 newlike 673 db eval { 674 SELECT 1, x FROM t8 WHERE x LIKE '%h%'; 675 SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x'; 676 } 677 } {1 abcdef 1 ghijkl 1 mnopqr 2 abcdef 2 ghijkl 2 mnopqr} 678 679 680 ifcapable like_opt&&!icu { 681 # Evaluate SQL. Return the result set followed by the 682 # and the number of full-scan steps. 683 # 684 db close 685 sqlite3 db test.db 686 proc count_steps {sql} { 687 set r [db eval $sql] 688 lappend r scan [db status step] sort [db status sort] 689 } 690 do_test like-9.1 { 691 count_steps { 692 SELECT x FROM t2 WHERE x LIKE 'x%' 693 } 694 } {xyz scan 0 sort 0} 695 do_test like-9.2 { 696 count_steps { 697 SELECT x FROM t2 WHERE x LIKE '_y%' 698 } 699 } {xyz scan 19 sort 0} 700 do_test like-9.3.1 { 701 set res [sqlite3_exec_hex db { 702 SELECT x FROM t2 WHERE x LIKE '%78%25' 703 }] 704 } {0 {x xyz}} 705 ifcapable explain { 706 do_test like-9.3.2 { 707 set res [sqlite3_exec_hex db { 708 EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%78%25' 709 }] 710 regexp {INDEX i2} $res 711 } {1} 712 } 713 do_test like-9.4.1 { 714 sqlite3_exec_hex db {INSERT INTO t2 VALUES('%ffhello')} 715 set res [sqlite3_exec_hex db { 716 SELECT substr(x,2) AS x FROM t2 WHERE +x LIKE '%ff%25' 717 }] 718 } {0 {x hello}} 719 do_test like-9.4.2 { 720 set res [sqlite3_exec_hex db { 721 SELECT substr(x,2) AS x FROM t2 WHERE x LIKE '%ff%25' 722 }] 723 } {0 {x hello}} 724 ifcapable explain { 725 do_test like-9.4.3 { 726 set res [sqlite3_exec_hex db { 727 EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%ff%25' 728 }] 729 regexp {SCAN t2} $res 730 } {1} 731 } 732 do_test like-9.5.1 { 733 set res [sqlite3_exec_hex db { 734 SELECT x FROM t2 WHERE x LIKE '%fe%25' 735 }] 736 } {0 {}} 737 ifcapable explain { 738 do_test like-9.5.2 { 739 set res [sqlite3_exec_hex db { 740 EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%fe%25' 741 }] 742 regexp {INDEX i2} $res 743 } {1} 744 } 745 746 # Do an SQL statement. Append the search count to the end of the result. 747 # 748 proc count sql { 749 set ::sqlite_search_count 0 750 set ::sqlite_like_count 0 751 return [concat [execsql $sql] scan $::sqlite_search_count \ 752 like $::sqlite_like_count] 753 } 754 755 # The LIKE and GLOB optimizations do not work on columns with 756 # affinity other than TEXT. 757 # Ticket #3901 758 # 759 do_test like-10.1 { 760 db close 761 sqlite3 db test.db 762 execsql { 763 CREATE TABLE t10( 764 a INTEGER PRIMARY KEY, 765 b INTEGER COLLATE nocase UNIQUE, 766 c NUMBER COLLATE nocase UNIQUE, 767 d BLOB COLLATE nocase UNIQUE, 768 e COLLATE nocase UNIQUE, 769 f TEXT COLLATE nocase UNIQUE 770 ); 771 INSERT INTO t10 VALUES(1,1,1,1,1,1); 772 INSERT INTO t10 VALUES(12,12,12,12,12,12); 773 INSERT INTO t10 VALUES(123,123,123,123,123,123); 774 INSERT INTO t10 VALUES(234,234,234,234,234,234); 775 INSERT INTO t10 VALUES(345,345,345,345,345,345); 776 INSERT INTO t10 VALUES(45,45,45,45,45,45); 777 } 778 count { 779 SELECT a FROM t10 WHERE b LIKE '12%' ORDER BY +a; 780 } 781 } {12 123 scan 5 like 6} 782 do_test like-10.2 { 783 count { 784 SELECT a FROM t10 WHERE c LIKE '12%' ORDER BY +a; 785 } 786 } {12 123 scan 5 like 6} 787 do_test like-10.3 { 788 count { 789 SELECT a FROM t10 WHERE d LIKE '12%' ORDER BY +a; 790 } 791 } {12 123 scan 5 like 6} 792 do_test like-10.4 { 793 count { 794 SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY +a; 795 } 796 } {12 123 scan 5 like 6} 797 ifcapable like_match_blobs { 798 do_test like-10.5a { 799 count { 800 SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a; 801 } 802 } {12 123 scan 4 like 0} 803 } else { 804 do_test like-10.5b { 805 count { 806 SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a; 807 } 808 } {12 123 scan 3 like 0} 809 } 810 do_test like-10.6 { 811 count { 812 SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY +a; 813 } 814 } {12 123 scan 5 like 6} 815 do_test like-10.10 { 816 execsql { 817 CREATE TABLE t10b( 818 a INTEGER PRIMARY KEY, 819 b INTEGER UNIQUE, 820 c NUMBER UNIQUE, 821 d BLOB UNIQUE, 822 e UNIQUE, 823 f TEXT UNIQUE 824 ); 825 INSERT INTO t10b SELECT * FROM t10; 826 } 827 count { 828 SELECT a FROM t10b WHERE b GLOB '12*' ORDER BY +a; 829 } 830 } {12 123 scan 5 like 6} 831 do_test like-10.11 { 832 count { 833 SELECT a FROM t10b WHERE c GLOB '12*' ORDER BY +a; 834 } 835 } {12 123 scan 5 like 6} 836 do_test like-10.12 { 837 count { 838 SELECT a FROM t10b WHERE d GLOB '12*' ORDER BY +a; 839 } 840 } {12 123 scan 5 like 6} 841 do_test like-10.13 { 842 count { 843 SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY +a; 844 } 845 } {12 123 scan 5 like 6} 846 ifcapable like_match_blobs { 847 do_test like-10.14 { 848 count { 849 SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a; 850 } 851 } {12 123 scan 4 like 0} 852 } else { 853 do_test like-10.14 { 854 count { 855 SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a; 856 } 857 } {12 123 scan 3 like 0} 858 } 859 do_test like-10.15 { 860 count { 861 SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY +a; 862 } 863 } {12 123 scan 5 like 6} 864 } 865 866 # LIKE and GLOB where the default collating sequence is not appropriate 867 # but an index with the appropriate collating sequence exists. 868 # 869 do_test like-11.0 { 870 execsql { 871 CREATE TABLE t11( 872 a INTEGER PRIMARY KEY, 873 b TEXT COLLATE nocase, 874 c TEXT COLLATE binary 875 ); 876 INSERT INTO t11 VALUES(1, 'a','a'); 877 INSERT INTO t11 VALUES(2, 'ab','ab'); 878 INSERT INTO t11 VALUES(3, 'abc','abc'); 879 INSERT INTO t11 VALUES(4, 'abcd','abcd'); 880 INSERT INTO t11 VALUES(5, 'A','A'); 881 INSERT INTO t11 VALUES(6, 'AB','AB'); 882 INSERT INTO t11 VALUES(7, 'ABC','ABC'); 883 INSERT INTO t11 VALUES(8, 'ABCD','ABCD'); 884 INSERT INTO t11 VALUES(9, 'x','x'); 885 INSERT INTO t11 VALUES(10, 'yz','yz'); 886 INSERT INTO t11 VALUES(11, 'X','X'); 887 INSERT INTO t11 VALUES(12, 'YZ','YZ'); 888 SELECT count(*) FROM t11; 889 } 890 } {12} 891 do_test like-11.1 { 892 db eval {PRAGMA case_sensitive_like=OFF;} 893 queryplan { 894 SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; 895 } 896 } {abc abcd ABC ABCD nosort t11 *} 897 do_test like-11.2 { 898 db eval {PRAGMA case_sensitive_like=ON;} 899 queryplan { 900 SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; 901 } 902 } {abc abcd nosort t11 *} 903 do_test like-11.3 { 904 db eval { 905 PRAGMA case_sensitive_like=OFF; 906 CREATE INDEX t11b ON t11(b); 907 } 908 queryplan { 909 SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; 910 } 911 } {abc abcd ABC ABCD sort {} t11b} 912 do_test like-11.4 { 913 db eval {PRAGMA case_sensitive_like=ON;} 914 queryplan { 915 SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; 916 } 917 } {abc abcd nosort t11 *} 918 do_test like-11.5 { 919 db eval { 920 PRAGMA case_sensitive_like=OFF; 921 DROP INDEX t11b; 922 CREATE INDEX t11bnc ON t11(b COLLATE nocase); 923 } 924 queryplan { 925 SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; 926 } 927 } {abc abcd ABC ABCD sort {} t11bnc} 928 do_test like-11.6 { 929 db eval {CREATE INDEX t11bb ON t11(b COLLATE binary);} 930 queryplan { 931 SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; 932 } 933 } {abc abcd ABC ABCD sort {} t11bnc} 934 do_test like-11.7 { 935 db eval {PRAGMA case_sensitive_like=ON;} 936 queryplan { 937 SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; 938 } 939 } {abc abcd sort {} t11bb} 940 do_test like-11.8 { 941 db eval {PRAGMA case_sensitive_like=OFF;} 942 queryplan { 943 SELECT b FROM t11 WHERE b GLOB 'abc*' ORDER BY +a; 944 } 945 } {abc abcd sort {} t11bb} 946 do_test like-11.9 { 947 db eval { 948 CREATE INDEX t11cnc ON t11(c COLLATE nocase); 949 CREATE INDEX t11cb ON t11(c COLLATE binary); 950 } 951 queryplan { 952 SELECT c FROM t11 WHERE c LIKE 'abc%' ORDER BY +a; 953 } 954 } {abc abcd ABC ABCD sort {} t11cnc} 955 do_test like-11.10 { 956 queryplan { 957 SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY +a; 958 } 959 } {abc abcd sort {} t11cb} 960 961 # A COLLATE clause on the pattern does not change the result of a 962 # LIKE operator. 963 # 964 do_execsql_test like-12.1 { 965 CREATE TABLE t12nc(id INTEGER, x TEXT UNIQUE COLLATE nocase); 966 INSERT INTO t12nc VALUES(1,'abcde'),(2,'uvwxy'),(3,'ABCDEF'); 967 CREATE TABLE t12b(id INTEGER, x TEXT UNIQUE COLLATE binary); 968 INSERT INTO t12b VALUES(1,'abcde'),(2,'uvwxy'),(3,'ABCDEF'); 969 SELECT id FROM t12nc WHERE x LIKE 'abc%' ORDER BY +id; 970 } {1 3} 971 do_execsql_test like-12.2 { 972 SELECT id FROM t12b WHERE x LIKE 'abc%' ORDER BY +id; 973 } {1 3} 974 do_execsql_test like-12.3 { 975 SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id; 976 } {1 3} 977 do_execsql_test like-12.4 { 978 SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id; 979 } {1 3} 980 do_execsql_test like-12.5 { 981 SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id; 982 } {1 3} 983 do_execsql_test like-12.6 { 984 SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id; 985 } {1 3} 986 987 # Adding a COLLATE clause to the pattern of a LIKE operator does nothing 988 # to change the suitability of using an index to satisfy that LIKE 989 # operator. 990 # 991 do_execsql_test like-12.11 { 992 EXPLAIN QUERY PLAN 993 SELECT id FROM t12nc WHERE x LIKE 'abc%' ORDER BY +id; 994 } {/SEARCH/} 995 do_execsql_test like-12.12 { 996 EXPLAIN QUERY PLAN 997 SELECT id FROM t12b WHERE x LIKE 'abc%' ORDER BY +id; 998 } {/SCAN/} 999 do_execsql_test like-12.13 { 1000 EXPLAIN QUERY PLAN 1001 SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id; 1002 } {/SEARCH/} 1003 do_execsql_test like-12.14 { 1004 EXPLAIN QUERY PLAN 1005 SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id; 1006 } {/SCAN/} 1007 do_execsql_test like-12.15 { 1008 EXPLAIN QUERY PLAN 1009 SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id; 1010 } {/SEARCH/} 1011 do_execsql_test like-12.16 { 1012 EXPLAIN QUERY PLAN 1013 SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id; 1014 } {/SCAN/} 1015 1016 # Ticket [https://www.sqlite.org/src/tktview/80369eddd5c94d49f7fbbcf5] 1017 # 2016-01-20 1018 # 1019 do_execsql_test like-13.1 { 1020 SELECT char(0x304d) LIKE char(0x306d); 1021 } {0} 1022 do_execsql_test like-13.2 { 1023 SELECT char(0x4d) LIKE char(0x306d); 1024 } {0} 1025 do_execsql_test like-13.3 { 1026 SELECT char(0x304d) LIKE char(0x6d); 1027 } {0} 1028 do_execsql_test like-13.4 { 1029 SELECT char(0x4d) LIKE char(0x6d); 1030 } {1} 1031 1032 # Performance testing for patterns with many wildcards. These LIKE and GLOB 1033 # patterns were quite slow with SQLite 3.15.2 and earlier. 1034 # 1035 do_test like-14.1 { 1036 set x [lindex [time { 1037 db one {SELECT 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaz'GLOB'*a*a*a*a*a*a*a*a*y'} 1038 }] 0] 1039 set tlimit [expr {1000 * $::sqlite_options(configslower)}] 1040 puts -nonewline " ($x ms - want less than $tlimit) " 1041 expr {$x<$tlimit} 1042 } {1} 1043 ifcapable !icu { 1044 do_test like-14.2 { 1045 set x [lindex [time { 1046 db one {SELECT 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaz'LIKE'%a%a%a%a%a%a%a%a%y'} 1047 }] 0] 1048 set tlimit [expr {1000 * $::sqlite_options(configslower)}] 1049 puts -nonewline " ($x ms - want less than $tlimit) " 1050 expr {$x<$tlimit} 1051 } {1} 1052 } 1053 1054 ifcapable !icu { 1055 # As of 2017-07-27 (3.21.0) the LIKE optimization works with ESCAPE as 1056 # long as the ESCAPE is a single-byte literal. 1057 # 1058 db close 1059 sqlite3 db :memory: 1060 do_execsql_test like-15.100 { 1061 CREATE TABLE t15(x TEXT COLLATE nocase, y, PRIMARY KEY(x)); 1062 INSERT INTO t15(x,y) VALUES 1063 ('abcde',1), ('ab%de',2), ('a_cde',3), 1064 ('uvwxy',11),('uvwx%',12),('uvwx_',13), 1065 ('_bcde',21),('%bcde',22), 1066 ('abcd_',31),('abcd%',32), 1067 ('ab%xy',41); 1068 SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '/'; 1069 } {2} 1070 do_execsql_test like-15.101 { 1071 EXPLAIN QUERY PLAN 1072 SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '/'; 1073 } {/SEARCH/} 1074 do_execsql_test like-15.102 { 1075 EXPLAIN QUERY PLAN 1076 SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '//'; 1077 } {/SCAN/} 1078 do_execsql_test like-15.103 { 1079 EXPLAIN QUERY PLAN 1080 SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE ''; 1081 } {/SCAN/} 1082 do_execsql_test like-15.110 { 1083 SELECT y FROM t15 WHERE x LIKE 'abcdx%%' ESCAPE 'x'; 1084 } {32} 1085 do_execsql_test like-15.111 { 1086 SELECT y FROM t15 WHERE x LIKE 'abx%%' ESCAPE 'x' ORDER BY +y 1087 } {2 41} 1088 do_execsql_test like-15.112 { 1089 EXPLAIN QUERY PLAN 1090 SELECT y FROM t15 WHERE x LIKE 'abx%%' ESCAPE 'x' ORDER BY +y 1091 } {/SEARCH/} 1092 do_execsql_test like-15.120 { 1093 SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/'; 1094 } {22} 1095 do_execsql_test like-15.121 { 1096 EXPLAIN QUERY PLAN 1097 SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/'; 1098 } {/SEARCH/} 1099 } 1100 1101 #------------------------------------------------------------------------- 1102 # Tests for ticket [b1d8c79314]. 1103 # 1104 reset_db 1105 do_execsql_test 16.0 { 1106 CREATE TABLE t1(a INTEGER COLLATE NOCASE); 1107 CREATE INDEX i1 ON t1(a); 1108 INSERT INTO t1 VALUES(' 1x'); 1109 INSERT INTO t1 VALUES(' 1-'); 1110 } 1111 do_execsql_test 16.1 { 1112 SELECT * FROM t1 WHERE a LIKE ' 1%'; 1113 } {{ 1x} { 1-}} 1114 do_execsql_test 16.2 { 1115 SELECT * FROM t1 WHERE a LIKE ' 1-'; 1116 } {{ 1-}} 1117 1118 # 2020-03-19 1119 # The ESCAPE clause on LIKE takes precedence over wildcards 1120 # 1121 do_execsql_test 17.0 { 1122 DROP TABLE IF EXISTS t1; 1123 CREATE TABLE t1(id INTEGER PRIMARY KEY, x TEXT); 1124 INSERT INTO t1 VALUES 1125 (1,'abcde'), 1126 (2,'abc_'), 1127 (3,'abc__'), 1128 (4,'abc%'), 1129 (5,'abc%%'); 1130 SELECT id FROM t1 WHERE x LIKE 'abc%%' ESCAPE '%'; 1131 } {4} 1132 do_execsql_test 17.1 { 1133 SELECT id FROM t1 WHERE x LIKE 'abc__' ESCAPE '_'; 1134 } {2} 1135 1136 # 2021-02-15 ticket c0aeea67d58ae0fd 1137 # 1138 do_execsql_test 17.1 { 1139 SELECT 'x' LIKE '%' ESCAPE '_'; 1140 } {1} 1141 1142 1143 finish_test