[Django] #33590: Related object is not resolved
Django
--------------------------------------------+------------------------
Reporter: Stefan de Konink | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 4.0
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 |
--------------------------------------------+------------------------
I have the following setup; two managed models, and an unmanaged model
which is a facade for a view in PostgreSQL. I can reduce the issue to only
the unmanaged table with ordinary integer fields.
{{{
class NextSchedule(models.Model):
scheduled = models.DateTimeField(blank=False, null=False,
verbose_name=_('at'))
exact_schedule_id = models.IntegerField(null=False)
negative_schedule_id = models.IntegerField(null=True)
class Meta:
managed = False
}}}
My practical issue is that I am unable to get negative_schedule_id to be
shown up. It is worse: when I explicitly query only a column with this
value the queryset is empty.
{{{
blxa=> SELECT "blxadmin_nextschedule"."id",
"blxadmin_nextschedule"."scheduled",
"blxadmin_nextschedule"."exact_schedule_id",
"blxadmin_nextschedule"."negative_schedule_id" FROM
"blxadmin_nextschedule";
id | scheduled | exact_schedule_id | negative_schedule_id
----+---------------------+-------------------+----------------------
7 | 2022-03-21 01:00:00 | 1 | 1
(1 row)
}}}
{{{
>>> NextSchedule.objects.all()
<QuerySet []>
}}}
The crazy thing is, if the to_time of the negative schedule is increased
over one hour, it will give a result. Mind you: we are still talking about
an unrelated IntegerField, where as the query from PostgreSQL returns the
same values.
{{{
>>> NextSchedule.objects.all()
<QuerySet [<NextSchedule: NextSchedule object (7)>]>
}}}
So what about the view? The most simple view I can break it with is below,
absolutely no fancy stuff other than a left join.
{{{
blxa=> create view blxadmin_nextschedule as select row_number() over
(order by scheduled) as id, scheduled, exact_schedule_id, v.id as
negative_schedule_id from (select '2022-03-21'::date + '01:00:00'::time as
scheduled, 1 as exact_schedule_id) as u left join
blxadmin_negativeschedule as v on (u.scheduled between v.from_datetime and
v.to_datetime) order by scheduled asc;
CREATE VIEW
blxa=> SELECT "blxadmin_nextschedule"."id",
"blxadmin_nextschedule"."scheduled",
"blxadmin_nextschedule"."exact_schedule_id",
"blxadmin_nextschedule"."negative_schedule_id" FROM
"blxadmin_nextschedule";
id | scheduled | exact_schedule_id | negative_schedule_id
----+---------------------+-------------------+----------------------
1 | 2022-03-21 01:00:00 | 1 | 1
(1 row)
}}}
Empty result.
{{{
NextSchedule.objects.get(id=1).negative_schedule_id
}}}
The model to reproduce it with simplified;
{{{
class NegativeSchedule(models.Model):
from_datetime = models.DateTimeField(blank=False, null=False)
to_datetime = models.DateTimeField(blank=False, null=False)
}}}
Date range that it does not work with:
21-03-2022 01:00:00 - 21-03-2022 01:59:59
Date range that it shows up with a value:
21-03-2022 01:00:00 - 21-03-2022 02:00:00
--
Ticket URL: <https://code.djangoproject.com/ticket/33590>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Stefan de Konink | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 4.0
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 Stefan de Konink:
Old description:
New description:
{{{
>>> NextSchedule.objects.all()
<QuerySet []>
}}}
Empty result.
{{{
NextSchedule.objects.get(id=1).negative_schedule_id
}}}
When the view is changed to have a coalesce, the coalesce value appears in
the ORM.
{{{
blxa=> create view blxadmin_nextschedule as select row_number() over
(order by scheduled) as id, scheduled, exact_schedule_id, coalesce(v.id,
0) as negative_schedule_id from (select '2022-03-21'::date +
'01:00:00'::time as scheduled, 1 as exact_schedule_id) as u left join
blxadmin_negativeschedule as v on (u.scheduled between v.from_datetime and
v.to_datetime) order by scheduled asc;
CREATE VIEW
blxa=> select * from blxadmin_nextschedule ;
id | scheduled | exact_schedule_id | negative_schedule_id
----+---------------------+-------------------+----------------------
1 | 2022-03-21 01:00:00 | 1 | 1
(1 row)
}}}
{{{
>>> NextSchedule.objects.all()[0].scheduled,
NextSchedule.objects.all()[0].exact_schedule_id,
NextSchedule.objects.get(id=1).negative_schedule_id
(datetime.datetime(2022, 3, 21, 1, 0), 1, 0)
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33590#comment:1>
Django
Old description:
> When the view is changed to have a coalesce, the coalesce value appears
New description:
{{{
>>> NextSchedule.objects.all()
<QuerySet []>
}}}
Empty result.
{{{
NextSchedule.objects.get(id=1).negative_schedule_id
}}}
In addition psycopg2 versus the Django connection;
{{{
>>> c = connection.cursor()
>>> c.execute('SELECT * FROM blxadmin_nextschedule where id = 1647824400')
>>> c.fetchone()
(1647824400.0, datetime.datetime(2022, 3, 21, 1, 0), 1, None)
>>> c = psycopg2.connect('user=blxa dbname=blxa port=5432 host=127.0.0.1')
>>> cur = c.cursor()
>>> cur.execute('SELECT * FROM blxadmin_nextschedule where id =
1647824400')
>>> cur.fetchone()
(1647824400.0, datetime.datetime(2022, 3, 21, 1, 0), 1, 1)
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33590#comment:2>
Django
----------------------------------+--------------------------------------
Reporter: Stefan de Konink | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 4.0
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
----------------------------------+--------------------------------------
* Attachment "queries.pcapng" added.
PCAP capture of server connection. It shows that the result from the
server between each connection is different for the same query.
--
Ticket URL: <https://code.djangoproject.com/ticket/33590>
Django
----------------------------------+--------------------------------------
Reporter: Stefan de Konink | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 4.0
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 Stefan de Konink):
* Attachment "reproduce.sql" added.
Full reproducible case, SQL-only.
Django
----------------------------------+--------------------------------------
Reporter: Stefan de Konink | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 4.0
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 Stefan de Konink):
To reproduce add the reproduce case to you django postgresql database.
{{{
from django.db import connection
djc = connection.cursor()
djc.execute('SELECT * FROM myview where id = 1647824400')
djc.fetchone()
import psycopg2
c = psycopg2.connect('dbname=yourdjango port=5432 host=127.0.0.1')
direct = c.cursor()
direct.execute('SELECT * FROM myview where id = 1647824400')
direct.fetchone()
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33590#comment:3>
Django
----------------------------------+--------------------------------------
Reporter: Stefan de Konink | Owner: nobody
Component: Uncategorized | Version: 4.0
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------------------------
* status: new => closed
* resolution: => needsinfo
Comment:
If you need help debugging, see TicketClosingReasons/UseSupportChannels.
If you find that Django is at fault, please reopen with an explanation.
Perhaps the issue is related to Django's time zone handling.
--
Ticket URL: <https://code.djangoproject.com/ticket/33590#comment:4>
Django
----------------------------------+--------------------------------------
Reporter: Stefan de Konink | Owner: nobody
Component: Uncategorized | Version: 4.0
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+--------------------------------------
* status: closed => new
* resolution: needsinfo =>
Comment:
A fully reproducable case has been added. Support channels have been used
for the past hours. I think this is a valid bug. That does not need
anymore explanation than was already provided.
--
Ticket URL: <https://code.djangoproject.com/ticket/33590#comment:5>
Django
----------------------------------+--------------------------------------
Reporter: Stefan de Konink | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 4.0
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 Matthew Schinckel):
I've been working with Stefan to try to figure out what is going on in
IRC.
It appears that something about the Django SQL infrastructure is changing
the results (or maybe the query) so that one column is getting set to NULL
(by the database) rather than having the value.
I'm not sure that the title of this bug accurately reflects the problem
yet though.
--
Ticket URL: <https://code.djangoproject.com/ticket/33590#comment:6>
Django
----------------------------------+--------------------------------------
Reporter: Stefan de Konink | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 4.0
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
----------------------------------+--------------------------------------
* Attachment "reproduce.sql" added.
Reproducable SQL example.
--
Ticket URL: <https://code.djangoproject.com/ticket/33590>
Django
----------------------------------+--------------------------------------
Reporter: Stefan de Konink | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 4.0
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 Stefan de Konink):
* Attachment "reproduce.sql" removed.
Full reproducible case, SQL-only.