SQLite Forum

Use custom string function in FTS5 match clause
What I gather from your post is that the middle query of the above 3 fails to yield the results you hope to see.  You have presented a lot of code to read, which indicates that you have not narrowed this problem much, if at all.

One problem that does appear from a superficial study is this: Your function, simple_query(), when given the SQL literal '''' as its argument, appears to return either the SQL empty string literal token (two successive single-quotes), or that token bounded by double-quotes. [a] Neither one is likely to be a suitable right operand for the match operator.

[a. Showing the .mode meta-command output would show whether the double-quotes are being emitted by the sqlite3 shell or by your simple_query() function. ]

I think maybe your problem is that you expect that a pair of concatenated single-quotes, as a **string value**, should be interpreted as a length 1 string value containing a single-quote character. That is not so. The conversion of a SQL literal such as <code>''''</code> to that length 1 string value happens when the SQL is parsed (or lexically analyzed, actually), and not as its parsed and compiled form is executed. Of course, the return from your custom function known as simple_query() is never passed through the SQL lexer or compiler; instead it is made, (by the compiler's output code), to participate in evaluation of the binary match expression with whatever string value it has, without any quoting, quote-escaping, or any such finagling.