1

I want to create a SQLite virtual table with a content of a real one.

I have a small sample which demonstrates my problem. I already red the official tutorial, but can't find anything wrong in this code. Some users use a rebuild option, but it doesn't work for me.

CREATE TABLE if NOT EXISTS posts (a INTEGER PRIMARY KEY);  
INSERT OR IGNORE INTO posts (a) VALUES(510000);  
INSERT OR IGNORE INTO posts (a) VALUES(510001);  
INSERT OR IGNORE INTO posts (a) VALUES(510300);  
CREATE VIRTUAL TABLE IF NOT EXISTS posts_fts using fts5(content=posts, content_rowid=a, a);  

SELECT * FROM posts_fts where posts_fts MATCH '10' ORDER BY a ASC;

If I run this, I get:

0 rows returned in 2ms from: SELECT * FROM posts_fts where posts_fts match '10' ORDER BY a ASC;

Does anyone have an idea wat I do wrong?

Roman B.
  • 117
  • 9

1 Answers1

0

"10" is not a token in the FTS table.

From the doc:

4.3.1. Unicode61 Tokenizer

The unicode tokenizer classifies all unicode characters as either "separator" or "token" characters. By default all space and punctuation characters, as defined by Unicode 6.1, are considered separators, and all other characters as token characters. More specifically, all unicode characters assigned to a general category beginning with "L" or "N" (letters and numbers, specifically) or to category "Co" ("other, private use") are considered tokens. All other characters are separators.

Each contiguous run of one or more token characters is considered to be a token. The tokenizer is case-insensitive according to the rules defined by Unicode 6.1.

Also from the doc:

3.2. FTS5 Phrases

FTS queries are made up of phrases. A phrase is an ordered list of one or more tokens.

You might try a "prefix query" i.e. MATCH "5*" to see that you get results.

Community
  • 1
  • 1
DinoCoderSaurus
  • 6,110
  • 2
  • 10
  • 15
  • Thanks @DinoCoderSaurus! I tried to match with 'prefix', but it does't work. Did you try to run this code with other `match` expressions? It creates a virtual table with the content of real one, but you can't match anything. I also worked with other builtin tokenizers with the same result. – Roman B. Apr 09 '19 at 09:26
  • Do an integrity check `insert into posts_fts(posts_fts) VALUES ('integrity-check');` If it returns errors, rebuild `insert into posts_fts(posts_fts) VALUES ('rebuild');`. I ran your script on an in-memory database from SQLite command line (version 3.17.0). `validity-check` gave `Error: database disk image is malformed`. After rebuild, the prefix query worked. – DinoCoderSaurus Apr 09 '19 at 11:32
  • Now it works after update to SQLite version 3.27.2 and it works after `rebuild`. It's still a mystery why this issue occurs. – Roman B. Apr 09 '19 at 13:41