Opened 6 years ago

Closed 3 years ago

#29865 closed New feature (fixed)

Add logical XOR support to Q() and QuerySet().

Reported by: Griffith Rees Owned by: Ryan Heard
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: xor
Cc: Mariusz Felisiak Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

XOR seems to be available in Postgresql, MySQL, SequelServer and Oracle but NOT sqlite. Two stackoverflow questions cover this sort of thing: https://stackoverflow.com/questions/50408142/django-models-xor-at-the-model-level and https://stackoverflow.com/questions/14711203/perform-a-logical-exclusive-or-on-a-django-q-object.

I propose adding XOR to work with Q queries like the answer to the second question above. This will be my first time making a major contribution so we'll see how this goes (apologies in advance if this is annoying!).

Change History (20)

comment:1 by Tim Graham, 6 years ago

Triage Stage: UnreviewedSomeday/Maybe

It's probably best to write to the DevelopersMailingList to see if there's consensus about this (although having a working patch may help evaluate the idea). I wonder if it's possible to emulate XOR on SQLite similar to what we do for some other database functions.

comment:2 by Mariusz Felisiak, 6 years ago

XOR is not officially supported on Oracle (see doc) you pointed to the old MySQL documentation.

comment:3 by Mariusz Felisiak, 6 years ago

Cc: Mariusz Felisiak added

comment:4 by Marten Kenbeek, 6 years ago

To be clear, you're talking about logical XOR, and not bitwise XOR?

You linked to PostgreSQL's bitwise XOR operator, #. At the moment it does not have a logical XOR operator. The only logical operators it supports are AND, OR and NOT.

in reply to:  4 comment:5 by Nick Pope, 5 years ago

Replying to Marten Kenbeek:

To be clear, you're talking about logical XOR, and not bitwise XOR?

As you've highlighted, this should be for logical XOR and not bitwise XOR. So this is only supported for MariaDB and MySQL which have XOR.

This could be implemented by defining Q.XOR and Q.__xor__() and then propagating that around the place.
It could be possible to support this for other backends by specifying connection.features.supports_logical_xor = False and then writing out the query differently.

For Q(a=1) ^ Q(b=2), the supporting backends would output (a = 1 XOR a = 2), while the others could output ((a = 1 OR b = 2) AND NOT (a = 1 AND b = 2)).

comment:6 by jishansingh, 5 years ago

XOR can be implemented by

def __xor__(self,other):
        return self.__or__(other).__and__(self.__invert__().__or__(other.__invert__()))

it works for sqlite (possibly for others)
wouldn't it solves the problem

in reply to:  description comment:7 by Ryan Heard, 3 years ago

Replying to Griffith Rees:

XOR seems to be available in Postgresql, MySQL, SequelServer and Oracle but NOT sqlite. Two stackoverflow questions cover this sort of thing: https://stackoverflow.com/questions/50408142/django-models-xor-at-the-model-level and https://stackoverflow.com/questions/14711203/perform-a-logical-exclusive-or-on-a-django-q-object.

I propose adding XOR to work with Q queries like the answer to the second question above. This will be my first time making a major contribution so we'll see how this goes (apologies in advance if this is annoying!).

I started on this hoping to use it on my own postgres site, only to realize that postgres does not support logical XOR. Too bad, as it would help with not executing large subqueries multiple times.

Never-the-less I have created a PR with the proposed changes for this here, which probably needs some TLC from a more advanced contributor. This code should add support for XOR across the codebase, to both Q objects and QuerySets, and ensure it gets down the SQL fed to the database.

Note that a TypeError is raised if XOR is attempted on an unsupported backend. This seemed safer than converting on the fly to (A AND ~B) OR (~A AND B), since doing that could lead to some unintended results when the user is expecting XOR to be used. If it is decided that a conversion would be more desirable, then the code can be changed.

comment:8 by Jacob Walls, 3 years ago

Has patch: set
Owner: changed from nobody to Ryan Heard
Status: newassigned

comment:9 by Ryan Heard, 3 years ago

After careful consideration I have decided not to raise a TypeError on unsupported backends, and instead convert on the fly from A XOR B to (A OR B) AND NOT (A AND B).

MySQL will still take advantage of logical XOR.

comment:10 by Nick Pope, 3 years ago

Patch needs improvement: set

comment:11 by Ryan Heard, 3 years ago

Patch needs improvement: unset

comment:12 by Nick Pope, 3 years ago

Triage Stage: Someday/MaybeAccepted

I'd missed that this was triaged as "Someday/Maybe", but I'm accepting based on the fact that we have a plausible implementation in this PR.

comment:13 by Nick Pope, 3 years ago

Summary: Add XOR for use in Q QueriesAdd logical XOR support to Q() and QuerySet().

in reply to:  12 ; comment:14 by Mariusz Felisiak, 3 years ago

Replying to Nick Pope:

I'd missed that this was triaged as "Someday/Maybe", but I'm accepting based on the fact that we have a plausible implementation in this PR.

Tim asked for starting a discussion on DevelopersMailingList, as far as I'm aware we don't have any. Also I'm not sure how widely XOR is used in queries, I never needed this.

in reply to:  14 comment:15 by Ryan Heard, 3 years ago

Replying to Mariusz Felisiak:

Tim asked for starting a discussion on DevelopersMailingList, as far as I'm aware we don't have any. Also I'm not sure how widely XOR is used in queries, I never needed this.

Mariusz, thank you for taking a look at this. As far I know there is no discussion on the mailing list but I welcome one.

I have needed this in one instance and it would've helped, as one part of the operation was a large complex subquery that I would've liked to have only once. Unfortunately I found out doing this that my backend, Postgres, doesn't even support the XOR operator. It would still be nice to have to clean up the code though, and users of MySQL can still benefit from it.

comment:16 by Ryan Heard, 3 years ago

Version: 2.1dev

comment:17 by Mariusz Felisiak, 3 years ago

Patch needs improvement: set

comment:18 by Ryan Heard, 3 years ago

Patch needs improvement: unset

comment:19 by Mariusz Felisiak, 3 years ago

Triage Stage: AcceptedReady for checkin

comment:20 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

Resolution: fixed
Status: assignedclosed

In c6b4d62f:

Fixed #29865 -- Added logical XOR support for Q() and querysets.

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