[IGNITE-15100] Ignite spring data - IgniteRepository Annotated Queries with LIKE and named parameter

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.9.1
• Component/s: springdata
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Mark Egloff
• Created: 11/Jul/21 13:46
• Updated: 11/Jul/21 13:47

Description

When I add a method with annotated @Query to my IgniteRepository interface calss which is using a LIKE operator of a QuerySqlField or QueryTextField I get a SQL synatx error, although the query syntax itself is according JPA standards correct

// Example
import org.springframework.data.repository.query.Param;

@RepositoryConfig(cacheName = Item.cacheName)
public interface ItemIgniteRepository extends IgniteRepository<Item, Long>, CustomItemIngniteRepository {
    @Query("SELECT i.* from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE :value")
    public List<Item> findByTextAttribute(@Param("value") String value)
...

This returns the following error

log.info("search by parameter text value...");
items = itemRepoIgnite.findByTextAttribute("Road Rider");

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT I._KEY, I._VAL FROM ITEM AS I JOIN ITEMATTRIBUTE AS IA ON I.ID = IA.ITEMID WHERE IA.TYPE = 'TEXT' AND IA.TEXTVALUE LIKE :[*]VALUE "; expected "SELECT, FROM, WITH"; SQL statement:
SELECT i._KEY, i._VAL from ITEM as i JOIN ITEMATTRIBUTE AS ia ON i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE :value [42001-197]

When I use the same query without a named parameter "?" it works i.e.

@Query("SELECT i.* from  ITEM as i JOIN ITEMATTRIBUTE AS ia ON  i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE ?")
public List<Item> findByTextAttribute(String value);

or this is also working

@Query("SELECT i.* from  ITEM as i JOIN ITEMATTRIBUTE AS ia ON  i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue LIKE %?1%")
public List<Item> findByTextAttribute(String value);

The named parameter is working with equals "=" operator

@Query("SELECT i.* from  ITEM as i JOIN ITEMATTRIBUTE AS ia ON  i.id = ia.itemid WHERE ia.type = 'TEXT' AND ia.textValue = :value")
public List<Item> findByTextAttribute(@Param("value") String value)