SQL Parsing Error

[Devharajan-Rangarajan]

There is an issue with the parsing of the native SQL Queries for both the .id options and the Group by clause

Sample Code for Quick Copy:

@query(
value =
"""
SELECT v.name AS vendor_name,
COUNT(m.id) AS mould_count
FROM moulds m
LEFT JOIN vendor v ON v.id = m.converter_id
WHERE AND m.mould_code_lifecycle_stage != 'RETIRED'
AND m.mould_type = 'PRODUCTION'
AND m.id IN (SELECT unnest(allowed_moulds::bigint[])
FROM user_allowed_items
WHERE user_id = :userId)
GROUP BY v.name
ORDER BY mould_count DESC
LIMIT 5
""",
nativeQuery = true)
List<Object[]> getTopConverter(@param("userId") Long userId);

Errors Thrown:

1. PostgreSQL: no viable alternative at input '(m' [SQL_SYNTAX]
2. PostgreSQL: no viable alternative at input 'GROUP' [SQL_SYNTAX]

[BoykoAlex]

Which STS version is this? Did you upgrade from another version and these error markers appeared?
You can workaround it by disabling SQL syntax validation in the Settings -> Spring -> Validation -> Data Queries

[Devharajan-Rangarajan]

The above is the version I am using which is the latest version. I upgraded from the previous version.

[Devharajan-Rangarajan]

System Details Report

---

Hardware Information:

• Hardware Model: Hewlett-Packard HP 240 14 inch G9 Notebook PC
• Memory: 32.0 GiB
• Processor: 12th Gen Intel® Core™ i5-1235U × 12
• Graphics: Intel® Graphics (ADL GT2)
• Disk Capacity: 512.1 GB

Software Information:

• Firmware Version: F.05
• OS Name: Ubuntu 24.04.1 LTS
• OS Build: (null)
• OS Type: 64-bit
• GNOME Version: 46
• Windowing System: X11
• Kernel Version: Linux 6.8.0-51-generic

[BoykoAlex]

@Devharajan-Rangarajan you query has WHERE AND m.mould_code_lifecycle_stage != 'RETIRED' the AND after WHERE shouldn't be there I think... There is an error at unnest(allowed_moulds::bigint[]) after the AND is removed and the syntax error at :: is a bug I think.

[BoykoAlex]

Okay, I had an syntax error at :: in unnest(allowed_moulds::bigint[]) because I has MySql connector rather than PostgreSql. Switching to PostgreSql does not produce any syntax error.
@Devharajan-Rangarajan please remove the erroneous AND as mentioned in the comment above and everything should work.