#18833: Model returns incorrect values from Postgres DB View
----------------------------------------------+--------------------
Reporter: paul.ortman@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.4
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
This is essentially the same report as
[http://stackoverflow.com/questions/11781867/django-queryset-returns-
wrong-values-from-postgresql-view]
I have a database view that returns aggregate data from table.
{{{
=> \d+ valid_logins_dow_popularity
View "public.valid_logins_dow_popularity"
Column | Type | Modifiers | Storage | Description
------------+---------+-----------+---------+-------------
logins_avg | integer | | plain |
dow | integer | | plain |
View definition:
WITH by_dow AS (
SELECT valid_logins_over_time.count, date_part('dow'::text,
valid_logins_over_time.date) AS dow
FROM valid_logins_over_time
)
SELECT avg(by_dow.count)::integer AS logins_avg, by_dow.dow::integer AS
dow
FROM by_dow
GROUP BY by_dow.dow;
}}}
I have a Django model that uses that view as its data source
{{{
from django.db import models
# Create your models here.
class ValidLoginsDowPopularity(models.Model):
class Meta:
db_table = 'valid_logins_dow_popularity'
managed = False
logins_avg = models.IntegerField(db_column='logins_avg')
# Day of Week (dow)
dow = models.IntegerField(db_column='dow',
primary_key=True)
def __unicode__(self):
return u"%d : " % (self.dow, self.logins_avg )
}}}
I have a test program that simply prints the values returned via the model
{{{
$ cat test_model.py
from core.models import *
v = ValidLoginsDowPopularity.objects.all().order_by('dow')
for i in v:
print "logins_avg : %d | dow : %d" % (i.logins_avg, i.dow)
}}}
and the values printed
{{{
$ python test_model.py
logins_avg : 45 | dow : 0
logins_avg : 137 | dow : 1
logins_avg : 141 | dow : 2
logins_avg : 140 | dow : 3
logins_avg : 142 | dow : 4
logins_avg : 114 | dow : 5
logins_avg : 45 | dow : 6
}}}
Unfortunately, these values are not the same as returned directly from the
database. I cannot tell if there is a pattern to how they are different,
but they vary nevertheless. Here is a direct query result.
{{{
=> select * from valid_logins_dow_popularity order by dow;
logins_avg | dow
------------+-----
51 | 0
153 | 1
142 | 2
145 | 3
142 | 4
102 | 5
45 | 6
(7 rows)
}}}
Finally, here is a test in pure python using the psycopg2 driver (same as
in use in Django) that gets the correct data and prints it as expected.
{{{
$ cat test_psycopg2.py
import psycopg2
def main():
conn_string = "dbname='auser' user='auser'"
conn = psycopg2.connect(conn_string)
cursor = conn.cursor()
sql = "select * from valid_logins_dow_popularity order by dow"
cursor.execute(sql)
for rec in cursor.fetchall():
print rec
if __name__ == '__main__':
main()
}}}
and the correct data
{{{
$ python test_psycopg2.py
(51, 0)
(153, 1)
(142, 2)
(145, 3)
(142, 4)
(102, 5)
(45, 6)
}}}
finally, here is the critical version info
{{{
Django==1.4
psycopg2==2.4.5
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/18833>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
