SQLite custom functions as match string

Question

I create a SQLite function which takes a string and returns another string, then I can use the return value as match strings. Code is here

It works very well except for the single quotes. In this case, it can't match any rows, but if I directly use the returned string, it can match. Anyone know what's the problem here?

sqlite> select simple_query('''');
"''"
sqlite> select '    ', simple_highlight(t1, 0, '[', ']') from t1 where x match simple_query('''');
sqlite> select '    ', simple_highlight(t1, 0, '[', ']') from t1 where x match '"''"';
    |@English &special _characters."[']bacon-&and[']-eggs%

Full example here

Answer 1

This question finally answered in sqlite-forum, and I'd like to post the reason in here.

The reason is SQLite will try to escape the string for us, we can verify that after turn on quote mode, as you can see, our return value will be escaped from "'" to "''" by SQLite. Which means we don't need to escape single quote in our function.

sqlite> select simple_query('''');
"'"
sqlite> select simple_query('"');
""""
sqlite> .mode quote
sqlite> select simple_query('"');
'""""'
sqlite> select simple_query('''');
'"''"'