Column from view which has a left outer join invalidly becomes NULL (including coalesce) over a django cursor, but not via a direct psycopg2 cursor

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

[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()

[Tim Graham]

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.

[Stefan de Konink]

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.

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

[Stefan de Konink]

Reproducable SQL example.

[Stefan de Konink]

Was able to figure out the difference in the connection setup between psycopg2 native and Django. The latter applied set time zone 'utc'; when setting up the connection. That rules out that Django itself is the problem.

blxa=> select * from myview ;
     id     |      scheduled      | exact_schedule_id | negative_schedule_id 
------------+---------------------+-------------------+----------------------
 1647824400 | 2022-03-21 01:00:00 |                 1 |                    1
 1647824400 | 2022-03-21 01:00:00 |                 1 |                    1
 1647824400 | 2022-03-21 01:00:00 |                 1 |                    1
(3 rows)

blxa=> set time zone  'utc';
SET
blxa=> select * from myview ;
     id     |      scheduled      | exact_schedule_id | negative_schedule_id 
------------+---------------------+-------------------+----------------------
 1647824400 | 2022-03-21 01:00:00 |                 1 |                    0
(1 row)

[Stefan de Konink]

My "solution" has been to select ((expanded::date + time::time) at time zone 'Europe/Amsterdam')::timestamptz as scheduled manually set the timezone.