Opened 11 years ago

Closed 10 years ago

#12545 closed (duplicate)

contains and F() don't work well together

Reported by: Matthew Owned by: nobody
Component: Uncategorized Version: 1.1
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


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 = []
    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.

Change History (2)

comment:1 Changed 10 years ago by 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?

comment:2 Changed 10 years ago by Ubercore

Resolution: duplicate
Status: newclosed

Closing this as a duplicate of #11722.

Note: See TracTickets for help on using tickets.
Back to Top