Improve Error Messages for Query Syntax Issues in Oracle NoSQL Database

When executing a query with a syntax issue, the error message provided does not clearly indicate where the problem lies. Instead, it returns a generic message that can be misleading and does not help pinpoint the exact issue.

Example

Consider the following query:

DELETE FROM entity WHERE entity = 'Drink' AND BETWEEN content.alcoholPercentage ? AND ?

The query fails with the following error message:

java.lang.IllegalArgumentException: PREPARE: Illegal Argument: Table, index and unquoted field names may contain only alphanumeric values plus the character "_". Error: at (1, 80) mismatched input 'entity' expecting {<EOF>, AND, IS, OR, RETURNING, '[', '*', '.', '<', '<=', '>', '>=', '=', '!=', LT_ANY, LTE_ANY, GT_ANY, GTE_ANY, EQ_ANY, NEQ_ANY, '+', '-', '/', RDIV, '||'}, at line 1:80
rule stack: [parse]

This message suggests that the issue is with the field reference syntax (e.g., the use of .), but that is the correct way to navigate JSON fields. The issue is with the incorrect placement of the BETWEEN clause.

Correct Query

The correct syntax for this query is:

DELETE FROM entity WHERE entity = 'Drink' AND content.alcoholPercentage BETWEEN ? AND ?

When the query is written this way, it executes successfully.

Suggested Enhancement

The error message should be more descriptive and guide the user in identifying the actual issue. Instead of providing a generic error like:

mismatched input 'entity' expecting ...

It could suggest specific query syntax rules that might be violated or point to possible misplacements like:

• "Syntax error: Check the placement of the BETWEEN clause."
• "Unexpected token 'BETWEEN'. Ensure it is correctly structured as <field> BETWEEN <value> AND <value>."

Improving error messages will significantly enhance the developer experience by reducing confusion and helping quickly identify issues.

The issue names no files, tests, or entry points; start by locating the query parser and the code that formats syntax errors in the Java SDK. Reproduce the shown DELETE query and compare its diagnostic with the corrected query, then define tests that verify the reported location and guidance are accurate without misidentifying JSON field syntax.