Re: [Django] #35586: Aggregation optimization doesn't account for not referenced set-returning annotations on Postgres

[Django]

#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.

[Django]

#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
-------------------------------------+-------------------------------------

Changes (by David Sanders):

* cc: David Sanders (added)

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

[Django]

#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
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* cc: Jacob Walls (added)

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

[Django]

#35586: Aggregation optimization doesn't account for not referenced set-returning
annotations on Postgres
-------------------------------------+-------------------------------------

Reporter: Devin Cox | 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 Devin Cox):

Went ahead and implemented/tested your suggestions and they work great for
our individual use case. Also pushed a PR for this. I know there may be
additional items now within the scope, such as support for `Unnest`, but I
wanted to go ahead and get the ball rolling. Also will need to add
documentation for this. Let me know if there are other ways I can help.
Thanks!
--
Ticket URL: <https://code.djangoproject.com/ticket/35586#comment:8>

[Django]

#35586: Aggregation optimization doesn't account for not referenced set-returning
annotations on Postgres
-------------------------------------+-------------------------------------
Reporter: Devin Cox | 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: 1 | Needs documentation: 0

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

Changes (by Devin Cox):

* has_patch: 0 => 1

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

[Django]

#35586: Aggregation optimization doesn't account for not referenced set-returning
annotations on Postgres
-------------------------------------+-------------------------------------

Reporter: Devin Cox | Owner: Devin Cox
Type: Bug | Status: assigned

Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres, set- | Triage Stage: Accepted
returning, aggregation, |
annotation |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* owner: (none) => Devin Cox
* status: new => assigned

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

[Django]

#35586: Aggregation optimization doesn't account for not referenced set-returning
annotations on Postgres
-------------------------------------+-------------------------------------
Reporter: Devin Cox | Owner: Devin Cox
Type: Bug | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres, set- | Triage Stage: Accepted
returning, aggregation, |
annotation |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Sarah Boyce):

* needs_better_patch: 0 => 1

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

[Django]

#35586: Aggregation optimization doesn't account for not referenced set-returning
annotations on Postgres
-------------------------------------+-------------------------------------
Reporter: Devin Cox | Owner: Devin Cox
Type: Bug | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres, set- | Triage Stage: Accepted
returning, aggregation, |
annotation |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Sarah Boyce):

* needs_better_patch: 1 => 0

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

[Django]

#35586: Aggregation optimization doesn't account for not referenced set-returning
annotations on Postgres
-------------------------------------+-------------------------------------
Reporter: Devin Cox | Owner: Devin Cox
Type: Bug | Status: assigned
Component: Database layer | Version: 5.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: postgres, set- | Triage Stage: Ready for
returning, aggregation, | checkin

annotation |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Sarah Boyce):

* stage: Accepted => Ready for checkin

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

[Django]

#35586: Aggregation optimization doesn't account for not referenced set-returning
annotations on Postgres
-------------------------------------+-------------------------------------
Reporter: Devin Cox | Owner: Devin Cox

Type: Bug | Status: closed

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

Severity: Normal | Resolution: fixed

Keywords: postgres, set- | Triage Stage: Ready for
returning, aggregation, | checkin
annotation |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce <42296566+sarahboyce@…>):

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

Comment:

In [changeset:"e03083917db03757e48f8edac4c8491b72c8a3c4" e030839]:
{{{#!CommitTicketReference repository=""
revision="e03083917db03757e48f8edac4c8491b72c8a3c4"
Fixed #35586 -- Added support for set-returning database functions.

Aggregation optimization didn't account for not referenced set-returning
annotations on Postgres.

Co-authored-by: Simon Charette <chare...@gmail.com>
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35586#comment:14>