Thanks a lot Mike.
Although I did see the "Specify 'fetch' or False for the
synchronize_session parameter" in the error I associated that with a
connection-time parameter and I thought 'no.. that has nothing to do with
my problem'.
My bad.
Thanks a lot.
On Mon, Apr 13, 2015 at 3:31 PM, Mike Bayer <mike...@zzzcomputing.com>
wrote:
>
>
> 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 sqlalchemy+...@googlegroups.com.
>> To post to this group, send email to sqlal...@googlegroups.com.
>> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/lRWebyWl_A4/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
