The JPQL validation in Spring Tool Suite (STS) is showing several errors for valid JPQL/PostgreSQL syntax in combination with Eclipselink

[zarembo]

The JPQL validation in Spring Tool Suite (STS) is showing several errors for native and JPQL queries that seem to be valid PostgreSQL syntax. It appears to be a problem with using Eclipselink instead of Hibernate as well.

The queries are executable in a PostgreSQL environment.

IDE Version
Spring Tool Suite 4
Version: 4.25.0.RELEASE
Build Id: 202409101855
Revision: a82190b

To Reproduce

A sample application with a minimal, reproducible sample is attached jpql_issue.zip

Steps to manually reproduce the behavior:

1. Create a Spring Boot project with Spring Data JPA and a PostgreSQL database (test scope is enough).
2. Exclude Hibernate and include Eclipselink

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
    <parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.3.4</version>
	</parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.hibernate.orm</groupId>
                    <artifactId>hibernate-core</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.eclipse.persistence</groupId>
            <artifactId>org.eclipse.persistence.jpa</artifactId>
            <version>4.0.3</version>
        </dependency>
        
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <optional>true</optional>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

3. Define a repository interface with several query methods as shown in the sample code below.
4. Observe the errors in the IDE (Spring Tool Suite 4) during validation.

Here is the repository interface code demonstrating the issue:

public interface SampleTableRepository extends JpaRepository<SampleTable, Long> {

    // IDE error: PostgreSQL: mismatched input 'not' expecting {<EOF>, ';'} [SQL_SYNTAX]
    @Query(value = "delete from SAMPLE_TABLE where id not in (select top 1 id from SAMPLE_TABLE order by TABLE_NAME desc)"
    , nativeQuery = true)
    void deleteEntries();

    // IDE error: PostgreSQL: no viable alternative at input 'SELECTSCHEMA_NAME,TABLE_NAME,VERSION' [SQL_SYNTAX]
    // It seems the parser has an issue with the VERSION column, which is not the version for other purposes
    @Query(value = "SELECT SCHEMA_NAME, TABLE_NAME, VERSION from SAMPLE_TABLE", nativeQuery = true)
    List<SampleTable> findAll();
    // the error is gone if the column has a different name
    @Query(value = "SELECT SCHEMA_NAME, TABLE_NAME, VERSION2 from SAMPLE_TABLE", nativeQuery = true)
    List<SampleTable> findAllNoVersion();

    // IDE error: PostgreSQL: no viable alternative at input 'selecttop1*from' [SQL_SYNTAX]
    // Issue with a "select top x" query
    @Query(value = "select top 1 * from SAMPLE_TABLE where SCHEMA_NAME = ?1", nativeQuery = true)
    SampleTable findOneBySchemaName(String schemaName);

    // IDE error: PostgreSQL: no viable alternative at input 'SCHEMA_NAME=?1andRECORD_COUNTnotin' [SQL_SYNTAX]
    @Query(value = "select tablename from SAMPLE_TABLE where SCHEMA_NAME = ?1 and RECORD_COUNT not in (0, 1)", nativeQuery = true)
    List<SampleTable> exampleInNotWorking(String schemaName);

    // IDE error: PostgreSQL: no viable alternative at input 'WITHcteAS(SELECTq.*,ROW_NUMBER()OVER(' [SQL_SYNTAX]
    /** To run the query below, you can
     * Create a table:
         CREATE TABLE SAMPLE_TABLE (
            id serial PRIMARY KEY,
            database_id int,
            order_id int,
            status int,
            scenario int
        );
    * Add dummy data:
        INSERT INTO SAMPLE_TABLE (database_id, order_id, status, scenario) VALUES (1, 1, 0, 11);
        INSERT INTO SAMPLE_TABLE (database_id, order_id, status, scenario) VALUES (1, 2, 5, 8);
        INSERT INTO SAMPLE_TABLE (database_id, order_id, status, scenario) VALUES (2, 1, 10, 11);
        INSERT INTO SAMPLE_TABLE (database_id, order_id, status, scenario) VALUES (2, 2, 1, 8);
        INSERT INTO SAMPLE_TABLE (database_id, order_id, status, scenario) VALUES (3, 1, 10, 11);
    * now you can run the example!
    */
    @Query(value = """
            WITH cte AS (
                SELECT
                    q.*,
                    ROW_NUMBER() OVER (PARTITION BY q.database_id ORDER BY q.order_id) AS rn
                FROM
                    SAMPLE_TABLE AS q
                WHERE
                    q.status IN (0, 1, 5, 10)
            )
            SELECT *
            FROM cte
            WHERE
                (rn = 1 OR status = 10)
                AND (scenario = 11 OR scenario = 8)
            ORDER BY status DESC
            """, nativeQuery = true)
    List<SampleTable> getOneOpenEntry();
}

public interface MyRepo extends JpaRepository<SampleTable, String> {

    // IDE Error: JPQL: mismatched input 'sum' expecting {COUNT, DATE, FLOOR, FROM, INNER, KEY, LEFT, NEW, ORDER, OUTER, POWER, SIGN, TIME, TREAT, TYPE, VALUE, IDENTIFICATION_VARIABLE} [JPQL_SYNTAX]
    @Query(value = " SELECT new com.example.ls.issue.SampleTableSizePojo(t.schemaName, sum(t.tableSize) ) FROM MTables t GROUP BY t.schemaName ORDER BY sum(t.tableSize) DESC")
    List<SampleTableSizePojo> getTableSizes();
}

Used entity

@Entity
@Table(name = "SAMPLE_TABLE")
public class SampleTable implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "DATABASE_ID", nullable = false)
    private Integer databaseId;

    @Column(name = "ORDER_ID", nullable = false)
    private Integer orderId;

    @Column(name = "STATUS", nullable = false)
    private Integer status;

    @Column(name = "SCENARIO", nullable = false)
    private Integer scenario;
    
    @Id
    @Column(name = "SCHEMA_NAME", length = 256)
    private String schemaName;

    @Id
    @Column(name = "TABLE_NAME", length = 256)
    private String tableName;

    @Column(name = "RECORD_COUNT")
    private BigInteger recordCount;

    @Column(name = "TABLE_SIZE")
    private BigInteger tableSize;

    @Column(name = "VERSION", length = 30)
    private String version;
    // Getters and Setters...
}

Pojo example

public class SampleTableSizePojo {
    private String schemaName;
    private BigInteger tableSize;
    // Getters and Setters...
}

Additional Notes

These errors seem to be false positives or issues with the JPQL validation within STS. The queries are executable in a real PostgreSQL environment as they conform to the expected SQL syntax for PostgreSQL.

[martinlippert]

Thanks @zarembo for this detailed report, very very much appreciated. Let's try to get this fixed for the next release.

[BoykoAlex]

Should be fixed with:

• 899389e
• d260847
• ec8b9ea
• 1d02a04

@zarembo please do file more of these!-)

[zarembo]

@BoykoAlex / @martinlippert Thank you for fixing it so fast. I build it locally and there is another question and I do not know if I should raise another issue for this.
If I brake the query e.g. misspelling select:
@Query(value = "WRONGselect tablename from SAMPLE_TABLE where SCHEMA_NAME = ?1 and RECORD_COUNT not in (0, 1)", nativeQuery = true)

Then the IDE shows it as an error. Correct, but if I make it as an concatenated string:
@Query(value = ""+"**WRONGselect** tablename from SAMPLE_TABLE where SCHEMA_NAME = ?1 and RECORD_COUNT not in (0, 1)", nativeQuery = true)
Then the error is gone.
Should I open another issue for this?

[martinlippert]

Then the IDE shows it as an error. Correct, but if I make it as an concatenated string:
@Query(value = ""+"**WRONGselect** tablename from SAMPLE_TABLE where SCHEMA_NAME = ?1 and RECORD_COUNT not in (0, 1)", nativeQuery = true)
Then the error is gone.
Should I open another issue for this?

Yes, that would be awesome! Many thanks for giving this a try and for the detailed issues. That is fantastic!

[BoykoAlex]

@zarembo @martinlippert concatenated strings support has already been requested: #1262 We felt as this is not a high priority issue hence it in the backlog for now