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

[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!).

[Tim Graham]

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.

[Mariusz Felisiak]

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

[Marten Kenbeek]

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.

[Nick Pope]

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)).

[jishansingh]

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

[Ryan Heard]

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.

[Ryan Heard]

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.

[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.

[Mariusz Felisiak]

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.

[Ryan Heard]

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.

[Mariusz Felisiak <felisiak.mariusz@…>]

In c6b4d62f:

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