Opened 5 years ago

Last modified 5 years ago

#30911 closed Bug

Django query returns no data; pasting query into MySQL prompt shows correct data — at Version 1

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

Description (last modified by Christopher Sardegna)

I have a query that generates the following SQL code (names changed):

SELECT `db_name`.`index`,
    `db_name`.`prov_id`,
    `db_name`.`beg_date`,
    `db_name`.`method`,
    `db_name`.`category`,
    `db_name`.`code`,
    `db_name`.`allowed`,
    `db_name`.`count`,
    `db_name`.`charge`,
FROM   `db_name`
WHERE  ( (      `db_name`.`beg_date` BETWEEN '2018-10-01' AND '2018-12-31'
        OR      `db_name`.`beg_date` BETWEEN '2019-01-01' AND '2019-03-31' )
        AND     `db_name`.`prov_id` IN ( '0123456' )
        AND     `db_name`.`med_cat` = 'N'
        AND     NOT (( `db_name`.`category` IS NULL
                        OR `db_name`.`code` IS NULL ))
        AND     NOT (( `db_name`.`category` = """"
                        OR `db_name`.`code` = """"
                        OR `db_name`.`category` = ""
                        OR `db_name`.`code` = "" ))
        );

When run through the Django ORM, this returns 0 rows.

If I execute the queryset multiple times (i.e., calling len() before loading it to a Pandas DataFrame) I will get one of the following two errors:

error (2027, 'Malformed packet')
<class 'django.db.utils.OperationalError'> on line 513

or

error (2013, 'Lost connection to MySQL server during query')
<class 'django.db.utils.OperationalError'> on line 513

Where line 513 is where the QuerySet gets executed.

If I login to the SQL server and paste in this query (after adding the quotes Django removes when printing to the console), the query returns the data I asked for.

Other behaviors:

  • Changing _any_ query parameters will make them successful (e.x., adjust a date by one day, even if no rows occur on that day)
  • Removing _any_ parameters will make it work as well, even if those conditions are never hit in the data (e.x., removing nulls when there are no rows with null data)
  • Adding a LIMIT, even if it is larger than the data returned, will make it work (e.x. LIMIT 99999999999999 for an 8,527 row query)
  • Only two prov_id items have this issue, not all of them
  • Combining a broken prov_id with a prov_id that has no rows (one not in the db) works
  • Significantly larger queries work

Here is the code used to generate the query:

claims = ClaimLine.objects.filter(Q(beg_date__range=prior_range) |
                                  Q(beg_date__range=current_range),
                                  med_cat='N',
                                  prov_id__in=facility_ids,
                                  ).values(
                                      'prov_id',
                                      'beg_date',
                                      'method',
                                      'category',
                                      'code',
                                      'allowed',
                                      'count',
                                      'charge'
                                  )

# Handle removing nulls and blanks
if remove_null:
    claims = claims.exclude(Q(category__isnull=True) | Q(code__isnull=True))
if remove_blank:
    # Handle case where empty strings were stored as string literal `""`
    claims = claims.exclude(Q(category__exact='') | Q(code__exact='') |
                            Q(category__exact='""') | Q(code__exact='""'))

# Handle excluding categories
claims = claims.exclude(category__in=excluded_categories)

# Handle payment type filter
if payment_method != 'all':
    claims = claims.filter(method=payment_method')

Change History (1)

comment:1 by Christopher Sardegna, 5 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top