On 4/13/15 11:50 AM, Gabriel Becedillas wrote:
Dear all,
I have a table that has 2 numeric columns, and I'm writing a query
that performs some arithmetic on the filter clause between those
columns and a Decimal. The problem that I'm facing is that I don't get
any results at all. After a while I realized that the SQL statement
getting generated is dumping Decimals as strings, and when strings are
involved in a numeric expression they get converted to floats. So, my
query is not returning anything at all due to float representation
limitations.
I tried casting my decimals using sqlalcheme.cast(...,
sqlalchemy.Numeric(precision=16, scale=8)) but that didn't work
because the filter expression failed. Can anyone help me in getting a
cast over a query parameter to work in a filter expression ?
this is driver stuff. If you change the query to see what you're getting:
query = session.query(Balance.available_balance + amount,
Balance.full_balance)
you can see there's some floating point noise in there, not to mention
we're even getting the value back as a floating point:
Col (u'anon_1', u'balance_full_balance')
2015-04-13 13:10:39,798 DEBUG sqlalchemy.engine.base.Engine Row
(3.0000000000000004e-08, Decimal('3E-8'))
I'm getting the same result with MySQL-Python, PyMySQL, and
Mysql-connector. The issue is definitely in the drivers however, the
code below produces no result for all three drivers:
conn = engine.raw_connection()
cursor = conn.cursor()
cursor.execute(
"SELECT balance.available_balance + %s AS anon_1, "
"balance.full_balance AS balance_full_balance FROM balance "
"WHERE balance.available_balance + %s <= balance.full_balance",
(amount, amount))
print cursor.fetchall()
If I embed the number 0000000.1 as is into the query without using a
parameter, then it works, as it does on the command line.
Looking into PyMySQL since that's the driver I'm most familiar with, if
we send a Decimal it is doing this:
SELECT balance.available_balance + 1E-8 AS anon_1, balance.full_balance
AS balance_full_balance FROM balance WHERE balance.available_balance +
1E-8 <= balance.full_balance
What is interesting is that these values as passed in an INSERT *are*
working correctly. Which means really, this is a MySQL bug; I can
prove it at the command line.
First, we illustrate that scientific notation *is* accepted directly by
MySQL:
mysql> insert into balance (full_balance, available_balance) values
(3E-8, 2E-8);
Query OK, 1 row affected (0.00 sec)
values go in just fine (note I increased the scale in the table here,
hence the two trailing 0's):
mysql> select * from balance;
+----+--------------+-------------------+
| id | full_balance | available_balance |
+----+--------------+-------------------+
| 2 | 0.0000000300 | 0.0000000200 |
+----+--------------+-------------------+
1 row in set (0.00 sec)
but in the WHERE clause, *it fails*:
mysql> select * from balance where available_balance + 1E-8 <= full_balance;
Empty set (0.00 sec)
writing out the whole value, *it succeeds*:
mysql> select * from balance where available_balance + 0.00000001 <=
full_balance;
+----+--------------+-------------------+
| id | full_balance | available_balance |
+----+--------------+-------------------+
| 2 | 0.0000000300 | 0.0000000200 |
+----+--------------+-------------------+
1 row in set (0.00 sec)
we can see that *MySQL itself is doing floating point*, so that's really
the bug here:
mysql> select available_balance + 1E-8 from balance;
+----------------------------+
| available_balance + 1E-8 |
+----------------------------+
| 0.000000030000000000000004 |
+----------------------------+
1 row in set (0.00 sec)
We can in fact make it work with a CAST. However! crazytown time. Even
though NUMERIC and DECIMAL are equivalent in MySQL, cast will *not*
accept NUMERIC (note SQLAlchemy only warns on these and only as of 1.0 I
think):
mysql> select available_balance + CAST(1E-8 AS NUMERIC) from balance;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'NUMERIC) from balance' at line 1
But it *will* accept DECIMAL:
mysql> select available_balance + CAST(1E-8 AS DECIMAL) from balance;
+-------------------------------------------+
| available_balance + CAST(1E-8 AS DECIMAL) |
+-------------------------------------------+
| 0.0000000200 |
+-------------------------------------------+
1 row in set (0.00 sec)
So there's our answer:
from sqlalchemy import cast, DECIMAL
amount = decimal.Decimal('0.00000001')
query = session.query(Balance.available_balance + cast(amount,
DECIMAL()), Balance.full_balance)
query = query.filter(
Balance.available_balance + cast(amount, DECIMAL()) <=
Balance.full_balance
)
SELECT balance.available_balance + CAST(%s AS DECIMAL) AS anon_1,
balance.full_balance AS balance_full_balance
FROM balance
WHERE balance.available_balance + CAST(%s AS DECIMAL) <=
balance.full_balance
2015-04-13 13:43:16,630 INFO sqlalchemy.engine.base.Engine
(Decimal('1E-8'), Decimal('1E-8'))
2015-04-13 13:43:16,631 DEBUG sqlalchemy.engine.base.Engine Col
(u'anon_1', u'balance_full_balance')
2015-04-13 13:43:16,631 DEBUG sqlalchemy.engine.base.Engine Row
(Decimal('2E-8'), Decimal('3E-8'))
1 should be > 0
The drivers would really be nice if they expanded out these values from
scientific notation. However, MySQL's bizarrely inconsistent behavior
here is probably why the need for this hasn't been observed. So stick
with cast(value, DECIMAL()) for now when using the value in the WHERE
clause.
I'm attaching a sample to reproduce the issue.
Thanks a lot.
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
