#30911: Django query returns no data; pasting query into MySQL prompt shows
correct
data
-------------------------------------+-------------------------------------
Reporter: Christopher | Owner: nobody
Sardegna |
Type: Bug | Status: new
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Christopher Sardegna:
Old description:
> 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')
> }}}
New description:
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:
{{{
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#comment:2>
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/066.df6b24196e9c4827a13a7a8b71b217de%40djangoproject.com.
