Makes sense. I modified my code to look like this for cross DB
compatibility - which I believe is identical to the workaround you mention
in issue #3891 - I'm pasting it here for others who may face the same
problem:
class _cast_on_mysql(ColumnElement):
def __init__(self, bindvalue, *args, **kwargs):
self.bindvalue = bindvalue
self.args = args
self.kwargs = kwargs
@compiles(_cast_on_mysql)
def _compile(element, compiler, **kw):
return compiler.process(element.bindvalue, **kw)
@compiles(_cast_on_mysql, 'mysql')
def _compile_mysql(element, compiler, **kw):
coerced_value = type_coerce(element.bindvalue, String)
return compiler.process(cast(coerced_value, *element.args,
**element.kwargs), **kw)
class CustomNumericType(types.TypeDecorator):
impl = types.DECIMAL
def __init__(self, *args, **kwargs):
kwargs['precision'] = self.precision
kwargs['scale'] = self.scale
kwargs['asdecimal'] = True
super(CustomNumericType, self).__init__(*args, **kwargs)
@property
def precision(self):
return 10 + Money.HIGH_PRECISION_SCALE
@property
def scale(self):
return Money.HIGH_PRECISION_SCALE
def bind_expression(self, value):
"""
The objective of this is to force MySQL to deal with bind
parameters as
decimals rather than as strings. MySQL for some insane reason
falls back
to floating point arithmetic when you add a Decimal to a String.
"""
if value is not None:
return _cast_on_mysql(value, DECIMAL(self.precision,
self.scale))
return super(CustomNumericType, self).bind_expression(value)
On Fri, Jul 28, 2017 at 3:32 PM, Mike Bayer <[email protected]>
wrote:
> On Fri, Jul 28, 2017 at 1:46 PM, Samer Atiani <[email protected]> wrote:
> > The way I'm handling this is by creating a TypeDecorator with the
> following
> > function:
> >
> >
> > def bind_expression(self, value):
> > """
> > The objective of this is to force MySQL to deal with bind
> > parameters as
> > decimals rather than as strings. MySQL for some insane reason
> > falls back
> > to floating point arithmetic when you add a Decimal to a
> String.
> > """
> > if value is not None:
> > value = type_coerce(value, String)
> > return cast(value, DECIMAL(self.precision, self.scale))
> >
> >
> > return super(ClassName, self).bind_expression(value)
> >
> > Does that make sense? Any reason why this isn't the default behavior of
> the
> > Numeric column type?
>
> Well the first reason is as I said a few years ago, the drivers should
> really be doing this. The psycopg2 driver for Postgresql doesn't have
> problems like these because they ensure the correct casts are also
> sent over.
>
> However, it's likely the drivers are never going to do this.
>
> So the next reason is that the bind_expression() construct is not yet
> able to accommodate this use case. Issue
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3981/bind_
> expression-column_expression-need-to
> is targeted for 1.3 to allow dialect-level bind_expression() to be
> feasible. Issue
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4036/mysql-
> decimal-cast-on-bind-parameters
> targets the MySQL use case.
>
>
>
> >
> > On Monday, April 13, 2015 at 2:47:13 PM UTC-4, Gabriel Becedillas wrote:
> >>
> >> 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 <[email protected]>
> >> 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 [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.
> >>>>
> >>>>
> >>> --
> >>> 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.
> >>>
> >>>
> >>> --
> >>> 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
> >>> [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.
> >>
> >>
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> > description.
> > ---
> > 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 https://groups.google.com/group/sqlalchemy.
> >
> > For more options, visit https://groups.google.com/d/optout.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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
> [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
