Closed
Description
Repository class:
@Repository
public interface ProjectsRepository extends JpaRepository<Project, Integer> {
String QUERY = """
WITH all_projects AS
(
SELECT *
FROM projects
)
SELECT * FROM all_projects
""";
@Query(value = QUERY, nativeQuery = true)
List<Project> findAllNativeQuery();
@Query(value = QUERY, nativeQuery = true)
Page<Project> findAllNativeQuery(Pageable pageable);
}Test class for the repository
@DataJpaTest
class ProjectsRepositoryTest {
@Autowired
private ProjectsRepository projectsRepository;
@Test
void findAll() {
var actual = projectsRepository.findAllNativeQuery();
assertThat(actual).isEmpty();
}
@Test
void findAllPageableUnsorted() {
var actual = projectsRepository.findAllNativeQuery(PageRequest.of(0, 10));
assertThat(actual.getContent()).isEmpty();
}
@Test
void findAllPageableSorted() {
var actual = projectsRepository.findAllNativeQuery(PageRequest.of(0, 10, Sort.Direction.ASC, "name"));
assertThat(actual.getContent()).isEmpty();
}
}When JSqlParser is not on the class path, all three tests pass.
When JSqlParser is on the class path, the third tests fails with an exception:
org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement [Table "ALL_PROJECTS" not found; SQL statement:
SELECT * FROM all_projects ORDER BY name ASC fetch first ? rows only [42102-224]]
[SELECT * FROM all_projects ORDER BY name ASC fetch first ? rows only]; SQL [SELECT * FROM all_projects ORDER BY name ASC fetch first ? rows only].
Both the printed query "SELECT * FROM all_projects ORDER BY name ASC fetch first ? rows only" and the error message "Table "ALL_PROJECTS" not found" imply that the WITH clause
WITH all_projects AS
(
SELECT *
FROM projects
)has been lost while preprocessing the query in JSqlParserQueryEnhancer. This happens in public String applySorting(Sort sort, @Nullable String alias)