contains and F() don't work well together

[Matthew]

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.

[Ubercore]

I believe this might be a duplicate of #11722, which I've spent some time working on, but haven't fixed properly yet. Basically any sql operation with an infix argument, like the contains operator in sqlite, produces incorrect sql with F() expressions.

Can you confirm that, and close as a dupe if you agree?

[Ubercore]

Closing this as a duplicate of #11722.