Escape characters are mishandled in the LIKE clause on PostgreSQL

[KyleAure]

Current Behavior

When persisting an entity to a PostgreSQL database EclipseLink
with a column that contains a string with the backslash character (\)
the data is correctly inserted into the table.

For example, using the following entity:

@Entity(name = "Orders") // overrides the default name PurchaseOrder
public class PurchaseOrder {

    @GeneratedValue(strategy = GenerationType.UUID)
    @Id
    public UUID id;

    public String purchasedBy;

    public float total;

    @Version
    public int versionNum;

    public static PurchaseOrder of(String purchasedBy, float total) {
        PurchaseOrder inst = new PurchaseOrder();
        inst.purchasedBy = purchasedBy;
        inst.total = total;
        return inst;
    }
}

Executing the following persist methods:

PurchaseOrder order1 = PurchaseOrder.of("Escape\\Characters", 23.93f);
PurchaseOrder order2 = PurchaseOrder.of("Escape\\\\Characters", 27.97f);

tx.begin();
em.persist(order1);
em.persist(order2);
tx.commit();

Results in the following table in PostgreSQL

[12/11/24, 14:56:26:465 CST] 0000004a id=00000000 eclipselink.sql                                              3 [eclipselink.sql] INSERT INTO ORDERS (ID, PURCHASEDBY, TOTAL, VERSIONNUM) VALUES (?, ?, ?, ?)
	bind => [d07ccfe8-07df-4d1f-8702-f0e40503237a, Escape\\Characters, 27.97, 1]
[12/11/24, 14:56:26:550 CST] 0000004a id=00000000 eclipselink.sql                                              3 [eclipselink.sql] INSERT INTO ORDERS (ID, PURCHASEDBY, TOTAL, VERSIONNUM) VALUES (?, ?, ?, ?)
	bind => [8f96987f-1760-4493-9a9e-95b69df6277a, Escape\Characters, 23.93, 1]

id	purchaseBy	total	v
8f96987f-1760-4493-9a9e-95b69df6277a	Escape\Characters	23.93	1
d07ccfe8-07df-4d1f-8702-f0e40503237a	Escape\Characters	27.97	1

Later when attempting to execute the a query with a LIKE clause:

tx.begin();
List<Float> totals = em.createQuery("SELECT total FROM Orders WHERE purchasedBy LIKE ?1 ORDER BY total", Float.class)
                .setParameter(1, "Escape\\\\Characters") //attempt to find `Escape\\Characters` in the database
                .getResultList();
tx.commit();

Eclipselink attempts to execute the following SQL query:

[12/11/24, 14:56:26:741 CST] 0000004a id=00000000 eclipselink.sql                                              3 [eclipselink.sql] SELECT TOTAL FROM ORDERS WHERE PURCHASEDBY LIKE ? ORDER BY TOTAL
	bind => [Escape\\Characters]

Which returns the result:

[23.93000030517578]

Because PostgreSQL will always escape backslash characters in a LIKE clause by default.
As described in their documentation here: https://www.postgresql.org/docs/7.1/functions-matching.html

To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. The default escape character is the backslash but a different one may be selected by using the ESCAPE clause. To match the escape character itself, write two escape characters.

NOTE: The PostgreSQL setting standard_conforming_strings=on affects the default behavior of backslash in other areas, but never within a LIKE clause.

Expected behavior

I would expect EclipseLink to modify the input parameter for any LIKE clause to escape any backslashes.
Which would result in the following SQLQuery:

[12/11/24, 14:56:26:741 CST] 0000004a id=00000000 eclipselink.sql                                              3 [eclipselink.sql] SELECT TOTAL FROM ORDERS WHERE PURCHASEDBY LIKE ? ORDER BY TOTAL
	bind => [Escape\\\\Characters]

[ajaypaul-ibm]

Eclipselink issue created : eclipse-ee4j/eclipselink#2324