Opened 18 years ago

Closed 18 years ago

#3723 closed (fixed)

[boulder-oracle] Oracle case insensitive text searches using 'icontains' does not lower case both values before comparison.

Reported by: Ben Khoo <benk@…> Owned by: Adrian Holovaty
Component: Database layer (models, ORM) Version: other branch
Severity: Keywords: Oracle icontains lower
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I believe there is an error in the django backend for the keyword 'icontains' when using the Oracle database.

The keyword for 'icontains' is mapped to "LIKE LOWER(%s) ESCAPE '\\'"

The problem is that for a case insensitive text search, both sides of the comparison must be lower cased before the comparison. The way it is implemented currently, it will lower case only one of the values. For example, the query currently being produced is something like 

SELECT * FROM MYAPPLICATION_PERSON WHERE MYAPPLICATION_PERSON.NAME LIKE LOWER('%Benard%');

instead of

SELECT * FROM MYAPPLICATION_PERSON WHERE LOWER(MYAPPLICATION_PERSON.NAME) LIKE LOWER('%Benard%');

I am using the boulder-oracle-sprint branch.

Change History (2)

comment:1 by Simon G. <dev@…>, 18 years ago

Component: Uncategorized → Database wrapper
Owner: changed from Jacob to Adrian Holovaty
Summary: Oracle case insensitive text searches using 'icontains' does not lower case both values before comparison. → [boulder-oracle] Oracle case insensitive text searches using 'icontains' does not lower case both values before comparison.
Triage Stage: Unreviewed → Accepted

comment:2 by Matt Boersma, 18 years ago

Resolution: → fixed
Status: new → closed

(In [4741]) boulder-oracle-sprint: Fixed #3723 and the get_object_or_404 tests as a
result. Thanks again to Ben Khoo for finding the bug.

Note: See TracTickets for help on using tickets.
Back to Top