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

[Stephen Burrows]

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

[Russell Keith-Magee]

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.

[Harro]

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

[Stephen Burrows]

Is there any chance that #16172 is related?

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Anssi Kääriäinen]

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.