Opened 2 years ago

Closed 2 years ago

Last modified 2 years ago

#33849 closed Bug (duplicate)

Like statement is not case-sensitive in SQLite backend

Reported by: Ali Owned by: nobody
Component: Database layer (models, ORM) Version:
Severity: Normal Keywords: Sqlite __contains __icontains
Cc: Like, statement, is, not, case-sensitive, in, SQLite, backend Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Like statement is not case-sensitive in SQLite backend.
I know that was mentioned in the documentation, but I think the Glob statement may solve it or the alternative statements for Like,
the contains and icontains lookup has the same behavior because using the Like statement in the query, like this :

 "SELECT ### FROM #### WHERE ### LIKE %####%"

but we can change it to this :

"SELECT ### FROM #### WHERE ### GLOB *####*"

for contains lookup in the SQLite backend, because the Glob statement is case-sensitive,the Regex statement also its a candidate

Change History (3)

comment:1 by Mariusz Felisiak, 2 years ago

Resolution: duplicate
Status: newclosed

Duplicate of #15659, see comment:

"but looking at GLOB, this doesn't seem possible, because it cannot be used as a drop in replacement for LIKE with different wildcards, since it lacks the ESCAPE clause that LIKE has, which we need"

in reply to:  1 ; comment:2 by Ali, 2 years ago

Replying to Mariusz Felisiak:

Duplicate of #15659, see comment:

"but looking at GLOB, this doesn't seem possible, because it cannot be used as a drop in replacement for LIKE with different wildcards, since it lacks the ESCAPE clause that LIKE has, which we need"

We have PRAGMA statement called case_sensitive_like, which is designed to specifically make the LIKE operator case-sensitive for ASCII characters, see the document, so we can use it to fix this?

in reply to:  2 comment:3 by Mariusz Felisiak, 2 years ago

Replying to Ali:

We have PRAGMA statement called case_sensitive_like, which is designed to specifically make the LIKE operator case-sensitive for ASCII characters, see the document, so we can use it to fix this?

This is also discussed in the original ticket:

" (With the case_sensitive_like=true, just the top left is changed, which is not an improvement). Unfortunately, this means we've got problems with Unicode whichever way we go, because we don't have a way of doing case insensitive matching. "

Note: See TracTickets for help on using tickets.
Back to Top