mysql 5.1 large decimalfield lookups return too few results

[Walter Doekes]

It seems that there is a bug in the mysql 5.1 (and probably lower) conversion of strings to decimals. Since Decimal value lookups are always passed to the mysql backend as strings, Django is affected.

See this example:

mysql> select version() as server_version\G
*************************** 1. row ***************************
server_version: 5.1.66-0+squeeze1-log
...
mysql> create table abc (value decimal(31,0));
...
mysql> insert into abc values (1234567890123456789012345678901);
Query OK, 1 row affected (0.00 sec)

mysql> select value from abc where value = 1234567890123456789012345678901\G
*************************** 1. row ***************************
value: 1234567890123456789012345678901
...

mysql> select value from abc where value = '1234567890123456789012345678901'\G
Empty set (0.01 sec)

Obviously, a fix could be to upgrade to a newer MySQL server version -- the MySQL 5.5.28 I tested didn't have this issue -- but that isn't always possible.

I suppose a workaround/fix might not be included in Django because the bug lies in MySQL, but I'll file it here for the record.

Test cases are included:

FAIL: test_decimal_field_broken1 (test_long_decimal.tests.DecimalTests)
...
AssertionError: book with isbn 1234567890123456789012345678901 was not found

I tried to work around the bug by surrounding a decimal value in lookup with CAST(..), but that (sometimes!) triggers an SQL Warning (in other tests!) instead:

ERROR: test_decimal_field_works1 (test_long_decimal.tests.DecimalTests)
...
Warning: Truncated incorrect DECIMAL value: ''

So, my easy fix did not work out as expected.

The proper fix, if any, would probably to force the backend to take the decimal as an *unquoted* value. But I didn't find an easy path to achieve that.

Regards,
Walter Doekes
OSSO B.V.

[Walter Doekes]

Tests.

[Walter Doekes]

Workaround, which raises SQL Warnings instead.

[Walter Doekes]

More mysql oddness, captured in a log.

[Walter Doekes]

Proper fix that seems to work. Includes tests.

[Walter Doekes]

I added django19625-master.fix_using_django_conversions.patch​ which seems to do the trick.

It was written by my colleague Harm Geerts (hgeerts).

[Tim Graham]

As MySQL 5.1 is End of life as of December 31, 2013, I think we should close this.