[Django] #33849: Like statement is not case-sensitive in SQLite backend

[Django]

#33849: Like statement is not case-sensitive in SQLite backend
-------------------------------------+-------------------------------------
Reporter: Ali | Owner: nobody
Type: Bug | Status: new
Component: Database | Version:
layer (models, ORM) | Keywords: Sqlite __contains
Severity: Normal | __icontains
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
**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

--
Ticket URL: <https://code.djangoproject.com/ticket/33849>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#33849: Like statement is not case-sensitive in SQLite backend
-------------------------------------+-------------------------------------
Reporter: Ali | Owner: nobody

Type: Bug | Status: closed
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: Sqlite __contains | Triage Stage:
__icontains | Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => duplicate

Comment:

Duplicate of #15659, see
[https://code.djangoproject.com/ticket/15659#comment:8 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"''

--
Ticket URL: <https://code.djangoproject.com/ticket/33849#comment:1>

[Django]

#33849: Like statement is not case-sensitive in SQLite backend
-------------------------------------+-------------------------------------
Reporter: Ali | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: Sqlite __contains | Triage Stage:
__icontains | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Ali):

Replying to [comment:1 Mariusz Felisiak]:

> Duplicate of #15659, see
[https://code.djangoproject.com/ticket/15659#comment:8 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
[https://www.sqlite.org/pragma.html#pragma_case_sensitive_like document],
so we can use it to fix this?

--
Ticket URL: <https://code.djangoproject.com/ticket/33849#comment:2>

[Django]

#33849: Like statement is not case-sensitive in SQLite backend
-------------------------------------+-------------------------------------
Reporter: Ali | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: Sqlite __contains | Triage Stage:
__icontains | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak):

Replying to [comment:2 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
[https://www.sqlite.org/pragma.html#pragma_case_sensitive_like 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. "''

--
Ticket URL: <https://code.djangoproject.com/ticket/33849#comment:3>