setHint does not work with updateAllStatement

[fabiohecht]

Hi there, I'm trying to use setHint with updateAllStatement but the hint does not get copied to the SQL statement.

I believe a updateAllStatement.setHintString(getQuery().getHintString()); is missing around this part:

eclipselink/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/queries/ExpressionQueryMechanism.java

Line 846 in 87d4b56

updateAllStatement.setPrimaryKeyFieldsForAutoJoin(primaryKeyFields);

Can someone please confirm?

Here is my code in case anyone is interested:

    Query bulkAssignUnassigned = entityManager().createQuery(""
            + "UPDATE Message e"
            + "   SET e.handlerId.value = :handlerId,"
            + "       e.modifiedStamp = :currentTimeStamp,"
            + "       e.version = e.version + 1"
            + " WHERE e.id IN :ids"
            + "   AND e.status IN :acceptableStatus"
            + "   AND e.handlerId IS NULL"
        )
        .setParameter("handlerId", handlerId.value())
        .setParameter("currentTimeStamp", ZonedDateTime.now())
        .setParameter("ids", ids)
        .setParameter("acceptableStatus", acceptableStatus)
        .setHint(HINT, "/*+ INDEX (T_MESSAGE I_PK_DIMSGI_ID) */");
    int updateCount = bulkAssignUnassigned.executeUpdate();

Cheers,

Fabio

[AleksNo]

Hi, TypedQuery.setHint(String, Object) is not meant to pass hints to the SQL server. It is meant to pass hints to Eclipselink itself. If you want to pass hints to the SQL server then put them into the SQL statement.

[fabiohecht]

Hi @AleksNo , thanks for looking into it.

I tried putting the hint into the query and I get an error message:

Exception Description: Syntax error parsing [UPDATE /*+ INDEX (T_MESSAGE I_PK_DIMSGI_ID) */ Message e   SET e.handlerId.value = :handlerId,... 
[9, 9] The SET identifier is missing from the UPDATE clause.
[8, 9] The identification variable '*' cannot be a reserved word.
[42, 42] The equal sign must be specified.
[9, 41]  The expression is invalid, which means it does not follow the JPQL grammar.
[57, 411] The query contains a malformed ending.

I also tried the hint before "UPDATE" to no avail. I believe right now my only chance would be to use a native query, which would be a pity.

When looking at the source code, to try and understand why my other hints do get passed to the SQL Server (Oracle in my case) but the hint for this specific query does not, I can see that the hint is copied for delete, insert, select and update statements, but not for updateAll. Thus, it looks to me that it is missing. But maybe there is a good reason for that, which I don't understand.

SQLDeleteStatement:

eclipselink/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/queries/ExpressionQueryMechanism.java

Line 547 in 87d4b56

deleteStatement.setHintString(getQuery().getHintString());

eclipselink/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/queries/ExpressionQueryMechanism.java

Line 562 in 87d4b56

deleteStatement.setHintString(getQuery().getHintString());

SQLInsertStatement:

eclipselink/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/queries/ExpressionQueryMechanism.java

Line 585 in 87d4b56

insertStatement.setHintString(getQuery().getHintString());

SQLSelectStatement:

eclipselink/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/queries/ExpressionQueryMechanism.java

Line 804 in 87d4b56

selectStatement.setHintString(getQuery().getHintString());

SQLUpdateStatement:

eclipselink/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/queries/ExpressionQueryMechanism.java

Line 874 in 87d4b56

updateStatement.setHintString(getQuery().getHintString());

SQLUpdateAllStatement: missing

[fabiohecht]

@AleksNo can you please have a look, I'm pretty sure the hint does get passed to the queries for every statement, except the SQLUpdateAllStatement, or am I missing something?