sqlite/test/like4.test

# 2026-10-15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file implements regression tests for SQLite library.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix like4

ifcapable !like_match_blobs {
  finish_test
  return
}

do_execsql_test 1.0 {
  CREATE TABLE t0(a, c);
  INSERT INTO t0 VALUES(1, 'A');
  INSERT INTO t0 VALUES(2, 'A');
  INSERT INTO t0 VALUES(1, X'41');
  INSERT INTO t0 VALUES(2, X'41');
}

foreach {tn idx} {
  1 ""
  2 "CREATE INDEX idx ON t0(a, c)"
  3 "CREATE INDEX idx ON t0(c, a)"
} {

  execsql { DROP INDEX IF EXISTS idx }
  execsql $idx

  do_execsql_test 1.$tn.1 {
    SELECT a, count(*) FROM t0 WHERE a IN (1,2) AND c GLOB 'A' GROUP BY a;
  } {1 2 2 2}

  do_execsql_test 1.$tn.2 {
    SELECT a, quote(c) FROM t0 WHERE a IN (1,2) AND c GLOB 'A' ORDER BY a;
  } {
    1 'A'
    1 X'41'
    2 'A'
    2 X'41'
  }
}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 2.0 {
  CREATE TABLE t1(a, b);
  CREATE INDEX t1b ON t1(b);

  INSERT INTO t1 VALUES(1, 'abc');
  INSERT INTO t1 VALUES(2, X'616263');
  INSERT INTO t1 VALUES(3, 'abd');
  INSERT INTO t1 VALUES(4, X'616264');
}

do_execsql_test 2.1 {
  SELECT a FROM t1 WHERE b GLOB 'ab*' ORDER BY b
} {1 3 2 4}
do_execsql_test 2.2 {
  SELECT a FROM t1 WHERE b GLOB 'ab*' ORDER BY b DESC
} {4 2 3 1}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 3.0 {
  CREATE TABLE t(a, c);
  CREATE INDEX ti ON t(a, c);
  WITH RECURSIVE n(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM n WHERE i<100)
    INSERT INTO t SELECT i%3, 'x'||i FROM n;
  INSERT INTO t VALUES(2, 'A'), (1, x'41');
}

do_execsql_test 3.1 {
  SELECT a, quote(c) FROM t WHERE c GLOB 'A' ORDER BY a;
} {
  1 X'41'
  2 'A'
}

do_execsql_test 3.2 {
  ANALYZE;
}

do_execsql_test 3.3 {
  SELECT a, quote(c) FROM t WHERE c GLOB 'A' ORDER BY a;
} {
  1 X'41'
  2 'A'
}

#-------------------------------------------------------------------------
# Skip-scan on combined with IN(...) and the LIKE optimization.
#
reset_db

do_execsql_test 4.0 {
  CREATE TABLE t(a, b, c);
  CREATE INDEX ti ON t(a, b, c);
  WITH RECURSIVE n(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM n WHERE i<300)
    INSERT INTO t SELECT i%3, i%2, 'x'||i FROM n;
  INSERT INTO t VALUES(2, 0, 'A'), (2, 1, x'41');
  INSERT INTO t VALUES(1, 1, 'A'), (1, 0, x'41');
  INSERT INTO t VALUES(0, 1, 'A');
  ANALYZE;
}

do_eqp_test 4.1 {
  SELECT a, b, quote(c) FROM t WHERE b IN (0,1) AND c GLOB 'A' ORDER BY a, b;
} {SEARCH t USING COVERING INDEX ti (ANY(a) AND b=? AND c>? AND c<?)}

do_execsql_test 4.2 {
  SELECT a, b, quote(c) FROM t WHERE b IN (0,1) AND c GLOB 'A' ORDER BY a, b;
} {
  0 1 'A'
  1 0 X'41'
  1 1 'A'
  2 0 'A'
  2 1 X'41'
}

do_execsql_test 4.3 {
  SELECT a, b, quote(c) FROM t WHERE b IN (0,1) AND c GLOB 'A'
  ORDER BY a DESC, b DESC;
} {
  2 1 X'41'
  2 0 'A'
  1 1 'A'
  1 0 X'41'
  0 1 'A'
}

do_execsql_test 4.4 {
  SELECT a, count(*) FROM t WHERE b IN (0,1) AND c GLOB 'A' GROUP BY a;
} {0 1 1 2 2 2}

# Mixed ASC and DESC.
#
foreach {tn idx nosort} {
  1  "a DESC, b, c"       {"a, b DESC" "a DESC, b" "a DESC, b, c"
                           "a, b DESC, c DESC"}
  2  "a, b DESC, c"       {"a, b DESC" "a DESC, b"}
  3  "a, b, c DESC"       {"a, b, c DESC" "a DESC, b DESC, c"}
  4  "a DESC, b DESC, c"  {"a, b, c DESC" "a DESC, b DESC, c"}
} {
  execsql "
    DROP INDEX ti; 
    CREATE INDEX ti ON t($idx); 
    ANALYZE;
  "

  foreach {tn2 ob res} {
    1 "a, b DESC"         {0 1 'A' 1 1 'A' 1 0 X'41' 2 1 X'41' 2 0 'A'}
    2 "a DESC, b"         {2 0 'A' 2 1 X'41' 1 0 X'41' 1 1 'A' 0 1 'A'}
    3 "a, b, c DESC"      {0 1 'A' 1 0 X'41' 1 1 'A' 2 0 'A' 2 1 X'41'}
    4 "a DESC, b, c"      {2 0 'A' 2 1 X'41' 1 0 X'41' 1 1 'A' 0 1 'A'}
    5 "a, b DESC, c DESC" {0 1 'A' 1 1 'A' 1 0 X'41' 2 1 X'41' 2 0 'A'}
    6 "a DESC, b DESC, c" {2 1 X'41' 2 0 'A' 1 1 'A' 1 0 X'41' 0 1 'A'}
  } {
    do_execsql_test 4.5.$tn.$tn2 "
      SELECT a, b, quote(c) FROM t WHERE b IN (0,1) AND c GLOB 'A'
      ORDER BY $ob
    " $res
  }
}

#-------------------------------------------------------------------------
# LIKE optimization on the right-hand table of a RIGHT JOIN.
#
reset_db

do_execsql_test 5.0 {
  CREATE TABLE t1(a, b);
  CREATE TABLE t2(x, y);
  CREATE INDEX t2xy ON t2(x, y);
  INSERT INTO t1 VALUES(1, 'one'), (3, 'three');
  INSERT INTO t2 VALUES(1, 'Ax'), (1, x'4179'), (1, 'Bz');
  INSERT INTO t2 VALUES(3, x'417a'), (3, 'Az');
  INSERT INTO t2 VALUES(4, 'Aq'), (4, x'4171');
}

do_eqp_test 5.1 {
  SELECT b, x, quote(y) FROM t1 RIGHT JOIN t2 ON a=x AND y GLOB 'A*'
  ORDER BY x, y
} {
  QUERY PLAN
  |--SCAN t1
  |--SEARCH t2 USING INDEX t2xy (x=? AND y>? AND y<?)
  |--RIGHT-JOIN t2
  |  `--SCAN t2
  `--USE TEMP B-TREE FOR ORDER BY
}

do_execsql_test 5.2 {
  SELECT b, x, quote(y) FROM t1 RIGHT JOIN t2 ON a=x AND y GLOB 'A*'
  ORDER BY x, y
} {
  one 1 'Ax'  {} 1 'Bz'  one 1 X'4179'
  three 3 'Az'  three 3 X'417A'
  {} 4 'Aq'  {} 4 X'4171'
}


finish_test