Annotation of embedaddon/sqlite3/test/like.test, revision 1.1
1.1 ! misho 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:
! 21: # Create some sample data to work with.
! 22: #
! 23: do_test like-1.0 {
! 24: execsql {
! 25: CREATE TABLE t1(x TEXT);
! 26: }
! 27: foreach str {
! 28: a
! 29: ab
! 30: abc
! 31: abcd
! 32:
! 33: acd
! 34: abd
! 35: bc
! 36: bcd
! 37:
! 38: xyz
! 39: ABC
! 40: CDE
! 41: {ABC abc xyz}
! 42: } {
! 43: db eval {INSERT INTO t1 VALUES(:str)}
! 44: }
! 45: execsql {
! 46: SELECT count(*) FROM t1;
! 47: }
! 48: } {12}
! 49:
! 50: # Test that both case sensitive and insensitive version of LIKE work.
! 51: #
! 52: do_test like-1.1 {
! 53: execsql {
! 54: SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
! 55: }
! 56: } {ABC abc}
! 57: do_test like-1.2 {
! 58: execsql {
! 59: SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;
! 60: }
! 61: } {abc}
! 62: do_test like-1.3 {
! 63: execsql {
! 64: SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;
! 65: }
! 66: } {ABC abc}
! 67: do_test like-1.4 {
! 68: execsql {
! 69: SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1;
! 70: }
! 71: } {ABC abc}
! 72: do_test like-1.5.1 {
! 73: # Use sqlite3_exec() to verify fix for ticket [25ee81271091] 2011-06-26
! 74: sqlite3_exec db {PRAGMA case_sensitive_like=on}
! 75: } {0 {}}
! 76: do_test like-1.5.2 {
! 77: execsql {
! 78: SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
! 79: }
! 80: } {abc}
! 81: do_test like-1.5.3 {
! 82: execsql {
! 83: PRAGMA case_sensitive_like; -- no argument; does not change setting
! 84: SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
! 85: }
! 86: } {abc}
! 87: do_test like-1.6 {
! 88: execsql {
! 89: SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;
! 90: }
! 91: } {abc}
! 92: do_test like-1.7 {
! 93: execsql {
! 94: SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;
! 95: }
! 96: } {ABC}
! 97: do_test like-1.8 {
! 98: execsql {
! 99: SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1;
! 100: }
! 101: } {}
! 102: do_test like-1.9 {
! 103: execsql {
! 104: PRAGMA case_sensitive_like=off;
! 105: SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
! 106: }
! 107: } {ABC abc}
! 108: do_test like-1.10 {
! 109: execsql {
! 110: PRAGMA case_sensitive_like; -- No argument, does not change setting.
! 111: SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
! 112: }
! 113: } {ABC abc}
! 114:
! 115: # Tests of the REGEXP operator
! 116: #
! 117: do_test like-2.1 {
! 118: proc test_regexp {a b} {
! 119: return [regexp $a $b]
! 120: }
! 121: db function regexp -argcount 2 test_regexp
! 122: execsql {
! 123: SELECT x FROM t1 WHERE x REGEXP 'abc' ORDER BY 1;
! 124: }
! 125: } {{ABC abc xyz} abc abcd}
! 126: do_test like-2.2 {
! 127: execsql {
! 128: SELECT x FROM t1 WHERE x REGEXP '^abc' ORDER BY 1;
! 129: }
! 130: } {abc abcd}
! 131:
! 132: # Tests of the MATCH operator
! 133: #
! 134: do_test like-2.3 {
! 135: proc test_match {a b} {
! 136: return [string match $a $b]
! 137: }
! 138: db function match -argcount 2 test_match
! 139: execsql {
! 140: SELECT x FROM t1 WHERE x MATCH '*abc*' ORDER BY 1;
! 141: }
! 142: } {{ABC abc xyz} abc abcd}
! 143: do_test like-2.4 {
! 144: execsql {
! 145: SELECT x FROM t1 WHERE x MATCH 'abc*' ORDER BY 1;
! 146: }
! 147: } {abc abcd}
! 148:
! 149: # For the remaining tests, we need to have the like optimizations
! 150: # enabled.
! 151: #
! 152: ifcapable !like_opt {
! 153: finish_test
! 154: return
! 155: }
! 156:
! 157: # This procedure executes the SQL. Then it appends to the result the
! 158: # "sort" or "nosort" keyword (as in the cksort procedure above) then
! 159: # it appends the ::sqlite_query_plan variable.
! 160: #
! 161: proc queryplan {sql} {
! 162: set ::sqlite_sort_count 0
! 163: set data [execsql $sql]
! 164: if {$::sqlite_sort_count} {set x sort} {set x nosort}
! 165: lappend data $x
! 166: return [concat $data $::sqlite_query_plan]
! 167: }
! 168:
! 169: # Perform tests on the like optimization.
! 170: #
! 171: # With no index on t1.x and with case sensitivity turned off, no optimization
! 172: # is performed.
! 173: #
! 174: do_test like-3.1 {
! 175: set sqlite_like_count 0
! 176: queryplan {
! 177: SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
! 178: }
! 179: } {ABC {ABC abc xyz} abc abcd sort t1 {}}
! 180: do_test like-3.2 {
! 181: set sqlite_like_count
! 182: } {12}
! 183:
! 184: # With an index on t1.x and case sensitivity on, optimize completely.
! 185: #
! 186: do_test like-3.3 {
! 187: set sqlite_like_count 0
! 188: execsql {
! 189: PRAGMA case_sensitive_like=on;
! 190: CREATE INDEX i1 ON t1(x);
! 191: }
! 192: queryplan {
! 193: SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
! 194: }
! 195: } {abc abcd nosort {} i1}
! 196: do_test like-3.4 {
! 197: set sqlite_like_count
! 198: } 0
! 199:
! 200: # The LIKE optimization still works when the RHS is a string with no
! 201: # wildcard. Ticket [e090183531fc2747]
! 202: #
! 203: do_test like-3.4.2 {
! 204: queryplan {
! 205: SELECT x FROM t1 WHERE x LIKE 'a' ORDER BY 1;
! 206: }
! 207: } {a nosort {} i1}
! 208: do_test like-3.4.3 {
! 209: queryplan {
! 210: SELECT x FROM t1 WHERE x LIKE 'ab' ORDER BY 1;
! 211: }
! 212: } {ab nosort {} i1}
! 213: do_test like-3.4.4 {
! 214: queryplan {
! 215: SELECT x FROM t1 WHERE x LIKE 'abcd' ORDER BY 1;
! 216: }
! 217: } {abcd nosort {} i1}
! 218: do_test like-3.4.5 {
! 219: queryplan {
! 220: SELECT x FROM t1 WHERE x LIKE 'abcde' ORDER BY 1;
! 221: }
! 222: } {nosort {} i1}
! 223:
! 224:
! 225: # Partial optimization when the pattern does not end in '%'
! 226: #
! 227: do_test like-3.5 {
! 228: set sqlite_like_count 0
! 229: queryplan {
! 230: SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1;
! 231: }
! 232: } {abc nosort {} i1}
! 233: do_test like-3.6 {
! 234: set sqlite_like_count
! 235: } 6
! 236: do_test like-3.7 {
! 237: set sqlite_like_count 0
! 238: queryplan {
! 239: SELECT x FROM t1 WHERE x LIKE 'ab%d' ORDER BY 1;
! 240: }
! 241: } {abcd abd nosort {} i1}
! 242: do_test like-3.8 {
! 243: set sqlite_like_count
! 244: } 4
! 245: do_test like-3.9 {
! 246: set sqlite_like_count 0
! 247: queryplan {
! 248: SELECT x FROM t1 WHERE x LIKE 'a_c%' ORDER BY 1;
! 249: }
! 250: } {abc abcd nosort {} i1}
! 251: do_test like-3.10 {
! 252: set sqlite_like_count
! 253: } 6
! 254:
! 255: # No optimization when the pattern begins with a wildcard.
! 256: # Note that the index is still used but only for sorting.
! 257: #
! 258: do_test like-3.11 {
! 259: set sqlite_like_count 0
! 260: queryplan {
! 261: SELECT x FROM t1 WHERE x LIKE '%bcd' ORDER BY 1;
! 262: }
! 263: } {abcd bcd nosort {} i1}
! 264: do_test like-3.12 {
! 265: set sqlite_like_count
! 266: } 12
! 267:
! 268: # No optimization for case insensitive LIKE
! 269: #
! 270: do_test like-3.13 {
! 271: set sqlite_like_count 0
! 272: queryplan {
! 273: PRAGMA case_sensitive_like=off;
! 274: SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
! 275: }
! 276: } {ABC {ABC abc xyz} abc abcd nosort {} i1}
! 277: do_test like-3.14 {
! 278: set sqlite_like_count
! 279: } 12
! 280:
! 281: # No optimization without an index.
! 282: #
! 283: do_test like-3.15 {
! 284: set sqlite_like_count 0
! 285: queryplan {
! 286: PRAGMA case_sensitive_like=on;
! 287: DROP INDEX i1;
! 288: SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
! 289: }
! 290: } {abc abcd sort t1 {}}
! 291: do_test like-3.16 {
! 292: set sqlite_like_count
! 293: } 12
! 294:
! 295: # No GLOB optimization without an index.
! 296: #
! 297: do_test like-3.17 {
! 298: set sqlite_like_count 0
! 299: queryplan {
! 300: SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
! 301: }
! 302: } {abc abcd sort t1 {}}
! 303: do_test like-3.18 {
! 304: set sqlite_like_count
! 305: } 12
! 306:
! 307: # GLOB is optimized regardless of the case_sensitive_like setting.
! 308: #
! 309: do_test like-3.19 {
! 310: set sqlite_like_count 0
! 311: db eval {CREATE INDEX i1 ON t1(x);}
! 312: queryplan {
! 313: SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
! 314: }
! 315: } {abc abcd nosort {} i1}
! 316: do_test like-3.20 {
! 317: set sqlite_like_count
! 318: } 0
! 319: do_test like-3.21 {
! 320: set sqlite_like_count 0
! 321: queryplan {
! 322: PRAGMA case_sensitive_like=on;
! 323: SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
! 324: }
! 325: } {abc abcd nosort {} i1}
! 326: do_test like-3.22 {
! 327: set sqlite_like_count
! 328: } 0
! 329: do_test like-3.23 {
! 330: set sqlite_like_count 0
! 331: queryplan {
! 332: PRAGMA case_sensitive_like=off;
! 333: SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1;
! 334: }
! 335: } {abd acd nosort {} i1}
! 336: do_test like-3.24 {
! 337: set sqlite_like_count
! 338: } 6
! 339:
! 340: # GLOB optimization when there is no wildcard. Ticket [e090183531fc2747]
! 341: #
! 342: do_test like-3.25 {
! 343: queryplan {
! 344: SELECT x FROM t1 WHERE x GLOB 'a' ORDER BY 1;
! 345: }
! 346: } {a nosort {} i1}
! 347: do_test like-3.26 {
! 348: queryplan {
! 349: SELECT x FROM t1 WHERE x GLOB 'abcd' ORDER BY 1;
! 350: }
! 351: } {abcd nosort {} i1}
! 352: do_test like-3.27 {
! 353: queryplan {
! 354: SELECT x FROM t1 WHERE x GLOB 'abcde' ORDER BY 1;
! 355: }
! 356: } {nosort {} i1}
! 357:
! 358:
! 359:
! 360: # No optimization if the LHS of the LIKE is not a column name or
! 361: # if the RHS is not a string.
! 362: #
! 363: do_test like-4.1 {
! 364: execsql {PRAGMA case_sensitive_like=on}
! 365: set sqlite_like_count 0
! 366: queryplan {
! 367: SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1
! 368: }
! 369: } {abc abcd nosort {} i1}
! 370: do_test like-4.2 {
! 371: set sqlite_like_count
! 372: } 0
! 373: do_test like-4.3 {
! 374: set sqlite_like_count 0
! 375: queryplan {
! 376: SELECT x FROM t1 WHERE +x LIKE 'abc%' ORDER BY 1
! 377: }
! 378: } {abc abcd nosort {} i1}
! 379: do_test like-4.4 {
! 380: set sqlite_like_count
! 381: } 12
! 382: do_test like-4.5 {
! 383: set sqlite_like_count 0
! 384: queryplan {
! 385: SELECT x FROM t1 WHERE x LIKE ('ab' || 'c%') ORDER BY 1
! 386: }
! 387: } {abc abcd nosort {} i1}
! 388: do_test like-4.6 {
! 389: set sqlite_like_count
! 390: } 12
! 391:
! 392: # Collating sequences on the index disable the LIKE optimization.
! 393: # Or if the NOCASE collating sequence is used, the LIKE optimization
! 394: # is enabled when case_sensitive_like is OFF.
! 395: #
! 396: do_test like-5.1 {
! 397: execsql {PRAGMA case_sensitive_like=off}
! 398: set sqlite_like_count 0
! 399: queryplan {
! 400: SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1
! 401: }
! 402: } {ABC {ABC abc xyz} abc abcd nosort {} i1}
! 403: do_test like-5.2 {
! 404: set sqlite_like_count
! 405: } 12
! 406: do_test like-5.3 {
! 407: execsql {
! 408: CREATE TABLE t2(x TEXT COLLATE NOCASE);
! 409: INSERT INTO t2 SELECT * FROM t1;
! 410: CREATE INDEX i2 ON t2(x COLLATE NOCASE);
! 411: }
! 412: set sqlite_like_count 0
! 413: queryplan {
! 414: SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1
! 415: }
! 416: } {abc ABC {ABC abc xyz} abcd nosort {} i2}
! 417: do_test like-5.4 {
! 418: set sqlite_like_count
! 419: } 0
! 420: do_test like-5.5 {
! 421: execsql {
! 422: PRAGMA case_sensitive_like=on;
! 423: }
! 424: set sqlite_like_count 0
! 425: queryplan {
! 426: SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1
! 427: }
! 428: } {abc abcd nosort {} i2}
! 429: do_test like-5.6 {
! 430: set sqlite_like_count
! 431: } 12
! 432: do_test like-5.7 {
! 433: execsql {
! 434: PRAGMA case_sensitive_like=off;
! 435: }
! 436: set sqlite_like_count 0
! 437: queryplan {
! 438: SELECT x FROM t2 WHERE x GLOB 'abc*' ORDER BY 1
! 439: }
! 440: } {abc abcd nosort {} i2}
! 441: do_test like-5.8 {
! 442: set sqlite_like_count
! 443: } 12
! 444: do_test like-5.11 {
! 445: execsql {PRAGMA case_sensitive_like=off}
! 446: set sqlite_like_count 0
! 447: queryplan {
! 448: SELECT x FROM t1 WHERE x LIKE 'ABC%' ORDER BY 1
! 449: }
! 450: } {ABC {ABC abc xyz} abc abcd nosort {} i1}
! 451: do_test like-5.12 {
! 452: set sqlite_like_count
! 453: } 12
! 454: do_test like-5.13 {
! 455: set sqlite_like_count 0
! 456: queryplan {
! 457: SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1
! 458: }
! 459: } {abc ABC {ABC abc xyz} abcd nosort {} i2}
! 460: do_test like-5.14 {
! 461: set sqlite_like_count
! 462: } 0
! 463: do_test like-5.15 {
! 464: execsql {
! 465: PRAGMA case_sensitive_like=on;
! 466: }
! 467: set sqlite_like_count 0
! 468: queryplan {
! 469: SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1
! 470: }
! 471: } {ABC {ABC abc xyz} nosort {} i2}
! 472: do_test like-5.16 {
! 473: set sqlite_like_count
! 474: } 12
! 475: do_test like-5.17 {
! 476: execsql {
! 477: PRAGMA case_sensitive_like=off;
! 478: }
! 479: set sqlite_like_count 0
! 480: queryplan {
! 481: SELECT x FROM t2 WHERE x GLOB 'ABC*' ORDER BY 1
! 482: }
! 483: } {ABC {ABC abc xyz} nosort {} i2}
! 484: do_test like-5.18 {
! 485: set sqlite_like_count
! 486: } 12
! 487:
! 488: # Boundary case. The prefix for a LIKE comparison is rounded up
! 489: # when constructing the comparison. Example: "ab" becomes "ac".
! 490: # In other words, the last character is increased by one.
! 491: #
! 492: # Make sure this happens correctly when the last character is a
! 493: # "z" and we are doing case-insensitive comparisons.
! 494: #
! 495: # Ticket #2959
! 496: #
! 497: do_test like-5.21 {
! 498: execsql {
! 499: PRAGMA case_sensitive_like=off;
! 500: INSERT INTO t2 VALUES('ZZ-upper-upper');
! 501: INSERT INTO t2 VALUES('zZ-lower-upper');
! 502: INSERT INTO t2 VALUES('Zz-upper-lower');
! 503: INSERT INTO t2 VALUES('zz-lower-lower');
! 504: }
! 505: queryplan {
! 506: SELECT x FROM t2 WHERE x LIKE 'zz%';
! 507: }
! 508: } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}
! 509: do_test like-5.22 {
! 510: queryplan {
! 511: SELECT x FROM t2 WHERE x LIKE 'zZ%';
! 512: }
! 513: } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}
! 514: do_test like-5.23 {
! 515: queryplan {
! 516: SELECT x FROM t2 WHERE x LIKE 'Zz%';
! 517: }
! 518: } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}
! 519: do_test like-5.24 {
! 520: queryplan {
! 521: SELECT x FROM t2 WHERE x LIKE 'ZZ%';
! 522: }
! 523: } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}
! 524: do_test like-5.25 {
! 525: db eval {
! 526: PRAGMA case_sensitive_like=on;
! 527: CREATE TABLE t3(x TEXT);
! 528: CREATE INDEX i3 ON t3(x);
! 529: INSERT INTO t3 VALUES('ZZ-upper-upper');
! 530: INSERT INTO t3 VALUES('zZ-lower-upper');
! 531: INSERT INTO t3 VALUES('Zz-upper-lower');
! 532: INSERT INTO t3 VALUES('zz-lower-lower');
! 533: }
! 534: queryplan {
! 535: SELECT x FROM t3 WHERE x LIKE 'zz%';
! 536: }
! 537: } {zz-lower-lower nosort {} i3}
! 538: do_test like-5.26 {
! 539: queryplan {
! 540: SELECT x FROM t3 WHERE x LIKE 'zZ%';
! 541: }
! 542: } {zZ-lower-upper nosort {} i3}
! 543: do_test like-5.27 {
! 544: queryplan {
! 545: SELECT x FROM t3 WHERE x LIKE 'Zz%';
! 546: }
! 547: } {Zz-upper-lower nosort {} i3}
! 548: do_test like-5.28 {
! 549: queryplan {
! 550: SELECT x FROM t3 WHERE x LIKE 'ZZ%';
! 551: }
! 552: } {ZZ-upper-upper nosort {} i3}
! 553:
! 554:
! 555: # ticket #2407
! 556: #
! 557: # Make sure the LIKE prefix optimization does not strip off leading
! 558: # characters of the like pattern that happen to be quote characters.
! 559: #
! 560: do_test like-6.1 {
! 561: foreach x { 'abc 'bcd 'def 'ax } {
! 562: set x2 '[string map {' ''} $x]'
! 563: db eval "INSERT INTO t2 VALUES($x2)"
! 564: }
! 565: execsql {
! 566: SELECT * FROM t2 WHERE x LIKE '''a%'
! 567: }
! 568: } {'abc 'ax}
! 569:
! 570: do_test like-7.1 {
! 571: execsql {
! 572: SELECT rowid, * FROM t1 WHERE rowid GLOB '1*' ORDER BY rowid;
! 573: }
! 574: } {1 a 10 ABC 11 CDE 12 {ABC abc xyz}}
! 575:
! 576: # ticket #3345.
! 577: #
! 578: # Overloading the LIKE function with -1 for the number of arguments
! 579: # will overload both the 2-argument and the 3-argument LIKE.
! 580: #
! 581: do_test like-8.1 {
! 582: db eval {
! 583: CREATE TABLE t8(x);
! 584: INSERT INTO t8 VALUES('abcdef');
! 585: INSERT INTO t8 VALUES('ghijkl');
! 586: INSERT INTO t8 VALUES('mnopqr');
! 587: SELECT 1, x FROM t8 WHERE x LIKE '%h%';
! 588: SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';
! 589: }
! 590: } {1 ghijkl 2 ghijkl}
! 591: do_test like-8.2 {
! 592: proc newlike {args} {return 1} ;# Alternative LIKE is always return TRUE
! 593: db function like newlike ;# Uses -1 for nArg in sqlite3_create_function
! 594: db cache flush
! 595: db eval {
! 596: SELECT 1, x FROM t8 WHERE x LIKE '%h%';
! 597: SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';
! 598: }
! 599: } {1 ghijkl 2 ghijkl}
! 600: do_test like-8.3 {
! 601: db function like -argcount 2 newlike
! 602: db eval {
! 603: SELECT 1, x FROM t8 WHERE x LIKE '%h%';
! 604: SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';
! 605: }
! 606: } {1 abcdef 1 ghijkl 1 mnopqr 2 ghijkl}
! 607: do_test like-8.4 {
! 608: db function like -argcount 3 newlike
! 609: db eval {
! 610: SELECT 1, x FROM t8 WHERE x LIKE '%h%';
! 611: SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';
! 612: }
! 613: } {1 abcdef 1 ghijkl 1 mnopqr 2 abcdef 2 ghijkl 2 mnopqr}
! 614:
! 615:
! 616: ifcapable like_opt&&!icu {
! 617: # Evaluate SQL. Return the result set followed by the
! 618: # and the number of full-scan steps.
! 619: #
! 620: db close
! 621: sqlite3 db test.db
! 622: proc count_steps {sql} {
! 623: set r [db eval $sql]
! 624: lappend r scan [db status step] sort [db status sort]
! 625: }
! 626: do_test like-9.1 {
! 627: count_steps {
! 628: SELECT x FROM t2 WHERE x LIKE 'x%'
! 629: }
! 630: } {xyz scan 0 sort 0}
! 631: do_test like-9.2 {
! 632: count_steps {
! 633: SELECT x FROM t2 WHERE x LIKE '_y%'
! 634: }
! 635: } {xyz scan 19 sort 0}
! 636: do_test like-9.3.1 {
! 637: set res [sqlite3_exec_hex db {
! 638: SELECT x FROM t2 WHERE x LIKE '%78%25'
! 639: }]
! 640: } {0 {x xyz}}
! 641: ifcapable explain {
! 642: do_test like-9.3.2 {
! 643: set res [sqlite3_exec_hex db {
! 644: EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%78%25'
! 645: }]
! 646: regexp {INDEX i2} $res
! 647: } {1}
! 648: }
! 649: do_test like-9.4.1 {
! 650: sqlite3_exec_hex db {INSERT INTO t2 VALUES('%ffhello')}
! 651: set res [sqlite3_exec_hex db {
! 652: SELECT substr(x,2) AS x FROM t2 WHERE +x LIKE '%ff%25'
! 653: }]
! 654: } {0 {x hello}}
! 655: do_test like-9.4.2 {
! 656: set res [sqlite3_exec_hex db {
! 657: SELECT substr(x,2) AS x FROM t2 WHERE x LIKE '%ff%25'
! 658: }]
! 659: } {0 {x hello}}
! 660: ifcapable explain {
! 661: do_test like-9.4.3 {
! 662: set res [sqlite3_exec_hex db {
! 663: EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%ff%25'
! 664: }]
! 665: regexp {INDEX i2} $res
! 666: } {0}
! 667: }
! 668: do_test like-9.5.1 {
! 669: set res [sqlite3_exec_hex db {
! 670: SELECT x FROM t2 WHERE x LIKE '%fe%25'
! 671: }]
! 672: } {0 {}}
! 673: ifcapable explain {
! 674: do_test like-9.5.2 {
! 675: set res [sqlite3_exec_hex db {
! 676: EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%fe%25'
! 677: }]
! 678: regexp {INDEX i2} $res
! 679: } {1}
! 680: }
! 681:
! 682: # Do an SQL statement. Append the search count to the end of the result.
! 683: #
! 684: proc count sql {
! 685: set ::sqlite_search_count 0
! 686: set ::sqlite_like_count 0
! 687: return [concat [execsql $sql] scan $::sqlite_search_count \
! 688: like $::sqlite_like_count]
! 689: }
! 690:
! 691: # The LIKE and GLOB optimizations do not work on columns with
! 692: # affinity other than TEXT.
! 693: # Ticket #3901
! 694: #
! 695: do_test like-10.1 {
! 696: db close
! 697: sqlite3 db test.db
! 698: execsql {
! 699: CREATE TABLE t10(
! 700: a INTEGER PRIMARY KEY,
! 701: b INTEGER COLLATE nocase UNIQUE,
! 702: c NUMBER COLLATE nocase UNIQUE,
! 703: d BLOB COLLATE nocase UNIQUE,
! 704: e COLLATE nocase UNIQUE,
! 705: f TEXT COLLATE nocase UNIQUE
! 706: );
! 707: INSERT INTO t10 VALUES(1,1,1,1,1,1);
! 708: INSERT INTO t10 VALUES(12,12,12,12,12,12);
! 709: INSERT INTO t10 VALUES(123,123,123,123,123,123);
! 710: INSERT INTO t10 VALUES(234,234,234,234,234,234);
! 711: INSERT INTO t10 VALUES(345,345,345,345,345,345);
! 712: INSERT INTO t10 VALUES(45,45,45,45,45,45);
! 713: }
! 714: count {
! 715: SELECT a FROM t10 WHERE b LIKE '12%' ORDER BY +a;
! 716: }
! 717: } {12 123 scan 5 like 6}
! 718: do_test like-10.2 {
! 719: count {
! 720: SELECT a FROM t10 WHERE c LIKE '12%' ORDER BY +a;
! 721: }
! 722: } {12 123 scan 5 like 6}
! 723: do_test like-10.3 {
! 724: count {
! 725: SELECT a FROM t10 WHERE d LIKE '12%' ORDER BY +a;
! 726: }
! 727: } {12 123 scan 5 like 6}
! 728: do_test like-10.4 {
! 729: count {
! 730: SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY +a;
! 731: }
! 732: } {12 123 scan 5 like 6}
! 733: do_test like-10.5 {
! 734: count {
! 735: SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a;
! 736: }
! 737: } {12 123 scan 3 like 0}
! 738: do_test like-10.6 {
! 739: count {
! 740: SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY +a;
! 741: }
! 742: } {12 123 scan 5 like 6}
! 743: do_test like-10.10 {
! 744: execsql {
! 745: CREATE TABLE t10b(
! 746: a INTEGER PRIMARY KEY,
! 747: b INTEGER UNIQUE,
! 748: c NUMBER UNIQUE,
! 749: d BLOB UNIQUE,
! 750: e UNIQUE,
! 751: f TEXT UNIQUE
! 752: );
! 753: INSERT INTO t10b SELECT * FROM t10;
! 754: }
! 755: count {
! 756: SELECT a FROM t10b WHERE b GLOB '12*' ORDER BY +a;
! 757: }
! 758: } {12 123 scan 5 like 6}
! 759: do_test like-10.11 {
! 760: count {
! 761: SELECT a FROM t10b WHERE c GLOB '12*' ORDER BY +a;
! 762: }
! 763: } {12 123 scan 5 like 6}
! 764: do_test like-10.12 {
! 765: count {
! 766: SELECT a FROM t10b WHERE d GLOB '12*' ORDER BY +a;
! 767: }
! 768: } {12 123 scan 5 like 6}
! 769: do_test like-10.13 {
! 770: count {
! 771: SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY +a;
! 772: }
! 773: } {12 123 scan 5 like 6}
! 774: do_test like-10.14 {
! 775: count {
! 776: SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a;
! 777: }
! 778: } {12 123 scan 3 like 0}
! 779: do_test like-10.15 {
! 780: count {
! 781: SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY +a;
! 782: }
! 783: } {12 123 scan 5 like 6}
! 784: }
! 785:
! 786: # LIKE and GLOB where the default collating sequence is not appropriate
! 787: # but an index with the appropriate collating sequence exists.
! 788: #
! 789: do_test like-11.0 {
! 790: execsql {
! 791: CREATE TABLE t11(
! 792: a INTEGER PRIMARY KEY,
! 793: b TEXT COLLATE nocase,
! 794: c TEXT COLLATE binary
! 795: );
! 796: INSERT INTO t11 VALUES(1, 'a','a');
! 797: INSERT INTO t11 VALUES(2, 'ab','ab');
! 798: INSERT INTO t11 VALUES(3, 'abc','abc');
! 799: INSERT INTO t11 VALUES(4, 'abcd','abcd');
! 800: INSERT INTO t11 VALUES(5, 'A','A');
! 801: INSERT INTO t11 VALUES(6, 'AB','AB');
! 802: INSERT INTO t11 VALUES(7, 'ABC','ABC');
! 803: INSERT INTO t11 VALUES(8, 'ABCD','ABCD');
! 804: INSERT INTO t11 VALUES(9, 'x','x');
! 805: INSERT INTO t11 VALUES(10, 'yz','yz');
! 806: INSERT INTO t11 VALUES(11, 'X','X');
! 807: INSERT INTO t11 VALUES(12, 'YZ','YZ');
! 808: SELECT count(*) FROM t11;
! 809: }
! 810: } {12}
! 811: do_test like-11.1 {
! 812: queryplan {
! 813: PRAGMA case_sensitive_like=OFF;
! 814: SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
! 815: }
! 816: } {abc abcd ABC ABCD nosort t11 *}
! 817: do_test like-11.2 {
! 818: queryplan {
! 819: PRAGMA case_sensitive_like=ON;
! 820: SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
! 821: }
! 822: } {abc abcd nosort t11 *}
! 823: do_test like-11.3 {
! 824: queryplan {
! 825: PRAGMA case_sensitive_like=OFF;
! 826: CREATE INDEX t11b ON t11(b);
! 827: SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a;
! 828: }
! 829: } {abc abcd ABC ABCD sort {} t11b}
! 830: do_test like-11.4 {
! 831: queryplan {
! 832: PRAGMA case_sensitive_like=ON;
! 833: SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
! 834: }
! 835: } {abc abcd nosort t11 *}
! 836: do_test like-11.5 {
! 837: queryplan {
! 838: PRAGMA case_sensitive_like=OFF;
! 839: DROP INDEX t11b;
! 840: CREATE INDEX t11bnc ON t11(b COLLATE nocase);
! 841: SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a;
! 842: }
! 843: } {abc abcd ABC ABCD sort {} t11bnc}
! 844: do_test like-11.6 {
! 845: queryplan {
! 846: CREATE INDEX t11bb ON t11(b COLLATE binary);
! 847: SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a;
! 848: }
! 849: } {abc abcd ABC ABCD sort {} t11bnc}
! 850: do_test like-11.7 {
! 851: queryplan {
! 852: PRAGMA case_sensitive_like=ON;
! 853: SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a;
! 854: }
! 855: } {abc abcd sort {} t11bb}
! 856: do_test like-11.8 {
! 857: queryplan {
! 858: PRAGMA case_sensitive_like=OFF;
! 859: SELECT b FROM t11 WHERE b GLOB 'abc*' ORDER BY +a;
! 860: }
! 861: } {abc abcd sort {} t11bb}
! 862: do_test like-11.9 {
! 863: queryplan {
! 864: CREATE INDEX t11cnc ON t11(c COLLATE nocase);
! 865: CREATE INDEX t11cb ON t11(c COLLATE binary);
! 866: SELECT c FROM t11 WHERE c LIKE 'abc%' ORDER BY +a;
! 867: }
! 868: } {abc abcd ABC ABCD sort {} t11cnc}
! 869: do_test like-11.10 {
! 870: queryplan {
! 871: SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY +a;
! 872: }
! 873: } {abc abcd sort {} t11cb}
! 874:
! 875:
! 876: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to like.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test