[Django] #30791: str(Queryset.query) does not properly encapsulate values
Django
-------------------------------------+-------------------------------------
Reporter: Ryan | Owner: (none)
Gartin |
Type: Bug | Status: new
Component: | Version: 2.2
contrib.postgres |
Severity: Normal | Keywords: queryset, sql
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
**Expectation**: `print(str(qs.query))` will output correctly formatted
SQL, ready to copy/paste, and execute.
**Bug**: Values such as dates, uuids are not encapsulated with single
quotes. ArrayFields are incorrectly outputted. When debugging large
queries, it is a pain to have to modify every output manually in order to
successfully execute query outside of Django application.
**Environment**: Django 2.2.5, PostgreSQL 10.5, x86_64-apple-darwin14.5.0.
Tested on PostgreSQL 9.6 as well.
**Note**: I was debugging earlier in the year on Django 2.0 and 2.1 and I
feel that this was not an issue in those version.
**Example INCORRECT Output**:
`print(str(qs.query))`
SELECT .... FROM stuff
WHERE "asdf"."end" <= 2019-09-21 02:48:43.856895+00:00
AND "asdf"."start" >= 2019-09-21 02:48:43.856895+00:00
AND "asdf"."uuid" = 854af4a2-cb2a-4b22-ae04-90f14aa86ddd
AND "asdf"."date" BETWEEN 1989-09-20 20:12:45.927138+00:00 AND
2001-09-20 18:03:09.927151+00:00
AND "asdf"."sex" IN (f)
AND "asdf"."gender" @> ['m']::VARCHAR(1)[]
**FIX**:
Dates and uuid must be single quoted.
CHARFIELD "EQUAL" query needs to be single quoted `= 'a'`
CHARFIELD "IN" query needs to be single quoted `IN ('a', 'b', 'c')`.
ARRAYFIELD (aka `character varying [] {1}` ) "IN" query should be in
single quoted, encased in brackets, no quotes on individual characters
`@> '{a,b,c}' `
--
Ticket URL: <https://code.djangoproject.com/ticket/30791>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: contrib.postgres | Version: 2.2
Severity: Normal | Resolution:
Keywords: queryset, sql, | Triage Stage:
postgres | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* keywords: queryset, sql => queryset, sql, postgres
Old description:
> **Expectation**: `print(str(qs.query))` will output correctly formatted
> SQL, ready to copy/paste, and execute.
>
> **Bug**: Values such as dates, uuids are not encapsulated with single
> quotes. ArrayFields are incorrectly outputted. When debugging large
> queries, it is a pain to have to modify every output manually in order to
> successfully execute query outside of Django application.
>
> **Environment**: Django 2.2.5, PostgreSQL 10.5, x86_64-apple-
> darwin14.5.0. Tested on PostgreSQL 9.6 as well.
> **Note**: I was debugging earlier in the year on Django 2.0 and 2.1 and I
> feel that this was not an issue in those version.
>
> **Example INCORRECT Output**:
> `print(str(qs.query))`
> SELECT .... FROM stuff
> WHERE "asdf"."end" <= 2019-09-21 02:48:43.856895+00:00
> AND "asdf"."start" >= 2019-09-21 02:48:43.856895+00:00
> AND "asdf"."uuid" = 854af4a2-cb2a-4b22-ae04-90f14aa86ddd
> AND "asdf"."date" BETWEEN 1989-09-20 20:12:45.927138+00:00 AND
> 2001-09-20 18:03:09.927151+00:00
> AND "asdf"."sex" IN (f)
> AND "asdf"."gender" @> ['m']::VARCHAR(1)[]
>
> **FIX**:
> Dates and uuid must be single quoted.
> CHARFIELD "EQUAL" query needs to be single quoted `= 'a'`
> CHARFIELD "IN" query needs to be single quoted `IN ('a', 'b', 'c')`.
> ARRAYFIELD (aka `character varying [] {1}` ) "IN" query should be in
> single quoted, encased in brackets, no quotes on individual characters
> `@> '{a,b,c}' `
New description:
**Expectation**: `print(str(qs.query))` will output correctly formatted
SQL, ready to copy/paste, and execute.
**Bug**: Values such as dates, uuids are not encapsulated with single
quotes. ArrayFields are incorrectly outputted. When debugging large
queries, it is a pain to have to modify every output manually in order to
successfully execute query outside of Django application.
**Environment**: Django 2.2.5, PostgreSQL 10.5, x86_64-apple-darwin14.5.0.
Tested on PostgreSQL 9.6 as well.
**Note**: I was debugging earlier in the year on Django 2.0 and 2.1 and I
feel that this was not an issue in those version.
**Example INCORRECT Output**:
`print(str(qs.query))`
`SELECT .... FROM stuff`
`WHERE "asdf"."end" <= 2019-09-21 02:48:43.856895+00:00 `
`AND "asdf"."start" >= 2019-09-21 02:48:43.856895+00:00 `
`AND "asdf"."uuid" = 854af4a2-cb2a-4b22-ae04-90f14aa86ddd `
`AND "asdf"."qwerty" BETWEEN 1989-09-20 20:12:45.927138+00:00 AND
2001-09-20 18:03:09.927151+00:00 `
`AND "asdf"."gender" IN (f) `
`AND "asdf"."letter" @> ['a']::VARCHAR(1)[] `
**FIX**:
Dates and uuid must be single quoted.
CHARFIELD "EQUAL" query needs to be single quoted `= 'a'`
CHARFIELD "IN" query needs to be single quoted `IN ('a', 'b', 'c')`.
ARRAYFIELD (aka `character varying [] {1}` ) "IN" query should be in
single quoted, encased in brackets, no quotes on individual characters
`@> '{a,b,c}' `
--
--
Ticket URL: <https://code.djangoproject.com/ticket/30791#comment:1>
Django
-------------------------------------+-------------------------------------
Component: contrib.postgres | Version: 2.2
Severity: Normal | Resolution: duplicate
Keywords: queryset, sql, | Triage Stage:
postgres | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => duplicate
Comment:
Duplicate of #29891
--
Ticket URL: <https://code.djangoproject.com/ticket/30791#comment:2>