“Floor” (and likely other reserved identifiers) cannot be used as entity name or entity field name in custom queries

[devopsix]

Affected version: Spring Data 2023.0.0

Given 2 entities Floor and Room

@Entity
public class Floor {
    @Id
    private Long id;
    @Column
    private String name;
    // Getters and setters ommitted for brevity
}

@Entity
public class Room {
    @Id
    private Long id;
    @Column
    private String name;
    @ManyToOne(fetch = LAZY)
    private Floor floor;
    // Getters and setters ommitted for brevity
}

And given 2 JPA repositories with some custom queries

public interface FloorRepository extends CrudRepository<Floor, Long> {
    @Query("""
        SELECT f
        FROM Floor f
        WHERE f.name = :name
        """)
    Collection<Floor> findAllByName(@Param("name") String name);
}

public interface RoomRepository extends CrudRepository<Room, Long> {
    @Query("""
        SELECT r
        FROM Room r
        JOIN r.floor f
        WHERE f.name = :name
        """)
    Collection<Room> findAllByFloorName(@Param("name") String name);
}

When using Spring Data 2023.0.0 (Spring Boot 3.1.0) invoking either custom query repository method fails.

Invoking FloorRepository.findAllByName(String) fails with:

org.springframework.dao.InvalidDataAccessApiUsageException: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 2:5 mismatched input 'Floor' expecting {<EOF>, ',', EXCEPT, FROM, GROUP, INTERSECT, ORDER, UNION, WHERE}; Bad JPQL grammar [SELECT f
FROM Floor f
WHERE f.name = :name
]

Invoking RoomRepository.findAllByFloorName(String) fails with:

org.springframework.dao.InvalidDataAccessApiUsageException: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 3:6 mismatched input '.' expecting {<EOF>, ',', '[', ALL, AND, ANY, AS, ASC, AVG, BETWEEN, BOTH, BREADTH, BY, CASE, CAST, COLLATE, COUNT, CROSS, CUBE, CURRENT, CURRENT_DATE, CURRENT_INSTANT, CURRENT_TIME, CURRENT_TIMESTAMP, CYCLE, DATE, DATETIME, DAY, DEFAULT, DELETE, DEPTH, DESC, DISTINCT, ELEMENT, ELEMENTS, ELSE, EMPTY, END, ENTRY, EPOCH, ERROR, ESCAPE, EVERY, EXCEPT, EXCLUDE, EXISTS, EXTRACT, FETCH, FILTER, FIRST, FOLLOWING, FOR, FORMAT, FROM, FULL, FUNCTION, GROUP, GROUPS, HAVING, HOUR, ID, IGNORE, ILIKE, IN, INDEX, INDICES, INNER, INSERT, INSTANT, INTERSECT, INTO, IS, JOIN, KEY, LAST, LEADING, LEFT, LIKE, LIMIT, LIST, LISTAGG, LOCAL, LOCAL_DATE, LOCAL_DATETIME, LOCAL_TIME, MAP, MATERIALIZED, MAX, MAXELEMENT, MAXINDEX, MEMBER, MICROSECOND, MILLISECOND, MIN, MINELEMENT, MININDEX, MINUTE, MONTH, NANOSECOND, NATURALID, NEW, NEXT, NO, NOT, NULLS, OBJECT, OF, OFFSET, OFFSET_DATETIME, ON, ONLY, OR, ORDER, OTHERS, OUTER, OVER, OVERFLOW, OVERLAY, PAD, PARTITION, PERCENT, PLACING, POSITION, PRECEDING, QUARTER, RANGE, RESPECT, RIGHT, ROLLUP, ROW, ROWS, SEARCH, SECOND, SELECT, SET, SIZE, SOME, SUBSTRING, SUM, THEN, TIES, TIME, TIMESTAMP, TIMEZONE_HOUR, TIMEZONE_MINUTE, TO, TRAILING, TREAT, TRIM, TRUNC, TRUNCATE, TYPE, UNBOUNDED, UNION, UPDATE, USING, VALUE, VALUES, VERSION, VERSIONED, WEEK, WHEN, WHERE, WITH, WITHIN, WITHOUT, YEAR, IDENTIFICATION_VARIABLE}; Bad JPQL grammar [SELECT r
FROM Room r
JOIN r.floor f
WHERE f.name = :name
]

With Spring Data 2022.0.6 (Spring Boot 3.0.7) neither of these failures occurs.

According to the JPA Query Language specification, “floor” is a reserved identifier. As far as I understand the specification that prohibits FROM Floor floor and JOIN r.floor floor but it does not prohibit FROM Floor f and JOIN r.floor f. The specification even contains an example where a reserved identifier (“order”) is used for an entity name:

SELECT DISTINCT o
FROM Order o JOIN o.lineItems l JOIN l.product p
WHERE p.productType = 'office_supplies'

After renaming Floor to FloorEntity and Room.floor to Room.floorEntity in above example, both custom queries do execute successfully.

This is possibly related to #2814 (commit 0d8c06d).

jpql-floor.zip contains a minimal reproducible example.

[gregturn]

Yes, it's true. Reserved words can be used as entity names. FLOOR simply slipped through the cracks. We'll get this patched right away.

[gregturn]

Merged to main and backported to 3.1.x.