Opened 13 years ago

Closed 11 years ago

#16781 closed Bug (fixed)

Wrong SQL for a query-set

Reported by: German M. Bravo Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: German M. Bravo Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I'm using Django 1.3 and the postgres (postgresql_psycopg2) backend. A query set is producing the wrong (invalid) SQL.

Query that breaks the SQL:
Address.objects.exclude(user__relationships_from__from_user__id=100)
Resulting in:

Code highlighting: 

SELECT "bug3_address"."id", "bug3_address"."user_id", "bug3_address"."address" FROM "bug3_address" WHERE NOT ("bug3_address"."user_id" IN (SELECT U2."from_user_id" FROM "entities_user" U1 WHERE (U0."user_id" = 100  AND U2."from_user_id" IS NOT NULL)))

However, this works fine:
Address.objects.exclude(user__relationships_to__from_user__id=100)
Resulting in:

Code highlighting: 

SELECT "bug3_address"."id", "bug3_address"."user_id", "bug3_address"."address" FROM "bug3_address" WHERE NOT (("bug3_address"."user_id" IN (SELECT U2."to_user_id" FROM "bug3_relationship" U2 WHERE (U2."from_user_id" = 100  AND U2."to_user_id" IS NOT NULL)) AND "bug3_address"."user_id" IS NOT NULL))

Also accessing other attributes works fine:
Address.objects.exclude(user__relationships_from__status='A')
Resulting in:

Code highlighting: 

SELECT "bug3_address"."id", "bug3_address"."user_id", "bug3_address"."address" FROM "bug3_address" WHERE NOT (("bug3_address"."user_id" IN (SELECT U2."from_user_id" FROM "bug3_relationship" U2 WHERE (U2."status" = A  AND U2."from_user_id" IS NOT NULL)) AND "bug3_address"."user_id" IS NOT NULL))

These are the models:

Code highlighting: 

from django.db import models
from django.contrib.auth.models import User

class Address(models.Model):
    user = models.ForeignKey(User)
    address = models.CharField(max_length=100)

class Relationship(models.Model):
    status = models.CharField(max_length=1)
    from_user = models.ForeignKey(User, related_name='relationships_from')
    to_user = models.ForeignKey(User, related_name='relationships_to')

Attachments (1)

t16781-tests-modeltest-reverse_lookup-multi_level_test.diff​ (3.2 KB ) - added by jamesp 13 years ago.
Corrected tests location to regressiontests

Download all attachments as: .zip

Change History (7)

comment:1 by anonymous, 13 years ago

This bug could be related to #9188

comment:2 by jamesp, 13 years ago

Duplicated issue. It occurs when a reverse lookup is chained with the field it is reversing...?

(Total newbie, had a hard time expressing what exactly was being queried here, and I hope the test is located in the correct spot.)

It works fine when the reverse lookups are chained from different fields.

comment:3 by jamesp, 13 years ago

Oops, looked again, looks like it should be in regressions :)

by jamesp, 13 years ago

Attachment: t16781-tests-modeltest-reverse_lookup-multi_level_test.diff​ added

Corrected tests location to regressiontests

comment:4 by Alex Gaynor, 13 years ago

Triage Stage: Unreviewed → Accepted

comment:5 by German M. Bravo, 13 years ago

Cc: German M. Bravo added

comment:6 by Anssi Kääriäinen, 11 years ago

Resolution: → fixed
Status: new → closed

I've tested that this doesn't generate breaking SQL, and that the attached tests pass when fixed (the test_ticket16781_same_reverse_field() test wasn't correct).

I am not going to add tests for this ticket, I recall there being some other test additions in this area (especially for invalid SQL), and adding yet three more models to queries/models.py to have a test that is very likely a duplicate doesn't feel good to me.

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