negation operator does not preserve original expression type

When using

DECIMAL(asdecimal=False)

with case statement SQLAlchemy not always casts Decimal to float.

Here is a working example how to reproduce the problem with output. I don't know exactly how is this happening:

from __future__ import print_function
from sqlalchemy import Column, Integer
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.scoping import scoped_session
from sqlalchemy.types import DECIMAL
from sqlalchemy import case

conn_string = '...'

engine = create_engine(conn_string,
                       convert_unicode=True,
                       echo=False,
                       connect_args={'application_name': 'myApp'})

session = scoped_session(sessionmaker(bind=engine))
base = declarative_base()
base.query = session.query_property()

class TestDecimal(base):
    __tablename__ = 'test_decimal'

    id = Column(Integer, primary_key=True, autoincrement=True)
    money = Column(DECIMAL(asdecimal=False))
    state = Column(Integer)

    def __init__(self, money, state):
        self.money = money
        self.state = state

base.metadata.bind = session.bind
base.metadata.create_all()


if __name__ == '__main__':
    session.add(TestDecimal(1.0, 1))
    session.add(TestDecimal(2.0, 2))
    session.commit()

    rec = session.query(case([(TestDecimal.state == 1, -TestDecimal.money),
                              (TestDecimal.state == 2, TestDecimal.money)], else_=0)).filter().all()

    print(rec)

    rec = session.query(case([(TestDecimal.state == 1, TestDecimal.money),
                              (TestDecimal.state == 2, -TestDecimal.money)], else_=0)).filter().all()

    print(rec)

which prints:

[(-1.0,), (2.0,)]
[(Decimal('1.0'),), (Decimal('-2.0'),)]

you can see that two case statements almost identical except of minus sign. That is weird.

Comments (9)