#29222: Substr on NULL values returns incorrect results with pattern lookups.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: Siddharth
| Panditrao
Type: Bug | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: Oracle | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Siddharth Panditrao):
Replying to [comment:8 Mariusz Felisiak]:
> Replying to [comment:7 Simon Charette]:
> > I left a note to this effect [https://forum.djangoproject.com/t/fix-
oracle-bug-where-substr-null-with-pattern-lookups-matches-all-rows-
instead-of-none/43757 on the forum] but I'm afraid there isn't much we can
do here given `__startswith` and related lookups
[https://dryorm.xterm.info/ticket-29222 have historically accepted an
empty strings as their right-side-hand to match all rows].
> >
> > Because `''` and `NULL` are the same thing on Oracle we have to
choose; we can't return all rows in the first case and no rows in the
second like on all other backends.
> >
> > Given these lookups are meant to treat with text fields left-hand-
sides in which
[https://docs.djangoproject.com/en/6.0/ref/models/fields/#django.db.models.Field.null
we document that storing `NULL` is an anti-pattern]
> >
> > > Avoid using `null` on string-based fields such as `CharField` and
`TextField`. The Django convention is to use an empty string, not `NULL`,
as the “no data” state for string-based fields.
> >
> > and things have been working this way on Oracle since their inception
I'd be more inclined to wont-fix this ticket and accept it as a
unfortunate side effect of `interprets_empty_strings_as_nulls` than change
things for an arguably worst default.
>
> Yes, exactly, that's why I didn't fix this for so long.
Thanks for explaining that! Makes sense now why fixing this would break
the existing empty-string behavior on Oracle. I agree this should be
marked as wontfix given the trade-offs involved. Should I update the
ticket status myself, or will a maintainer handle that? And would it be
helpful if I added a test or docs clarifying this Oracle-specific
behavior?
This was my first attempt at contributing to Django, so I appreciate you
taking the time to walk through the reasoning.
--
Ticket URL: <https://code.djangoproject.com/ticket/29222#comment:9>
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 visit
https://groups.google.com/d/msgid/django-updates/0107019b3ff6711e-b15e6064-9afa-47c1-8521-b8861b4d7a58-000000%40eu-central-1.amazonses.com.
