id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
12545	contains and F() don't work well together	Matthew	nobody	"Sorry if this is noted, I did search for a while, but it's hard to search for ""F"" :) I can't see anything in the documentation saying you shouldn't use F() with contains, especially given one of the examples is with lt. But the following code:

{{{
from thing.models import Thing
from django.db import connection
from django.db.models import F
def do(q):
    connection.queries = []
    try:
        list(q)
    except Exception, e:
        print ""Exception thrown: %s"" % e
    print connection.queries[0]['sql']
do( Thing.objects.filter( foo='bar' ) )
do( Thing.objects.filter( foo=F('bar') ) )
do( Thing.objects.filter( foo__contains='bar' ) )
do( Thing.objects.filter( foo__contains=F('bar') ) )
}}}

gives the following (spaces tweaked for display) with sqlite3:
{{{
SELECT ""thing_thing"".""id"", ""thing_thing"".""foo"", ""thing_thing"".""bar""
  FROM ""thing_thing"" WHERE ""thing_thing"".""foo"" = bar 
SELECT ""thing_thing"".""id"", ""thing_thing"".""foo"", ""thing_thing"".""bar""
  FROM ""thing_thing"" WHERE ""thing_thing"".""foo"" =  ""thing_thing"".""bar""
SELECT ""thing_thing"".""id"", ""thing_thing"".""foo"", ""thing_thing"".""bar""
  FROM ""thing_thing"" WHERE ""thing_thing"".""foo"" LIKE %bar% ESCAPE '\' 
Exception thrown: near ""ESCAPE"": syntax error
SELECT ""thing_thing"".""id"", ""thing_thing"".""foo"", ""thing_thing"".""bar""
  FROM ""thing_thing"" WHERE ""thing_thing"".""foo"" LIKE  ESCAPE '\' ""thing_thing"".""bar""
}}}

and the following with MySQL:

{{{
SELECT `thing_thing`.`id`, `thing_thing`.`foo`, `thing_thing`.`bar`
  FROM `thing_thing` WHERE `thing_thing`.`foo` = bar 
SELECT `thing_thing`.`id`, `thing_thing`.`foo`, `thing_thing`.`bar`
  FROM `thing_thing` WHERE `thing_thing`.`foo` =  `thing_thing`.`bar`
SELECT `thing_thing`.`id`, `thing_thing`.`foo`, `thing_thing`.`bar`
  FROM `thing_thing` WHERE `thing_thing`.`foo` LIKE BINARY %bar% 
SELECT `thing_thing`.`id`, `thing_thing`.`foo`, `thing_thing`.`bar`
  FROM `thing_thing` WHERE `thing_thing`.`foo` LIKE BINARY  `thing_thing`.`bar`
}}}

So the sqlite3 appears to generate bad SQL, and the MySQL doesn't add the ""%"" (presumably it would have to use string concatenation), and so just does the equivalent of an exact, not a contain. Hope that's helpful."		closed	Uncategorized	1.1		duplicate			Unreviewed	0	0	0	0	0	0