#29222: Substr on NULL values returns incorrect results with pattern lookups.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle | 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 felixxm:
Old description:
> feb683c4c2c5ecfb61e4cb490c3e357450c0c0e8 revealed an unrelated issue on
> Oracle. `SUBSTR(NULL, x, y)` returns `NULL` on Oracle which can be
> concatenate with other strings, hence if we use it with pattern lookups
> then all rows match an query, e.g.
>
> - `__startswith=Substr(sth, x, y)` -> `LIKE SUBSTR(sth, x, y) || '%'` ->
> `LIKE '%'`,
> - `__endswith=Substr(sth, x, y)` -> `LIKE '%' || SUBSTR(sth, x, y)` ->
> `LIKE '%'`,
> - `__contains=Substr(sth, x, y)` -> `LIKE '%' || SUBSTR(sth, x, y) || '%'
> ` -> `LIKE '%%'`,
>
> which is unexpected.
New description:
feb683c4c2c5ecfb61e4cb490c3e357450c0c0e8 revealed an unrelated issue on
Oracle. `SUBSTR(NULL, x, y)` returns `NULL` on Oracle which can be
concatenate with other strings, hence if we use it with pattern lookups
then all rows match an query, e.g.
- `__startswith=Substr(sth, x, y)` -> `LIKE SUBSTR(sth, x, y) || '%'` ->
`LIKE '%'`,
- `__endswith=Substr(sth, x, y)` -> `LIKE '%' || SUBSTR(sth, x, y)` ->
`LIKE '%'`,
- `__contains=Substr(sth, x, y)` -> `LIKE '%' || SUBSTR(sth, x, y) || '%'
` -> `LIKE '%%'`,
which is unexpected.
''"Although Oracle treats zero-length character strings as nulls,
concatenating a zero-length character string with another operand always
results in the other operand, so null can result only from the
concatenation of two null strings. However, this may not continue to be
true in future versions of Oracle Database. To concatenate an expression
that might be null, use the NVL function to explicitly convert the
expression to a zero-length string."'' (see
[https://docs.oracle.com/database/121/SQLRF/operators003.htm#SQLRF51158
documentation]).
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29222#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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.d3f4547a6de2daffcecad8f90f4c88df%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
