NullPointerException in version 3.0.2 when using modifying native queries or SELECT queries that Spring Data cannot parse

[The-Arne]

Commit 64b5a22 introduced a null pointer exception in line 620 of QueryUtils. If a native query is not a select, but, for example, a delete, "variable" will be null, leading to said exception.

Example code in repository:

	@Modifying
	@Query(value = "delete from some_table where id in :ids", nativeQuery = true)
	void deleteStuffFromSomeTable(@Param("ids") Collection<UUID> ids);

Relevant part of the trace:

Caused by: java.lang.NullPointerException: Cannot invoke "String.contains(java.lang.CharSequence)" because "variable" is null
	at org.springframework.data.jpa.repository.query.QueryUtils.createCountQueryFor(QueryUtils.java:620)
	at org.springframework.data.jpa.repository.query.DefaultQueryEnhancer.createCountQueryFor(DefaultQueryEnhancer.java:49)
	at org.springframework.data.jpa.repository.query.StringQuery.deriveCountQuery(StringQuery.java:111)
	at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.<init>(AbstractStringBasedJpaQuery.java:82)
	at org.springframework.data.jpa.repository.query.NativeJpaQuery.<init>(NativeJpaQuery.java:58)
	at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:53)
	at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:170)
	at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:252)
	at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:95)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:111)
	... 79 more

[unkish]

Also affects 2.7.8

A simple test for QueryEnhancerTckTests that would reproduce issue:

    @Test
    void nativeUpdateQueryThrowsException() {
        DeclaredQuery declaredQuery = DeclaredQuery.of("delete from some_table where id in :ids", true);
        QueryEnhancer enhancer = createQueryEnhancer(declaredQuery);
        assertThatNoException().isThrownBy(() -> enhancer.createCountQueryFor(null));
    }

"Offending" query itself can be also:

• UPDATE table_name SET column_name = a_value"
• BEGIN ... WHEN MATCHED THEN ... END;
• ...

[ayrtonnadgel]

In my case, the NPE occurs querying a sequence value.

@Query(value = "SELECT nexval('public.my_sequence')", nativeQuery = true)
Integer getNextvalMySequence();

[gideruette]

Same here, with non CRUD native query (spring-jpa 2.7.8)

@Query(value = """
select count(1) as totalItems,
sum(case when myField is null then 0 else 1 end) as notNullItems
from myClass
""", nativeQuery = true)

Found a workaround by adding a fictive countQuery

@Query(value = """
select count(1) as totalItems,
sum(case when myField is null then 0 else 1 end) as notNullItems
from myClass
""", countQuery = "select 1", nativeQuery = true)

[ghost]

Same with an UPDATE query in 2.7.9:

  @Modifying
  @Query(nativeQuery = true, value = "UPDATE seq_mgmt SET seq_no = :value WHERE seq_id = :key")
  int setNextValue(@Param("key") String key, @Param("value") Long value);

[gregturn]

This has been patched in main (3.1.0-SNAPSHOT) and backported to 3.0.x and 2.7.x).

[therealaleko]

Is this warranting a release of SDJPA asap, or is it still within your release cadence? If not re-releasing ASAP, when is the next release? I'm not aware of the cadence this project follows. Thanks!

[ChristianCiach]

Workaround for people using Gradle if you don't want to downgrade Spring Boot altogether:

configurations.all {
    resolutionStrategy.eachDependency { DependencyResolveDetails details ->
        if (details.requested.name == 'spring-data-jpa' && details.requested.version == '3.0.2') {
            details.useVersion '3.0.1'
            details.because 'https://github.com/spring-projects/spring-data-jpa/issues/2812'
        }
    }
}

[otavioprado]

This has been patched in main (3.1.0-SNAPSHOT) and backported to 3.0.x and 2.7.x).

I'm still experiencing this issue in version 3.0.4-SNAPSHOT, 3.1.0-SNAPSHOT and 2.7.10-SNAPSHOT. Is there anything else I should do or check to resolve this using the SNAPSHOT version?

[mp911de]

@otavioprado we released just today versions 2.7.9 and 3.0.3. Please file a new ticket along with a stack trace and a way to reproduce your issue.

[otavioprado]

I'm not able to reproduce the issue with 3.0.3 anymore.

Unfortunately the last stable version is 2.7.8

[breun]

If you use Spring Boot 2.7.9: Spring Data BOM 2021.2.9 has been released, with Spring Data 2.7.9. This will be included in Spring Boot 2.7.10 (scheduled for March 23), but you can temporarily override spring-data-bom.version to 2021.2.9 for now.

[otavioprado]

If you use Spring Boot 2.7.9: Spring Data BOM 2021.2.9 has been released, with Spring Data 2.7.9. This will be included in Spring Boot 2.7.10 (scheduled for March 23), but you can temporarily override spring-data-bom.version to 2021.2.9 for now.

Thanks @breun
It works

	...
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.7.9</version>
		<relativePath/>
	</parent>
	...
	<dependencyManagement>
		<dependencies>
			<dependency>
				<groupId>org.springframework.data</groupId>
				<artifactId>spring-data-bom</artifactId>
				<version>2021.2.9</version>
				<scope>import</scope>
				<type>pom</type>
			</dependency>
		</dependencies>
	</dependencyManagement>
	...
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-data-jpa</artifactId>
	</dependency>
	...

To use spring-boot 3.0.3 I need to upgrade the bom to 2022.0.3 ?

[breun]

@otavioprado You don’t even need that full import, this should be enough:

<properties>
  <spring-data-bom.version>2021.2.9</spring-data-bom.version><!-- Temporary override until Spring Boot 2.7.10 -->
</properties>

If you’re on Spring Boot 3, you can upgrade to Spring Boot 3.0.4, which was just released.

[sergmain]

2.7.9 can't parse this

@Modifying
@Query(nativeQuery = true, value="update mh_variable as trg, (select data from mh_cache_variable where id=:srcId) as src " +
            "set trg.DATA= src.data, trg.FILENAME=:filename, trg.IS_INITED=true, trg.IS_NULLIFIED=false, trg.UPLOAD_TS=:uploadedOn " +
            "where trg.id=:trgId")
 void copyData(Long srcId, Long trgId, @Nullable String filename, Timestamp uploadedOn);

2.7.8 is Ok

mysql dialect

[pfuerholz]

According to my experience even a simple insert statement does not work with 2.7.9:

  @Modifying
  @Query(value = "INSERT INTO myTable VALUES (:attributeName, :attributeValue)",
         nativeQuery = true)
  void insertMyTable(@Param("attributeName") @NotNull String attributeName,
                            @Param("attributeValue") @NotNull String attributeValue);

This was okay with 2.7.8.

[breun]

@pfuerholz Are you referring to Spring Boot 2.7.9 or Spring Data JPA 2.7.9? This bug should have been fixed in the latter.

[sergmain]

@pfuerholz Are you referring to Spring Boot 2.7.9 or Spring Data JPA 2.7.9? This bug should have been fixed in the latter.

Thanks for pointing this out. spring-boot-starter-data-jpa 2.7.9 is actually using spring-data-jpa 2.7.8

      <groupId>org.springframework.data</groupId>
      <artifactId>spring-data-jpa</artifactId>
      <version>2.7.8</version>
      <scope>compile</scope>

[pfuerholz]

When I use version 2.7.9 throughout Spring Boot.

[javadev-jef]

Works on 2.7.10. Thank you all!

[pfuerholz]

When I use version 2.7.10 throughout Spring Boot it works!
(When I compare spring-data-jpa the difference in spring-data-jpa is neglectable: 2.7.9...2.7.10. This means other spring libs have an impact here...)