Closed
Description
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:
- Create a Spring Boot project with Spring Data JPA and a PostgreSQL database (test scope is enough).
- 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>- Define a repository interface with several query methods as shown in the sample code below.
- 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.