Fix Oracle bug where Substr(NULL) with pattern lookups matches all rows instead of none

Django Internals ORM
1

Hi everyone! I’m looking to tackle ticket #29222 (Substr on NULL values returns incorrect results with pattern lookups on Oracle) as my first Django contribution. Would appreciate any guidance to make sure I’m heading in the right direction.

The Issue
When using Substr with pattern lookups like __startswith on Oracle, if Substr returns NULL, the query matches every row instead of none. This is because Oracle treats NULL || ‘%’ as just ‘%’ (universal wildcard), while other databases correctly treat it as NULL (no match).

What I’m Planning
My approach is to wrap expressions with NVL({}, CHR(0)) in the Oracle backend’s _pattern_ops method. This converts NULL to a null byte character, preventing it from disappearing during concatenation.

Current behavior:
“startswith”: “{} || ‘%%’”

Proposed fix:
“startswith”: “NVL({}, CHR(0)) || ‘%%’”

Progress So Far
I’ve written a test case that reproduces the bug - it passes on Oracle (confirming the bug exists) and fails on SQLite/PostgreSQL (showing it’s Oracle-specific).

Questions

  • Does this approach sound reasonable for fixing the issue?
  • Should I wrap all pattern operations (startswith, endswith, contains) or handle them differently?
  • Any other Oracle-specific gotchas I should watch out for?

Thanks in advance for any feedback! Looking forward to contributing.

2

Hello @WalkingDevFlag!

I think there are no perfect solutions here given how Oracle treats zero-length strings and `NULL` as the same thing.

Your approach of turning `NULL` into a null byte character seems like a better default than returning all rows but it causes another inconsistency as it treats empty strings (zero-length strings) the same way which breaks the asymetry with other backends.

In other words, today doing using `__startswith` and friends with an empty string right-hand-side matches all rows so if we also turn empty strings into a null byte character (remember `'’` and `NULL` are the same thing on Oracle) we break something else.

I’ll add that the while the problem is described using `SubStr` we should not over index on it.

2 Likes
3

Hey @charettes !!

That makes sense.

I agree that given Oracle’s treatment of '' and NULL as identical, there’s no way to preserve both:

  • Empty string RHS matching all rows (documented behavior), and
  • Substr(...) → NULL producing no matches (as on other backends)

My proposal optimizes for the second case, but I see how that would introduce a worse inconsistency by breaking the first, especially given that empty strings are the recommended “no data” value for text fields.

I’m happy to treat this as a won’t-fix and consider it an unfortunate Oracle limitation.

If useful, I can:

  • Add a regression test that documents this behavior explicitly for Oracle, or
  • Help improve documentation around pattern lookups + Oracle NULL semantics

Thanks again for taking the time to explain the trade-offs — I learned a lot from digging into this.