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)
Change History (7)
comment:1 by , 13 years ago
comment:2 by , 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.
by , 13 years ago
Attachment: | t16781-tests-modeltest-reverse_lookup-multi_level_test.diff added |
---|
Corrected tests location to regressiontests
comment:4 by , 13 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:5 by , 13 years ago
Cc: | added |
---|
comment:6 by , 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.
This bug could be related to #9188