Related object is not resolved

[Stefan de Konink]

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

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)

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)

[Stefan de Konink]

PCAP capture of server connection. It shows that the result from the server between each connection is different for the same query.