#36152: Postgres backend could quote `%` in column aliases instead of failing at
the db level
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Jacob Walls:
Old description:
> When developing with postgres, if you are creating dynamic annotations,
> you have to know to reject `%` in the provided alias or transform it to
> `%%`, otherwise you end up with an unhelpful exception from the db
> adapter.
>
> test case:
> {{{#!diff
> diff --git a/tests/annotations/tests.py b/tests/annotations/tests.py
> index 29660a827e..6bc9bc9ed4 100644
> --- a/tests/annotations/tests.py
> +++ b/tests/annotations/tests.py
> @@ -1171,6 +1171,9 @@ class NonAggregateAnnotationTestCase(TestCase):
> with self.assertRaisesMessage(ValueError, msg):
> Book.objects.annotate(**{crafted_alias: Value(1)})
>
> + def test_annotation_contains_percent(self):
> + Book.objects.annotate(**{"contains_percent%": F("pk")}).first()
> +
> @skipUnless(connection.vendor == "postgresql", "PostgreSQL tests")
> @skipUnlessDBFeature("supports_json_field")
> def test_set_returning_functions(self):
> }}}
>
> Gives on psycopg2:
>
> {{{
> IndexError: tuple index out of range
> }}}
>
> On psycopg 3, the errors are more helpful, either:
> {{{
> django.db.utils.ProgrammingError: only '%s', '%b', '%t' are allowed as
> placeholders, got '%"'
> }}}
>
> Or if the annotation indeed included %s:
> {{{
> django.db.utils.ProgrammingError: the query has 2 placeholders but 1
> parameters were passed
> }}}
>
> But I still think we could easily fail at the ORM level instead of going
> to the backend and failing there.
>
> The Oracle backend
> [https://github.com/django/django/blob/efec74b90868c2e611f863bf4301d92ce08067e8/django/db/backends/oracle/operations.py#L377
> does this for you].
New description:
When developing with postgres, if you are creating dynamic annotations,
you have to know to reject `%` in the provided alias or transform it to
`%%`, otherwise you end up with an unhelpful exception from the db
adapter.
test case:
{{{#!diff
diff --git a/tests/annotations/tests.py b/tests/annotations/tests.py
index 29660a827e..6bc9bc9ed4 100644
--- a/tests/annotations/tests.py
+++ b/tests/annotations/tests.py
@@ -1171,6 +1171,9 @@ class NonAggregateAnnotationTestCase(TestCase):
with self.assertRaisesMessage(ValueError, msg):
Book.objects.annotate(**{crafted_alias: Value(1)})
+ def test_annotation_contains_percent(self):
+ Book.objects.annotate(**{"contains_percent%": F("pk")}).first()
+
@skipUnless(connection.vendor == "postgresql", "PostgreSQL tests")
@skipUnlessDBFeature("supports_json_field")
def test_set_returning_functions(self):
}}}
Gives on psycopg2:
{{{
IndexError: tuple index out of range
}}}
On psycopg 3, the errors are more helpful, either:
{{{
django.db.utils.ProgrammingError: only '%s', '%b', '%t' are allowed as
placeholders, got '%"'
}}}
Or if the annotation indeed included %s:
{{{
django.db.utils.ProgrammingError: the query has 2 placeholders but 1
parameters were passed
}}}
But I still think we could easily ~~fail at the ORM level instead of going
to the backend and failing there~~ just quote `%` to `%%`.
The Oracle backend
[https://github.com/django/django/blob/efec74b90868c2e611f863bf4301d92ce08067e8/django/db/backends/oracle/operations.py#L377
does this for you].
--
--
Ticket URL: <https://code.djangoproject.com/ticket/36152#comment:1>
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 visit
https://groups.google.com/d/msgid/django-updates/01070194ad8333d4-7a27efc4-85da-4a53-b03b-67015f5ea951-000000%40eu-central-1.amazonses.com.
