wrong SQL generated on Oracle when using like

Bug #692478 reported by kws on 2010-12-20
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Querydsl
Fix Released
Medium
Timo Westkämper

Bug Description

I have a Person with multiple addresses. When searching a Person living in a street starting with SomeValue_, the query becomes:
select *
from PERSON person
join ADDRESS address
on address.PERSON = person.ID
where address.STREET like 'SomeValue\_%'

(note the backslash)

Apparently, the backslash character is a default escape character for most databases. Oracle, however does not have this, so the query should read:
select *
from PERSON person
join ADDRESS address
on address.PERSON = person.ID
where address.STREET like 'SomeValue\_%' escape '\'

IMHO the OracleTemplates class should override the STARTS_WITH, ENDS_WITH and STRING_CONTAINS operations like this:
templates.put(Ops.STARTS_WITH, templateFactory.create("{0} like {1%} escape '\\'"));
templates.put(Ops.ENDS_WITH, templateFactory.create("{0} like {%1} escape '\\'"));
templates.put(Ops.STRING_CONTAINS, templateFactory.create("{0} like {%1%} escape '\\'"));

The same applies for the like - ignoreCase templates.

It should also be possible to configure this escape character. Right now, this is hardcoded in the StringEscape class. IMHO this should be provided by the appropriate SqlTemplates implementation.

Thanks.

I fixed the Oracle like templates in SVN trunk. I am still trying to figure out the best way to make the escape character configurable.

Changed in querydsl:
status: New → In Progress
assignee: nobody → Timo Westkämper (timo-westkamper)
importance: Undecided → Medium

The escape char is now configurable in all SQLTemplates subclasses :

e.g.

new OracleTemplates('!', false)

Changed in querydsl:
status: In Progress → Fix Committed

Released in 2.0.7

Changed in querydsl:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers