Opened 2 years ago

Closed 17 months ago

Last modified 8 months ago

#32226 closed Bug (fixed)

QuerySet.explain(format='json') outputs repr'd JSON on PostgreSQL

Reported by: Adam Johnson Owned by: Wu Haotian
Component: Database layer (models, ORM) Version: 3.0
Severity: Normal Keywords:
Cc: Tom Forbes, Michael Christofides Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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 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().

Change History (20)

comment:1 Changed 2 years ago by Carlton Gibson

Triage Stage: UnreviewedAccepted

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

comment:2 Changed 2 years ago by kosc

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

comment:3 Changed 2 years ago by kosc

Owner: changed from nobody to kosc
Status: newassigned

comment:4 Changed 2 years ago by Adam Johnson

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

comment:5 Changed 2 years ago by Tom Forbes

Cc: Tom Forbes added

comment:6 Changed 2 years ago 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.

comment:7 Changed 2 years ago by Adam Johnson

It could be something to do with JSONField changes?

comment:8 in reply to:  6 Changed 2 years ago by kosc

Replying to 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}}]" (the query: TestModel.objects.all().explain(format='json')).

Last edited 2 years ago by kosc (previous) (diff)

comment:9 in reply to:  7 Changed 2 years ago by Mariusz Felisiak

Replying to 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.

comment:10 Changed 2 years ago by ankitdawadi

Owner: changed from kosc to ankitdawadi

comment:11 Changed 2 years ago by udaykiran

Owner: changed from ankitdawadi to udaykiran

we have to add a condition for json in explain_query_prefix

if format == 'JSON': prefix = json.loads(prefix)

comment:12 Changed 22 months ago by Jacob Walls

Has patch: set

comment:13 Changed 22 months ago by Adam Johnson

Patch needs improvement: set

comment:14 Changed 19 months ago by Michael Christofides

Cc: Michael Christofides added

comment:15 Changed 19 months ago by Mariusz Felisiak

Owner: udaykiran deleted
Status: assignednew

comment:16 Changed 18 months ago by Wu Haotian

Has patch: unset
Owner: set to Wu Haotian
Patch needs improvement: unset
Status: newassigned

comment:18 Changed 17 months ago by Tim McCurrach

Triage Stage: AcceptedReady for checkin

comment:19 Changed 17 months ago by Mariusz Felisiak <felisiak.mariusz@…>

Resolution: fixed
Status: assignedclosed

In aba9c2de:

Fixed #32226 -- Fixed JSON format of QuerySet.explain() on PostgreSQL.

comment:20 Changed 8 months ago by GitHub <noreply@…>

In f4f2afeb:

Refs #32226 -- Fixed JSON format of QuerySet.explain() on PostgreSQL when format is uppercased.

Follow up to aba9c2de669dcc0278c7ffde7981be91801be00b.

Note: See TracTickets for help on using tickets.
Back to Top