Hi
I do appreciate what I was asking about wasn't your problem, I was
looking at learning for myself etc.
For those that are following this, it seems that Postgres (as does
MySQL) loosens the SQL requirements. In Postgres (
https://www.postgresql.org/docs/11/sql-select.html#SQL-GROUPBY ) the
rule is:
When GROUP BY is present, or any aggregate functions are present, it is
not valid for the SELECT list expressions to refer to ungrouped
columns except within aggregate functions or when the ungrouped column
is functionally dependent on the grouped columns, since there would
otherwise be more than one possible value to return for an ungrouped
column. A functional dependency exists if the grouped columns (or a
subset thereof) are the primary key of the table containing the
ungrouped column.
Which is what we are seeing here. In django the postgres backend has a
feature set so that the django SQL compiler knows that postgres can
accepts simplified GROUP BY clauses.
Actually I've simplified this a little as there are two seperate
bits ( allows_group_by_pk, andself.allows_group_by_selected_pks) for
different type of simplification, and a backend helper function.
Thank you for that fascinating excursion.
On Sun, 2020-07-19 at 07:48 -0700, karthik challa wrote:
> Hi Roger,
>
> I have fetched this query from the Django Debug tool bar. I am using
> POSTGRES database and Django 2.2.3 version.
>
> I am able to fetch the records which i require but the issue is with
> the performance the django query set or the query which i am making.
>
>
> Thanks & Regards,karthik
>
> On Sunday, July 19, 2020 at 7:01:28 AM UTC-4, Roger Gammans wrote:
> > Hi
> >
> > Just a side issue I spotted here, but I'm probably missing
> > something, so I'm seeking to understand.
> >
> > Is this the SQL the django query builder made? If so which database
> > backend and django version are you using?
> >
> > On Sun, 2020-07-19 at 08:30 +0100, Damanjeet Singh wrote:
> > > 2>SQL Query
> > >
> > > SELECT "urls_subdomain"."id", "urls_subdomain"."created_at",
> > > "urls_subdomain"."name", COUNT("urls_url"."id") AS "numItems"
> > > FROM "urls_subdomain" LEFT OUTER JOIN "urls_url" ON
> > > ("urls_subdomain"."id" = "urls_url"."subdomain_id") GROUP BY
> > > "urls_subdomain"."id" ORDER BY "urls_subdomain"."name" ASC
> >
> > I only ask because normally in SQL the rule is "However, each
> > table or view column in any nonaggregate expression in the
> > <select> list must be included in the GROUP BY list" (quote from
> > MS TSQL docs which was just the first hit my google search found) .
> > And the name and create_at columns are missing in the GROUP BY
> > clause in the quoted SQL statement.
> >
> > It seems unlikely that Django is generating invalid SQL as we would
> > have seen more issues (although the intent of the SQL expression
> > above is clear, and I have met engines which can execute it fine)
> >
> > TTFN
> > --
> > Roger Gammans <[email protected]>
> >
> >
>
>
>
> --
>
> You received this message because you are subscribed to the Google
> Groups "Django users" 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-users/8ab2bcac-7f72-45e4-82c2-10f9cad5d8e2o%40googlegroups.com
> .
>
--
You received this message because you are subscribed to the Google Groups
"Django users" 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-users/036a70854c81a80d0a9e2599433538556ba39e2b.camel%40gammascience.co.uk.
