id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
33590	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	nobody	"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)
}}}"	Bug	closed	Uncategorized	4.0	Normal	invalid			Unreviewed	0	0	0	0	0	0