mysql dialect discards cast to float, should probably emit a warning

While debugging an error stemming from the incompatibility of decimal.Decimal with certain numerical operations in python, we discovered that sqlalchemy actually returns decimal when computing the average of an integer column in mysql, even if we wrap the query in a cast to float.

Please see this example:

import sqlalchemy
print "sqlalchemy.__version__", sqlalchemy.__version__

engine = sqlalchemy.create_engine('mysql://yeepa_demo:yeepa_demo@localhost/yeepa_demo?charset=utf8', echo=True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy import Column, Integer, String
class Track(Base):
    __tablename__ = 'track'
    id = Column('idtrack', Integer, primary_key=True)
    score = Column('score', Integer, server_default="0", nullable=False)
    user_id = Column('userid', Integer, nullable=False)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
from sqlalchemy.sql.expression import cast
from sqlalchemy.types import Integer, Float
from sqlalchemy import func, desc, not_
# print session.query(cast(func.avg(func.coalesce(Track.score, 0)), Float).label('average_game_score'))
print session.query(Track.score, Track.user_id).limit(20).all()
print session.query(cast(func.avg(func.coalesce(Track.score, 0)), Float).label('average_game_score')).group_by(Track.user_id).all()

Which creates this output:

% ./sqlalchemy_test.py
sqlalchemy.__version__ 0.9.8
2014-10-28 22:44:36,051 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2014-10-28 22:44:36,051 INFO sqlalchemy.engine.base.Engine ()
2014-10-28 22:44:36,053 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2014-10-28 22:44:36,053 INFO sqlalchemy.engine.base.Engine ()
2014-10-28 22:44:36,053 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2014-10-28 22:44:36,053 INFO sqlalchemy.engine.base.Engine ()
2014-10-28 22:44:36,055 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2014-10-28 22:44:36,056 INFO sqlalchemy.engine.base.Engine ()
2014-10-28 22:44:36,056 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2014-10-28 22:44:36,056 INFO sqlalchemy.engine.base.Engine ()
2014-10-28 22:44:36,057 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
2014-10-28 22:44:36,057 INFO sqlalchemy.engine.base.Engine ()
2014-10-28 22:44:36,059 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-10-28 22:44:36,059 INFO sqlalchemy.engine.base.Engine SELECT track.score AS track_score, track.userid AS track_userid 
FROM track 
 LIMIT %s
2014-10-28 22:44:36,060 INFO sqlalchemy.engine.base.Engine (20,)
[(30L, 11L), (40L, 12L), (50L, 13L), (60L, 14L), (70L, 15L), (60L, 16L), (70L, 17L), (80L, 18L), (90L, 19L), (50L, 20L), (50L, 21L), (40L, 22L), (40L, 23L), (30L, 11L), (40L, 12L), (50L, 13L), (60L, 14L), (70L, 15L), (60L, 16L), (70L, 17L)]
2014-10-28 22:44:36,062 INFO sqlalchemy.engine.base.Engine SELECT avg(coalesce(track.score, %s)) AS average_game_score 
FROM track GROUP BY track.userid
2014-10-28 22:44:36,062 INFO sqlalchemy.engine.base.Engine (0,)
[(Decimal('222.4444'),), (Decimal('215.1481'),), (Decimal('23.6667'),), (Decimal('60.0000'),), (Decimal('70.0000'),), (Decimal('60.0000'),), (Decimal('70.0000'),), (Decimal('80.0000'),), (Decimal('90.0000'),), (Decimal('50.0000'),), (Decimal('50.0000'),), (Decimal('52.5000'),), (Decimal('45.0000'),), (Decimal('14.5000'),), (Decimal('0.0000'),), (Decimal('121.5000'),), (Decimal('42.0000'),), (Decimal('550.0000'),)]

This shows that the cast is actually discarded in sql (which makes some sense as mysql can't cast to float, but only to decimal - which doesn't make sense, but seems to be the case). But more importantly, there seems to be no better way to express what format the returned column should have. In normal columns I can tell SQLAlchemy that we want the type to be Float, even though the underlying type might be NUMERIC, and still sqlalchemy will convert the result to float for us. To my understanding because Float implies Float(asdecimal=false).

As a workaround we added this custom type:

import sqlalchemy.types as types
class MyFloat(types.TypeDecorator):

    impl = types.Float

    def process_bind_param(self, value, dialect):
        return value

    def process_result_value(self, value, dialect):
        return float(value)

    def copy(self):
        return MyFloat()

print session.query(cast(func.avg(func.coalesce(Track.score, 0)), MyFloat).label('average_game_score')).group_by(Track.user_id).all()

which creates the output

2014-10-28 22:44:36,067 INFO sqlalchemy.engine.base.Engine SELECT avg(coalesce(track.score, %s)) AS average_game_score 
FROM track GROUP BY track.userid
2014-10-28 22:44:36,067 INFO sqlalchemy.engine.base.Engine (0,)
[(222.4444,), (215.1481,), (23.6667,), (60.0,), (70.0,), (60.0,), (70.0,), (80.0,), (90.0,), (50.0,), (50.0,), (52.5,), (45.0,), (14.5,), (0.0,), (121.5,), (42.0,), (550.0,)]

Is it the intentional and expected behavior for a cast to float for the result of a column in a query that it is just discarded? If so, how do I annotate a query to tell sqlalchemy that I would please like to get the result in a specific type?

I think there is a good argument that the type Float(asdecimal=False) would behave more consistently if it would would allow to convert a column from a query via the cast operator as well as the column from a model. Or perhaps a different operator than cast should/could be chosen? Right now, the current behavior certainly surprised me.

Would you change sqlalchemys Float implementation to force the conversion to float in def process_result_value(...) or is there some other better way to achieve this?

Comments (17)