[Django] #29865: Add XOR for use in Q Queries

Django

unread,

Oct 18, 2018, 2:55:37 PM10/18/18

to django-...@googlegroups.com

#29865: Add XOR for use in Q Queries
-------------------------------------+-------------------------------------
Reporter: spool | Owner: nobody
Type: New | Status: new
feature |
Component: Database | Version: 2.1
layer (models, ORM) |
Severity: Normal | Keywords: xor
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
XOR seems to be available in [https://www.postgresql.org/docs/8.4/static
/functions-math.html Postgresql], [https://dev.mysql.com/doc/refman/8.0/en
/logical-operators.html MySQL], [https://docs.microsoft.com/en-us/sql/mdx
/xor-mdx?view=sql-server-2017 SequelServer] and
[https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/logical-operators.html
Oracle] but NOT [https://stackoverflow.com/questions/2294137/sqlite-
exclusive-or-how-do-i-clear-a-flag 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
[https://stackoverflow.com/a/21220712/678486 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!).

--
Ticket URL: <https://code.djangoproject.com/ticket/29865>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Oct 18, 2018, 8:20:30 PM10/18/18

to django-...@googlegroups.com

#29865: Add XOR for use in Q Queries
-------------------------------------+-------------------------------------

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* stage: Unreviewed => Someday/Maybe

Comment:

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.

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:1>

Django

unread,

Oct 20, 2018, 5:51:35 AM10/20/18

to django-...@googlegroups.com

#29865: Add XOR for use in Q Queries
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by felixxm):

`XOR` is not officially supported on Oracle (see
[https://docs.oracle.com/database/121/SQLRF/functions002.htm#SQLRF51178
doc]) you pointed to the old MySQL documentation.

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:2>

Django

unread,

Oct 20, 2018, 6:09:07 AM10/20/18

to django-...@googlegroups.com

#29865: Add XOR for use in Q Queries
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* cc: felixxm (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:3>

Django

unread,

Oct 20, 2018, 6:27:07 AM10/20/18

to django-...@googlegroups.com

#29865: Add XOR for use in Q Queries
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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
[https://www.postgresql.org/docs/current/static/functions-logical.html
logical operators] it supports are `AND`, `OR` and `NOT`.

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:4>

Django

unread,

Jul 31, 2019, 12:07:17 PM7/31/19

to django-...@googlegroups.com

#29865: Add XOR for use in Q Queries
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Nick Pope):

Replying to [comment:4 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))`.

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:5>

Django

unread,

Dec 7, 2019, 5:11:51 AM12/7/19

to django-...@googlegroups.com

#29865: Add XOR for use in Q Queries
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:6>

Django

unread,

Jun 2, 2021, 4:00:20 PM6/2/21

to django-...@googlegroups.com

#29865: Add XOR for use in Q Queries
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Ryan Heard):

Replying to [ticket:29865 Griffith Rees]:

> XOR seems to be available in [https://www.postgresql.org/docs/8.4/static
/functions-math.html Postgresql], [https://dev.mysql.com/doc/refman/8.0/en
/logical-operators.html MySQL], [https://docs.microsoft.com/en-us/sql/mdx
/xor-mdx?view=sql-server-2017 SequelServer] and
[https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/logical-operators.html
Oracle] but NOT [https://stackoverflow.com/questions/2294137/sqlite-
exclusive-or-how-do-i-clear-a-flag 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
[https://stackoverflow.com/a/21220712/678486 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
[https://github.com/django/django/pull/14480 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:7>

Django

unread,

Jun 2, 2021, 4:51:11 PM6/2/21

to django-...@googlegroups.com

#29865: Add XOR for use in Q Queries
-------------------------------------+-------------------------------------

Reporter: Griffith Rees | Owner: Ryan
| Heard
Type: New feature | Status: assigned

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* owner: nobody => Ryan Heard
* status: new => assigned
* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:8>

Django

unread,

Jun 7, 2021, 4:22:09 PM6/7/21

to django-...@googlegroups.com

#29865: Add XOR for use in Q Queries
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:9>

Django

unread,

Jul 2, 2021, 3:07:30 PM7/2/21

to django-...@googlegroups.com

#29865: Add XOR for use in Q Queries
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Nick Pope):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:10>

Django

unread,

Jul 2, 2021, 4:34:51 PM7/2/21

to django-...@googlegroups.com

#29865: Add XOR for use in Q Queries
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Ryan Heard):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:11>

Django

unread,

Jul 14, 2021, 5:50:50 AM7/14/21

to django-...@googlegroups.com

#29865: Add XOR for use in Q Queries
-------------------------------------+-------------------------------------

Keywords: xor | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Nick Pope):

* stage: Someday/Maybe => Accepted

Comment:

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
[https://github.com/django/django/pull/14480 this PR].

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:12>

Django

unread,

Jul 14, 2021, 5:53:01 AM7/14/21

to django-...@googlegroups.com

#29865: Add logical XOR support to Q() and QuerySet().

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:13>

Django

unread,

Jul 14, 2021, 7:01:55 AM7/14/21

to django-...@googlegroups.com

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak):

Replying to [comment:12 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
[https://github.com/django/django/pull/14480 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:14>

Django

unread,

Jul 14, 2021, 2:01:20 PM7/14/21

to django-...@googlegroups.com

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Ryan Heard):

Replying to [comment:14 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:15>

Django

unread,

Jul 22, 2021, 12:31:44 PM7/22/21

to django-...@googlegroups.com

#29865: Add logical XOR support to Q() and QuerySet().
-------------------------------------+-------------------------------------
Reporter: Griffith Rees | Owner: Ryan
| Heard
Type: New feature | Status: assigned

Component: Database layer | Version: dev

(models, ORM) |
Severity: Normal | Resolution:
Keywords: xor | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Ryan Heard):

* version: 2.1 => dev

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:16>

Django

unread,

Sep 23, 2021, 2:22:59 AM9/23/21

to django-...@googlegroups.com

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:17>

Django

unread,

Dec 4, 2021, 9:31:27 PM12/4/21

to django-...@googlegroups.com

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Ryan Heard):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:18>

Django

unread,

Mar 5, 2022, 6:22:15 AM3/5/22

to django-...@googlegroups.com

#29865: Add logical XOR support to Q() and QuerySet().
-------------------------------------+-------------------------------------
Reporter: Griffith Rees | Owner: Ryan
| Heard

Type: New feature | Status: closed

Component: Database layer | Version: dev
(models, ORM) |

Severity: Normal | Resolution: fixed
Keywords: xor | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"c6b4d62fa2c7f73b87f6ae7e8cf1d64ee5312dc5" c6b4d62f]:
{{{
#!CommitTicketReference repository=""
revision="c6b4d62fa2c7f73b87f6ae7e8cf1d64ee5312dc5"
Fixed #29865 -- Added logical XOR support for Q() and querysets.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29865#comment:20>

Django

unread,

Mar 5, 2022, 6:22:15 AM3/5/22

to django-...@googlegroups.com

#29865: Add logical XOR support to Q() and QuerySet().
-------------------------------------+-------------------------------------
Reporter: Griffith Rees | Owner: Ryan
| Heard

Type: New feature | Status: assigned

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: xor | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------