[Django] #32369: CheckConstraint with pattern lookup & another field crashes on PostgreSQL
Django
-------------------------------------+-------------------------------------
Reporter: Jaroslav | Owner: nobody
Semančík |
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
After fixing of #30408 a similar bug persists in case of indirect right-
hand side, e.g. when comparing two model fields.
Consider model:
{{{
class Foo(models.Model):
foo = models.CharField(max_length=255)
bar = models.CharField(max_length=255)
class Meta:
constraints = (
models.CheckConstraint(
check=models.Q(foo__startswith=models.F('bar')),
name='check_foo_starts_with_bar',
),
)
}}}
After making migration and running `sqlmigrate` with PostgreSQL backend we
get `TypeError: not enough arguments for format string`.
Traceback:
{{{
Traceback (most recent call last):
File "/snap/pycharm-
professional/228/plugins/python/helpers/pycharm/django_manage.py", line
52, in <module>
run_command()
File "/snap/pycharm-
professional/228/plugins/python/helpers/pycharm/django_manage.py", line
46, in run_command
run_module(manage_file, None, '__main__', True)
File "/usr/lib/python3.9/runpy.py", line 210, in run_module
return _run_module_code(code, init_globals, run_name, mod_spec)
File "/usr/lib/python3.9/runpy.py", line 97, in _run_module_code
_run_code(code, mod_globals, init_globals,
File "/usr/lib/python3.9/runpy.py", line 87, in _run_code
exec(code, run_globals)
File "/home/giro/Projects/djangostartswith/manage.py", line 22, in
<module>
main()
File "/home/giro/Projects/djangostartswith/manage.py", line 18, in main
execute_from_command_line(sys.argv)
File "/home/giro/src/django/core/management/__init__.py", line 401, in
execute_from_command_line
utility.execute()
File "/home/giro/src/django/core/management/__init__.py", line 395, in
execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/home/giro/src/django/core/management/base.py", line 330, in
run_from_argv
self.execute(*args, **cmd_options)
File "/home/giro/src/django/core/management/commands/sqlmigrate.py",
line 29, in execute
return super().execute(*args, **options)
File "/home/giro/src/django/core/management/base.py", line 371, in
execute
output = self.handle(*args, **options)
File "/home/giro/src/django/core/management/commands/sqlmigrate.py",
line 65, in handle
sql_statements = loader.collect_sql(plan)
File "/home/giro/src/django/db/migrations/loader.py", line 345, in
collect_sql
state = migration.apply(state, schema_editor, collect_sql=True)
File "/home/giro/src/django/db/migrations/migration.py", line 124, in
apply
operation.database_forwards(self.app_label, schema_editor, old_state,
project_state)
File "/home/giro/src/django/db/migrations/operations/models.py", line
808, in database_forwards
schema_editor.add_constraint(model, self.constraint)
File "/home/giro/src/django/db/backends/base/schema.py", line 362, in
add_constraint
self.execute(sql)
File "/home/giro/src/django/db/backends/base/schema.py", line 137, in
execute
self.collected_sql.append((sql % tuple(map(self.quote_value, params)))
+ ending)
TypeError: not enough arguments for format string
}}}
The cause is again an unescaped `%` in the generated SQL.
--
Ticket URL: <https://code.djangoproject.com/ticket/32369>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
crash on Oracle and PostgreSQL.
-------------------------------------+-------------------------------------
Reporter: Jaroslav Semančík | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Ian Foote (added)
* stage: Unreviewed => Accepted
Comment:
Thanks for the report.
Reproduced at bbd18943c6c00fb1386ecaaf6771a54f780ebf62.
--
Ticket URL: <https://code.djangoproject.com/ticket/32369#comment:1>
Django
crash on Oracle and PostgreSQL.
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Simon Charette
* status: new => assigned
Comment:
Kind of related to #32231 due to
[https://github.com/django/django/blob/90ddf46ef7b3d775b124d81e1846bec7961c7f1f/django/db/backends/postgresql/base.py#L123-L139
our weird way of ''baking'' parameters of pattern ops]
[https://github.com/django/django/commit/a906c9898284a9aecb5f48bdc534e9c1273864a6
#diff-f7637259acc1ecbb74910a7df2879cae5f3209d7a87ab0ad06dbd594b27f1831R329
which we already happen to deal with] for `Index.condition`.
--
Ticket URL: <https://code.djangoproject.com/ticket/32369#comment:2>
Django
crash on Oracle and PostgreSQL.
-------------------------------------+-------------------------------------
Reporter: Jaroslav Semančík | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/32369#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Jaroslav Semančík | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
Comment:
[https://github.com/django/django/pull/13934 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/32369#comment:4>
Django
crash on Oracle, MySQL, and PostgreSQL.
-------------------------------------+-------------------------------------
Reporter: Jaroslav Semančík | Owner: Simon
| Charette
Component: Database layer | Version: 3.1
(models, ORM) |
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"42e8cf47c7ee2db238bf91197ea398126c546741" 42e8cf47]:
{{{
#!CommitTicketReference repository=""
revision="42e8cf47c7ee2db238bf91197ea398126c546741"
Fixed #32369 -- Fixed adding check constraints with pattern lookups and
expressions as rhs.
This disables interpolation of constraint creation statements. Since
Constraint.create_sql interpolates its parameters instead of deferring
this responsibility to the backend connection it must disable
connection level parameters interpolation.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32369#comment:5>
Django
crash on Oracle, MySQL, and PostgreSQL.
-------------------------------------+-------------------------------------
Reporter: Jaroslav Semančík | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"9607e3a0cc4b812a4a1ce0e693357902408fc0b2" 9607e3a0]:
{{{
#!CommitTicketReference repository=""
revision="9607e3a0cc4b812a4a1ce0e693357902408fc0b2"
[3.2.x] Fixed #32369 -- Fixed adding check constraints with pattern
lookups and expressions as rhs.
This disables interpolation of constraint creation statements. Since
Constraint.create_sql interpolates its parameters instead of deferring
this responsibility to the backend connection it must disable
connection level parameters interpolation.
Backport of 42e8cf47c7ee2db238bf91197ea398126c546741 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32369#comment:6>
Django
crash on Oracle, MySQL, and PostgreSQL.
-------------------------------------+-------------------------------------
Reporter: Jaroslav Semančík | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"e0f8104a968d316925f736e961a63bef73a686b4" e0f8104a]:
{{{
#!CommitTicketReference repository=""
revision="e0f8104a968d316925f736e961a63bef73a686b4"
Refs #34553 -- Split constraint escaping test in subtests.
This ensures that constraint violations are tested in isolation from
each other as an IntegrityError only ensures a least one constraint is
violated.
For example, the assertion added in 42e8cf4 break both the
name_constraint_rhs and the rebate_constraint constraints and thus
doesn't constitute a proper regression test. Refs #32369.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32369#comment:7>
Django
crash on Oracle, MySQL, and PostgreSQL.
-------------------------------------+-------------------------------------
Reporter: Jaroslav Semančík | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"ffff17d4b0117cce59f65c9f56fa164694eafd23" ffff17d4]:
{{{
#!CommitTicketReference repository=""
revision="ffff17d4b0117cce59f65c9f56fa164694eafd23"
Fixed #34553 -- Fixed improper % escaping of literal in constraints.
Proper escaping of % in string literals used when defining constaints
was attempted (a8b3f96f6) by overriding quote_value of Postgres and
Oracle schema editor. The same approach was used when adding support for
constraints to the MySQL/MariaDB backend (1fc2c70).
Later on it was discovered that this approach was not appropriate and
that a preferable one was to pass params=None when executing the
constraint creation DDL to avoid any form of interpolation in the first
place (42e8cf47).
When the second patch was applied the corrective of the first were not
removed which caused % literals to be unnecessary doubled. This flew
under the radar because the existings test were crafted in a way that
consecutive %% didn't catch regressions.
This commit introduces an extra test for __exact lookups which
highlights more adequately % doubling problems but also adjust a
previous __endswith test to cover % doubling problems (%\% -> %%\%%).
Thanks Thomas Kolar for the report.
Refs #32369, #30408, #30593.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32369#comment:8>