0030477: Generate a query with UPPER when using the ilike operator in Hibernate

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0030477

Openbravo ERP

A. Platform

public

2015-07-31 14:57

2022-02-01 08:09

Reporter

AugustoMauch

Assigned To

Triage Platform Base

Priority

normal

Severity

minor

Reproducibility

have not tried

Status

new

Resolution

open

Platform

OS Version

Product Version

Target Version

Fixed in Version

Merge Request Status

Review Assigned To

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0030477: Generate a query with UPPER when using the ilike operator in Hibernate

Description

When the ilike operator is used in Hibernate, it is translated to SQL like this:

Postgres: this_.columName ilike ?

Oracle: lower(this_.columName) like ?

There are two problems with this:
- It is not possible to use indexes when the ilike operator is used in postgres [1]
- We are consolidating the use of lower instead of upper (see [2])

[1] http://postgresql.nabble.com/How-to-force-Postgres-to-use-index-on-ILIKE-td2057162.html#a2057163 [^]
[2] https://issues.openbravo.com/view.php?id=30476 [^]

Steps To Reproduce

See the definition of IlikeExpression.toSqlString [1]

[1] http://www.grepcode.com/file/repo1.maven.org/maven2/hibernate/hibernate/3.1beta3/org/hibernate/criterion/IlikeExpression.java?av=h#29 [^]

Proposed Solution

Additional Information