ParameterBindingParser parse incorrect sql [DATAJPA-1471]

[spring-projects-issues]

Linh Nguyen Dinh opened DATAJPA-1471 and commented

I've used @Query annotation in Spring Data JPA to execute JPQL. Here is my JPQL:

SELECT new com.alskaiecxpacdeade.tms.entity.custom.BranchSearchDTO(br.id,br.code,br.nameJp,br.addressJp,br.mainPhone,br.email,br.fax, br.status)  FROM Branch br  WHERE br.deleteFlag = false AND (:#{#searchForm.code} = '' OR br.code LIKE %:#{#searchForm.code}%) AND (:#{#searchForm.name} = '' OR br.nameJp LIKE %:#{#searchForm.name}%) AND (:#{#searchForm.address} = '' OR br.addressJp LIKE %:#{#searchForm.address}%) AND (:#{#searchForm.email} = '' OR br.email LIKE %:#{#searchForm.email}%) AND (:#{#searchForm.fax} = '' OR br.fax LIKE %:#{#searchForm.fax}%) AND (:#{#searchForm.phone} = '' OR br.mainPhone LIKE %:#{#searchForm.phone}%) AND (:#{#searchForm.status} = '' OR br.status = :#{#searchForm.status})

 

But I got an exception when Spring jpa parse my JPQL.

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: % near line 1, column 401 [SELECT new com.alskaiecxpacdeade.tms.entity.custom.BranchSearchDTO(br.id,br.code,br.nameJp,br.addressJp,br.mainPhone,br.email,br.fax, br.status)  FROM com.alskaiecxpacdeade.tms.entity.Branch br  WHERE br.deleteFlag = false AND (:__$synthetic$__1 = '' OR br.code LIKE :__$synthetic$__2) AND (:__$synthetic$__3 = '' OR br.nameJp LIKE :__$synthetic$__4) AND (:__$synthetic$__5 = '' OR br.addressJp LIKE %:__$synthetic$__6%) AND (:__$synthetic$__7 = '' OR br.email LIKE :__$synthetic$__8) AND (:__$synthetic$__9 = '' OR br.fax LIKE :__$synthetic$__10) AND (:__$synthetic$__11 = '' OR br.mainPhone LIKE :__$synthetic$__12) AND (:__$synthetic$__13 = '' OR br.status = :__$synthetic$__14)]

I debug and figure out that the

org.springframework.data.jpa.repository.query.StringQuery.ParameterBindingParser#parseParameterBindingsOfQueryIntoBindingsAndReturnCleanedQuery

has ignored the %:__$synthetic$__6% parameter.

---

Because of 

if (spelExtractor.isQuoted(matcher.start())) {
   continue;
}

The spelExtractor#isQuoted is used new QuotationMap() to check index of Quotation to ignore the parser. But the matcher is PARAMETER_BINDING_PATTERN.matcher()

and  and   is not the same.

in my case, the *SpelExtractor* is

SELECT new com.alskaiecxpacdeade.tms.entity.custom.BranchSearchDTO(br.id,br.code,br.nameJp,br.addressJp,br.mainPhone,br.email,br.fax, br.status)  FROM Branch br  WHERE br.deleteFlag = false AND (:__$synthetic$__1 = '' OR br.code LIKE %:__$synthetic$__2%) AND (:__$synthetic$__3 = '' OR br.nameJp LIKE %:__$synthetic$__4%) AND (:__$synthetic$__5 = '' OR br.addressJp LIKE %:__$synthetic$__6%) AND (:__$synthetic$__7 = '' OR br.email LIKE %:__$synthetic$__8%) AND (:__$synthetic$__9 = '' OR br.fax LIKE %:__$synthetic$__10%) AND (:__$synthetic$__11 = '' OR br.mainPhone LIKE %:__$synthetic$__12%) AND (:__$synthetic$__13 = '' OR br.status = :__$synthetic$__14)

The QuotationMap has a Range 365-366 (range of double quote character at {#searchForm.address} = '')

And the *matcher.start()* at

LIKE %:__$synthetic$__6%

is *366.*

That why the ParameterBindingParser has ignored the 

%:__$synthetic$__6% parameter.

---

Affects: 2.1.3 (Lovelace SR3)

Issue Links:

• DATACMNS-1683 SpelExtractor does not consider quotation offsets from rewritten query ("duplicates")

1 votes, 3 watchers

[spring-projects-issues]

Carlos Pereda commented

Hi,

i've seen the same problem calculating quotation when upgrading StringQuery class to use SpelExtractor from data commons in DATAJPA-1267 - Using SpEL infrastructure from commons. Jens Schauder* 17/05/18 10:51.

It looks like in SpelExtractor constructor final QuotationMap attribute is assigned from original query string and not with new resultQuery calculated, making  isQuoted method failing, so the bug could be in spring data commons.

this is the code i'm referring

 

SpelExtractor(String query) {

   Assert.notNull(query, "Query must not be null");

   Map<String, String> expressions = new HashMap<>();
   Matcher matcher = SPEL_PATTERN.matcher(query);
   StringBuilder resultQuery = new StringBuilder();
   QuotationMap quotedAreas = new QuotationMap(query);

   int expressionCounter = 0;
   int matchedUntil = 0;

   while (matcher.find()) {
       ....
   }

   resultQuery.append(query.substring(matchedUntil));

   this.expressions = Collections.unmodifiableMap(expressions);
   this.query = resultQuery.toString();
   this.quotations = quotedAreas;   ==> maybe something as new QuotationMap(this.query);
}

Hope this can help to fix it soon.

 

Thanks!

 

[spring-projects-issues]

Mark Paluch commented

Thanks for report. That issue is going to be fixed with DATACMNS-1683