Opened 11 years ago

Closed 10 years ago

#19625 closed Bug (wontfix)

mysql 5.1 large decimalfield lookups return too few results

Reported by: Walter Doekes Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Walter Doekes Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Attachments (4)

django19625-master.mysql-decimalfield-lookup-fail.tests.patch (4.8 KB ) - added by Walter Doekes 11 years ago.
Tests.
django19625-master.mysql-decimalfield-lookup-fail.workaround.patch (2.9 KB ) - added by Walter Doekes 11 years ago.
Workaround, which raises SQL Warnings instead.
django19625.more-superfun-with-mysql5.1.txt (4.0 KB ) - added by Walter Doekes 11 years ago.
More mysql oddness, captured in a log.
django19625-master.fix_using_django_conversions.patch (7.6 KB ) - added by Walter Doekes 11 years ago.
Proper fix that seems to work. Includes tests.

Download all attachments as: .zip

Change History (8)

by Walter Doekes, 11 years ago

Workaround, which raises SQL Warnings instead.

by Walter Doekes, 11 years ago

More mysql oddness, captured in a log.

comment:1 by Anssi Kääriäinen, 11 years ago

Triage Stage: UnreviewedAccepted

by Walter Doekes, 11 years ago

Proper fix that seems to work. Includes tests.

comment:2 by Walter Doekes, 11 years ago

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

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

comment:3 by Walter Doekes, 11 years ago

Has patch: set
Type: UncategorizedBug

comment:4 by Tim Graham, 10 years ago

Resolution: wontfix
Status: newclosed

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

Note: See TracTickets for help on using tickets.
Back to Top