Opened 5 years ago

Closed 22 months ago

#14609 closed Bug (fixed)

__or__ method of queries does not return a correctly combined query.

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

Description

Unfortunately, I don't have enough experience in SQL or the internals of Django ORM to better pin down where exactly the problem is, but the symptom is as follows. Given the following code:

def get_view_forms(view, node):
	return get_entity_forms(view, passthrough=False) | get_entity_forms(node)

get_entity_forms returns a queryset. The particular issue I'm running across is that:
get_entity_forms(view, passthrough=False) returns []
get_entity_forms(node) returns [<Form 1>]
get_view_forms(view, node) returns [] when it ought to return [<Form 1>]

More generally, get_entity_forms(node) [i.e. "other"] is being ignored altogether.

I'm not sure what the easiest way to reproduce this would be, other than pulling commit f90e2376351f29fd2ef7106484c72c9e1ab2186e from the bartleby branch of https://github.com/melinath/philo/ and making a basic deployment.


Complete code:

from django.contrib.contenttypes.models import ContentType
from django.db.models import Q
from philo.contrib.bartleby.models import Form
from philo.models import Attribute, Entity


def get_entity_forms(entity, passthrough=True):
	if not isinstance(entity, Entity):
		raise TypeError
	
	if passthrough and entity.attributes.passthrough is not None:
		exclude_keys = [] # Exclude keys already found
		
		def get_attribute_forms(qs_mapper):
			qs = Form.objects.none()
			
			for attribute in qs_mapper.queryset.exclude(key__in=exclude_keys):
				fk_q = Q(foreignkeyvalue_set__attribute_set=attribute)
				m2m_q = Q(foreignkeyvalue_set__manytomanyvalue__attribute_set=attribute)
				
				qs |= Form.objects.filter(fk_q | m2m_q)
				exclude_keys.append(attribute.key)
			
			if qs_mapper.passthrough:
				qs |= get_attribute_forms(qs_mapper.passthrough)
			
			return qs
		
		return get_attribute_forms(entity.attributes).distinct()
	
	entity_ct = ContentType.objects.get_for_model(entity)
	
	fk_q = Q(foreignkeyvalue_set__attribute_set__entity_content_type=entity_ct) &\
		Q(foreignkeyvalue_set__attribute_set__entity_object_id=entity.pk)
	m2m_q = Q(foreignkeyvalue_set__manytomanyvalue__attribute_set__entity_content_type=entity_ct) &\
		Q(foreignkeyvalue_set__manytomanyvalue__attribute_set__entity_object_id=entity.pk)
	
	return Form.objects.filter(fk_q | m2m_q).distinct()


def get_view_forms(view, node):
	return get_entity_forms(view, passthrough=False) | get_entity_forms(node)

The sql generated by get_entity_forms(view, passthrough=False):

SELECT DISTINCT "bartleby_form"."id", "bartleby_form"."title", "bartleby_form"."slug", "bartleby_form"."help_text", "bartleby_form"."email_template_id", 
"bartleby_form"."email_from", "bartleby_form"."save_to_database", "bartleby_form"."record", "bartleby_form"."login_required", 
"bartleby_form"."allow_changes", "bartleby_form"."max_submissions" FROM "bartleby_form" INNER JOIN "philo_foreignkeyvalue" ON ("bartleby_form"."id" = 
"philo_foreignkeyvalue"."object_id") LEFT OUTER JOIN "philo_attribute" ON ("philo_foreignkeyvalue"."id" = "philo_attribute"."value_object_id") LEFT OUTER 
JOIN "philo_manytomanyvalue_values" ON ("philo_foreignkeyvalue"."id" = "philo_manytomanyvalue_values"."foreignkeyvalue_id") LEFT OUTER JOIN 
"philo_manytomanyvalue" ON ("philo_manytomanyvalue_values"."manytomanyvalue_id" = "philo_manytomanyvalue"."id") LEFT OUTER JOIN "philo_attribute" T9 ON 
("philo_manytomanyvalue"."id" = T9."value_object_id") LEFT OUTER JOIN "philo_attribute" T10 ON (T9."id" = T10."id") WHERE 
((("philo_attribute"."entity_content_type_id" = 23 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND "philo_attribute"."value_content_type_id" = 14 ) 
AND ("philo_attribute"."entity_object_id" = 1 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND "philo_attribute"."value_content_type_id" = 14 )) OR 
((T10."entity_content_type_id" = 23 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND T10."value_content_type_id" = 15 ) AND (T10."entity_object_id" 
= 1 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND T10."value_content_type_id" = 15 ))) ORDER BY "bartleby_form"."id" ASC

The sql generated by get_entity_forms(node):

SELECT DISTINCT "bartleby_form"."id", "bartleby_form"."title", "bartleby_form"."slug", "bartleby_form"."help_text", "bartleby_form"."email_template_id", 
"bartleby_form"."email_from", "bartleby_form"."save_to_database", "bartleby_form"."record", "bartleby_form"."login_required", 
"bartleby_form"."allow_changes", "bartleby_form"."max_submissions" FROM "bartleby_form" INNER JOIN "philo_foreignkeyvalue" ON ("bartleby_form"."id" = 
"philo_foreignkeyvalue"."object_id") LEFT OUTER JOIN "philo_attribute" ON ("philo_foreignkeyvalue"."id" = "philo_attribute"."value_object_id") LEFT OUTER 
JOIN "philo_manytomanyvalue_values" ON ("philo_foreignkeyvalue"."id" = "philo_manytomanyvalue_values"."foreignkeyvalue_id") LEFT OUTER JOIN 
"philo_manytomanyvalue" ON ("philo_manytomanyvalue_values"."manytomanyvalue_id" = "philo_manytomanyvalue"."id") LEFT OUTER JOIN "philo_attribute" T9 ON 
("philo_manytomanyvalue"."id" = T9."value_object_id") LEFT OUTER JOIN "philo_attribute" T10 ON (T9."id" = T10."id") WHERE 
((("philo_attribute"."entity_content_type_id" = 19 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND "philo_attribute"."value_content_type_id" = 14 ) 
AND ("philo_attribute"."entity_object_id" = 1 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND "philo_attribute"."value_content_type_id" = 14 )) OR 
((T10."entity_content_type_id" = 19 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND T10."value_content_type_id" = 15 ) AND (T10."entity_object_id" 
= 1 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND T10."value_content_type_id" = 15 ))) ORDER BY "bartleby_form"."id" ASC

The combined SQL:

SELECT DISTINCT "bartleby_form"."id", "bartleby_form"."title", "bartleby_form"."slug", "bartleby_form"."help_text", "bartleby_form"."email_template_id", 
"bartleby_form"."email_from", "bartleby_form"."save_to_database", "bartleby_form"."record", "bartleby_form"."login_required", 
"bartleby_form"."allow_changes", "bartleby_form"."max_submissions" FROM "bartleby_form" INNER JOIN "philo_foreignkeyvalue" ON ("bartleby_form"."id" = 
"philo_foreignkeyvalue"."object_id") LEFT OUTER JOIN "philo_attribute" ON ("philo_foreignkeyvalue"."id" = "philo_attribute"."value_object_id") LEFT OUTER 
JOIN "philo_manytomanyvalue_values" ON ("philo_foreignkeyvalue"."id" = "philo_manytomanyvalue_values"."foreignkeyvalue_id") LEFT OUTER JOIN 
"philo_manytomanyvalue" ON ("philo_manytomanyvalue_values"."manytomanyvalue_id" = "philo_manytomanyvalue"."id") LEFT OUTER JOIN "philo_attribute" T9 ON 
("philo_manytomanyvalue"."id" = T9."value_object_id") LEFT OUTER JOIN "philo_attribute" T10 ON (T9."id" = T10."id") LEFT OUTER JOIN "philo_attribute" T13 
ON ("philo_attribute"."id" = T13."id") WHERE (((("philo_attribute"."entity_content_type_id" = 23 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND 
"philo_attribute"."value_content_type_id" = 14 ) AND ("philo_attribute"."entity_object_id" = 1 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND 
"philo_attribute"."value_content_type_id" = 14 )) OR ((T10."entity_content_type_id" = 23 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND 
T10."value_content_type_id" = 15 ) AND (T10."entity_object_id" = 1 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND T10."value_content_type_id" = 15 
))) OR ((("philo_attribute"."entity_content_type_id" = 19 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND "philo_attribute"."value_content_type_id" 
= 14 ) AND ("philo_attribute"."entity_object_id" = 1 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND "philo_attribute"."value_content_type_id" = 14 
)) OR ((T13."entity_content_type_id" = 19 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND T13."value_content_type_id" = 15 ) AND 
(T13."entity_object_id" = 1 AND "philo_foreignkeyvalue"."content_type_id" = 33 AND T13."value_content_type_id" = 15 )))) ORDER BY "bartleby_form"."id" ASC

Change History (6)

comment:1 Changed 5 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

Marking accepted; you could really help us out here by finding the minimum test case that reproduces this problem. That means the smallest set of models and queries that reproduces the problem.

comment:2 Changed 4 years ago by hvdklauw

Ran into this problem two, tried to work around it with Q objects; still didn't work

class EntityManager(models.Manager):
    
    def between(self, entity1, entity2):
        """
            Returns a queryset that shows entities between the two given entities
        """
        
        result = self.get_query_set().filter(related_to__to_entity=entity1).filter(related_from__from_entity=entity2) | \
                 self.get_query_set().filter(related_to__to_entity=entity2).filter(related_from__from_entity=entity1) | \
                 self.get_query_set().filter(related_from__from_entity=entity1).filter(related_from__from_entity=entity2) | \
                 self.get_query_set().filter(related_to__to_entity=entity1).filter(related_to__to_entity=entity2)
        return result
        
    def between2(self, entity1, entity2):
        from django.db.models import Q
        result = self.get_query_set().filter(
                    (
                        Q(
                            related_to__to_entity=entity1
                        ) & Q(
                            related_from__from_entity=entity2
                        )
                    ) |    
                    (
                        Q( 
                            related_to__to_entity=entity2
                        ) & Q(
                            related_from__from_entity=entity1
                        )
                    ) |
                    (    
                        Q( 
                            related_from__from_entity=entity1
                        ) & Q( 
                             related_from__from_entity=entity1
                        )
                    ) |     
                    (
                        Q( 
                            related_to__to_entity=entity1
                        ) & Q(
                            related_to__to_entity=entity2
                        )
                    )      
                )
        return result

class Entity(models.Model):
    """
        Entity
    """
    name = models.CharField(_('naam'), max_length=250)
    
    objects = EntityManager()

class Relation(models.Model):
    """
        Relation
    """
    from_entity = models.ForeignKey(Entity, related_name='related_to', verbose_name=_('van entiteit'))
    to_entity = models.ForeignKey(Entity, related_name='related_from', verbose_name=_('naar entiteit'))

Resulting query for between is:

SELECT "entities_entity"."id", "entities_entity"."name", "entities_entity"."entity_type_id" 
FROM "entities_entity" 
INNER JOIN "entities_relation" ON ("entities_entity"."id" = "entities_relation"."from_entity_id") 
INNER JOIN "entities_relation" T4 ON ("entities_entity"."id" = T4."to_entity_id") 
LEFT OUTER JOIN "entities_relation" T6 ON ("entities_entity"."id" = T6."to_entity_id") 
LEFT OUTER JOIN "entities_relation" T7 ON ("entities_entity"."id" = T7."from_entity_id") 
WHERE (("entities_relation"."to_entity_id" = 1  AND T4."from_entity_id" = 3 ) 
	OR ("entities_relation"."to_entity_id" = 3  AND T4."from_entity_id" = 1 ) 
	OR (T4."from_entity_id" = 1  AND T6."from_entity_id" = 3 ) 
	OR ("entities_relation"."to_entity_id" = 1  AND T7."to_entity_id" = 3 ))

The problem are the two INNER JOINS I get the expected result when they are also LEFT (OUTER) JOINS

The Q solution gives me this query:

SELECT "entities_entity"."id", "entities_entity"."name", "entities_entity"."entity_type_id" 
FROM "entities_entity" 
LEFT OUTER JOIN "entities_relation" ON ("entities_entity"."id" = "entities_relation"."from_entity_id") 
LEFT OUTER JOIN "entities_relation" T4 ON ("entities_entity"."id" = T4."to_entity_id") 
WHERE (("entities_relation"."to_entity_id" = 1  AND T4."from_entity_id" = 3 ) 
	OR ("entities_relation"."to_entity_id" = 3  AND T4."from_entity_id" = 1 ) 
	OR (T4."from_entity_id" = 1  AND T4."from_entity_id" = 1 ) 
	OR ("entities_relation"."to_entity_id" = 1  AND "entities_relation"."to_entity_id" = 3 ))

Which gives me too many results

comment:3 Changed 4 years ago by julien

  • Severity set to Normal
  • Type set to Bug

comment:4 Changed 4 years ago by melinath

  • Easy pickings unset

Is there any chance that #16172 is related?

comment:5 Changed 4 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:6 Changed 22 months ago by akaariai

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

I tested and the .between() manager method now produces LEFT OUTER JOINS as the reported wanted. If the results are correct, well, hard to say from this ticket's description.

I am going to do a "fixed without adding tests" call on this. Two reasons:

  • The provided example test is far from minimalistic, and thus if the test was to fail later on this would be extremely hard to debug.
  • There have been a lot of added tests for query combining since the report of this, so this is likely already tested.
Note: See TracTickets for help on using tickets.
Back to Top