id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
29222	Substr on NULL values returns incorrect results with pattern lookups.	Mariusz Felisiak	Siddharth Panditrao	"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])."	Bug	closed	Database layer (models, ORM)	dev	Normal	wontfix	Oracle	Mariusz Felisiak	Unreviewed	0	0	0	0	0	0