Improve Error Messages for Query Syntax Issues in Oracle NoSQL Database

[otaviojava]

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.

[gmfeinberg]

Thanks Otavio! The system uses an Antlr lexer and parser and it can be difficult to get specific on error messages. I'll add this to a internal task to work on improving messages where they can be detected.

The syntax for BETWEEN is documented but I agree that if the query compiler can provide better messages it means less need to reference back to documentation that cannot be integrated into an IDE for convenience.

https://docs.oracle.com/en/database/other-databases/nosql-database/24.4/nsdev/operator1.html

[otaviojava]

Thanks, @gmfeinberg. The between was a sample; my point was more the error message.