ext/fts5/test/fts5misc.test

# 2019 September 02
#
# 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.  The
# focus of this script is testing the FTS5 module.
#

source [file join [file dirname [info script]] fts5_common.tcl]
set testprefix fts5misc

# If SQLITE_ENABLE_FTS5 is not defined, omit this file.
ifcapable !fts5 {
  finish_test
  return
}

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE t1 USING fts5(a);
}

do_catchsql_test 1.1.1 { 
  SELECT highlight(t1, 4, '<b>', '</b>') FROM t1('*'); 
} {1 {unknown special query: }}
do_catchsql_test 1.1.2 {
  SELECT a FROM t1
    WHERE rank = (SELECT highlight(t1, 4, '<b>', '</b>') FROM t1('*'));
} {1 {unknown special query: }}

do_catchsql_test 1.2.1 { 
  SELECT highlight(t1, 4, '<b>', '</b>') FROM t1('*id'); 
} {1 {no such cursor: 4}}

do_catchsql_test 1.2.2 {
  SELECT a FROM t1
    WHERE rank = (SELECT highlight(t1, 4, '<b>', '</b>') FROM t1('*id'));
} {1 {no such cursor: 6}}

do_catchsql_test 1.3.1 { 
  SELECT highlight(t1, 4, '<b>', '</b>') FROM t1('*reads'); 
} {1 {no such cursor: 0}}

do_catchsql_test 1.3.2 {
  SELECT a FROM t1
    WHERE rank = (SELECT highlight(t1, 4, '<b>', '</b>') FROM t1('*reads'));
} {1 {no such cursor: 0}}

db close
sqlite3 db test.db

do_catchsql_test 1.3.3 {
  SELECT a FROM t1
    WHERE rank = (SELECT highlight(t1, 4, '<b>', '</b>') FROM t1('*reads'));
} {1 {no such cursor: 0}}

fts5_aux_test_functions db
do_catchsql_test 1.3.4 {
  SELECT fts5_columntext(t1) FROM t1('*reads');
} {1 {no such cursor: 0}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 2.0 {
  CREATE TABLE t0(c0);
  CREATE VIRTUAL TABLE vt0 USING fts5(c0);
}
do_execsql_test 2.1.1 {
  BEGIN TRANSACTION;
  INSERT INTO vt0(c0) VALUES ('xyz');
}
do_execsql_test 2.1.2 {
  ALTER TABLE t0 ADD COLUMN c5;
}
do_execsql_test 2.1.3 {
  INSERT INTO vt0(vt0) VALUES('integrity-check');
}
do_execsql_test 2.1.4 {
  INSERT INTO vt0(c0) VALUES ('abc');
  COMMIT
}
do_execsql_test 2.1.5 {
  INSERT INTO vt0(vt0) VALUES('integrity-check');
}

reset_db
do_execsql_test 2.2.1 {
  CREATE TABLE t0(c0);
  CREATE VIRTUAL TABLE vt0 USING fts5(c0);
  BEGIN TRANSACTION;
  INSERT INTO vt0(c0) VALUES ('xyz');
}

do_execsql_test 2.2.2 {
  ALTER TABLE t0 RENAME TO t1;
}
do_execsql_test 2.2.3 {
  INSERT INTO vt0(vt0) VALUES('integrity-check');
}
do_execsql_test 2.2.4 {
  INSERT INTO vt0(c0) VALUES ('abc');
  COMMIT;
}
do_execsql_test 2.2.5 {
  INSERT INTO vt0(vt0) VALUES('integrity-check');
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 3.0 {
  CREATE VIRTUAL TABLE vt0 USING fts5(a);
  PRAGMA reverse_unordered_selects = true;
  INSERT INTO vt0 VALUES('365062398'), (0), (0);
  INSERT INTO vt0(vt0, rank) VALUES('pgsz', '38');
}
do_execsql_test 3.1 {
  UPDATE vt0 SET a = 399905135; -- unexpected: database disk image is malformed
}
do_execsql_test 3.2 {
  INSERT INTO vt0(vt0) VALUES('integrity-check');
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.0 {
  CREATE VIRTUAL TABLE vt0 USING fts5(c0);
  INSERT INTO vt0(c0) VALUES ('xyz');
}

do_execsql_test 4.1 {
  BEGIN;
    INSERT INTO vt0(c0) VALUES ('abc');
    INSERT INTO vt0(vt0) VALUES('rebuild');
  COMMIT;
}

do_execsql_test 4.2 {
  INSERT INTO vt0(vt0) VALUES('integrity-check');
}

do_execsql_test 4.3 {
  BEGIN;
    INSERT INTO vt0(vt0) VALUES('rebuild');
    INSERT INTO vt0(vt0) VALUES('rebuild');
  COMMIT;
}

do_execsql_test 4.4 {
  INSERT INTO vt0(vt0) VALUES('integrity-check');
}

#-------------------------------------------------------------------------
# Ticket [81a7f7b9].
#
reset_db
do_execsql_test 5.0 {
  CREATE VIRTUAL TABLE vt0 USING fts5(c0, c1);
  INSERT INTO vt0(vt0, rank) VALUES('pgsz', '65536');
  WITH s(i) AS (
    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1236
  )
  INSERT INTO vt0(c0) SELECT '0' FROM s;
} {}

do_execsql_test 5.1 {
  UPDATE vt0 SET c1 = 'T,D&p^y/7#3*v<b<4j7|f';
}

do_execsql_test 5.2 {
  INSERT INTO vt0(vt0) VALUES('integrity-check');
}

do_catchsql_test 5.3 {
  INSERT INTO vt0(vt0, rank) VALUES('pgsz', '65537');
} {1 {SQL logic error}}

#-------------------------------------------------------------------------
# Ticket [d392017c].
#
reset_db
do_execsql_test 6.0 {
  CREATE VIRTUAL TABLE vt0 USING fts5(c0);
  WITH s(i) AS (
    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<10000
  )
  INSERT INTO vt0(c0) SELECT '0' FROM s;
  INSERT INTO vt0(vt0, rank) VALUES('crisismerge', 2000);
  INSERT INTO vt0(vt0, rank) VALUES('automerge', 0);
} {}

do_execsql_test 6.1 {
  INSERT INTO vt0(vt0) VALUES('rebuild');
}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 7.0 {
  CREATE VIRTUAL TABLE t1 USING fts5(x);
  INSERT INTO t1(rowid, x) VALUES(1, 'hello world');
  INSERT INTO t1(rowid, x) VALUES(2, 'well said');
  INSERT INTO t1(rowid, x) VALUES(3, 'hello said');
  INSERT INTO t1(rowid, x) VALUES(4, 'well world');

  CREATE TABLE t2 (a, b);
  INSERT INTO t2 VALUES(1, 'hello');
  INSERT INTO t2 VALUES(2, 'world');
  INSERT INTO t2 VALUES(3, 'said');
  INSERT INTO t2 VALUES(4, 'hello');
}

do_execsql_test 7.1 {
  SELECT rowid FROM t1 WHERE (rowid, x) IN (SELECT a, b FROM t2); 
}

do_execsql_test 7.2 {
  SELECT rowid FROM t1 WHERE rowid=2 AND t1 = 'hello';
}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 8.0 {
  CREATE VIRTUAL TABLE vt0 USING fts5(c0, tokenize = "ascii", prefix = 1);
  INSERT INTO vt0(c0) VALUES (x'd1');
}

do_execsql_test 8.1 {
  INSERT INTO vt0(vt0) VALUES('integrity-check');
}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 9.0 {
  CREATE VIRTUAL TABLE t1 using FTS5(mailcontent);
  insert into t1(rowid, mailcontent) values
      (-4764623217061966105, 'we are going to upgrade'),
      (8324454597464624651, 'we are going to upgrade');
}

do_execsql_test 9.1 {
  INSERT INTO t1(t1) VALUES('integrity-check');
}

do_execsql_test 9.2 {
  SELECT rowid FROM t1('upgrade');
} {
  -4764623217061966105 8324454597464624651
}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 10.0 {
  CREATE VIRTUAL TABLE vt1 USING fts5(c1, c2, prefix = 1, tokenize = "ascii");
  INSERT INTO vt1 VALUES (x'e4', '䔬');
}

do_execsql_test 10.1 {
  SELECT quote(CAST(c1 AS blob)), quote(CAST(c2 AS blob)) FROM vt1
} {X'E4' X'E494AC'}

do_execsql_test 10.2 {
  INSERT INTO vt1(vt1) VALUES('integrity-check');
}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 11.0 {
  CREATE VIRTUAL TABLE vt0 USING fts5(
      c0, prefix = 71, tokenize = "porter ascii", prefix = 9
  );
} {}
do_execsql_test 11.1 {
  BEGIN;
  INSERT INTO vt0(c0) VALUES (x'e8');
}
do_execsql_test 11.2 {
  INSERT INTO vt0(vt0) VALUES('integrity-check');
}

#-------------------------------------------------------------------------
# Ticket [752fdbf6] 
#
reset_db
do_execsql_test 11.0 {
  PRAGMA encoding = 'UTF-16';
  CREATE VIRTUAL TABLE vt0 USING fts5(c0, c1);
  INSERT INTO vt0(vt0, rank) VALUES('pgsz', '37');
  INSERT INTO vt0(c0, c1) VALUES (0.66077, 1957391816);
}
do_execsql_test 11.1 {
  INSERT INTO vt0(vt0) VALUES('integrity-check');
}

#-------------------------------------------------------------------------
# Ticket [7c0e06b16] 
#
do_execsql_test 12.0 {
  CREATE TABLE t1(a, b, rank);
  INSERT INTO t1 VALUES('a', 'hello', '');
  INSERT INTO t1 VALUES('b', 'world', '');

  CREATE VIRTUAL TABLE ft USING fts5(a);
  INSERT INTO ft VALUES('b');
  INSERT INTO ft VALUES('y');

  CREATE TABLE t2(x, y, ft);
  INSERT INTO t2 VALUES(1, 2, 'x');
  INSERT INTO t2 VALUES(3, 4, 'b');
}

do_execsql_test 12.1 {
  SELECT * FROM t1 NATURAL JOIN ft WHERE ft MATCH('b')
} {b world {}}
do_execsql_test 12.2 {
  SELECT * FROM ft NATURAL JOIN t1 WHERE ft MATCH('b')
} {b world {}}
do_execsql_test 12.3 {
  SELECT * FROM t2 JOIN ft USING (ft)
} {3 4 b b}

#-------------------------------------------------------------------------
# Forum post https://sqlite.org/forum/forumpost/21127c1160
#
reset_db
sqlite3_db_config db DEFENSIVE 1

do_execsql_test 13.1.0 {
  CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
  CREATE VIRTUAL TABLE b USING fts5(name);
  CREATE TRIGGER a_trigger AFTER INSERT ON a BEGIN 
    INSERT INTO b (name) VALUES ('foo');
  END;
}

do_test 13.1.1 {
  set ::STMT [
    sqlite3_prepare db "INSERT INTO a VALUES (1, 'foo') RETURNING id;" -1 dummy
  ]
  sqlite3_step $::STMT
} {SQLITE_ROW}

do_test 13.1.2 {
  sqlite3_finalize $::STMT
} {SQLITE_OK}

do_test 13.1.3 {
  sqlite3_errmsg db
} {not an error}

reset_db
sqlite3_db_config db DEFENSIVE 1
do_execsql_test 13.2.0 {
  BEGIN;
  CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
  CREATE VIRTUAL TABLE b USING fts5(name);
  CREATE TRIGGER a_trigger AFTER INSERT ON a BEGIN 
    INSERT INTO b (name) VALUES ('foo');
  END;
}

do_test 13.2.1 {
  set ::STMT [
    sqlite3_prepare db "INSERT INTO a VALUES (1, 'foo') RETURNING id;" -1 dummy
  ]
  sqlite3_step $::STMT
} {SQLITE_ROW}

do_test 13.2.2 {
  sqlite3_finalize $::STMT
} {SQLITE_OK}

do_test 13.2.3 {
  sqlite3_errmsg db
} {not an error}

#-------------------------------------------------------------------------
reset_db
db close
sqlite3 db test.db -uri 1

do_execsql_test 14.0 {
  PRAGMA locking_mode=EXCLUSIVE;
  BEGIN;
  ATTACH 'file:/one?vfs=memdb' AS aux1;
  ATTACH 'file:/one?vfs=memdb' AS aux2;
  CREATE VIRTUAL TABLE t1 USING fts5(x);
} {exclusive}
do_catchsql_test 14.1 {
  ANALYZE;
} {1 {database is locked}}
do_catchsql_test 14.2 {
  COMMIT;
} {1 {database is locked}}
do_catchsql_test 14.3 {
  COMMIT;
} {1 {database is locked}}
do_catchsql_test 14.4 {
  ROLLBACK;
} {0 {}}

#-------------------------------------------------------------------------
reset_db
sqlite3 db2 test.db

do_execsql_test 15.0 {
  CREATE TABLE t1(a, b);
  BEGIN;
    SELECT * FROM t1;
}

do_execsql_test -db db2 15.1 {
  BEGIN; 
    CREATE VIRTUAL TABLE x1 USING fts5(y);
}
do_test 15.2 {
  list [catch { db2 eval COMMIT } msg] $msg
} {1 {database is locked}}
do_execsql_test -db db2 15.3 {
  SAVEPOINT one;
} {}
do_execsql_test 15.4 END
do_test 15.5 {
  list [catch { db2 eval COMMIT } msg] $msg
} {0 {}}

db2 close

#-------------------------------------------------------------------------
reset_db
forcedelete test.db2
sqlite3 db2 test.db
do_execsql_test 16.0 {

  ATTACH 'test.db2' AS aux;
  CREATE TABLE aux.t2(x,y);
  INSERT INTO t2 VALUES(1, 2);
  CREATE VIRTUAL TABLE x1 USING fts5(a);
  BEGIN;
    INSERT INTO x1 VALUES('abc');
    INSERT INTO t2 VALUES(3, 4);
}

do_execsql_test -db db2 16.1 {
  ATTACH 'test.db2' AS aux;
  BEGIN;
    SELECT * FROM t2
} {1 2}

do_catchsql_test 16.2 {
  COMMIT;
} {1 {database is locked}}

do_execsql_test 16.3 {
  INSERT INTO x1 VALUES('def');
}

do_execsql_test -db db2 16.4 { 
  END 
}

do_execsql_test 16.5 {
  COMMIT
}

do_execsql_test -db db2 16.6 {
  SELECT * FROM x1
} {abc def}

db2 close

#-------------------------------------------------------------------------
reset_db
do_execsql_test 17.1 {
  CREATE VIRTUAL TABLE ft USING fts5(x, tokenize="unicode61 separators 'X'");
}
do_execsql_test 17.2 {
  SELECT 0 FROM ft WHERE ft MATCH 'X' AND ft MATCH 'X'
}
do_execsql_test 17.3 {
  SELECT 0 FROM ft('X')
}

do_execsql_test 17.4 {
  CREATE VIRTUAL TABLE t0 USING fts5(c0, t="trigram");
  INSERT INTO t0 VALUES('assertionfaultproblem');
}
do_execsql_test 17.5 {
  SELECT 0 FROM t0(0) WHERE c0 GLOB 0;
} {}

do_execsql_test 17.5 {
  SELECT c0 FROM t0 WHERE c0 GLOB '*f*';
} {assertionfaultproblem}
do_execsql_test 17.5 {
  SELECT c0 FROM t0 WHERE c0 GLOB '*faul*';
} {assertionfaultproblem}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 18.0 {
  BEGIN;
    CREATE VIRTUAL TABLE t1 USING fts5(text);
    ALTER TABLE t1 RENAME TO t2;
}

do_execsql_test 18.1 {
    DROP TABLE t2;
}

do_execsql_test 18.2 {
  COMMIT;
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 19.0 {
  CREATE VIRTUAL TABLE t1 USING fts5(text);
  CREATE TABLE t2(text);
  BEGIN;
    INSERT INTO t1 VALUES('one');
    INSERT INTO t1 VALUES('two');
    INSERT INTO t1 VALUES('three');
    INSERT INTO t1 VALUES('one');
    INSERT INTO t1 VALUES('two');
    INSERT INTO t1 VALUES('three');
    SAVEPOINT one;
      INSERT INTO t2 VALUES('one');
      INSERT INTO t2 VALUES('two');
      INSERT INTO t2 VALUES('three');
    ROLLBACK TO one;
  COMMIT;
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 20.0 {
  CREATE VIRTUAL TABLE x1 USING fts5(a);
  INSERT INTO x1(rowid, a) VALUES
      (1, 'a b c d'),
      (2, 'x b c d'),
      (3, 'x y z d'),
      (4, 'a y c x');
}

do_execsql_test 20.1 {
  SELECT rowid FROM x1 WHERE x1 MATCH 'a' AND x1 MATCH 'b';
} {1}

do_execsql_test 20.2 {
  SELECT rowid FROM x1 WHERE x1 MATCH 'a' AND x1 MATCH 'y';
} {4}

do_execsql_test 20.3 {
  SELECT rowid FROM x1 WHERE x1 MATCH 'a' OR x1 MATCH 'y';
} {1 4 3}

do_execsql_test 20.4 {
  SELECT rowid FROM x1 WHERE x1 MATCH 'a' OR (x1 MATCH 'y' AND x1 MATCH 'd');
} {1 4 3}

do_execsql_test 20.5 {
  SELECT rowid FROM x1 WHERE x1 MATCH 'z' OR (x1 MATCH 'a' AND x1 MATCH 'd');
} {3 1}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 21.0 {
  CREATE TABLE t1(ii INTEGER, x TEXT, y TEXT);
  CREATE VIRTUAL TABLE xyz USING fts5(content_rowid=ii, content=t1, x, y);
  INSERT INTO t1 VALUES(1, 'one', 'i');
  INSERT INTO t1 VALUES(2, 'two', 'ii');
  INSERT INTO t1 VALUES(3, 'tree', 'iii');
  INSERT INTO xyz(xyz) VALUES('rebuild');
}

do_execsql_test 21.1 {
  UPDATE xyz SET y='TWO' WHERE rowid=2;
  UPDATE t1 SET y='TWO' WHERE ii=2;
}

do_execsql_test 21.2 {
  PRAGMA integrity_check
} {ok}

sqlite3_db_config db DEFENSIVE 1
do_execsql_test 21.3 {
  CREATE TABLE xyz_notashadow(x, y);
  DROP TABLE xyz_notashadow;
}
sqlite3_db_config db DEFENSIVE 0

#-------------------------------------------------------------------------
reset_db
do_execsql_test 22.0 {
  SELECT fts5(NULL);
} {{}}
do_execsql_test 22.1 {
  SELECT count(*) FROM (
    SELECT fts5_source_id()
  )
} {1}
execsql_pp {
  SELECT fts5_source_id()
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 23.0 {
  CREATE VIRTUAL TABLE x1 USING fts5(x);
  INSERT INTO x1 VALUES('one + two + three');
  INSERT INTO x1 VALUES('one + xyz + three');
  INSERT INTO x1 VALUES('xyz + two + xyz');
}
do_execsql_test 23.1 {
  SELECT rowid FROM x1('one + two + three');
} {1}

do_execsql_test 23.2 {
  SELECT rowid FROM x1('^".." AND one');
} {}

do_execsql_test 23.3 {
  SELECT rowid FROM x1('abc NEAR ".." NEAR def');
} {}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 24.0 {
  CREATE VIRTUAL TABLE t1 USING fts5(a, detail='none');
  INSERT INTO t1(a) VALUES('a');
}

do_execsql_test 24.2 {
  SELECT rank FROM ( SELECT rank FROM t1('a NOT "" NOT def') ) ORDER BY 1;
} {-1e-06}

do_execsql_test 24.3 {
  SELECT rank FROM ( SELECT rank FROM t1('a NOT � NOT def') ) ORDER BY 1;
} {-1e-06}

do_execsql_test 24.4 {
  SELECT rank FROM ( SELECT rank FROM t1('a NOT "" NOT def') );
} {-1e-06}

#-------------------------------------------------------------------------
reset_db
fts5_aux_test_functions db

do_execsql_test 25.0 {
  CREATE VIRTUAL TABLE t1 USING fts5(a, detail='none', content='');
  INSERT INTO t1(a) VALUES('a b c');
}

do_execsql_test 25.0 {
  SELECT fts5_test_poslist(t1) FROM t1('b') ORDER BY rank;
} {{}}

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

do_execsql_test 26.0 {
  PRAGMA foreign_keys = ON;
  CREATE TABLE t1(x INTEGER PRIMARY KEY);
  CREATE TABLE t2(y INTEGER PRIMARY KEY,
      z INTEGER REFERENCES t1(x) DEFERRABLE INITIALLY DEFERRED
  );
  CREATE VIRTUAL TABLE t3 USING fts5(a, b, content='', tokendata=1);
}

do_execsql_test 26.1 {
  BEGIN;
    INSERT INTO t2 VALUES(1,111);
    INSERT INTO t3 VALUES(3,3);
    PRAGMA defer_foreign_keys=ON;
    DELETE FROM t2 WHERE y+1;
  COMMIT;
}

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

do_execsql_test 27.0 {
  CREATE VIRTUAL TABLE ft1 USING fts5(a, b);
  INSERT INTO ft1(rowid, a, b) VALUES(3, '3', '3');
}

do_execsql_test 27.1 {
  SELECT * FROM ft1 WHERE rowid=3 AND b MATCH 'hello';
}

finish_test