On 4/13/15 2:25 PM, Gabriel Becedillas wrote:
Dear Michael,
Thanks a lot for your reply.
In trying to narrow the problem as much as possible, I missed
something important in my example. I'm actually doing an UPDATE, not a
SELECT. When I wrote 'I tried casting my decimals using
sqlalcheme.cast(..., sqlalchemy.Numeric(precision=16, scale=8)) but
that didn't work because the filter expression failed' I meant that it
didn't work in an update scenario. In a select scenario it works ok.
This is what I should have wrote in my bug_test.py:
amount = decimal.Decimal('0.00000001')
query = session.query(Balance)
query = query.filter(
Balance.available_balance + sqlalchemy.cast(amount,
sqlalchemy.Numeric(precision=16, scale=8)) <= Balance.full_balance
)
values = {}
values[Balance.available_balance] = Balance.available_balance + amount
row_count = query.update(values)
print row_count, "rows updated"
and the error I get is 'sqlalchemy.exc.InvalidRequestError: Could not
evaluate current criteria in Python. Specify 'fetch' or False for the
synchronize_session parameter.'. This is not even getting to MySQL.
for query.update() you usually want to send synchronize_session=False.
also the cast() needs to be Decimal(), not Numeric(). Will not work
with Numeric().
Thanks a lot
On Monday, April 13, 2015 at 2:46:14 PM UTC-3, Michael Bayer wrote:
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] <javascript:>.
To post to this group, send email to [email protected]
<javascript:>.
Visit this group at http://groups.google.com/group/sqlalchemy
<http://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<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]
<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.
