Code

Opened 15 months ago

Last modified 14 months ago

#19625 new Bug

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 15 months ago.
Tests.
django19625-master.mysql-decimalfield-lookup-fail.workaround.patch (2.9 KB) - added by wdoekes 15 months ago.
Workaround, which raises SQL Warnings instead.
django19625.more-superfun-with-mysql5.1.txt (4.0 KB) - added by wdoekes 15 months ago.
More mysql oddness, captured in a log.
django19625-master.fix_using_django_conversions.patch (7.6 KB) - added by wdoekes 14 months ago.
Proper fix that seems to work. Includes tests.

Download all attachments as: .zip

Change History (7)

Changed 15 months ago by wdoekes

Workaround, which raises SQL Warnings instead.

Changed 15 months ago by wdoekes

More mysql oddness, captured in a log.

comment:1 Changed 15 months ago by akaariai

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

Changed 14 months ago by wdoekes

Proper fix that seems to work. Includes tests.

comment:2 Changed 14 months 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 14 months ago by wdoekes

  • Has patch set
  • Type changed from Uncategorized to Bug

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.