[Django] #16731: startswith and contains doesn't work with F expression

Django

unread,

Aug 30, 2011, 1:12:13 PM8/30/11

to django-...@googlegroups.com

#16731: startswith and contains doesn't work with F expression
-------------------------------------+-------------------------------------
Reporter: ronnas@… | Owner: nobody
Type: Bug | Status: new
Milestone: | Component: Database layer
Version: 1.3 | (models, ORM)
Keywords: startswith, F(), | Severity: Normal
wildcards | Triage Stage: Unreviewed
Has patch: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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`,'%')

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

Django

unread,

Sep 4, 2011, 6:19:27 AM9/4/11

to django-...@googlegroups.com

#16731: startswith and contains doesn't work with F expression
-------------------------------------+-------------------------------------
Reporter: ronnas@… | Owner: nobody
Type: Bug | Status: new
Milestone: | Component: Database layer
Version: 1.3 | (models, ORM)

* needs_better_patch: => 0
* needs_docs: => 0
* needs_tests: => 0
* stage: Unreviewed => Accepted

Old description:

> 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`,'%')

New description:

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`,'%')
}}}

--

Comment:

Fixed formatting - please use preview.

I have confirmed the problem as follows:

{{{
# todo/models.py

class Todo(models.Model):
summary = models.CharField(max_length=100, verbose_name=_('summary'))
details = models.TextField(blank=True, verbose_name=_('details'))
}}}

{{{
% ./manage.py shell

>>> from todo.models import *>>> from django.db.models import F
>>> Todo.objects.filter(details__startswith=F('summary'))
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "django/db/models/query.py", line 66, in __repr__
data = list(self[:REPR_OUTPUT_SIZE + 1])
File "django/db/models/query.py", line 81, in __len__
self._result_cache.extend(self._iter)
File "django/db/models/query.py", line 266, in iterator
for row in compiler.results_iter():
File "django/db/models/sql/compiler.py", line 699, in results_iter
for rows in self.execute_sql(MULTI):
File "django/db/models/sql/compiler.py", line 754, in execute_sql
cursor.execute(sql, params)
File "django/db/backends/util.py", line 34, in execute
return self.cursor.execute(sql, params)
File "django/db/backends/sqlite3/base.py", line 261, in execute
return Database.Cursor.execute(self, query, params)
DatabaseError: near "ESCAPE": syntax error
>>> from django.db import connection
>>> connection.queries
[{'time': '0.000', 'sql': u'SELECT "todo_todo"."id",
"todo_todo"."summary", "todo_todo"."details" FROM "todo_todo" WHERE
"todo_todo"."details" LIKE ESCAPE \'\\\' "todo_todo"."summary" LIMIT
21'}]
}}}

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

Django

unread,

Sep 4, 2011, 10:12:27 AM9/4/11

to django-...@googlegroups.com

#16731: startswith and contains doesn't work with F expression
-------------------------------------+-------------------------------------
Reporter: ronnas@… | Owner: nobody
Type: Bug | Status: new
Milestone: | Component: Database layer
Version: 1.3 | (models, ORM)

Comment (by anonymous):

This issue also appears in "contains" query and subsequently in
istartswith and icontains.

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

Django

unread,

May 18, 2013, 10:28:22 AM5/18/13

to django-...@googlegroups.com

#16731: startswith and contains doesn't work with F expression
-------------------------------------+-------------------------------------
Reporter: ronnas@… | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.3

Changes (by kvsn):

* cc: kvsn (added)

Comment:

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`.

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

Django

unread,

May 19, 2013, 4:49:44 AM5/19/13

to django-...@googlegroups.com

#16731: startswith and contains doesn't work with F expression
-------------------------------------+-------------------------------------
Reporter: ronnas@… | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version:

Changes (by mjtamlyn):

* version: 1.3 =>

Comment:

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.

--
Ticket URL: <https://code.djangoproject.com/ticket/16731#comment:4>

Django

unread,

May 26, 2013, 9:32:41 PM5/26/13

to django-...@googlegroups.com

#16731: startswith and contains doesn't work with F expression
-------------------------------------+-------------------------------------
Reporter: ronnas@… | Owner: nobody
Type: Bug | Status: new

Comment (by uberj@…):

This issue also happens when `endswith` is used in conjunction with an F
expression.

--
Ticket URL: <https://code.djangoproject.com/ticket/16731#comment:5>

Django

unread,

Dec 19, 2013, 11:00:36 AM12/19/13

to django-...@googlegroups.com

#16731: startswith and contains doesn't work with F expression
-------------------------------------+-------------------------------------
Reporter: ronnas@… | Owner: nobody
Type: Bug | Status: new

Comment (by orontee):

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?

--
Ticket URL: <https://code.djangoproject.com/ticket/16731#comment:6>

Django

unread,

Dec 19, 2013, 11:19:33 AM12/19/13

to django-...@googlegroups.com

#16731: startswith and contains doesn't work with F expression
-------------------------------------+-------------------------------------
Reporter: ronnas@… | Owner: nobody
Type: Bug | Status: new

Comment (by timo):

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.

--
Ticket URL: <https://code.djangoproject.com/ticket/16731#comment:7>

Django

unread,

Dec 19, 2013, 12:08:54 PM12/19/13

to django-...@googlegroups.com

#16731: startswith and contains doesn't work with F expression
-------------------------------------+-------------------------------------
Reporter: ronnas@… | Owner: nobody
Type: Bug | Status: new

Comment (by orontee):

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
{{{
qs.extra(where=["""UPPER("my_table"."field1"::text) LIKE
UPPER("my_table"."field2"||'%%')"""])
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/16731#comment:8>

Django

unread,

Dec 28, 2013, 11:44:46 AM12/28/13

to django-...@googlegroups.com

#16731: startswith and contains doesn't work with F expression
-------------------------------------+-------------------------------------
Reporter: ronnas@… | Owner: nobody
Type: Bug | Status: new

Comment (by akaariai):

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.

--
Ticket URL: <https://code.djangoproject.com/ticket/16731#comment:9>

Django

unread,

Dec 31, 2013, 5:13:55 AM12/31/13

to django-...@googlegroups.com

#16731: startswith endswith and contains doesn't work with F expression

-------------------------------------+-------------------------------------
Reporter: ronnas@… | Owner: nobody
Type: Bug | Status: new

--
Ticket URL: <https://code.djangoproject.com/ticket/16731#comment:10>

Django

unread,

Sep 27, 2014, 6:57:53 AM9/27/14

to django-...@googlegroups.com

#16731: startswith endswith and contains doesn't work with F expression
-------------------------------------+-------------------------------------
Reporter: ronnas@… | Owner: tchaumeny
Type: Bug | Status: assigned
Component: Database layer | Version: master

(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: startswith, F(), | Needs documentation: 0
wildcards | Patch needs improvement: 0

Has patch: 1 | UI/UX: 0

Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Changes (by tchaumeny):

* status: new => assigned
* owner: nobody => tchaumeny
* has_patch: 0 => 1
* version: => master

Comment:

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 an `F()`
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 for 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...

--
Ticket URL: <https://code.djangoproject.com/ticket/16731#comment:11>

Django

unread,

Nov 28, 2014, 1:20:59 AM11/28/14

to django-...@googlegroups.com

#16731: startswith endswith and contains doesn't work with F expression
-------------------------------------+-------------------------------------

Reporter: ronnas@… | Owner: tchaumeny
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) | Resolution:

* stage: Accepted => Ready for checkin

Comment:

Looks good to me.

--
Ticket URL: <https://code.djangoproject.com/ticket/16731#comment:12>

Django

unread,

Nov 28, 2014, 5:44:46 AM11/28/14

to django-...@googlegroups.com

#16731: startswith endswith and contains doesn't work with F expression
-------------------------------------+-------------------------------------
Reporter: ronnas@… | Owner: tchaumeny
Type: Bug | Status: closed

Component: Database layer | Version: master

(models, ORM) | Resolution: fixed

Changes (by Anssi Kääriäinen <akaariai@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"6b5d82749c57a1aae8c9e81d2b85b2cadb9ef933"]:
{{{
#!CommitTicketReference repository=""
revision="6b5d82749c57a1aae8c9e81d2b85b2cadb9ef933"
Fixed #16731 -- Made pattern lookups work properly with F() expressions
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/16731#comment:13>

Reply all

Reply to author

Forward