Hi there,

we're investigating a few strange usability issues in one of our apps. After long research we finally tracked it down to sqlite and how it somewhat seems to have unexpected results (or rather none) when Japanese characters (CJK?) are involved. Testing the same situations with non-Japanese strings (ASCII, Greek, Cyrillic, German, etc.) works normally, but with Japanese it fails in rather strange ways and returning no results.

A somewhat unrelated issue involves matching with $ for "end of string". This typically breaks entirely.

Maybe someone could help me to understand the reasons? I appreciate any help and insight into this issue in advance. I hope the examples are self-explanatory, but not too complex/ messy either. Thank you very much!

Note: we tested this with up-to-date builds on Linux and macOS, also with an older Linux-based build (used by the app) and some matching external regexp module, all build from official sources. Also switching the character set to UTF16 didn't change a thing (but not 100% sure switching with PRAGMA encoding="UTF-16le"; actually did anything, so there's some question mark there.)

Build

$ gcc -DHAVE_READLINE -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DHAVE_USLEEP \
   -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_JSON1 -DSQLITE_TEMP_STORE=3 \
   shell.c sqlite3.c -o sqlite3regex /tmp/lib/libreadline.a \
   /tmp/lib/libhistory.a -ltermcap

Issue #1: Japanese Characters

Prepare

$ sqlite3
SQLite version 3.39.0 2022-06-25 14:57:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE TESTTABLE(ID INTEGER PRIMARY KEY AUTOINCREMENT, TESTSTRING TEXT NOT NULL);
INSERT INTO TESTTABLE VALUES(1,'foobar');
INSERT INTO TESTTABLE VALUES(2,'example');
INSERT INTO TESTTABLE VALUES(3,'Ελλάδα');
INSERT INTO TESTTABLE VALUES(4,'монгол хэл');
INSERT INTO TESTTABLE VALUES(5,'Übergrößengeschäft');
INSERT INTO TESTTABLE VALUES(6,'日本語');
COMMIT;

Example #1

Straight full 1:1 match, fails for the Japanese example string

> SELECT * FROM TESTTABLE WHERE regexp('foobar', TESTSTRING);
1|foobar
> SELECT * FROM TESTTABLE WHERE regexp('Ελλάδα', TESTSTRING);
3|Ελλάδα
> SELECT * FROM TESTTABLE WHERE regexp('монгол хэл', TESTSTRING);
4|монгол хэл
> SELECT * FROM TESTTABLE WHERE regexp('Übergrößengeschäft', TESTSTRING);
5|Übergrößengeschäft
> SELECT * FROM TESTTABLE WHERE regexp('日本語', TESTSTRING);
<no results>

Example #2

Matching start of string makes it work for Japanese too:

> SELECT * FROM TESTTABLE WHERE regexp('^foobar', TESTSTRING);
1|foobar
> SELECT * FROM TESTTABLE WHERE regexp('^Ελλάδα', TESTSTRING);
3|Ελλάδα
> SELECT * FROM TESTTABLE WHERE regexp('^монгол хэл', TESTSTRING);
4|монгол хэл
> SELECT * FROM TESTTABLE WHERE regexp('^Übergrößengeschäft', TESTSTRING);
5|Übergrößengeschäft
> SELECT * FROM TESTTABLE WHERE regexp('^日本語', TESTSTRING);
6|日本語

Example #3

Adding a "OR case" makes it work too

> SELECT * FROM TESTTABLE WHERE regexp('日本語|dummy', TESTSTRING);
6|日本語
> SELECT * FROM TESTTABLE WHERE regexp('日本語|日本語', TESTSTRING);
6|日本語

Example #4

But having to match more doesn't work again

> SELECT * FROM TESTTABLE WHERE regexp('^.*foobar', TESTSTRING);
1|foobar
> SELECT * FROM TESTTABLE WHERE regexp('^.*Ελλάδα', TESTSTRING);
3|Ελλάδα
> SELECT * FROM TESTTABLE WHERE regexp('^.*монгол хэл', TESTSTRING);
4|монгол хэл
> SELECT * FROM TESTTABLE WHERE regexp('^.*Übergrößengeschäft', TESTSTRING);
5|Übergrößengeschäft
> SELECT * FROM TESTTABLE WHERE regexp('^.*日本語', TESTSTRING);
<no results>

Example #5

Just matching part of the Japanese string, fails too

> SELECT * FROM TESTTABLE WHERE regexp('^.*本語', TESTSTRING);
<no results>
> SELECT * FROM TESTTABLE WHERE regexp('^.*語', TESTSTRING);
<no results>

Example #6

But like this it works again

> SELECT * FROM TESTTABLE WHERE regexp('.*日本語', TESTSTRING);
6|日本語
> SELECT * FROM TESTTABLE WHERE regexp('.*語', TESTSTRING);
6|日本語

Issue #2: Matching "End of String" with $ not at the end of the regular expression

Another, somewhat unrelated issue, matching "end of string" in complex situations seems to be broken too, e.g. "starts with 'foo' or ends with 'ample'":

> SELECT * FROM TESTTABLE WHERE regexp('ample$|^foo', TESTSTRING);
Runtime error: unrecognized character

Prepare

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE TESTTABLE(ID INTEGER PRIMARY KEY AUTOINCREMENT, TESTSTRING TEXT NOT NULL);
INSERT INTO TESTTABLE VALUES(1,'foobar #1');
INSERT INTO TESTTABLE VALUES(2,'foobar #2');
INSERT INTO TESTTABLE VALUES(3,'foobar #3');
INSERT INTO TESTTABLE VALUES(4,'foobar #10');
INSERT INTO TESTTABLE VALUES(5,'foobar #11');
INSERT INTO TESTTABLE VALUES(6,'foobar #200');
INSERT INTO TESTTABLE VALUES(7,'foobar #1000');
COMMIT;

Example

Goal to match "1", and "200", but not "10" or "1000", this won't do obviously:

> SELECT * FROM TESTTABLE WHERE regexp('#(1|200)', TESTSTRING);
1|foobar #1
4|foobar #10
5|foobar #11
6|foobar #200
7|foobar #1000

But limiting "1" to the end of the string should do, but it breaks:

> SELECT * FROM TESTTABLE WHERE regexp('#(1$|200)', TESTSTRING);
Runtime error: unmatched '('