AryaWu/sqlite
0
1# 2005 August 132#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# This file implements regression tests for SQLite library. The12# focus of this file is testing the LIKE and GLOB operators and13# in particular the optimizations that occur to help those operators14# run faster.15#16# $Id: like.test,v 1.13 2009/06/07 23:45:11 drh Exp $17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20set testprefix like21 22# Create some sample data to work with.23#24do_test like-1.0 {25 execsql {26 CREATE TABLE t1(x TEXT);27 }28 foreach str {29 a30 ab31 abc32 abcd33 34 acd35 abd36 bc37 bcd38 39 xyz40 ABC41 CDE42 {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#53do_test like-1.1 {54 execsql {55 SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;56 }57} {ABC abc}58do_test like-1.2 {59 execsql {60 SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;61 }62} {abc}63do_test like-1.3 {64 execsql {65 SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;66 }67} {ABC abc}68do_test like-1.4 {69 execsql {70 SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1;71 }72} {ABC abc}73do_test like-1.5.1 {74 # Use sqlite3_exec() to verify fix for ticket [25ee81271091] 2011-06-2675 sqlite3_exec db {PRAGMA case_sensitive_like=on}76} {0 {}}77do_test like-1.5.2 {78 execsql {79 SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;80 }81} {abc}82do_test like-1.5.3 {83 execsql {84 PRAGMA case_sensitive_like; -- no argument; does not change setting85 SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;86 }87} {abc}88do_test like-1.6 {89 execsql {90 SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;91 }92} {abc}93do_test like-1.7 {94 execsql {95 SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;96 }97} {ABC}98do_test like-1.8 {99 execsql {100 SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1;101 }102} {}103do_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}109do_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 operator117#118do_test like-2.1 {119 proc test_regexp {a b} {120 return [regexp $a $b]121 }122 db function regexp -argcount 2 test_regexp123 execsql {124 SELECT x FROM t1 WHERE x REGEXP 'abc' ORDER BY 1;125 }126} {{ABC abc xyz} abc abcd}127do_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 operator134#135do_test like-2.3 {136 proc test_match {a b} {137 return [string match $a $b]138 }139 db function match -argcount 2 test_match140 execsql {141 SELECT x FROM t1 WHERE x MATCH '*abc*' ORDER BY 1;142 }143} {{ABC abc xyz} abc abcd}144do_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 optimizations151# enabled.152#153ifcapable !like_opt {154 finish_test155 return156} 157 158# This procedure executes the SQL. Then it appends to the result the159# "sort" or "nosort" keyword (as in the cksort procedure above) then160# it appends the names of the table and index used.161#162proc queryplan {sql} {163 set ::sqlite_sort_count 0164 db cache flush165 set data [execsql $sql]166 if {$::sqlite_sort_count} {set x sort} {set x nosort}167 lappend data $x168 set eqp [execsql "EXPLAIN QUERY PLAN $sql"]169 # puts eqp=$eqp170 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 {} $idx174 } elseif {[regexp {(SCAN|SEARCH) (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \175 $x all ss as tab idx]} {176 lappend data $tab $idx177 } 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 optimization187# is performed.188#189do_test like-3.1 {190 set sqlite_like_count 0191 queryplan {192 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;193 }194} {ABC {ABC abc xyz} abc abcd sort t1 *}195do_test like-3.2 {196 set sqlite_like_count197} {12}198 199# With an index on t1.x and case sensitivity on, optimize completely.200#201do_test like-3.3.100 {202 set sqlite_like_count 0203 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}211do_test like-3.3.100.cnt {212 set sqlite_like_count213} 0214 215# The like optimization works even when the pattern is a bound parameter216#217# Exception: It does not work if sqlite3_prepare() is used instead of218# sqlite3_prepare_v2(), as in that case the statement cannot be reprepared219# after the parameter is bound.220#221unset -nocomplain ::likepat222set ::likepat abc%223if {[permutation]!="prepare"} {224 do_test like-3.3.102 {225 set sqlite_like_count 0226 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_count232 } 0233}234 235# Except, the like optimization does not work for bound parameters if236# the query planner stability guarantee is active.237#238do_test like-3.3.104 {239 set sqlite_like_count 0240 sqlite3_db_config db QPSG 1241 queryplan {242 SELECT x FROM t1 WHERE x LIKE $::likepat ORDER BY 1;243 }244} {abc abcd nosort {} i1}245do_test like-3.3.105 {246 set sqlite_like_count247} 12248 249# The query planner stability guarantee does not disrupt explicit patterns250#251do_test like-3.3.105 {252 set sqlite_like_count 0253 queryplan {254 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;255 }256} {abc abcd nosort {} i1}257do_test like-3.3.106 {258 set sqlite_like_count259} 0260sqlite3_db_config db QPSG 0261 262# The LIKE optimization still works when the RHS is a string with no263# wildcard. Ticket [e090183531fc2747]264#265do_test like-3.4.2 {266 queryplan {267 SELECT x FROM t1 WHERE x LIKE 'a' ORDER BY 1;268 }269} {a nosort {} i1}270do_test like-3.4.3 {271 queryplan {272 SELECT x FROM t1 WHERE x LIKE 'ab' ORDER BY 1;273 }274} {ab nosort {} i1}275do_test like-3.4.4 {276 queryplan {277 SELECT x FROM t1 WHERE x LIKE 'abcd' ORDER BY 1;278 }279} {abcd nosort {} i1}280do_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#289do_test like-3.5 {290 set sqlite_like_count 0291 queryplan {292 SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1;293 }294} {abc nosort {} i1}295do_test like-3.6 {296 set sqlite_like_count297} 6298do_test like-3.7 {299 set sqlite_like_count 0300 queryplan {301 SELECT x FROM t1 WHERE x LIKE 'ab%d' ORDER BY 1;302 }303} {abcd abd nosort {} i1}304do_test like-3.8 {305 set sqlite_like_count306} 4307do_test like-3.9 {308 set sqlite_like_count 0309 queryplan {310 SELECT x FROM t1 WHERE x LIKE 'a_c%' ORDER BY 1;311 }312} {abc abcd nosort {} i1}313do_test like-3.10 {314 set sqlite_like_count315} 6316 317# No optimization when the pattern begins with a wildcard.318# Note that the index is still used but only for sorting.319#320do_test like-3.11 {321 set sqlite_like_count 0322 queryplan {323 SELECT x FROM t1 WHERE x LIKE '%bcd' ORDER BY 1;324 }325} {abcd bcd nosort {} i1}326do_test like-3.12 {327 set sqlite_like_count328} 12329 330# No optimization for case insensitive LIKE331#332do_test like-3.13 {333 set sqlite_like_count 0334 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}339do_test like-3.14 {340 set sqlite_like_count341} 12342 343# No optimization without an index.344#345do_test like-3.15 {346 set sqlite_like_count 0347 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 *}355do_test like-3.16 {356 set sqlite_like_count357} 12358 359# No GLOB optimization without an index.360#361do_test like-3.17 {362 set sqlite_like_count 0363 queryplan {364 SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;365 }366} {abc abcd sort t1 *}367do_test like-3.18 {368 set sqlite_like_count369} 12370 371# GLOB is optimized regardless of the case_sensitive_like setting.372#373do_test like-3.19 {374 set sqlite_like_count 0375 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}380do_test like-3.20 {381 set sqlite_like_count382} 0383do_test like-3.21 {384 set sqlite_like_count 0385 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}390do_test like-3.22 {391 set sqlite_like_count392} 0393do_test like-3.23 {394 set sqlite_like_count 0395 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}400do_test like-3.24 {401 set sqlite_like_count402} 6403 404# GLOB optimization when there is no wildcard. Ticket [e090183531fc2747]405#406do_test like-3.25 {407 queryplan {408 SELECT x FROM t1 WHERE x GLOB 'a' ORDER BY 1;409 }410} {a nosort {} i1}411do_test like-3.26 {412 queryplan {413 SELECT x FROM t1 WHERE x GLOB 'abcd' ORDER BY 1;414 }415} {abcd nosort {} i1}416do_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 or425# if the RHS is not a string.426#427do_test like-4.1 {428 execsql {PRAGMA case_sensitive_like=on}429 set sqlite_like_count 0430 queryplan {431 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1432 }433} {abc abcd nosort {} i1}434do_test like-4.2 {435 set sqlite_like_count436} 0437do_test like-4.3 {438 set sqlite_like_count 0439 queryplan {440 SELECT x FROM t1 WHERE +x LIKE 'abc%' ORDER BY 1441 }442} {abc abcd nosort {} i1}443do_test like-4.4 {444 set sqlite_like_count445} 12446do_test like-4.5 {447 set sqlite_like_count 0448 queryplan {449 SELECT x FROM t1 WHERE x LIKE ('ab' || 'c%') ORDER BY 1450 }451} {abc abcd nosort {} i1}452do_test like-4.6 {453 set sqlite_like_count454} 12455 456# Collating sequences on the index disable the LIKE optimization.457# Or if the NOCASE collating sequence is used, the LIKE optimization458# is enabled when case_sensitive_like is OFF.459#460do_test like-5.1 {461 execsql {PRAGMA case_sensitive_like=off}462 set sqlite_like_count 0463 queryplan {464 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1465 }466} {ABC {ABC abc xyz} abc abcd nosort {} i1}467do_test like-5.2 {468 set sqlite_like_count469} 12470do_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 0477 queryplan {478 SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1479 }480} {abc ABC {ABC abc xyz} abcd nosort {} i2}481do_test like-5.4 {482 set sqlite_like_count483} 0484do_test like-5.5 {485 execsql {486 PRAGMA case_sensitive_like=on;487 }488 set sqlite_like_count 0489 queryplan {490 SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1491 }492} {abc abcd nosort {} i2}493do_test like-5.6 {494 set sqlite_like_count495} 12496do_test like-5.7 {497 execsql {498 PRAGMA case_sensitive_like=off;499 }500 set sqlite_like_count 0501 queryplan {502 SELECT x FROM t2 WHERE x GLOB 'abc*' ORDER BY 1503 }504} {abc abcd nosort {} i2}505do_test like-5.8 {506 set sqlite_like_count507} 12508do_test like-5.11 {509 execsql {PRAGMA case_sensitive_like=off}510 set sqlite_like_count 0511 queryplan {512 SELECT x FROM t1 WHERE x LIKE 'ABC%' ORDER BY 1513 }514} {ABC {ABC abc xyz} abc abcd nosort {} i1}515do_test like-5.12 {516 set sqlite_like_count517} 12518do_test like-5.13 {519 set sqlite_like_count 0520 queryplan {521 SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1522 }523} {abc ABC {ABC abc xyz} abcd nosort {} i2}524do_test like-5.14 {525 set sqlite_like_count526} 0527do_test like-5.15 {528 execsql {529 PRAGMA case_sensitive_like=on;530 }531 set sqlite_like_count 0532 queryplan {533 SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1534 }535} {ABC {ABC abc xyz} nosort {} i2}536do_test like-5.16 {537 set sqlite_like_count538} 12539do_test like-5.17 {540 execsql {541 PRAGMA case_sensitive_like=off;542 }543 set sqlite_like_count 0544 queryplan {545 SELECT x FROM t2 WHERE x GLOB 'ABC*' ORDER BY 1546 }547} {ABC {ABC abc xyz} nosort {} i2}548do_test like-5.18 {549 set sqlite_like_count550} 12551 552# Boundary case. The prefix for a LIKE comparison is rounded up553# 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 #2959560#561do_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}573do_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}578do_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}583do_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}588do_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}602do_test like-5.26 {603 queryplan {604 SELECT x FROM t3 WHERE x LIKE 'zZ%';605 }606} {zZ-lower-upper nosort {} i3}607do_test like-5.27 {608 queryplan {609 SELECT x FROM t3 WHERE x LIKE 'Zz%';610 }611} {Zz-upper-lower nosort {} i3}612do_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 #2407620#621# Make sure the LIKE prefix optimization does not strip off leading622# characters of the like pattern that happen to be quote characters.623#624do_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 634do_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 arguments643# will overload both the 2-argument and the 3-argument LIKE.644#645do_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}655do_test like-8.2 {656 proc newlike {args} {return 1} ;# Alternative LIKE is always return TRUE657 db function like newlike ;# Uses -1 for nArg in sqlite3_create_function658 db cache flush659 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}664do_test like-8.3 {665 db function like -argcount 2 newlike666 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}671do_test like-8.4 {672 db function like -argcount 3 newlike673 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 680ifcapable like_opt&&!icu {681 # Evaluate SQL. Return the result set followed by the682 # and the number of full-scan steps.683 #684 db close685 sqlite3 db test.db686 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} $res711 } {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} $res730 } {1}731 }732 do_test like-9.5.1 {733 set res [sqlite3_exec_hex db {734 SELECT 1 FROM t2 WHERE x LIKE '%fe%25'735 }]736 } {0 {1 1}}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} $res743 } {0}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 0750 set ::sqlite_like_count 0751 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 with756 # affinity other than TEXT.757 # Ticket #3901758 #759 do_test like-10.1 {760 db close761 sqlite3 db test.db762 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 UNIQUE770 );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 UNIQUE824 );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 appropriate867# but an index with the appropriate collating sequence exists.868#869do_test like-11.0 {870 execsql {871 CREATE TABLE t11(872 a INTEGER PRIMARY KEY,873 b TEXT COLLATE nocase,874 c TEXT COLLATE binary875 );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}891do_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 *}897do_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 *}903do_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}912do_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 *}918do_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}928do_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}934do_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}940do_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}946do_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}955do_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 a962# LIKE operator.963#964do_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}971do_execsql_test like-12.2 {972 SELECT id FROM t12b WHERE x LIKE 'abc%' ORDER BY +id;973} {1 3}974do_execsql_test like-12.3 {975 SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;976} {1 3}977do_execsql_test like-12.4 {978 SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;979} {1 3}980do_execsql_test like-12.5 {981 SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;982} {1 3}983do_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 nothing988# to change the suitability of using an index to satisfy that LIKE989# operator.990#991do_execsql_test like-12.11 {992 EXPLAIN QUERY PLAN993 SELECT id FROM t12nc WHERE x LIKE 'abc%' ORDER BY +id;994} {/SEARCH/}995do_execsql_test like-12.12 {996 EXPLAIN QUERY PLAN997 SELECT id FROM t12b WHERE x LIKE 'abc%' ORDER BY +id;998} {/SCAN/}999do_execsql_test like-12.13 {1000 EXPLAIN QUERY PLAN1001 SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;1002} {/SEARCH/}1003do_execsql_test like-12.14 {1004 EXPLAIN QUERY PLAN1005 SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;1006} {/SCAN/}1007do_execsql_test like-12.15 {1008 EXPLAIN QUERY PLAN1009 SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;1010} {/SEARCH/}1011do_execsql_test like-12.16 {1012 EXPLAIN QUERY PLAN1013 SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;1014} {/SCAN/}1015 1016# Ticket [https://sqlite.org/src/tktview/80369eddd5c94d49f7fbbcf5]1017# 2016-01-201018#1019do_execsql_test like-13.1 {1020 SELECT char(0x304d) LIKE char(0x306d);1021} {0}1022do_execsql_test like-13.2 {1023 SELECT char(0x4d) LIKE char(0x306d);1024} {0}1025do_execsql_test like-13.3 {1026 SELECT char(0x304d) LIKE char(0x6d);1027} {0}1028do_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 GLOB1033# patterns were quite slow with SQLite 3.15.2 and earlier.1034#1035do_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}1043ifcapable !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 1054ifcapable !icu {1055# As of 2017-07-27 (3.21.0) the LIKE optimization works with ESCAPE as1056# long as the ESCAPE is a single-byte literal.1057#1058db close1059sqlite3 db :memory:1060do_execsql_test like-15.100 {1061 CREATE TABLE t15(x TEXT COLLATE nocase, y, PRIMARY KEY(x));1062 INSERT INTO t15(x,y) VALUES1063 ('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}1070do_execsql_test like-15.101 {1071 EXPLAIN QUERY PLAN1072 SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '/';1073} {/SEARCH/}1074do_execsql_test like-15.102 {1075 EXPLAIN QUERY PLAN1076 SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '//';1077} {/SCAN/}1078do_execsql_test like-15.103 {1079 EXPLAIN QUERY PLAN1080 SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '';1081} {/SCAN/}1082do_execsql_test like-15.110 {1083 SELECT y FROM t15 WHERE x LIKE 'abcdx%%' ESCAPE 'x';1084} {32}1085do_execsql_test like-15.111 {1086 SELECT y FROM t15 WHERE x LIKE 'abx%%' ESCAPE 'x' ORDER BY +y1087} {2 41}1088do_execsql_test like-15.112 {1089 EXPLAIN QUERY PLAN1090 SELECT y FROM t15 WHERE x LIKE 'abx%%' ESCAPE 'x' ORDER BY +y1091} {/SEARCH/}1092do_execsql_test like-15.120 {1093 SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';1094} {22}1095do_execsql_test like-15.121 {1096 EXPLAIN QUERY PLAN1097 SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';1098} {/SEARCH/}1099}1100 1101#-------------------------------------------------------------------------1102# Tests for ticket [b1d8c79314].1103#1104reset_db1105do_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}1111do_execsql_test 16.1 {1112 SELECT * FROM t1 WHERE a LIKE ' 1%';1113} {{ 1x} { 1-}}1114do_execsql_test 16.2 {1115 SELECT * FROM t1 WHERE a LIKE ' 1-';1116} {{ 1-}}1117 1118# 2020-03-191119# The ESCAPE clause on LIKE takes precedence over wildcards1120#1121do_execsql_test 17.0 {1122 DROP TABLE IF EXISTS t1;1123 CREATE TABLE t1(id INTEGER PRIMARY KEY, x TEXT);1124 INSERT INTO t1 VALUES1125 (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}1132do_execsql_test 17.1 {1133 SELECT id FROM t1 WHERE x LIKE 'abc__' ESCAPE '_';1134} {2}1135 1136# 2021-02-15 ticket c0aeea67d58ae0fd1137#1138do_execsql_test 17.1 {1139 SELECT 'x' LIKE '%' ESCAPE '_';1140} {1}1141 1142 1143# 2023-08-15 https://sqlite.org/forum/forumpost/925dc9f67804c5401144#1145reset_db1146sqlite3_db_config db DEFENSIVE 11147db eval {PRAGMA trusted_schema=OFF}1148do_execsql_test 18.0 {1149 CREATE TABLE t1(x INT, y TEXT);1150 INSERT INTO t1 VALUES(1,'abc'),(2,'ABC'),(3,'Abc');1151 CREATE VIEW t2 AS SELECT * FROM t1 WHERE y LIKE 'a%';1152 SELECT * FROM t2;1153} {1 abc 2 ABC 3 Abc}1154do_execsql_test 18.1 {1155 PRAGMA case_sensitive_like=OFF;1156 SELECT * FROM t2;1157} {1 abc 2 ABC 3 Abc}1158do_execsql_test 18.2 {1159 PRAGMA case_sensitive_like=ON;1160 SELECT * FROM t2;1161} {1 abc}1162 1163finish_test1164