[Django] #30132: str(QuerySet.query) returns broken SQL for filter on text field

[Django]

#30132: str(QuerySet.query) returns broken SQL for filter on text field
-+-
   Reporter:  bernd- |  Owner:  nobody
  wechner|
   Type:  Bug| Status:  new
  Component:  Database   |Version:  2.1
  layer (models, ORM)|
   Severity:  Normal |   Keywords:  QuerySet SQL
   Triage Stage: |  Has patch:  0
  Unreviewed |
Needs documentation:  0  |Needs tests:  0
Patch needs improvement:  0  |  Easy pickings:  0
  UI/UX:  0  |
-+-
 The context is a Postgresql database. The observation is that
 str(QuerySet.query) returns valid SQL and is often advised as the method
 for returning the SQL that a QuerySet will execute. The desire, is for
 this to be true.

 I cannot find anywhere in the documentation that this is guaranteed alas.
 The closes I come is this:

 https://docs.djangoproject.com/en/2.1/faq/models/#how-can-i-see-the-raw-
 sql-queries-django-is-running

 which advises checking the connection log, but this only works when DEBUG
 is enabled and hence is not a production solution.

 In production, we must therefore, it seems, fall back on
 str(QuerySet.query), which empirically seems to work almost all the time
 and be regulalrly advised in response to questions on-line as the method
 to employ.

 To wit, we could expect that a round trip always works:

 {{{
 qs=model.objects.somequeryset
 sql=str(qs.query)
 raw_qs=model.objects.raw(sql)
 }}}

 and that qs and raw_qs return the same objects.

 And this in fact works most of the time. But if somequeryset is a filter
 on a text field it does not. The sql above fails to quote the text literal
 in the WHERE clause.

 I have a clear example tested and coded here:

 https://github.com/bernd-
 
wechner/DjangoTutorial/blob/238787c83ef8515aeeb405577980e71ff35664e8/Library/views.py

 using only the very basic tutorial examples. I am looking at a list of
 Book objects, and this works fine, but the code produces this output
 before crashing because of ill formed SQL.

 {{{
 Queryset returns 1 items.
 The executed SQL was:
  SELECT "Library_book"."id", "Library_book"."title",
 "Library_book"."author_id" FROM "Library_book" WHERE
 "Library_book"."title" = 'My Life'
 The SQL that queryset.query returns is:
  SELECT "Library_book"."id", "Library_book"."title",
 "Library_book"."author_id" FROM "Library_book" WHERE
 "Library_book"."title" = My Life
 }}}

 The second SQL dump should be identical to the first (in fact I need it to
 be on a production site - or some other way to get it!).

 The crash that follows is:

 {{{
 django.db.utils.ProgrammingError: syntax error at or near "Life"
 LINE 1: ..." FROM "Library_book" WHERE "Library_book"."title" = My Life
 }}}

 I've tried tracing into the Django source code to find where the second
 SQL string is generated but my ignorance has got the better of me and it's
 costingme too much time so I have to park it. I can't seem to single step
 my debugger sensibly to any point yet where I see str(QuerySet.query)
 doing its thing to generate SQL.

 So instead I content myself for now in concluding that if the premise
 holds (that str(QuerySet.query) should return the same SQl as the
 connection Log, and SQL that can be used to generate the same result using
 a raw query, then this is a bug.

 I'd gladly fix it and offer a PR but alas lack the skills to do that in a
 timely manner and will park it here for now as where I need this in my
 production site is not on a critical path and can easily wait (i.e. I have
 higher priorities calling for my skill set currently).

 If the premise does not hold, and there is no expectation that
 str(QuerySet.query) should return valid SQL that can be used like this, my
 apologies and this becomes a support question and I will move it to that
 context. The question being how can one in a production environment
 without DEBUG enabled extract the SQL from a QuerySet.

 This document:

 https://docs.djangoproject.com/en/2.1/faq/models/#how-can-i-see-the-raw-
 sql-queries-django-is-running

 seems to imply Django has no production environment method for extracting
 this SQL and the much advertised on-line method of str(QuerySet.query) is
 dangerous and not reliable.

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this 

Re: [Django] #30064: Admin search with a null character crashes with "A string literal cannot contain NUL (0x00) characters." on PostgreSQL

[Django]

#30064: Admin search with a null character crashes with "A string literal cannot
contain NUL (0x00) characters." on PostgreSQL
---+
 Reporter:  kenichi-cc |Owner:  nobody
 Type:  Bug|   Status:  new
Component:  contrib.admin  |  Version:  2.1
 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
---+

Comment (by Can Sarıgöl):

 ok Thanks for the detailed description, I will fix it as per your
 suggestion.

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/068.6811b18c8c4a75f69a7f3680de2fa1bb%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30128: Using database functions with tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect query (was: Using database functions with tzinfo=datetime.timedelta(...) re

[Django]

#30128: Using database functions with
tzinfo=datetime.timezone(datetime.timedelta(...)) results in an incorrect
query
-+-
 Reporter:  mvarnar  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  orm, postgresql, | Triage Stage:  Accepted
  timezone, datetime |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by mvarnar):

 It's about tzinfo=datetime.timezone(datetime.timedelta(...)) not
 tzinfo=datetime.timedelta(...).

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.30be8928d1fbfd8ab4ab8564c9ba9d4c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30130: Django .values().distinct() returns a lot more records than .values().distinct().count()

[Django]

#30130: Django .values().distinct() returns a lot more records than
.values().distinct().count()
-+-
 Reporter:  James Lin|Owner:  nobody
 Type:  Bug  |   Status:  closed
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:  duplicate
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Simon Charette):

 * status:  new => closed
 * resolution:   => duplicate


Comment:

 There doesn't seem to be a consensus on how to deal with these type of
 queries right now so I'm just going to close as a duplicate of #14357,
 #16025, #28560.

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.512cb7324e0560d2f82bedc56029d3b8%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30131: When I Do The code for the tutorial from 2017 it seems to be wrong that i have to fix it

[Django]

#30131: When I Do The code for the tutorial from 2017 it seems to be wrong that 
i
have to fix it
---+--
 Reporter:  Noah   |Owner:  Noah
 Type:  New feature|   Status:  closed
Component:  Uncategorized  |  Version:  2.2
 Severity:  Normal |   Resolution:  invalid
 Keywords: | Triage Stage:  Unreviewed
Has patch:  0  |  Needs documentation:  0
  Needs tests:  0  |  Patch needs improvement:  0
Easy pickings:  0  |UI/UX:  0
---+--
Changes (by Simon Charette):

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


-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/062.8201b5094d6f654bbda189675f5ba19e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30130: Django .values().distinct() returns a lot more records than .values().distinct().count()

[Django]

#30130: Django .values().distinct() returns a lot more records than
.values().distinct().count()
-+-
 Reporter:  James Lin|Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette):

 I'm pretty sure that's what's at play here as I managed to reproduce
 locally. You have a defined `Meta.ordering` (or are performing an
 `order_by`) which makes your non-count query something along the lines of

 {{{#!sql
 SELECT DISTINCT "machine", "cluster", "ordering" FROM "resources" ORDER BY
 "ordering" ASC
 }}}

 The `ordering` column is added to the `SELECT` clause to prevent the
 generation of invalid SQL.

 {{{#!sql
 SELECT DISTINCT "machine", "cluster" FROM "resources" ORDER BY "ordering"
 ASC
 > ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select
 list
 }}}

 The `.count()` query happens to be the following

 {{{#!sql
 SELECT COUNT(*) FROM (SELECT DISTINCT "ticket_30130_foo"."bat" AS Col1,
 "ticket_30130_foo"."baz" AS Col2 FROM "ticket_30130_foo") subquery
 }}}

 It looks like the issue is that
 
[https://github.com/django/django/blob/7e978fdc4228eb44cf97cb4243adc7b7bfd586c7/django/db/models/sql/query.py#L439-L443
 we clear the ordering when we shouldn't] on the `.count()`. I think the
 logic needs to be adjusted to branch of `not self.distinct` instead of
 `self.distinct_fields`.

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.391f20ea758b15f7eac92c14b96f6eac%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30131: When I Do The code for the tutorial from 2017 it seems to be wrong that i have to fix it

[Django]

#30131: When I Do The code for the tutorial from 2017 it seems to be wrong that 
i
have to fix it
---+--
 Reporter:  Noah   |Owner:  Noah
 Type:  New feature|   Status:  assigned
Component:  Uncategorized  |  Version:  2.2
 Severity:  Normal |   Resolution:
 Keywords: | Triage Stage:  Unreviewed
Has patch:  0  |  Needs documentation:  0
  Needs tests:  0  |  Patch needs improvement:  0
Easy pickings:  0  |UI/UX:  0
---+--
Changes (by Noah):

 * owner:  nobody => Noah
 * status:  new => assigned


-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/062.522c45f7f7ec4ad582f8198f1d7109d7%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30131: When I Do The code for the tutorial from 2017 it seems to be wrong that i have to fix it

[Django]

#30131: When I Do The code for the tutorial from 2017 it seems to be wrong that 
i
have to fix it
---+--
 Reporter:  Noah   |Owner:  nobody
 Type:  New feature|   Status:  new
Component:  Uncategorized  |  Version:  2.2
 Severity:  Normal |   Resolution:
 Keywords: | Triage Stage:  Unreviewed
Has patch:  0  |  Needs documentation:  0
  Needs tests:  0  |  Patch needs improvement:  0
Easy pickings:  0  |UI/UX:  0
---+--
Changes (by Noah):

 * status:  closed => new
 * resolution:  invalid =>


-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/062.4cedb2359fe7c8f26c7225f6e431a150%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30131: When I Do The code for the tutorial from 2017 it seems to be wrong that i have to fix it

[Django]

#30131: When I Do The code for the tutorial from 2017 it seems to be wrong that 
i
have to fix it
---+--
 Reporter:  Noah   |Owner:  nobody
 Type:  New feature|   Status:  closed
Component:  Uncategorized  |  Version:  2.2
 Severity:  Normal |   Resolution:  invalid
 Keywords: | Triage Stage:  Unreviewed
Has patch:  0  |  Needs documentation:  0
  Needs tests:  0  |  Patch needs improvement:  0
Easy pickings:  0  |UI/UX:  0
---+--
Changes (by Tim Graham):

 * status:  new => closed
 * resolution:   => invalid


Comment:

 See TicketClosingReasons/UseSupportChannels for places to get help,
 however, I'm not sure your request is understandable.

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/062.8d90f49c921c5e3b1bd50d9623e2958a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

[Django] #30131: When I Do The code for the tutorial from 2017 it seems to be wrong that i have to fix it

[Django]

#30131: When I Do The code for the tutorial from 2017 it seems to be wrong that 
i
have to fix it
-+
   Reporter:  Noah   |  Owner:  nobody
   Type:  New feature| Status:  new
  Component:  Uncategorized  |Version:  2.2
   Severity:  Normal |   Keywords:
   Triage Stage:  Unreviewed |  Has patch:  0
Needs documentation:  0  |Needs tests:  0
Patch needs improvement:  0  |  Easy pickings:  0
  UI/UX:  0  |
-+
 Hello when i do the  Net Ninja's tutorial on youtube which was filmed in
 2017 the code wasnt updated but i had to fix the code and it took a while
 can you put the code on django.

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/047.0b82db98439a2d850ca078c3df5d91b8%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30064: Admin search with a null character crashes with "A string literal cannot contain NUL (0x00) characters." on PostgreSQL

[Django]

#30064: Admin search with a null character crashes with "A string literal cannot
contain NUL (0x00) characters." on PostgreSQL
---+
 Reporter:  kenichi-cc |Owner:  nobody
 Type:  Bug|   Status:  new
Component:  contrib.admin  |  Version:  2.1
 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
---+

Comment (by Simon Charette):

 > I thought that the parameter of a single execution doesn't affect too
 much. when the validator is called, the same case about performance would
 happen here as well.

 I think it's safe to assume every non-static or cached request handled by
 Django results in at least one database queries and that it isn't uncommon
 for queries to have at least one string parameter. Given these assumptions
 it's unlikely that performing a one time per admin changelist search
 request validation is ever going to have the same performance implications
 as performing a search and replace for every `'\x00'` string parameters
 provided to the ORM.

 > Wouldn't it be better if a solution that also solves the raw query
 parameters? Users wouldn't have to check it out.

 I don't think so. User input should be sanitized before feeding it to the
 ORM.

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/068.124da056e7d61f5792851b422b0a3ae1%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30130: Django .values().distinct() returns a lot more records than .values().distinct().count()

[Django]

#30130: Django .values().distinct() returns a lot more records than
.values().distinct().count()
-+-
 Reporter:  James Lin|Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette):

 Do you happen to have a `VirtualMachineResources.Meta.ordering` defined?

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.bb9610da09ac41317931a8791b87965d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30064: Admin search with a null character crashes with "A string literal cannot contain NUL (0x00) characters." on PostgreSQL

[Django]

#30064: Admin search with a null character crashes with "A string literal cannot
contain NUL (0x00) characters." on PostgreSQL
---+
 Reporter:  kenichi-cc |Owner:  nobody
 Type:  Bug|   Status:  new
Component:  contrib.admin  |  Version:  2.1
 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
---+

Comment (by Can Sarıgöl):

 I thought that the parameter of a single execution doesn't affect too
 much. when the validator is called, the same case about performance would
 happen here as well.
 Wouldn't it be better if a solution that also solves the raw query
 parameters? Users wouldn't have to check it out.

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/068.eaca2eaf3f73a71be7c3099e79c4429e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30130: Django .values().distinct() returns a lot more records than .values().distinct().count()

[Django]

#30130: Django .values().distinct() returns a lot more records than
.values().distinct().count()
-+-
 Reporter:  James Lin|Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Description changed by James Lin:

Old description:

> I have a table virtualmachineresources, which has 100k+ rows, it has
> columns `machine` and `cluster`, some rows the cluster field is empty. it
> has repeating rows of machine + with/without cluster, hence I want to use
> the distinct() method.
>
> using .values().distinct().count(), it return 2k rows
>

> {{{
> In [6]: VirtualMachineResources.objects.all().values('machine',
> 'cluster')
>...: .distinct().count()
> Out[6]: 2247
> }}}
>

> When I loop through the distinct query
>
> {{{
> for resource in VirtualMachineResources.objects.all().values('machine',
> 'cluster').distinct():
> print(resource['machine'], resource['cluster'])
> }}}
>

> I observed it return 100k rows, with repeating rows that the same
> 'machine` with/without the cluster.
>
> Here is the corresponding stackoverflow question
> https://stackoverflow.com/questions/54354462/django-distinct-returns-
> more-records-than-count

New description:

 I have a table virtualmachineresources, which has 100k+ rows, it has
 columns `machine` and `cluster`, some rows the cluster field is empty. it
 has repeating rows of machine + with/without cluster, hence I want to use
 the distinct() method.

 using .values().distinct().count(), it returned 2k rows


 {{{
 In [6]: VirtualMachineResources.objects.all().values('machine', 'cluster')
...: .distinct().count()
 Out[6]: 2247
 }}}


 When I loop through the distinct query

 {{{
 for resource in VirtualMachineResources.objects.all().values('machine',
 'cluster').distinct():
 print(resource['machine'], resource['cluster'])
 }}}


 I observed it returned 100k rows, with repeating rows that the same
 'machine` with/without the cluster.

 Here is the corresponding stackoverflow question
 https://stackoverflow.com/questions/54354462/django-distinct-returns-more-
 records-than-count

--

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.56932a55c8a5a178be1bab2b451607fb%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30130: Django .values().distinct() returns a lot more records than .values().distinct().count()

[Django]

#30130: Django .values().distinct() returns a lot more records than
.values().distinct().count()
-+-
 Reporter:  James Lin|Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Description changed by James Lin:

Old description:

> I have a table virtualmachineresources, which has 100k+ rows, it has
> columns 'machine` and `cluster`, some rows the cluster field is empty. it
> has repeating rows of machine + with/without cluster, hence I want to use
> the distinct() method.
>
> using .values().distinct().count(), it return 2k rows
>

> {{{
> In [6]: VirtualMachineResources.objects.all().values('machine',
> 'cluster')
>...: .distinct().count()
> Out[6]: 2247
> }}}
>

> When I loop through the distinct query
>
> {{{
> for resource in VirtualMachineResources.objects.all().values('machine',
> 'cluster').distinct():
> print(resource['machine'], resource['cluster'])
> }}}
>

> I observed it return 100k rows, with repeating rows that the same
> 'machine` with/without the cluster.
>
> Here is the corresponding stackoverflow question
> https://stackoverflow.com/questions/54354462/django-distinct-returns-
> more-records-than-count

New description:

 I have a table virtualmachineresources, which has 100k+ rows, it has
 columns `machine` and `cluster`, some rows the cluster field is empty. it
 has repeating rows of machine + with/without cluster, hence I want to use
 the distinct() method.

 using .values().distinct().count(), it return 2k rows


 {{{
 In [6]: VirtualMachineResources.objects.all().values('machine', 'cluster')
...: .distinct().count()
 Out[6]: 2247
 }}}


 When I loop through the distinct query

 {{{
 for resource in VirtualMachineResources.objects.all().values('machine',
 'cluster').distinct():
 print(resource['machine'], resource['cluster'])
 }}}


 I observed it return 100k rows, with repeating rows that the same
 'machine` with/without the cluster.

 Here is the corresponding stackoverflow question
 https://stackoverflow.com/questions/54354462/django-distinct-returns-more-
 records-than-count

--

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/066.d7036eae8e6b2580ce61b9cdd21f1219%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

[Django] #30130: Django .values().distinct() returns a lot more records than .values().distinct().count()

[Django]

#30130: Django .values().distinct() returns a lot more records than
.values().distinct().count()
-+-
   Reporter:  James Lin  |  Owner:  nobody
   Type:  Bug| Status:  new
  Component:  Database   |Version:  2.1
  layer (models, ORM)|
   Severity:  Normal |   Keywords:
   Triage Stage: |  Has patch:  0
  Unreviewed |
Needs documentation:  0  |Needs tests:  0
Patch needs improvement:  0  |  Easy pickings:  0
  UI/UX:  0  |
-+-
 I have a table virtualmachineresources, which has 100k+ rows, it has
 columns 'machine` and `cluster`, some rows the cluster field is empty. it
 has repeating rows of machine + with/without cluster, hence I want to use
 the distinct() method.

 using .values().distinct().count(), it return 2k rows


 {{{
 In [6]: VirtualMachineResources.objects.all().values('machine', 'cluster')
...: .distinct().count()
 Out[6]: 2247
 }}}


 When I loop through the distinct query

 {{{
 for resource in VirtualMachineResources.objects.all().values('machine',
 'cluster').distinct():
 print(resource['machine'], resource['cluster'])
 }}}


 I observed it return 100k rows, with repeating rows that the same
 'machine` with/without the cluster.

 Here is the corresponding stackoverflow question
 https://stackoverflow.com/questions/54354462/django-distinct-returns-more-
 records-than-count

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/051.98e9e98ea07c57f358a9f479583a1742%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30129: Allow creating models with fields values as a Subquery() with F() expressions

[Django]

#30129: Allow creating models with fields values as a Subquery() with F()
expressions
-+-
 Reporter:  Charlie McBride  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  Subquery, F, Query   | Triage Stage:
  Expressions|  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Description changed by Charlie McBride:

Old description:

> I understand why F() expressions are generally disallowed inside insert
> statements, the columns you are referencing don't yet exist, so it
> wouldn't make any sense to do so. However, if you are performing an
> insert with a Subquery (as in the example below), it's possible to have
> otherwise valid statements rejected because of the blanket blacklist of
> F() expressions during inserts.
>
> For example:
>
> given the following models:
>

> {{{
> class Item(models.Model):
> pass
>
> class ItemVersion(models.Model):
> item = models.ForeignKey(Item, related_name='versions')
> version_number = models.IntegerField(default=0)
>
> class Meta:
> unique_together = ('item', 'version_number',)
> }}}
>
> I would like to be able to do the following operation to mitigate race
> conditions as described here
> (https://docs.djangoproject.com/en/2.1/ref/models/expressions/#avoiding-
> race-conditions-using-f)
>

>
> {{{
> item = Item.objects.create()
> # arbitrary number of other items created/destroyed etc
> item_version_2 = ItemVersion.objects.create(
> item=item,
> version_number=Subquery(
> item.versions.order_by('- version_number').annotate(
> max_version_number=Coalesce(Max('version_number'), 0)
> ).annotate(
> new_version_number=F('max_version_number) + 1
> ).values('new_version_number')[:1]
>  )
>  )
> }}}
>
> As written, I would expect the F() in the inner expression to always be
> resolvable, because it is in a Subquery (and not the result of an
> insert). However this query is blocked by the compiler because "F()
> expressions can only be used to update, not to insert." Would it be
> possible to allow F expressions in a Subquery even if it is being used in
> an insert? Is there an edge case that I'm missing that caused the team to
> not consider this?

New description:

 I understand why F() expressions are generally disallowed inside insert
 statements, the columns you are referencing don't yet exist, so it
 wouldn't make any sense to do so. However, if you are performing an insert
 with a Subquery (as in the example below), it's possible to have otherwise
 valid statements rejected because of the blanket blacklist of F()
 expressions during inserts.

 For example:

 given the following models:


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

 class ItemVersion(models.Model):
 item = models.ForeignKey(Item, related_name='versions')
 version_number = models.IntegerField(default=0)

 class Meta:
 unique_together = ('item', 'version_number',)
 }}}

 I would like to be able to do the following operation to mitigate race
 conditions as described here
 (https://docs.djangoproject.com/en/2.1/ref/models/expressions/#avoiding-
 race-conditions-using-f)



 {{{
 item = Item.objects.create()
 # arbitrary number of other items created/destroyed etc
 item_version_2 = ItemVersion.objects.create(
 item=item,
 version_number=Subquery(
 item.versions.order_by('-version_number').annotate(
 max_version_number=Coalesce(Max('version_number'), 0)
 ).annotate(
 new_version_number=F('max_version_number) + 1
 ).values('new_version_number')
  )
  )
 }}}

 As written, I would expect the F() in the inner expression to always be
 resolvable, because it is in a Subquery (and not the result of an insert).
 However this query is blocked by the compiler because "F() expressions can
 only be used to update, not to insert." Would it be possible to allow F
 expressions in a Subquery even if it is being used in an insert? Is there
 an edge case that I'm missing that caused the team to not consider this?

--

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

Re: [Django] #30129: Allow creating models with fields values as a Subquery() with F() expressions (was: Request to Allow F() Expressions in Subquery() on Insert)

[Django]

#30129: Allow creating models with fields values as a Subquery() with F()
expressions
-+-
 Reporter:  Charlie McBride  |Owner:  nobody
 Type:  New feature  |   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  Subquery, F, Query   | Triage Stage:
  Expressions|  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tim Graham):

 * easy:  1 => 0


-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/068.1f6b6c80fe3daade6600ef540d42652c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

[Django] #30129: Request to Allow F() Expressions in Subquery() on Insert

[Django]

#30129: Request to Allow F() Expressions in Subquery() on Insert
-+-
   Reporter:  Charlie|  Owner:  nobody
  McBride|
   Type:  New| Status:  new
  feature|
  Component:  Database   |Version:  2.1
  layer (models, ORM)|   Keywords:  Subquery, F, Query
   Severity:  Normal |  Expressions
   Triage Stage: |  Has patch:  0
  Unreviewed |
Needs documentation:  0  |Needs tests:  0
Patch needs improvement:  0  |  Easy pickings:  1
  UI/UX:  0  |
-+-
 I understand why F() expressions are generally disallowed inside insert
 statements, the columns you are referencing don't yet exist, so it
 wouldn't make any sense to do so. However, if you are performing an insert
 with a Subquery (as in the example below), it's possible to have otherwise
 valid statements rejected because of the blanket blacklist of F()
 expressions during inserts.

 For example:

 given the following models:


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

 class ItemVersion(models.Model):
 item = models.ForeignKey(Item, related_name='versions')
 version_number = models.IntegerField(default=0)

 class Meta:
 unique_together = ('item', 'version_number',)
 }}}

 I would like to be able to do the following operation to mitigate race
 conditions as described here
 (https://docs.djangoproject.com/en/2.1/ref/models/expressions/#avoiding-
 race-conditions-using-f)



 {{{
 item = Item.objects.create()
 # arbitrary number of other items created/destroyed etc
 item_version_2 = ItemVersion.objects.create(
 item=item,
 version_number=Subquery(
 item.versions.order_by('- version_number').annotate(
 max_version_number=Coalesce(Max('version_number'), 0)
 ).annotate(
 new_version_number=F('max_version_number) + 1
 ).values('new_version_number')[:1]
  )
  )
 }}}

 As written, I would expect the F() in the inner expression to always be
 resolvable, because it is in a Subquery (and not the result of an insert).
 However this query is blocked by the compiler because "F() expressions can
 only be used to update, not to insert." Would it be possible to allow F
 expressions in a Subquery even if it is being used in an insert? Is there
 an edge case that I'm missing that caused the team to not consider this?

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/053.5d4245b9994a466ea240f4a7585a73bd%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30128: Using database functions with tzinfo=datetime.timedelta(...) results in an incorrect query (was: Using timezone created with datetime.timedelta causes incorrect timezone in SQL qu

[Django]

#30128: Using database functions with tzinfo=datetime.timedelta(...) results in 
an
incorrect query
-+-
 Reporter:  mvarnar  |Owner:  nobody
 Type:  Bug  |   Status:  new
Component:  Database layer   |  Version:  2.1
  (models, ORM)  |
 Severity:  Normal   |   Resolution:
 Keywords:  orm, postgresql, | Triage Stage:  Accepted
  timezone, datetime |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tim Graham):

 * stage:  Unreviewed => Accepted


Comment:

 Using a timedelta as the tzinfo argument to database functions looks
 untested so I can understand if it's not working.

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.252c84768930a805f15720c08181%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30122: Add index in object_id column in LogEntry table to make fetch query faster.

[Django]

#30122: Add index in object_id column in LogEntry table to make fetch query 
faster.
-+-
 Reporter:  Shashank Parekh  |Owner:  nobody
 Type:   |   Status:  closed
  Cleanup/optimization   |
Component:  contrib.admin|  Version:  master
 Severity:  Normal   |   Resolution:  duplicate
 Keywords:   | Triage Stage:
 |  Unreviewed
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tim Graham):

 * status:  new => closed
 * resolution:   => duplicate


-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/063.d3e9ead3b114fb5a4155d055623e0516%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #17659: django_admin_log searches are slow

[Django]

#17659: django_admin_log searches are slow
--+
 Reporter:  anonymous |Owner:  keeff
 Type:  Cleanup/optimization  |   Status:  new
Component:  contrib.admin |  Version:  1.3
 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
--+

Comment (by Tim Graham):

 #30122 is a duplicate.

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.c2e8f1a71f240111ae6956707c3a5b74%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

[Django] #30128: Using timezone created with datetime.timedelta causes incorrect timezone in SQL query.

[Django]

#30128: Using timezone created with datetime.timedelta causes incorrect 
timezone in
SQL query.
-+-
   Reporter:  mvarnar|  Owner:  nobody
   Type:  Bug| Status:  new
  Component:  Database   |Version:  2.1
  layer (models, ORM)|   Keywords:  orm, postgresql,
   Severity:  Normal |  timezone, datetime
   Triage Stage: |  Has patch:  0
  Unreviewed |
Needs documentation:  0  |Needs tests:  0
Patch needs improvement:  0  |  Easy pickings:  0
  UI/UX:  0  |
-+-
 I haven’t checked this bug with other databases, but it definitely works
 improperly with postgres.
 Django ORM create incorrect query when I use timezone determined like
 "timezone(timedelta(hours=some_hours))".
 "timezone(timedelta(hours=5))" in query will look like "UTC+05:00", but
 postgres doesn't know this timezone name and handle it as POSIX style.
 "UTC" part will be interpreted as some zone abbreviation and timezone will
 be shifted by 5 hours to the west (positive shift is shift to the west in
 accordance with POSIX standart), i.e. actually timezone will be equal to
 UTC-5.

 From https://www.postgresql.org/docs/10/datatype-datetime.html :
 "In addition to the timezone names and abbreviations, PostgreSQL will
 accept POSIX-style time zone specifications of the form STDoffset or
 STDoffsetDST, where STD is a zone abbreviation, offset is a numeric offset
 in hours west from UTC"

 Checked with:
 django==2.1.5
 psycopg2==2.7.6.1
 postgreSQL==10.6

 Using the following example model:
 {{{
 class test(models.Model):
 class Meta:
 db_table = 'test_timezones'

 datetime = models.DateTimeField()
 }}}

 Sample of bug is bellow:

 {{{
 >>> from datetime import timezone, timedelta
 >>> from django.db.models.functions import ExtractWeekDay
 >>> from django_issues.models import test
 >>> from django.db.models.functions import ExtractHour
 >>> from pytz import timezone as pytz_timezone
 >>>
 print(test.objects.annotate(hour=ExtractHour('datetime')).values('datetime',
 'hour').get())
 {'datetime': datetime.datetime(2018, 1, 1, 7, 0, tzinfo=), 'hour': 7}
 >>> tz = timezone(timedelta(hours=5))
 >>> print(tz)
 UTC+05:00
 >>> print(test.objects.annotate(hour=ExtractHour('datetime',
 tzinfo=tz)).values('datetime', 'hour').get())
 {'datetime': datetime.datetime(2018, 1, 1, 7, 0, tzinfo=), 'hour': 2}
 >>> print(test.objects.annotate(hour=ExtractHour('datetime',
 tzinfo=tz)).values('datetime', 'hour').query)
 SELECT "test_timezones"."datetime", EXTRACT('hour' FROM
 "test_timezones"."datetime" AT TIME ZONE 'UTC+05:00') AS "hour" FROM
 "test_timezones"
 >>> tz2 = pytz_timezone('Asia/Yekaterinburg')
 >>> print(tz2)
 Asia/Yekaterinburg
 >>> print(test.objects.annotate(hour=ExtractHour('datetime',
 tzinfo=tz2)).values('datetime', 'hour').get())
 {'datetime': datetime.datetime(2018, 1, 1, 7, 0, tzinfo=), 'hour':
 12}
 }}}

-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/050.da304c19b5d1651765c2d3ff2bbfac5d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #28223: Form not always picklable due to template renderer

[Django]

#28223: Form not always picklable due to template renderer
--+-
 Reporter:  Claude Paroz  |Owner:  Gaurav Sehgal
 Type:  Bug   |   Status:  assigned
Component:  Forms |  Version:  1.11
 Severity:  Normal|   Resolution:
 Keywords:| Triage Stage:  Accepted
Has patch:  0 |  Needs documentation:  0
  Needs tests:  0 |  Patch needs improvement:  0
Easy pickings:  0 |UI/UX:  0
--+-
Changes (by Martin Honermeyer):

 * cc: Martin Honermeyer (added)


-- 
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/065.3cece135c5f1bff64f0a4f3a978cb3f0%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.