Invalid sort alias with subquery and sorted page request

[c-fraser]

PR #2516 appears to not completely fix the issue described in issue #2518.

Using Spring Boot 2.7.1 with the models...

@Entity
class This {

  @Id @GeneratedValue(strategy = GenerationType.AUTO) var id: Long? = null
}

@Entity
class That {

  @Id @GeneratedValue(strategy = GenerationType.AUTO) var id: Long? = null
}

and the repositories...

interface ThisRepository : JpaRepository<This, Long> {

  @Query(
      """
      select f from This f
          where exists (
              select 1 from That b
                  where b = ?1
          )
      """)
  fun demo(that: That, pageable: Pageable): Page<This>
}

interface ThatRepository : JpaRepository<That, Long>

the execution of...

@DataJpaTest
class ExampleApplicationTest {

  @Autowired private lateinit var thisRepository: ThisRepository

  @Autowired private lateinit var thatRepository: ThatRepository

  @Test
  fun test() {
    val that = thatRepository.save(That())
    thisRepository.demo(that, PageRequest.of(0, 1, Sort.by("id")))
  }
}

results in...

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'b.id' [
          select f from example.model.This f
              where exists (
                  select 1 from example.model.That b
                      where b = ?1
              )
           order by b.id asc]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'b.id' [
          select f from example.model.This f
              where exists (
                  select 1 from example.model.That b
                      where b = ?1
              )
           order by b.id asc]

[gregturn]

Okay, I've captured this scenario in a test method inside Spring Data JPA.

@ExtendWith(SpringExtension.class)
@ContextConfiguration
public class PageRequestSortQueryDoesntWorkIntegrationTests {

	@Autowired ThisRepository thisRepository;
	@Autowired ThatRepository thatRepository;

	@Test
	void test() {
		That that = thatRepository.save(new That());
		thisRepository.demo(that, PageRequest.of(0, 1, Sort.by("id")));
	}

	@Entity
	@Data
	@NoArgsConstructor(access = AccessLevel.PROTECTED)
	@AllArgsConstructor
	static class This {
		@Id
		@GeneratedValue private Long id;
	}

	@Entity
	@Data
	@NoArgsConstructor(access = AccessLevel.PROTECTED)
	@AllArgsConstructor
	static class That {
		@Id
		@GeneratedValue private Long id;
	}

	interface ThisRepository extends JpaRepository<This, Long> {
		@Query("""
				select f from This f
				where exists (
					select 1 from That b
					where b = ?1
				)
				""")
		Page<This> demo(That that, Pageable pageable);
	}

	interface ThatRepository extends JpaRepository<That, Long> {

	}

	@Configuration
	@ImportResource("classpath:infrastructure.xml")
	@EnableJpaRepositories(considerNestedRepositories = true,
			basePackageClasses = CustomNonBindableJpaParametersIntegrationTests.ProductRepository.class, //
			includeFilters = @ComponentScan.Filter(value = { ThisRepository.class, ThatRepository.class },
					type = FilterType.ASSIGNABLE_TYPE))
	static class Config {

	}

}

There is no Spring Boot. Just plain old Spring Framwork and Spring Data JPA.

And the error I'm seeing is this:

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: This is not mapped [select f from This f
where exists (
	select 1 from That b
	where b = ?1
)

And to be honest, I don't see where the outer select correlates with the inner select. The inner select matches on the b = ?1, but it never connects to the outer one.

[c-fraser]

Okay, I've captured this scenario in a test method inside Spring Data JPA.

@ExtendWith(SpringExtension.class)
@ContextConfiguration
public class PageRequestSortQueryDoesntWorkIntegrationTests {

	@Autowired ThisRepository thisRepository;
	@Autowired ThatRepository thatRepository;

	@Test
	void test() {
		That that = thatRepository.save(new That());
		thisRepository.demo(that, PageRequest.of(0, 1, Sort.by("id")));
	}

	@Entity
	@Data
	@NoArgsConstructor(access = AccessLevel.PROTECTED)
	@AllArgsConstructor
	static class This {
		@Id
		@GeneratedValue private Long id;
	}

	@Entity
	@Data
	@NoArgsConstructor(access = AccessLevel.PROTECTED)
	@AllArgsConstructor
	static class That {
		@Id
		@GeneratedValue private Long id;
	}

	interface ThisRepository extends JpaRepository<This, Long> {
		@Query("""
				select f from This f
				where exists (
					select 1 from That b
					where b = ?1
				)
				""")
		Page<This> demo(That that, Pageable pageable);
	}

	interface ThatRepository extends JpaRepository<That, Long> {

	}

	@Configuration
	@ImportResource("classpath:infrastructure.xml")
	@EnableJpaRepositories(considerNestedRepositories = true,
			basePackageClasses = CustomNonBindableJpaParametersIntegrationTests.ProductRepository.class, //
			includeFilters = @ComponentScan.Filter(value = { ThisRepository.class, ThatRepository.class },
					type = FilterType.ASSIGNABLE_TYPE))
	static class Config {

	}

}

There is no Spring Boot. Just plain old Spring Framwork and Spring Data JPA.

And the error I'm seeing is this:

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: This is not mapped [select f from This f
where exists (
	select 1 from That b
	where b = ?1
)

And to be honest, I don't see where the outer select correlates with the inner select. The inner select matches on the b = ?1, but it never connects to the outer one.

Thanks for your response. When I was debugging the issue I found the subquery parenthesis weren't being identified and removed. What do you think the PR I submitted?

[edoswaldgo]

Good day @c-fraser , @gregturn ,

I also encountered this error while using org.springframework.data:spring-data-jpa:2.7.1. The order by column uses the alias of the subquery.

Given Chris' fix of using DOTALL and MULTILINE, I tried removing all the new lines on my query and Spring JPA can successfully use the correct table alias for pageable.

May we know when's the earliest release date of the fix? Thank you!

[c-fraser]

@edoswaldgo This fix was backported to 2.7.x and 2.6.x via https://github.com/spring-projects/spring-data-jpa/pull/2582.