#35586: Aggregation optimization doesn't account for not referenced
set-returning
annotations on Postgres
-------------------------------------+-------------------------------------
Reporter: devin13cox | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres, set- | Triage Stage: Accepted
returning, aggregation, |
annotation |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
Adding a bit more details about set-returning functions close equivalents
on [https://www.sqlite.org/json1.html#jeach SQLite],
[https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html MySQL],
and [https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn
/function-JSON_TABLE.html#GUID-0172660F-CE29-4765-BF2C-C405BDE8369A
Oracle].
The gist is that a the query
{{{#!sql
SELECT
testmodel.*
, jsonb_path_query(testmodel.data, '$.test_key[*]') AS table_element
FROM testmodel
}}}
can also be expressed as
{{{#!sql
SELECT
testmodel.*
, table_element.value AS table_element
FROM
testmodel
, jsonb_path_query(testmodel.data, '$.test_key[*]') AS
table_element_tbl(value)
}}}
And if we added support the automatic addition of `set_returning` (or
`table_valued` functions?) to `alias_map` (what is used to generate the
`FROM` clause) it could also possibly allow to solve the long standing
problem of [https://forum.djangoproject.com/t/proposal-add-generate-
series-support-to-contrib-postgres/21947/4 adding support] for features
such as `generate_series`? That would allow the
`sql.Query.get_aggregation` logic to remain unchanged and keep pruning
unreferenced aliased (which is really specific to Postgres) as the
reference in the `FROM` clause would still span the rows
{{{#!sql
SELECT COUNT(*) FROM (
SELECT id
FROM testmodel, jsonb_path_query(testmodel.data, '$.test_key[*]') AS
table_element_tbl(value)
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35586#comment:5>
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 on the web visit
https://groups.google.com/d/msgid/django-updates/010701909a218f5b-03fe1bc3-bcb0-426e-9cc2-5abf72f38616-000000%40eu-central-1.amazonses.com.
