Does this make it weirder or what? If you do the replace after the insert you get the expected result.
But if you do the replace, followed by 2 more inserts you get this: SQLite version 3.7.14.1 2012-10-04 19:37:12 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE VIRTUAL TABLE IF NOT EXISTS fts USING fts4(body); sqlite> REPLACE INTO fts ( docid, body ) VALUES (1, "one two three four"); sqlite> REPLACE INTO fts ( docid, body ) VALUES (2, "one two"); sqlite> SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; X'0100000006000000' sqlite> INSERT INTO fts ( docid, body ) VALUES (3, "one two three four"); sqlite> REPLACE INTO fts ( docid, body ) VALUES (4, "one two"); sqlite> SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; X'0300000004000000' X'0300000004000000' 3 rows in table and average columns is 4 now??? Should this still be 4/3 ?? And if you do the INSERT followed by REPLACE you get this which is what you expect. X'0400000003000000' Should this be order dependent? Seems all you need is 1 insert at the beginning and all is as expected. CREATE VIRTUAL TABLE IF NOT EXISTS fts USING fts4(body); INSERT INTO fts ( docid, body ) VALUES (1, "one two three four"); REPLACE INTO fts ( docid, body ) VALUES (2, "one two"); REPLACE INTO fts ( docid, body ) VALUES (3, "one two three four"); REPLACE INTO fts ( docid, body ) VALUES (4, "one two"); SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; X'0400000003000000' And does this help explain it? Malformed DB after the first REPLACE? SQLite version 3.7.14.1 2012-10-04 19:37:12 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE VIRTUAL TABLE IF NOT EXISTS fts USING fts4(body); sqlite> REPLACE INTO fts ( docid, body ) VALUES (1, "one two three four"); sqlite> SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; Error: database disk image is malformed sqlite> REPLACE INTO fts ( docid, body ) VALUES (2, "one two"); sqlite> SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; X'0100000006000000' sqlite> REPLACE INTO fts ( docid, body ) VALUES (3, "one two three four"); sqlite> SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; X'0200000005000000' X'0200000005000000' sqlite> REPLACE INTO fts ( docid, body ) VALUES (4, "one two"); sqlite> SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; X'0300000004000000' X'0300000004000000' Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Eric [ergo...@gmail.com] Sent: Monday, November 26, 2012 10:42 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Replace on fts4 table results in unexpected matchinfo result The following SQL results in X'0100000006000000'. For reference, "na" option should generate total number of documents and the average number of tokens per document. CREATE VIRTUAL TABLE IF NOT EXISTS fts USING fts4(body); REPLACE INTO fts ( docid, body ) VALUES (1, "one two three four"); REPLACE INTO fts ( docid, body ) VALUES (2, "one two"); SELECT quote(matchinfo(fts,'na')) FROM fts WHERE fts.body match 'three'; If "REPLACE" is replaced with "INSERT" in the above, the result is X'0200000003000000', as expected. In either case, the number of rows in fts is as expected, body column is correct, and other matchinfo options (pclx at least), seem to function correctly. Is REPLACE not allowed for fts4 tables, or is this a bug? Output of .version in sqlite3: SQLite 3.7.13 2012-06-11 02:05:22 f5b5a13f7394dc143aa136f1d4faba6839eaa6dc -- Eric _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
