Opened 13 years ago
Closed 10 years ago
#16731 closed Bug (fixed)
startswith endswith and contains doesn't work with F expression
Reported by: | Owned by: | Thomas C | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | startswith, F(), wildcards |
Cc: | Koen Vossen | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
The expression:
myTable.objects.filter(field1__startswith=F('field2'))
rendered to:
SELECT * FROM `my_table` WHERE `my_table`.`field1` LIKE `my_table`.`field2`
should be:
SELECT * FROM `my_table` WHERE `my_table`.`field1` LIKE CONCAT(`my_table`.`field2`,'%')
Attachments (1)
Change History (14)
comment:1 by , 13 years ago
Description: | modified (diff) |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 13 years ago
This issue also appears in "contains" query and subsequently in istartswith and icontains.
comment:3 by , 11 years ago
Cc: | added |
---|
I've added a test that should succeed when the ticket's been fixed: https://github.com/django/django/pull/1117. I've currently marked it as @expectedFailure
.
comment:4 by , 11 years ago
Version: | 1.3 |
---|
Thanks for your contribution. We can't merge this in without the fix, it's not quite the purpose of expectedFailure
. I'll close the PR for now, I've added your failing test as a patch file to this ticket for posterity.
comment:5 by , 11 years ago
This issue also happens when endswith
is used in conjunction with an F expression.
comment:6 by , 11 years ago
Since this bug was reported three years ago and is still there, I suggest to update the documentation (cf. https://docs.djangoproject.com/en/1.6/topics/db/queries/#filters-can-reference-fields-on-the-model) to say that F() expressions are broken with like queries.
Do you agree?
comment:7 by , 11 years ago
In general we prefer to spend our time fixing bugs rather than documenting them. There are currently 575 open bugs in our ticket tracker. It would take a while to document them all. Hopefully this ticket serves as "documentation" in the sense that if someone searches for this problem they would hopefully find this ticket. That said, if someone were to look into fixing this and decides it's too complex to implement, then I would be open to calling this a limitation, documenting it, and closing the ticket.
comment:8 by , 11 years ago
You are right.
For people reading this after they encounters that bug, a quick workaround is to replace:
myTable.filter(field1__startswith=F('field2'))
with:
myTable.extra(where=["""UPPER("my_table"."field1"::text) LIKE UPPER("my_table"."field2"||'%%')"""])
comment:9 by , 11 years ago
The custom lookups branch has proof-of-concept implementation for this. See https://github.com/akaariai/django/commit/1016159f34674c0df871ed891cde72be8340bb5d.
The problem here is that the lookups are somewhat laborious to write correctly. For example the correct rhs operator for istartswith is something like:
LIKE UPPER(replace(replace(%s, %%%%, '\\%%%%'), '_', '\\_')) || '%%%%')
That is, we need to replace % and _ chars in the field's value (%s is reference to the field in above) with escaped versions, then uppercase that, and finally append % to the string for pattern matching. There isn't anything too complicated in doing this, but 4x backend * 5+ lookups is going to take some time to write with tests.
comment:10 by , 11 years ago
Summary: | startswith and contains doesn't work with F expression → startswith endswith and contains doesn't work with F expression |
---|
comment:11 by , 10 years ago
Has patch: | set |
---|---|
Owner: | changed from | to
Status: | new → assigned |
Version: | → master |
The current implementation (master) looks definitely broken:
- A special case is made of startswith / istartswith which inherit from PatternLookup whereas contains/icontains/endswith/iendswith don't — see https://github.com/django/django/blob/master/django/db/models/lookups.py#L263
- Postgres is the only backend defining the corresponding
pattern_ops
https://github.com/django/django/blob/master/django/db/backends/postgresql_psycopg2/base.py#L88 — used to concatenate the wildcard character when using anF()
expression — and this definition does not escape the database content meaning that % stored in database will be interpreted as wildcard characters.
In https://github.com/django/django/pull/3284, PatternLookup
is inherited by all "LIKE" lookups and the appropriate pattern_ops
are provided for Postgresql, MySQL and SQLite — I don't have an Oracle database to test and there seems to be too much specific code...
comment:13 by , 10 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
Fixed formatting - please use preview.
I have confirmed the problem as follows: