decimal processor has to use "%.<precision>f" % (value) when converting floats

[Mike Bayer]

test:

from sqlalchemy import *
import decimal

engine = create_engine('sqlite://', echo=True)

t = Table('t', MetaData(), Column('x', Numeric(precision=15, scale=11)))
t.create(engine)

numbers = set(
[   decimal.Decimal("54.23424645165"),
    decimal.Decimal("876734.59406965401"),
    decimal.Decimal("0.004354"), 
    decimal.Decimal("900.0"),

](
))

engine.execute(t.insert(), [for x in numbers]({'x':x}))

ret = set([row[0](row[0) for row in engine.execute(t.select()).fetchall()])

print numbers
print ret
assert numbers == ret

[Mike Bayer]

will commit the attached patch soon. The C processor is disabled for now.

[Mike Bayer]

2d95ef1f252b2752a3840e84a01d989af9921033

[Former user]

(original author: ged)

[Former user]

(original author: ged) I'll handle the C part.

[Former user]

(original author: ged) C part done in 08f2c2c19a2f66eaf3c243bd1db7ace82f0e1286. Note str() does not default to 10 decimal precision, but rather has a varying precision, which depends on the version of python you are running on and on the integer part, so I guess we need to add a note in the migration guide since this was a slightly incompatible change...

[Mike Bayer]

OK, i'm not very concerned about backwards compat since it was somewhat random behavior before. I think this can be closed.

[Former user]

This test case fails with SQLAlchemy 0.6beta2, 0.6beta3, and the current SQLAlchemy trunk. It passes with 0.6beta1.

Also, what I see in patch.txt concerns me. SQLAlchemy should not have any code that converts between Decimal and float, should it? It should convert Decimal to strings using nothing but str(x) or "'%s' % x"; it should convert them back using "Decimal(x)".

Shane Hathaway

[Mike Bayer]

Replying to guest:

Also, what I see in patch.txt concerns me. SQLAlchemy should not have any code that converts between Decimal and float, should it? It should convert Decimal to strings using nothing but str(x) or "'%s' % x"; it should convert them back using "Decimal(x)".

we have a flag called "usedecimal=False" on Numeric. If that flag is set, and the DBAPI returns Decimal natively, then yes we must convert.

[Mike Bayer]

Replying to guest:

This test case fails with SQLAlchemy 0.6beta2, 0.6beta3, and the current SQLAlchemy trunk. It passes with 0.6beta1.

Also, what I see in patch.txt concerns me. SQLAlchemy should not have any code that converts between Decimal and float, should it? It should convert Decimal to strings using nothing but str(x) or "'%s' % x"; it should convert them back using "Decimal(x)".

also str(x)/"%s" is not reliable for floats:

>>> f = 4.545238694568399
>>> str(f)
'4.54523869457'
>>> "%s" % f
'4.54523869457'
>>> "%.25f" % f
'4.5452386945683986851918235'

Shane Hathaway

[Mike Bayer]

I've adjusted the test to specify precision/scale for the datatypes as well as to use test values with as many decimal places as we expect to get back (the test can fail if we get back a float that has more decimal places than what we put in, which again is known FP behavior)

[Mike Bayer]

finally I'll point you to our current test suite for numerics - this was many days effort several weeks ago getting as many decimal places accurate as possible across at least 17 backend/DBAPI combinations (sqlite+pysqlite, sqlite+pysqlite+py3k, mysql+mysqldb, mysql+oursql, mysql+myconnpy, postgresql+psycopg2, postgresql+pg8000, postgresql+pg8000+py3k, firebird+kintersbasdb, mssql+pymssql, mssql+pyodbc, mssql+mxodbc, mssql+pyodbc+freetds, mssql+pyodbc+freetds, oracle+cx_oracle, sybase+pysybase, sybase+pyodbc+freetds) . Patches which extend the number of decimal places and/or covered databases are welcome:

http://www.sqlalchemy.org/trac/browser/test/sql/test_types.py#L1052

[Mike Bayer]

one more in case this wasn't apparent:

Type "help", "copyright", "credits" or "license" for more information.
>>> import decimal
>>> f = 245.245436345737362
>>> decimal.Decimal(f)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/decimal.py", line 649, in __new__
    "First convert the float to a string")
TypeError: Cannot convert float to Decimal.  First convert the float to a string
>>>

[Former user]

In any case, I attempted to upgrade a project I'm working on to 0.6beta3 today and my test suite failed. It failed because the SQLAlchemy backend is currently adding decimal places to Decimal objects. I created a minimal test case to demonstrate the bug, but then I found this bug report, which has essentially the same test case. This test case currently fails, so I don't understand why you're closing this bug.

I'm working with monetary values, so it is not acceptable for the number of decimal places to change. Note that the psycopg backend does not seem to have this issue.

Thanks for your attention and quick response.

[Former user]

I think I see now how you're interpreting the Numeric datatype. Unfortunately, your interpretation makes it difficult for me to use the features of the database. PostgreSQL treats Numeric columns as arbitrary-precision numbers, not as floating-point numbers, and users are not required to specify the precision for the whole column.

In my schema, I am storing a currency value in one column and a currency code in another column, so I can't set a single precision for the entire column. PostgreSQL fully supports this usage:

http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

The PostgreSQL SQLAlchemy backend does not appear to have the bug originally described by this bug report, but the SQLite backend does.

Perhaps I need to open a new bug report, because the issue remains.

[Mike Bayer]

I don't see a test case, can you please attach it ? the test that is actually on this ticket passes. We get back one additional decimal place than the number that gets sent. There's no way around this. The old behavior was essentially arbitrary and did not work for users with high precision requirements.

Note that if you don't pass any scale, we essentially use str(f). There is no option with Pysqlite as it does not support direct Decimal usage (it has a converter feature, but it doesn't work any better than ours).

If you are using Postgresql/psycopg2 with Numeric, there is no float/decimal conversion performed by SQLAlchemy whatsoever. You're getting the data direct from psycopg2.

[Former user]

Thanks. I've opened a new bug:

http://www.sqlalchemy.org/trac/ticket/1759

[Mike Bayer]

Also I can't reproduce your "the test passes on 0.6beta1" behavior. If I run the original test on 0.6beta1 on mac osx:

set([Decimal('54.23424645165'), Decimal('900.0'), Decimal('0.004354')](Decimal('876734.594069654'),))
set([Decimal('900'), Decimal('876734.59407'), Decimal('0.004354')](Decimal('54.2342464517'),))
Traceback (most recent call last):
  File "test.py", line 23, in <module>
    assert numbers == ret
AssertionError

You'll see my numbers get truncated. This is because the behavior of str(f) is also platform dependent. Before the fixes here, the "precision" and "scale" arguments are completely ignored when converting.

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)