Opened 5 years ago

Last modified 11 months ago

#29222 new Bug

Substr on NULL values returns incorrect results with pattern lookups.

Reported by: Mariusz Felisiak Owned by:
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: Oracle
Cc: Mariusz Felisiak Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Mariusz Felisiak)

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/en/database/oracle/oracle-database/21/sqlrf/Concatenation-Operator.html#GUID-08C10738-706B-4290-B7CD-C279EBC90F7E).

Change History (5)

comment:1 Changed 5 years ago by Mariusz Felisiak

Cc: Mariusz Felisiak added

comment:2 Changed 5 years ago by Mariusz Felisiak

Description: modified (diff)

comment:3 Changed 5 years ago by Tim Graham

Triage Stage: UnreviewedAccepted

comment:4 Changed 5 years ago by Mariusz Felisiak

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

comment:5 Changed 11 months ago by Mariusz Felisiak

Description: modified (diff)
Owner: Mariusz Felisiak deleted
Status: assignednew
Note: See TracTickets for help on using tickets.
Back to Top