COLLATE NOCASE

[mtissington]

This is a UTF16 database

I have a very strange problem when using COLLATE NOCASE on a column.
It seems that when I do a SELECT the WHERE clause is only looking at the first letter.

CREATE TABLE words (
word TEXT COLLATE NOCASE NOT NULL CHECK(word <> ''),
);

INSERT INTO words (word) VALUE ('aw');

SELECT * FROM WORDS WHERE word = 'a'

returns ALL rows that being with 'a'

I might add that using sqlite3.exe produces the correct result.

[justinclift]

Interesting. With the sqlite3.exe version that's working, which version is that? Not sure if it makes a difference, but it might be helpful. 😄

[mtissington]

Its 3.20.1

[justinclift]

Just tried it here with 3.10.1 (on Linux though), with the following SQL in a brand new (unsaved) database. The query correctly returned no rows:

PRAGMA encoding = "UTF-16";

CREATE TABLE words (
    word TEXT COLLATE NOCASE NOT NULL CHECK(word <> '')
);

INSERT INTO words (word) VALUES ('aw');

SELECT * FROM WORDS WHERE word = 'a';

Note that your SQL has two slight typos in it, which stopped it from running as-is when cut-n-pasted. 😉

• word TEXT COLLATE NOCASE NOT NULL CHECK(word <> ''),
  • The comma on the end needed removing
• INSERT INTO words (word) VALUE ('aw');
  • Should be VALUES not VALUE

They're both minor, so I guess you've adapted your original SQL manually. Not really sure what to look at, as it's working for me. Maybe someone else can try it on their Windows system and see if it mucks up?

[justinclift]

Ahhh, it seems like my test database above is still UTF-8. Running:

PRAGMA encoding;

Returns UTF-8. I'll probably need to muck around with creating the database from the command line as UTF-16 first instead. 😄

[justinclift]

Yep, when creating the database as UTF-16 using the sqlite3 CLI, the problem happens for me too when running the select in DB4S.

DB4S does display a warning for the database though, indicating there's likely to be trouble:

@MKleusberg Shouldn't "NOCASE" work?

    https://sqlite.org/datatype3.html#collating_sequences

[justinclift]

As a data point #411 is related. 😄

[mtissington]

Good you see the problem ... need to get rid of that scary warning :)

[MKleusberg]

Ok, this seems to be two issues at once 😄

As described in @justinclift's link, the NOCASE function is built into SQLite and we don't need to override that at all. I've fixed that which has two effects: 1) the warning is gone for NOCASE, BINARY, and RTRIM collations, and 2) the SELECT query posted by @mtissington is working now.

The other issue was in our own collation function which was invoked before. It didn't handle UTF16 correctly which led to the initial problem. Even though it doesn't matter for this issue any more, I've still fixed it too 😄

@mtissington Are you ok to download tomorrow's nightly build and see if it's working for you? 😄

[mtissington]

great - thanks - happy to test.

[mtissington]

I can confirm the issue is fixed - many thanks :)

[justinclift]

Excellent, thanks for confirming it @mtissington. Should we close #411 too?

[MKleusberg]

Same for #367. Can we close that, too?

[justinclift]

Good point. Just closed that one too. 😄