JSqlParserQueryEnhancer removes WITH clauses for paginated and sorted native queries

[lumi77]

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)

JSqlParserQueryEnhancerBug.zip.

[christophstrobl]

@lumi77 thanks for reporting and the reproducer!

[quaff]

It should be fixed by #3248.

[christophstrobl]

Thanks @quaff for linking the PR. We're currently looking into ways to mitigate the issue without (major/minor) version bumps for the upcoming service release.