[TORQUE-321] Wrong optimization of iLike without wildcards

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.0
• Fix Version/s: 5.0
• Component/s: Runtime
• Environment: Using java 8 and Postgresql 9.3.4.
• Assignee: Thomas Fox
• Reporter: Martin Tilsted
• Created: 25/Jul/14 14:20
• Updated: 03/Sep/14 19:53
• Resolved: 03/Sep/14 19:53

Description

Torque transform a criteria with SqlEnum.ILIKE to a simple compare using(=) if the string it searches for don't contain any wildcards.

But this optimization is not valid, because ILIKE also does a case insensitive compare. This bug causes ILIKE to behave like LIKE when not using any wildcards.

Example code:
Criteria c=new Criteria();
c.and(VoucherinstancePeer.CODE,"myCode",SqlEnum.ILIKE);
log.debug("Got criteria=" + c);
c=new Criteria();
c.and(VoucherinstancePeer.CODE,"myCode%",SqlEnum.ILIKE);
log.debug("Got criteria=" + c);

Gives this output:

DEBUG - Got criteria=Criteria: Current Query SQL (may not be complete or applicable): SELECT FROM voucherInstance WHERE voucherInstance.code=? Replacements: [myCode]

DEBUG - Got criteria=Criteria: Current Query SQL (may not be complete or applicable): SELECT FROM voucherInstance WHERE voucherInstance.code ILIKE ? Replacements: [myCode%]

where the invalid transformation can be seen.