COLLATE DIACRITIC Error

[diegocgaona]

In my Android App (in a KMP project with N1QL, using Kotbase, but I think the error come from CouchbaseLite v3.1.9), I'm trying to use COLLATE DIACRITIC (from here: https://kotbase.dev/current/n1ql-query-strings/#arguments_8) to use with ORDER BY and search, but I'm having an error. I'm using like this: "ORDER BY $orderByExpr COLLATE NODIACRITIC $sortDir"

Even if I use DIACRITIC (without NO), I have the error. If I try COLLATE UNICODE or COLLATE NOCASE, it works fine.

My SQL is like: SELECT META().id as _id, Tags.* FROM TagsWHEREmeta.type = $typeParam0 AND (LOWER(name.txt) LIKE $term1 OR LOWER(name.en) LIKE $term2 OR LOWER(name.es) LIKE $term3 OR LOWER(name.pt) LIKE $term4) ORDER BY LOWER(IFMISSINGORNULL(name.pt, name.txt, "")) COLLATE DIACRITIC ASC

The error is:

java.lang.IllegalStateException: Failed creating query
Caused by: CouchbaseLiteException{CouchbaseLite,23,'N1QL syntax error near character 165
(CouchbaseLite Android v3.1.9-1@35 (CE/release, Commit/unofficial@4be996b9d781 Core/3.1.9 (5) at 2024-07-31T23:42:52.615748797Z) on Java; Android 15; sdk_gphone64_x86_64)'}
at com.couchbase.lite.N1qlQuery.prepQueryLocked(N1qlQuery.java:54)
at com.couchbase.lite.AbstractQuery.getC4QueryLocked(AbstractQuery.java:274)
at com.couchbase.lite.AbstractQuery.setParameters(AbstractQuery.java:133)
130 more
Caused by: LiteCoreException{1, 23, "N1QL syntax error near character 165"}
at com.couchbase.lite.internal.core.impl.NativeC4Query.createQuery(Native Method)
at com.couchbase.lite.internal.core.impl.NativeC4Query.nCreateQuery(NativeC4Query.java:28)
at com.couchbase.lite.internal.core.C4Query.<init>(C4Query.java:83)
at com.couchbase.lite.internal.core.C4Query.create(C4Query.java:63)
at com.couchbase.lite.internal.core.C4Database.createN1qlQuery(C4Database.java:547)
at com.couchbase.lite.AbstractDatabase.createN1qlQuery(AbstractDatabase.java:1282)
at com.couchbase.lite.N1qlQuery.prepQueryLocked(N1qlQuery.java:53)
132 more

What could be causing it? I don't think it is my code because it works fine if I use NOCASE or UNICODE.

Thanks!

[diegocgaona]

I found (thanks to Kotbase creator) the documentation is wrong, it should be DIAC, not DIACRITIC.

But, I don't know if you can test it, but it is not working for me. Even with NODIAC (and I tested DIAC too, and variations with UNICODE), It doesn't return results with accentuations. Like: Search term: cafe / item.name: café = No results Search term: acido / item.name: ácido = No results

Search term: café / item.name: café = Item found Search term: ácido / item.name: ácido = Item found

UNICODE alone works for me to other cases, then I think my expression is correct, but something is not working.

[jeffdgr8]

This was resolved here https://github.com/jeffdgr8/kotbase/issues/43.