Re: [Django] #34255: Annotation/group by with an expression on psycopg3

[Django]

#34255: Annotation/group by with an expression on psycopg3
-+-
 Reporter:  Guillaume Andreu |Owner:  nobody
  Sabater|
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:
 Keywords:  orm postgres | Triage Stage:  Accepted
  psycopg3 annotation groupby|
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Florian Apolloner):

 Replying to [comment:2 Simon Charette]:
 > Edit, another solution could be to have the psycopg3 query `%s -> $n`
 logic de-duplicate equal values and avoid using multiple placeholders for
 them.

 I would love to see this done for Django in general and not at the
 individual backend level. That said it is probably to late for 4.1

 If we cannot fix the issue at hand nicely I think we could fall back to
 client side cursors and provide a setting (backend option) to switch back
 to server side cursors so people can experiment with them. Btw from the
 looks of it and issues we ran into developing psycopg3 support, this also
 means that the postgresql backend is basically the only backend using
 server side bindings or is it just stricter than every other backend?

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070185a9fd5576-a0970ef3-aa31-41b5-b768-104cbbbf924e-00%40eu-central-1.amazonses.com.

Re: [Django] #34255: Annotation/group by with an expression on psycopg3

[Django]

#34255: Annotation/group by with an expression on psycopg3
-+-
 Reporter:  Guillaume Andreu |Owner:  nobody
  Sabater|
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:
 Keywords:  orm postgres | Triage Stage:  Accepted
  psycopg3 annotation groupby|
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Mariusz Felisiak):

 > Edit, another solution could be to have the psycopg3 query %s -> $n
 logic de-duplicate equal values and avoid using multiple placeholders for
 them. That would result in the query being written as the user reported
 here at the expense of equality checks (or possibly identity/hash checks
 which are cheaper) on each query creation which I believe would require
 adjustments to the current caching strategy.

 Yeah, the same as
 
[https://github.com/django/django/blob/648005dee62481acc1784e5c9625e90f0fd6aab4/django/db/backends/oracle/base.py#L533-L546
 we do on Oracle]. Unfortunately, it has [https://groups.google.com/g
 /django-developers/c/1hqw_HTpn_8/m/JzA_ma2pAQAJ side-effects] :|

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070185a9f4f49b-3789bdb3-5e86-4a27-8802-38d056fee088-00%40eu-central-1.amazonses.com.

Re: [Django] #34255: Annotation/group by with an expression on psycopg3

[Django]

#34255: Annotation/group by with an expression on psycopg3
-+-
 Reporter:  Guillaume Andreu |Owner:  nobody
  Sabater|
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:
 Keywords:  orm postgres | Triage Stage:  Accepted
  psycopg3 annotation groupby|
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette):

 Right, it doesn't need `ARRAY` to be present to reproduce the crash but
 the reason why we can't simply augment the select clause with `year` in
 order to `GROUP BY 1` (or `GROUP BY year`) is that it would return a row
 with two columns and differ from what the user is asking for when doing an
 explicit `values("json")`.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070185a990c15c-ae3659e1-edaa-4b03-91d6-9ff7dad3e062-00%40eu-central-1.amazonses.com.

Re: [Django] #34255: Annotation/group by with an expression on psycopg3

[Django]

#34255: Annotation/group by with an expression on psycopg3
-+-
 Reporter:  Guillaume Andreu |Owner:  nobody
  Sabater|
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:
 Keywords:  orm postgres | Triage Stage:  Accepted
  psycopg3 annotation groupby|
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Mariusz Felisiak):

 Just want to add that it also crashes without `ARRAY`, e.g. for a queryset
 from [https://code.djangoproject.com/ticket/34255#comment:1 comment:1]:
 {{{#!sql
 SELECT
 JSONB_BUILD_OBJECT(
 (%s)::text,
 EXTRACT(YEAR FROM "ticket_34255_point"."start_at" AT TIME ZONE
 %s),
 (%s)::text,
 AVG("ticket_34255_point"."value")
 ) AS "json"
 FROM "ticket_34255_point"
 GROUP BY
 EXTRACT(YEAR FROM "ticket_34255_point"."start_at" AT TIME ZONE %s)
 }}}
 {{{
 psycopg.errors.GroupingError: column "ticket_34255_point.start_at" must
 appear in the GROUP BY clause or be used in an aggregate function
 }}}

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070185a98dcd99-a1ea281c-296b-4c02-8c35-96897804914d-00%40eu-central-1.amazonses.com.

Re: [Django] #34255: Annotation/group by with an expression on psycopg3

[Django]

#34255: Annotation/group by with an expression on psycopg3
-+-
 Reporter:  Guillaume Andreu |Owner:  nobody
  Sabater|
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:
 Keywords:  orm postgres | Triage Stage:  Accepted
  psycopg3 annotation groupby|
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette):

 I believe here the issue is that the `year` annotation is ''elided'' from
 the query for a good reason, only one column must be present in the
 `ARRAY`/`SubqueryArray` construct.

 In other words, the problem is not about grouping by alias or column index
 but grouping by an expression that is elided from the select clause and
 thus cannot be grouped by reference.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070185a9668c7d-a01936b1-c880-4bf8-9f90-cd482d958e09-00%40eu-central-1.amazonses.com.

Re: [Django] #34255: Annotation/group by with an expression on psycopg3

[Django]

#34255: Annotation/group by with an expression on psycopg3
-+-
 Reporter:  Guillaume Andreu |Owner:  nobody
  Sabater|
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:
 Keywords:  orm postgres | Triage Stage:  Accepted
  psycopg3 annotation groupby|
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Mariusz Felisiak):

 > This will be hard to solve as group by aliasing cannot be used since
 year is stripped from the query.

 What about grouping by the entire function? as we do when it's not pushed
 into subquery, e.g.
 {{{#!python
 Point.objects.annotate(
 year=ExtractYear("start_at", tzinfo=tz),
 ).values("year").annotate(
 baseload=Avg("value"),
 ).values("year", "baseload")
 }}}
 {{{#!sql
 SELECT
 EXTRACT(YEAR FROM "ticket_34255_point"."start_at" AT TIME ZONE
 'Europe/Paris') AS "year",
 AVG("ticket_34255_point"."value") AS "baseload"
 FROM "ticket_34255_point"
 GROUP BY 1
 }}}

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070185a96361ed-9d441f77-5a19-4b49-b20c-caeae30f986a-00%40eu-central-1.amazonses.com.

Re: [Django] #34254: Exists annotations can return non-boolean results (i.e. None) if used with an empty QuerySet.

[Django]

#34254: Exists annotations can return non-boolean results (i.e. None) if used 
with
an empty QuerySet.
-+-
 Reporter:  Keryn Knight |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  Exists   | Triage Stage:  Accepted
  EmptyQuerySet sqlite postgres  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Simon Charette):

 * stage:  Unreviewed => Accepted


Comment:

 Thanks for the detailed report Keryn.

 I suspect the solution is as simple as setting
 `Exists.empty_result_set_value = False` which was missed in
 dd1fa3a31b4680c0d3712e6ae122b878138580c7 and since `Exists` sublasses
 `Subquery` it inherited its `.empty_result_set_value = None`.

 {{{#!python
 diff --git a/django/db/models/expressions.py
 b/django/db/models/expressions.py
 index c270ef16c7..fc1d94fefb 100644
 --- a/django/db/models/expressions.py
 +++ b/django/db/models/expressions.py
 @@ -1548,6 +1548,7 @@ def get_group_by_cols(self):
  class Exists(Subquery):
  template = "EXISTS(%(subquery)s)"
  output_field = fields.BooleanField()
 +empty_result_set_value = False

  def __init__(self, queryset, **kwargs):
  super().__init__(queryset, **kwargs)
 }}}

 If you can confirm this addresses the problem you reported could you
 submit a PR with a regression test?

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070185a8fd7efa-2a64159f-89ef-43b9-9506-08cf2f893fed-00%40eu-central-1.amazonses.com.

Re: [Django] #34255: Annotation/group by with an expression on psycopg3

[Django]

#34255: Annotation/group by with an expression on psycopg3
-+-
 Reporter:  Guillaume Andreu |Owner:  nobody
  Sabater|
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:
 Keywords:  orm postgres | Triage Stage:  Accepted
  psycopg3 annotation groupby|
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette):

 This will be hard to solve as group by aliasing cannot be used since
 `year` is stripped from the query. All I can think of right now is doing
 something along the lines of

 {{{#!python
 SELECT
 "fail_curve"."id",
 ARRAY(
 SELECT "json" FROM (
 SELECT
 EXTRACT(
 YEAR
 FROM
 U0."start_at" AT TIME ZONE $1
 ) AS "year",
 jsonb_build_object(
 $2,
 "year",
 $3,
 AVG(U0."value")
 ) AS "json"
 FROM
 "fail_point" U0
 WHERE
 U0."curve_id" = ("fail_curve"."id")
 GROUP BY "year"
 )
 ) AS "_baseloads"
 FROM
 "fail_curve"
 WHERE
 "fail_curve"."id" = $4
 }}}

 When a group alias is ultimately masked, similarly to how we've done it
 with window functions filtering and value masking.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070185a7583a1e-40c0cbe1-16e8-4c4e-a10d-c2a742658dae-00%40eu-central-1.amazonses.com.

Re: [Django] #27029: Make EmailValidator accept non-ASCII characters

[Django]

#27029: Make EmailValidator accept non-ASCII characters
-+-
 Reporter:  Ramin Farajpour  |Owner:  j-bernard
  Cami   |
 Type:   |   Status:  assigned
  Cleanup/optimization   |
Component:  Core (Other) |  Version:  dev
 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
-+-

Comment (by j-bernard):

 Here is a little context for my use case. In general, I create my own
 validator whenever it's needed to override the default Django behavior but
 I have a particular case where django-allauth app is used and is using the
 EmailValidator. In that case, I cannot easily override it.
 My suggestion would then be to make the default validator more permissive
 to get some flexibility in the kind of use case that I have. One can still
 include another validation layer on top of that.

 If you don't mind implementing a more complex specific validator for
 internationalized email addresses it would be better to avoid using only a
 regex. I kept it the simplest as I could in my PR because I'm aware that
 changing the validator is touchy.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070185a66dd1b1-4bd0b52a-e3b1-4ea6-b91a-189887e19379-00%40eu-central-1.amazonses.com.

Re: [Django] #34255: Annotation/group by with an expression on psycopg3

[Django]

#34255: Annotation/group by with an expression on psycopg3
-+-
 Reporter:  Guillaume Andreu |Owner:  nobody
  Sabater|
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  dev
  (models, ORM)  |
 Severity:  Release blocker  |   Resolution:
 Keywords:  orm postgres | Triage Stage:  Accepted
  psycopg3 annotation groupby|
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Mariusz Felisiak):

 * cc: Florian Apolloner, Simon Charette (added)
 * severity:  Normal => Release blocker
 * stage:  Unreviewed => Accepted


Comment:

 Thanks for the report. I was to able to reproduce the issue without
 `SubqueryArray()` and with built-in `JSONObject`:
 {{{#!python
 @override_settings(USE_TZ=True)
 def test_group_by_crash(self):
 tz = zoneinfo.ZoneInfo("Europe/Paris")
 qs = Point.objects.annotate(
 year=ExtractYear("start_at", tzinfo=tz),
 ).values("year").annotate(
 baseload=Avg("value"),
 json=JSONObject(
 year=F("year"),
 baseload=F("baseload"),
 )
 ).values("json")
 list(qs)
 }}}

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070185a60e4225-f2b68585-0193-460e-8e8c-3b0cb9c59a6b-00%40eu-central-1.amazonses.com.

Re: [Django] #34234: Drop support for PROJ < 5.

[Django]

#34234: Drop support for PROJ < 5.
--+
 Reporter:  Mariusz Felisiak  |Owner:  Leo Tom
 Type:  Cleanup/optimization  |   Status:  assigned
Component:  GIS   |  Version:  dev
 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):

 * needs_better_patch:  0 => 1


-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070185a5f26da1-8b4d41f3-1155-438c-93f9-c3212f143ed0-00%40eu-central-1.amazonses.com.

[Django] #34255: Annotation/group by with an expression on psycopg3

[Django]

#34255: Annotation/group by with an expression on psycopg3
-+-
   Reporter:  Guillaume  |  Owner:  nobody
  Andreu Sabater |
   Type:  Bug| Status:  new
  Component:  Database   |Version:  dev
  layer (models, ORM)|   Keywords:  orm postgres
   Severity:  Normal |  psycopg3 annotation groupby
   Triage Stage: |  Has patch:  0
  Unreviewed |
Needs documentation:  0  |Needs tests:  0
Patch needs improvement:  0  |  Easy pickings:  0
  UI/UX:  0  |
-+-
 Given the following code:

 {{{
 import zoneinfo

 from django.db import models
 from django.db.models.functions import ExtractYear

 from django.contrib.postgres.fields import ArrayField

 TZ = zoneinfo.ZoneInfo("Europe/Paris")


 class JsonBuildObject(models.Func):
 function = "jsonb_build_object"
 output_field = models.JSONField()


 class SubqueryArray(models.Subquery):
 template = "ARRAY(%(subquery)s)"
 output_field = ArrayField(base_field=models.JSONField())


 class CurveQuerySet(models.QuerySet["Curve"]):
 """Curve QuerySet."""

 def annotate_loads(self) -> "CurveQuerySet":
 """Annotate baseload by year."""
 baseload_qs = (
 Point.objects.filter(curve=models.OuterRef("pk"))
 .annotate(year=ExtractYear("start_at", tzinfo=TZ))
 .values("year")
 .alias(baseload=models.Avg("value"))
 .annotate(
 json=JsonBuildObject(
 models.Value("year"),
 models.F("year"),
 models.Value("baseload"),
 models.F("baseload"),
 )
 )
 .values("json")
 )

 return self.annotate(_baseloads=SubqueryArray(baseload_qs))


 CurveManager = models.Manager.from_queryset(CurveQuerySet)


 class Curve(models.Model):
 """Curve."""

 objects = CurveManager()


 class Point(models.Model):
 """Curve point."""

 curve = models.ForeignKey(
 Curve,
 on_delete=models.CASCADE,
 related_name="points",
 related_query_name="point",
 )
 start_at = models.DateTimeField()
 value = models.FloatField()

 }}}

 I use the ''annotate_loads'' to compute yearly averages (with
 .values("year") acting as a GROUP BY) and dump the results in a json
 field.

 With psycopg3, from what I've seen, the query params/values are not
 interpolated in the query anymore, but sent alongside the query to the
 server.

 In my case, it looks like this:

 {{{
 SELECT
 "fail_curve"."id",
 ARRAY(
 SELECT
 jsonb_build_object(
 $1,
 EXTRACT(
 YEAR
 FROM
 U0."start_at" AT TIME ZONE $2
 ),
 $3,
 AVG(U0."value")
 ) AS "json"
 FROM
 "fail_point" U0
 WHERE
 U0."curve_id" = ("fail_curve"."id")
 GROUP BY
 EXTRACT(
 YEAR
 FROM
 U0."start_at" AT TIME ZONE $4
 )
 ) AS "_baseloads"
 FROM
 "fail_curve"
 WHERE
 "fail_curve"."id" = $5
 }}}

 But postgres doesn't like: django.db.utils.ProgrammingError: column
 "u0.start_at" must appear in the GROUP BY clause or be used in an
 aggregate function

 because

 {{{
 EXTRACT(
 YEAR
 FROM
 U0."start_at" AT TIME ZONE $2
 )
 }}}
 is different from
 {{{
 EXTRACT(
 YEAR
 FROM
 U0."start_at" AT TIME ZONE $4
 )
 }}}

 I tested an updated query using the same placeholder ($4 -> $2) and it
 worked as expected:

 {{{
 PREPARE working (text, text, text, int) AS
 SELECT
 "fail_curve"."id",
 ARRAY(
 SELECT
 jsonb_build_object(
 $1,
 EXTRACT(
 YEAR
 FROM
 U0."start_at" AT TIME ZONE $2
 ),
 $3,
 AVG(U0."value")
 ) AS "json"
 FROM
 "fail_point" U0
 WHERE
 U0."curve_id" = ("fail_curve"."id")
 GROUP BY
 EXTRACT(
 YEAR
 FROM
 U0."start_at" AT TIME ZONE $2
 )
 ) AS "_baseloads"
 FROM
 "fail_curve"
 WHERE
 "fail_curve"."id" = $4
 LIMIT
 1;
 EXECUTE working('year', 'Europe/Paris', 'base', 1);
 }}}

 My understanding is as follow:
 * group by is an expression
 * this expressi

Re: [Django] #18468: Add the ability to define comments in table / columns

[Django]

#18468: Add the ability to define comments in table / columns
-+-
 Reporter:  Marc Rechté  |Owner:
 |  KimSoungRyoul
 Type:  New feature  |   Status:  closed
Component:  Database layer   |  Version:  dev
  (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 ):

 In [changeset:"648005dee62481acc1784e5c9625e90f0fd6aab4" 648005d]:
 {{{
 #!CommitTicketReference repository=""
 revision="648005dee62481acc1784e5c9625e90f0fd6aab4"
 Refs #18468 -- Used obj_description() with a catalog name on PostgreSQL.

 obj_description(object oid) without a catalog name is deprecated since
 there is no guarantee that OIDs are unique across different system
 catalogs.

 Thanks Tim Graham for the report.
 }}}

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070185a5efdc86-8f799a01-d1ee-45b2-9909-dae0f13f51e1-00%40eu-central-1.amazonses.com.

[Django] #34254: Exists annotations can return non-boolean results (i.e. None) if used with an empty QuerySet.

[Django]

#34254: Exists annotations can return non-boolean results (i.e. None) if used 
with
an empty QuerySet.
-+-
   Reporter:  Keryn  |  Owner:  nobody
  Knight |
   Type:  Bug| Status:  new
  Component:  Database   |Version:  dev
  layer (models, ORM)|   Keywords:  Exists
   Severity:  Normal |  EmptyQuerySet sqlite postgres
   Triage Stage: |  Has patch:  0
  Unreviewed |
Needs documentation:  0  |Needs tests:  0
Patch needs improvement:  0  |  Easy pickings:  0
  UI/UX:  0  |
-+-
 I suspect this is following on from, but potentially separate to #33018 --
 because that ticket starts out using `Django 3.2` to observe that an empty
 queryset (`EmptyQuerySet` or whatever, via `none()`) can short circuit
 evaluation to be `0 as ...` in the SQL query, which is exactly the same
 problem I observed.

 However, as far as I can tell, the result of an `Exists(queryset.none())`
 can still return values outside of `True/False`, namely, **None**.

 Using Django `main` as of `4593bc5da115f2e808a803a4ec24104b6c7a6152` (from
 `Wed Jan 11 ... 2023`), here's the behaviour on both postgres and sqlite.
 In both scenarios I'm using  `3.10.2` with `psycopg2==2.9.3` and
 `sqlite3.sqlite_version` is `3.37.0` and `sqlite3.version` is `2.6.0`.
 IPython outputs **8** and **11** are the problems.

 {{{
 class A(models.Model):
 pass

 class B(models.Model):
 pass
 }}}

 {{{
 In [1]: from app.models import A, B

 In [2]: A.objects.using("pg").create()
 Out[2]: 

 In [3]: B.objects.using("pg").create()
 Out[3]: 

 In [4]: A.objects.using("sqlite").create()
 Out[4]: 

 In [4]: B.objects.using("sqlite").create()
 Out[4]: 

 In [5]: from django.db.models import Exists

 In [6]:
 
A.objects.using("sqlite").annotate(should_be_bool=Exists(B.objects.all())).first().should_be_bool
 Out[6]: True

 In [7]:
 
A.objects.using("sqlite").annotate(should_be_bool=Exists(B.objects.filter(pk=))).first().should_be_bool
 Out[7]: False

 In [8]:
 
A.objects.using("sqlite").annotate(should_be_bool=Exists(B.objects.none())).first().should_be_bool
 # This is the problem, it returned neither True nor False

 In [9]:
 
A.objects.using("pg").annotate(should_be_bool=Exists(B.objects.all())).first().should_be_bool
 Out[9]: True

 In [10]:
 
A.objects.using("pg").annotate(should_be_bool=Exists(B.objects.filter(pk=))).first().should_be_bool
 Out[10]: False

 In [11]:
 
A.objects.using("pg").annotate(should_be_bool=Exists(B.objects.none())).first().should_be_bool
 # This is the problem, it returned neither True nor False
 }}}
 And the queries, which are the same for postgres & sqlite:
 {{{
 # ...
 {'sql': 'SELECT "app_a"."id", EXISTS(SELECT 1 AS "a" FROM "app_b" LIMIT 1)
 AS "should_be_bool" FROM "app_a" ORDER BY "app_a"."id" ASC LIMIT 1',
   'time': '0.001'},
  {'sql': 'SELECT "app_a"."id", EXISTS(SELECT 1 AS "a" FROM "app_b" U0
 WHERE U0."id" =  LIMIT 1) AS "should_be_bool" FROM "app_a" ORDER
 BY "app_a"."id" ASC LIMIT 1',
   'time': '0.001'},
  {'sql': 'SELECT "app_a"."id", NULL AS "should_be_bool" FROM "app_a" ORDER
 BY "app_a"."id" ASC LIMIT 1',
   'time': '0.001'}
 }}}

 Given  `Exists` has an `output_field` of `BooleanField` and that
 definition doesn't have `null=True` as an argument, it seems incongruent
 from both an expectations ("exists sounds boolean") and implementation
 ("it doesn't say it could be null") standpoint.


 

 Whilst the problem exists in `main`, it has also changed behaviour
 (presumably or potentially unexpectedly) since `3.2`, where `postgres` and
 `sqlite` actually do different things, hence we tested both above. So
 `main` is now ''consistent'', but I'd personally argue it's consistently
 ''wrong'' (for a given value thereof, no judgement made!)

 In `3.2.16`, under sqlite, using `annotate(x=Exists(y.none()))` returns
 `False` but on `main` it now returns `None` (see above) -- the `3.2`
 behaviour is correct for ''my expectations''
 {{{
 In [4]:
 
A.objects.using("sqlite").annotate(should_be_bool=Exists(B.objects.none())).first().should_be_bool
 Out[4]: False
 In [5]: connections['sqlite'].queries
 Out[5]:
  {'sql': 'SELECT "app_a"."id", 0 AS "should_be_bool" FROM "app_a" ORDER BY
 "app_a"."id" ASC LIMIT 1',
   'time': '0.000'}
 }}}
 In `3.2.16` with postgres we get neither `None` nor `False` but the
 integer `0` instead:
 {{{
 In [4]:
 
A.objects.using("pg").annotate(should_be_bool=Exists(B.objects.none())).first().should_be_bool
 Out[4]: 0
 In [5]: connections['pg'].queries
 Out[5]:
 {'sql': 'SELECT "app_a"."id", 0 AS "should_be_bool" FROM "app_a" ORDER BY
 "app_a"."id" ASC LIMIT 1',
   'time': '0.001'}
 }}}

 ---

Re: [Django] #26029: Provide an API to configure arbitrary file storage backends

[Django]

#26029: Provide an API to configure arbitrary file storage backends
-+-
 Reporter:  Aymeric Augustin |Owner:  Jarosław
 |  Wygoda
 Type:  New feature  |   Status:  closed
Component:  File |  Version:  dev
  uploads/storage|
 Severity:  Normal   |   Resolution:  fixed
 Keywords:   | Triage Stage:  Accepted
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Mariusz Felisiak ):

 In [changeset:"32940d390a00a30a6409282d314d617667892841" 32940d3]:
 {{{
 #!CommitTicketReference repository=""
 revision="32940d390a00a30a6409282d314d617667892841"
 Refs #26029 -- Deprecated DEFAULT_FILE_STORAGE and STATICFILES_STORAGE
 settings.
 }}}

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070185a575cbff-1aa513cb-8700-469f-bfcf-75777c160c9f-00%40eu-central-1.amazonses.com.