Opened 10 years ago

Closed 10 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: UI/UX:

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 Changed 10 years ago by Simon G. <dev@…>

Component: UncategorizedDatabase wrapper
Needs documentation: unset
Needs tests: unset
Owner: changed from Jacob to Adrian Holovaty
Patch needs improvement: unset
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: UnreviewedAccepted

comment:2 Changed 10 years ago by Matt Boersma

Resolution: fixed
Status: newclosed

(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