LikeParameterBinding does not function properly when same parameter used multiple times [DATAJPA-873]

[spring-projects-issues]

Eric Sirianni opened DATAJPA-873 and commented

In the following example (native query):

SELECT name, position(:myParam IN name) as pos
FROM foo
WHERE name LIKE %:myParam%

The LikeParameterBinding does not properly translate the second usage of the :myParam parameter. If I remove the first usage of the :myParam parameter, the query behaves as intended

---

1 votes, 2 watchers

[spring-projects-issues]

Eric Sirianni commented

Strangely, I am not hitting this assertion:

		private static final String MESSAGE = "Already found parameter binding with same index / parameter name but differing binding type! "
				+ "Already have: %s, found %s! If you bind a parameter multiple times make sure they use the same binding.";

		private static void checkAndRegister(ParameterBinding binding, List<ParameterBinding> bindings) {

			for (ParameterBinding existing : bindings) {
				if (existing.hasName(binding.getName()) || existing.hasPosition(binding.getPosition())) {
					Assert.isTrue(existing.equals(binding), String.format(MESSAGE, existing, binding));
				}
			}

			if (!bindings.contains(binding)) {
				bindings.add(binding);
			}
		}

[l7777777b]

Exactly the same issue as mine. Currently to overcome this i use a different named parameter for each usage.

[gregturn]

This was resolved by #2939.

Related: #2760.

[falcononrails]

Hello @gregturn,

I am upgrading from Spring Boot 3.0.9 to 3.1.3 and I'm having the same issue on this query (which was working perfectly before):

@Query(value = """
         /* select and joins */
         where
                and (:encryptedEmail = '' or :encryptedEmail is null or c.encrypted_email = :encryptedEmail) \
                and (:name = '' or :name is null or cp.name like %:name%) \
                and (org.id in :ids or coalesce(:ids) is null)
        """, nativeQuery = true)
    List<View> findAll(String encryptedEmail, String name, List<Integer> ids);

Caused by: java.lang.IllegalArgumentException: Already found parameter binding with same index / parameter name but differing binding type; Already have: ParameterBinding [identifier: ids, origin: MethodInvocationArgument[identifier=ids]], found ParameterBinding [identifier: ids, origin: MethodInvocationArgument[identifier=ids]]; If you bind a parameter multiple times make sure they use the same binding
	at org.springframework.util.Assert.isTrue(Assert.java:122)

What's surprising is that I'm using encryptedEmail and name twice in both clauses, yet the error arises only for the third clause containing ids. Has this been fixed only for certain cases?

I also tried without using coalesce: :ids is null or org.id in :ids and I'm getting the same error. I also tried inverting the where clauses, putting the last one in a different position, and I have the same error only on the statement containing :ids. I also tried going old school and using ?1, ?2, and ?3 instead of the variable names, but I also get the same error with identifier: 3 this time.

I think the difference is that the last parameter is a list and that confuses the validator.

[ghost]

Same here, with boot 3.1.2 -> 3.1.3

-    id 'org.springframework.boot' version '3.1.2'
+    id 'org.springframework.boot' version '3.1.3'

Symptoms same as in prev comment https://github.com/spring-projects/spring-data-jpa/issues/1230#issuecomment-1693586595

[alirezaalallah]

Actually , I have same situation in my query when I have upgraded to new version, I'm getting exactly this the error you are discussing. Where clause in query is like :cities is null or entity.city in (:cities) , also I used coalesce function but it didn't work

[falcononrails]

@alirezaalallah, @alianman, @SAMTV12345, @leo-jeff-app this has been resolved in 3.1.4-SNAPSHOT. Check this issue here.

[alirezaalallah]

@alirezaalallah, @alianman, @SamTV12345, @leo-jeff-app this has been resolved in 3.1.4-SNAPSHOT. Check this issue here.

As this is snapshot seems like I have to wait util final one is released. By the way thanks for the update

[SamTV12345]

@alirezaalallah, @alianman, @SamTV12345, @leo-jeff-app this has been resolved in 3.1.4-SNAPSHOT. Check this issue here.

As this is snapshot seems like I have to wait util final is released. By the way thanks for the update

Yes. Same case. I can't rollout snapshots in production 😄 . But great that this is presumably fixed in 3.1.4

[HengCC]

I've also encountered this issue, just saw this issue, and I tried upgrading to 3.1.4-snapshot locally, but the problem still persists. it tips:

 Already found parameter binding with same index / parameter name but differing binding type; Already have: ParameterBinding [identifier: userIds, origin: MethodInvocationArgument[identifier=userIds]], found ParameterBinding [identifier: userIds, origin: MethodInvocationArgument[identifier=userIds]]; If you bind a parameter multiple times make sure they use the same binding
	at org.springframework.util.Assert.isTrue(Assert.java:122)

My query is something like this:

and case when length(:userIds) > 0 then fr.fileCreator in (:userIds) else true end \n

and query method like

    Page<xxxxx> findDefaultSortedChildren(xxx dto, List<Long> userIds, String xx, Pageable pageable);