Code

Opened 4 years ago

Closed 4 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: UI/UX:

Description

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.

Attachments (0)

Change History (2)

comment:1 Changed 4 years ago by Ubercore

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 4 years ago by Ubercore

  • Resolution set to duplicate
  • Status changed from new to closed

Closing this as a duplicate of #11722.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.