#30911: Django query returns no data; pasting query into MySQL prompt shows
correct
data
-------------------------------------+-------------------------------------
Reporter: ReagentX | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 2.2
layer (models, ORM) |
Severity: Normal | Keywords: mysql
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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 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')
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30911>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/051.0a21c1a5106db913cf094deb918984b6%40djangoproject.com.
