Opened 2 years ago

Closed 13 months ago

#19625 closed Bug (wontfix)

mysql 5.1 large decimalfield lookups return too few results

Reported by: wdoekes Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: wdoekes 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 wdoekes 2 years ago.
Tests.
django19625-master.mysql-decimalfield-lookup-fail.workaround.patch (2.9 KB) - added by wdoekes 2 years ago.
Workaround, which raises SQL Warnings instead.
django19625.more-superfun-with-mysql5.1.txt (4.0 KB) - added by wdoekes 2 years ago.
More mysql oddness, captured in a log.
django19625-master.fix_using_django_conversions.patch (7.6 KB) - added by wdoekes 2 years ago.
Proper fix that seems to work. Includes tests.

Download all attachments as: .zip

Change History (8)

Changed 2 years ago by wdoekes

Workaround, which raises SQL Warnings instead.

Changed 2 years ago by wdoekes

More mysql oddness, captured in a log.

comment:1 Changed 2 years ago by akaariai

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

Changed 2 years ago by wdoekes

Proper fix that seems to work. Includes tests.

comment:2 Changed 2 years ago by wdoekes

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 Changed 2 years ago by wdoekes

  • Has patch set
  • Type changed from Uncategorized to Bug

comment:4 Changed 13 months ago by timo

  • Resolution set to wontfix
  • Status changed from new to closed

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