Code

Opened 3 years ago

Closed 8 months ago

#16781 closed Bug (fixed)

Wrong SQL for a query-set

Reported by: Kronuz Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: Kronuz 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 3 years ago.
Corrected tests location to regressiontests

Download all attachments as: .zip

Change History (7)

comment:1 Changed 3 years ago by anonymous

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

This bug could be related to #9188

comment:2 Changed 3 years ago by jamesp

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 Changed 3 years ago by jamesp

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

Changed 3 years ago by jamesp

Corrected tests location to regressiontests

comment:4 Changed 3 years ago by Alex

  • Triage Stage changed from Unreviewed to Accepted

comment:5 Changed 3 years ago by Kronuz

  • Cc Kronuz added

comment:6 Changed 8 months ago by akaariai

  • Resolution set to fixed
  • Status changed from new to 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.

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.