Django query returns no data; pasting query into MySQL prompt shows correct data.

[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. [:99999999999999] generating 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:

rows = Model.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:
    rows = rows.exclude(Q(category__isnull=True) | Q(code__isnull=True))
if remove_blank:
    # Handle case where empty strings were stored as string literal `""`
    rows = rows.exclude(Q(category__exact='') | Q(code__exact='') |
                            Q(category__exact='""') | Q(code__exact='""'))

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

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

[Mariusz Felisiak]

Thanks for detailed report, however I don't believe that is an issue in Django. The list of "Other behaviors" shows that it's probably some issue in a MySQL driver, database engine, or in database schema, e.g. it doesn't use an index in some cases. We can reconsider this as a bug in Django if you will be able to provide a small sample project that reproduces this issue.

[Natalya Kosenko]

Replying to Christopher Sardegna:

Hi Christopher, have you found any solution yet? I am struggling with the same problem for a couple of weeks, tried multiple things but nothing helped so far.