[Django] #31390: Using Q as rhs value in filter causes SQL Syntax error
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
MarcinWieczorek |
Type: Bug | Status: new
Component: Database | Version: 3.0
layer (models, ORM) |
Severity: Normal | Keywords: Q filter SQL
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Hello. I discovered this issue by accident. I couldn't find what
documentation says about undefined behaviour of filter, so I assume it is
a bug.
When a Q object is passed as right hand side value to filter it is treated
as allowed, but in the end serializes to empty string. I assume that a one
"word" AND is simplified to that word, (q AND nothing = q) and also two
"nothings" ANDed get simplified to empty string. This results in something
like "pk = LIMIT" in the example.
The model is trivial, it works for anything.
{{{
class A(models.Model):
pass
}}}
Steps to reproduce
{{{
./manage.py shell
>>> from app.models import *
>>> from django.db.models import Q
>>> A.objects.filter(pk=Q())
Traceback (most recent call last):
File "/secret/venv37/lib/python3.7/site-
packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "/secret/venv37/lib/python3.7/site-
packages/django/db/backends/sqlite3/base.py", line 396, in execute
return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: near "LIMIT": syntax error
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/secret/venv37/lib/python3.7/site-
packages/django/db/models/query.py", line 252, in __repr__
data = list(self[:REPR_OUTPUT_SIZE + 1])
File "/secret/venv37/lib/python3.7/site-
packages/django/db/models/query.py", line 276, in __iter__
self._fetch_all()
File "/secret/venv37/lib/python3.7/site-
packages/django/db/models/query.py", line 1261, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File "/secret/venv37/lib/python3.7/site-
packages/django/db/models/query.py", line 57, in __iter__
results = compiler.execute_sql(chunked_fetch=self.chunked_fetch,
chunk_size=self.chunk_size)
File "/secret/venv37/lib/python3.7/site-
packages/django/db/models/sql/compiler.py", line 1151, in execute_sql
cursor.execute(sql, params)
File "/secret/venv37/lib/python3.7/site-
packages/django/db/backends/utils.py", line 100, in execute
return super().execute(sql, params)
File "/secret/venv37/lib/python3.7/site-
packages/django/db/backends/utils.py", line 68, in execute
return self._execute_with_wrappers(sql, params, many=False,
executor=self._execute)
File "/secret/venv37/lib/python3.7/site-
packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/secret/venv37/lib/python3.7/site-
packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "/secret/venv37/lib/python3.7/site-packages/django/db/utils.py",
line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/secret/venv37/lib/python3.7/site-
packages/django/db/backends/utils.py", line 86, in _execute
return self.cursor.execute(sql, params)
File "/secret/venv37/lib/python3.7/site-
packages/django/db/backends/sqlite3/base.py", line 396, in execute
return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: near "LIMIT": syntax error
}}}
Bonus:
{{{
>>> A.objects.filter(pk=Q()).query.__str__()
'SELECT "app_a"."id" FROM "app_a" WHERE "app_a"."id" = '
}}}
Notes:
If this gets confirmed I'm willing to try to do a fix (plenty of time
nowadays, right?), or at least provide a failing test, because as far as I
dug into the code, the query interfaces are fairly complicated.
This example was made on sqlite, but the issue was originally discovered
on postgres. I assume the bug is not on db backend level.
As absurd as this code might look, I created it by adding an inline if
statemement to the filter (pk=1 if flag else Q()), which wasn't an obvious
error.
--
Ticket URL: <https://code.djangoproject.com/ticket/31390>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: Q filter SQL | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
> Using Q as rhs value in filter...
Sorry, this is just a misuse of `Q`. A `Q` represents the expression, not
just the rhs (or lhs).
Look at [https://docs.djangoproject.com/en/3.0/topics/db/queries/#complex-
lookups-with-q the examples from the docs]:
`Q(question__startswith='What')`.
So in your case you need `A.objects.filter(Q())` (not `pk=Q()`)
Please use support channels if you have further queries.
--
Ticket URL: <https://code.djangoproject.com/ticket/31390#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Marcin Wieczorek):
I know it's a misuse, I don't expect such code to work. I just thought an
exception should be raised, an SQL error could potentially do more damage.
As I said, I caused the error in a situation that IDE/parser/runtime
couldn't detect the issue until the SQL error (so the misuse wasn't
obvious). An exception is raised when you pass a string instead of an int,
so should be for Qs.
Anyway thanks for your input, I won't seek further justification for my
issue if you say it's invalid.
--
Ticket URL: <https://code.djangoproject.com/ticket/31390#comment:2>