django queryset returns wrong values from postgresql view

Question

I have a crazy bug somewhere in this setup.

The database is Postgres 9.1 and is pre-existing (not managed by Django). In it there exists 1 table and then a number of fairly simple views, one of which is called valid_logins_dow_popularity as defined:

 =>\d+ valid_logins_dow_popularity
             View "public.valid_logins_dow_popularity"
   Column   |       Type       | Modifiers | Storage | Description
------------+------------------+-----------+---------+-------------
 logins_avg | double precision |           | plain   |
 dow        | double precision |           | 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)::double precision AS logins_avg, by_dow.dow
   FROM by_dow
  GROUP BY by_dow.dow
  ORDER BY by_dow.dow;

In Django 1.4 I've defined a simple model that uses that view as it's datasource:

class ValidLoginsDowPopularity(models.Model):
    class Meta:
        db_table = 'valid_logins_dow_popularity'
        managed = False

    logins_avg = models.FloatField(
                            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 )

When I grab the data directly from the DB I get one set of numbers:

SELECT "valid_logins_dow_popularity"."logins_avg", "valid_logins_dow_popularity"."dow" 
  FROM "valid_logins_dow_popularity";

    logins_avg    | dow
------------------+-----
 28.8571428571429 |   0
 95.1428571428571 |   1
 91.4285714285714 |   2
           89.625 |   3
 82.6666666666667 |   4
 61.4285714285714 |   5
 28.4285714285714 |   6
(7 rows)

When I get the data through the Django model I get a somewhat vaguely related, but different set of numbers:

In [1]: from core.models import *

In [2]: v = ValidLoginsDowPopularity.objects.all()

In [3]: for i in v:
    print "logins_avg : %f | dow : %d" % (i.logins_avg, i.dow)
   ...:
logins_avg : 25.857143 | dow : 0
logins_avg : 85.571429 | dow : 1
logins_avg : 89.571429 | dow : 2
logins_avg : 86.375000 | dow : 3
logins_avg : 83.000000 | dow : 4
logins_avg : 67.000000 | dow : 5
logins_avg : 28.000000 | dow : 6

To date, I've verified the sql that Django generates, when run directly from psql returns the expected output. I've likewise tried with the Django model using a IntegerField, FloatField and DecimalField for the logins_avg attribute -- all have the same, but incorrect values. I've also written a simple test program to bypass the Django code and make sure it isn't a psycopg2 issue:

import psycopg2

def main():
    conn_string = "dbname='********' user='*********'"

    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor()

    sql = "select * from valid_logins_dow_popularity"
    cursor.execute(sql)

    for rec in cursor.fetchall():
        print rec

if __name__ == '__main__':
    main()

Which, when run give the correct fault, so psycopg2 seems to be doing the right thing:

$ python test_psycopg2.py
(28.8571428571429, 0.0)
(95.1428571428571, 1.0)
(91.4285714285714, 2.0)
(89.625, 3.0)
(82.6666666666667, 4.0)
(61.4285714285714, 5.0)
(28.4285714285714, 6.0)

How is this possible? Any clues would be appreciated. Where could I dig into the Django code and see where things go wrong? Should I report this issue with the Django Project?

Answer 1

Redefine the view and cast the value to a numeric instead of a double. In the Django model you need a DecimalField that matches the Postgres numeric (like numeric(15,10) -> DecimalField(max_digits=15, decimal_places=10)).

I've never had any luck at all with floating point values between Django and the db and have had similar float weirdness problems with other software talking to databases before as well. Doing numeric <-> DecimalField is the only way I've found to guarantee floating point values don't get weird -- by changing them into fixed-point values.