[Django] #32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------
Reporter: Adam | Owner: nobody
(Chainz) Johnson |
Type: Bug | Status: new
Component: Database | Version: 3.0
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 |
-------------------------------------+-------------------------------------
Call `.explain(format='json')` on a `QuerySet` on PostgreSQL, and the
result *looks* like JSON, but isn't:

{{{
In [5]: User.objects.filter(is_staff=True).explain(format='json')
Out[5]: "[{'Plan': {'Node Type': 'Seq Scan', 'Parallel Aware': False,
'Relation Name': 'auth_user', 'Alias': 'auth_user', 'Startup Cost': 0.0,
'Total Cost': 478.12, 'Plan Rows': 23, 'Plan Width': 157, 'Filter':
'is_staff'}}]"
}}}

One needs to use `ast.literal_eval` and `json.dumps` to get real JSON text
that can be pasted into tools like [https://tatiyants.com/pev/ PostgreSQL
explain viewer]:

{{{
In [10]: explain =
User.objects.filter(is_staff=True).explain(format='json') ;
print(json.dumps(ast.literal_eval(explain)))
[{"Plan": {"Node Type": "Seq Scan", "Parallel Aware": false, "Relation
Name": "auth_user", "Alias": "auth_user", "Startup Cost": 0.0, "Total
Cost": 478.12, "Plan Rows": 23, "Plan Width": 157, "Filter": "is_staff"}}]
}}}

I guess this is because `psycopg2` loads the JSON as a Python list of
dicts, then `SQLCompiler.explain_query` does `yield ' '.join(str(c) for c
in row)`, which turns that list into its `repr()`.

--
Ticket URL: <https://code.djangoproject.com/ticket/32226>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam (Chainz) | Owner: nobody

Johnson |
Type: Bug | Status: new

Component: Database layer | Version: 3.0
(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
-------------------------------------+-------------------------------------

Changes (by Carlton Gibson):

* stage: Unreviewed => Accepted

Comment:

OK, thanks Adam, yes. `EXPLAIN (FORMAT JSON) SELECT ...` certainly gives
you actual JSON. :)

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:1>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam (Chainz) | Owner: nobody

Johnson |
Type: Bug | Status: new

Component: Database layer | Version: 3.0
(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
-------------------------------------+-------------------------------------

Comment (by kosc):

Is this issue only for PostgreSQL? I see a similar result with at least
MySQL.

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:2>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam (Chainz) | Owner: kosc
Johnson |
Type: Bug | Status: assigned

Component: Database layer | Version: 3.0
(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
-------------------------------------+-------------------------------------

Changes (by kosc):

* owner: nobody => kosc
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:3>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam (Chainz) | Owner: kosc
Johnson |
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(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
-------------------------------------+-------------------------------------

Comment (by Adam (Chainz) Johnson):

I can imagine it exists there too, I only had PostgreSQL to hand.

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:4>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam (Chainz) | Owner: kosc
Johnson |
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(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
-------------------------------------+-------------------------------------

Changes (by Tom Forbes):

* cc: Tom Forbes (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:5>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam (Chainz) | Owner: kosc
Johnson |
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(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
-------------------------------------+-------------------------------------

Comment (by Tom Forbes):

I wonder if this changed at some point, because when I was developing this
feature I’m almost certain that it returned a string type with valid JSON
rather than a list of dicts 🤔. I can’t see anything in the psycopg2
release notes to support that though, so maybe I’m misremembering.

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:6>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam (Chainz) | Owner: kosc
Johnson |
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(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
-------------------------------------+-------------------------------------

Comment (by Adam (Chainz) Johnson):

It could be something to do with JSONField changes?

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:7>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam (Chainz) | Owner: kosc
Johnson |
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(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
-------------------------------------+-------------------------------------

Comment (by kosc):

Replying to [comment:6 Tom Forbes]:

> I wonder if this changed at some point, because when I was developing
this feature I’m almost certain that it returned a string type with valid
JSON rather than a list of dicts 🤔. I can’t see anything in the psycopg2
release notes to support that though, so maybe I’m misremembering.

Hello! I see that query "EXPLAIN (FORMAT JSON) SELECT * FROM table_name;"
returns valid JSON strings in double-quotes, but Django returns a string
with single-quotes, and when I trying to json.loads string from django I
getting an error: "JSONDecodeError: Expecting property name enclosed in
double quotes: line 1 column 3 (char 2)".
Also, when I trying to replace single quotes with double quotes, I getting
another error: "JSONDecodeError: Expecting value: line 1 column 55 (char
54)".

Here is a string returning by Django ORM: "[{'Plan': {'Node Type': 'Seq
Scan', 'Parallel Aware': False, 'Relation Name': 'main_testmodel',
'Alias': 'main_testmodel', 'Startup Cost': 0.0, 'Total Cost': 11.4, 'Plan
Rows': 140, 'Plan Width': 520}}]" (explain for simple
TestModel.objects.all().explain(format='json')).

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:8>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam (Chainz) | Owner: kosc
Johnson |
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(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
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak):

Replying to [comment:7 Adam (Chainz) Johnson]:

> It could be something to do with JSONField changes?

I can reproduce it at c1c163b42717ed5e051098ebf0e2f5c77810f20e, so it's
not related with JSONField changes.

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:9>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam (Chainz) | Owner:
Johnson | ankitdawadi

Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(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
-------------------------------------+-------------------------------------

Changes (by ankitdawadi):

* owner: kosc => ankitdawadi

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:10>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam Johnson | Owner: udaykiran

Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(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
-------------------------------------+-------------------------------------

Changes (by udaykiran):

* owner: ankitdawadi => udaykiran

Comment:

we have to add a condition for json in explain_query_prefix
if format == 'JSON': prefix = json.loads(prefix)

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:11>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam Johnson | Owner: udaykiran
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/13867 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:12>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam Johnson | Owner: udaykiran
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Adam Johnson):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:13>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam Johnson | Owner: udaykiran
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Michael Christofides):

* cc: Michael Christofides (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:14>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam Johnson | Owner: (none)
Type: Bug | Status: new

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* owner: udaykiran => (none)
* status: assigned => new

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:15>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam Johnson | Owner: Wu
| Haotian
Type: Bug | Status: assigned

Component: Database layer | Version: 3.0
(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
-------------------------------------+-------------------------------------

Changes (by Wu Haotian):

* owner: (none) => Wu Haotian
* needs_better_patch: 1 => 0
* has_patch: 1 => 0

* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:16>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam Johnson | Owner: Wu
| Haotian
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Wu Haotian):

* has_patch: 0 => 1

Comment:

https://github.com/django/django/pull/14574

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:17>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam Johnson | Owner: Wu
| Haotian
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim McCurrach):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:18>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam Johnson | Owner: Wu
| Haotian

Type: Bug | Status: closed

Component: Database layer | Version: 3.0
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"aba9c2de669dcc0278c7ffde7981be91801be00b" aba9c2de]:
{{{
#!CommitTicketReference repository=""
revision="aba9c2de669dcc0278c7ffde7981be91801be00b"
Fixed #32226 -- Fixed JSON format of QuerySet.explain() on PostgreSQL.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:19>

[Django]

#32226: QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Adam Johnson | Owner: Wu
| Haotian
Type: Bug | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by GitHub <noreply@…>):

In [changeset:"f4f2afeb457429f55d6325ed557f8e92a74ee028" f4f2afeb]:
{{{
#!CommitTicketReference repository=""
revision="f4f2afeb457429f55d6325ed557f8e92a74ee028"
Refs #32226 -- Fixed JSON format of QuerySet.explain() on PostgreSQL when
format is uppercased.

Follow up to aba9c2de669dcc0278c7ffde7981be91801be00b.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32226#comment:20>