#31390: Using Q as rhs value in filter causes SQL Syntax error
-------------------------------------+-------------------------------------
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/058.63b43bdb538c64441db9ede7f6d04b0b%40djangoproject.com.
