Decimals generated as strings in query

[Gabriel Becedillas]

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 ?

I'm attaching a sample to reproduce the issue.

Thanks a lot.

[Mike Bayer]

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.

[Gabriel Becedillas]

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

[Mike Bayer]

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

[Gabriel Becedillas]

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.

--
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+...@googlegroups.com.

[Samer Atiani]

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?

[Mike Bayer]

On Fri, Jul 28, 2017 at 1:46 PM, Samer Atiani <sat...@gmail.com> 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.

> --
> 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 sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.

> Visit this group at https://groups.google.com/group/sqlalchemy.

[Samer Atiani]

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)

> email to sqlalchemy+unsubscribe@googlegroups.com.

> To post to this group, send email to sqlal...@googlegroups.com.
> 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 sqlalchemy+unsubscribe@googlegroups.com.