#29222: Substr on NULL values returns incorrect results with pattern lookups.
-------------------------------------+-------------------------------------
Reporter: Mariusz Felisiak | Owner: Siddharth
| Panditrao
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Siddharth Panditrao):
* owner: (none) => Siddharth Panditrao
* status: new => assigned
Comment:
Hi! I'd like to work on this issue. This would be my first contribution to
Django, so please let me know if I'm on the right track.
'''What I've Found'''
When using Substr with pattern lookups like `__startswith` on Oracle, if
the Substr returns NULL, it ends up matching every row instead of none.
The problem is Oracle-specific: `NULL || '%'` becomes just `'%'` in Oracle
(universal match), but stays NULL in other databases (no match).
'''My Plan'''
I'm thinking of wrapping the expression with `NVL({}, CHR(0))` in the
Oracle backend's `_pattern_ops`. This would convert NULL to a null byte
character instead of letting it vanish during concatenation.
Something like:
{{{
Current: "startswith": "{} || '%%'"
Fixed: "startswith": "NVL({}, CHR(0)) || '%%'"
}}}
'''Testing'''
I've put together a test case on a branch that reproduces the bug - it
passes on Oracle (showing the bug) and fails on other databases
(confirming it's Oracle-only).
Does this approach make sense? Happy to submit a PR if this looks good,
just want to make sure I'm heading in the right direction before diving
deeper.
--
Ticket URL: <https://code.djangoproject.com/ticket/29222#comment:6>
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/0107019b3caff6a1-95042d66-a0ba-4de6-9398-ee80fbdbed03-000000%40eu-central-1.amazonses.com.
