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.
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.
> 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.
