Ambiguous grammar error when BETWEEN followed by two AND keywords

[jcgoette]

Describe the bug
Raises error:

SELECT
  1
FROM
  DUAL
WHERE
  SYSDATE BETWEEN '2022-11-28 00:00:00' AND '2022-11-28 23:59:59'
  AND EXISTS (
    SELECT
      1
    FROM
      DUAL
  )

Doesn't raise error:

SELECT
  1
FROM
  DUAL
WHERE
  EXISTS (
    SELECT
      1
    FROM
      DUAL
  )
  AND SYSDATE BETWEEN '2022-11-28 00:00:00' AND '2022-11-28 23:59:59'

Expected behavior
Format. :)

Actual behavior
Raised error:

An Unexpected Error Occurred
Parse error: Ambiguous grammar [ [ { "type": "statement", "children": [ { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_SELECT", "text": "SELECT", "raw": "SELECT" }, "children": [ { "type": "literal", "text": "1" } ] }, { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_CLAUSE", "text": "FROM", "raw": "FROM" }, "children": [ { "type": "identifier", "text": "DUAL" } ] }, { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_CLAUSE", "text": "WHERE", "raw": "WHERE" }, "children": [ { "type": "identifier", "text": "SYSDATE" }, { "type": "between_predicate", "betweenKw": { "type": "keyword", "tokenType": "BETWEEN", "text": "BETWEEN", "raw": "BETWEEN" }, "expr1": [ { "type": "literal", "text": "'2022-11-28 00:00:00'" } ], "andKw": { "type": "keyword", "tokenType": "AND", "text": "AND", "raw": "AND" }, "expr2": [ { "type": "literal", "text": "'2022-11-28 23:59:59'" } ] }, { "type": "keyword", "tokenType": "AND", "text": "AND", "raw": "AND" }, { "type": "keyword", "tokenType": "RESERVED_KEYWORD", "text": "EXISTS", "raw": "EXISTS" }, { "type": "parenthesis", "children": [ { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_SELECT", "text": "SELECT", "raw": "SELECT" }, "children": [ { "type": "literal", "text": "1" } ] }, { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_CLAUSE", "text": "FROM", "raw": "FROM" }, "children": [ { "type": "identifier", "text": "DUAL" } ] } ], "openParen": "(", "closeParen": ")" } ] } ], "hasSemicolon": false } ], [ { "type": "statement", "children": [ { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_SELECT", "text": "SELECT", "raw": "SELECT" }, "children": [ { "type": "literal", "text": "1" } ] }, { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_CLAUSE", "text": "FROM", "raw": "FROM" }, "children": [ { "type": "identifier", "text": "DUAL" } ] }, { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_CLAUSE", "text": "WHERE", "raw": "WHERE" }, "children": [ { "type": "identifier", "text": "SYSDATE" }, { "type": "between_predicate", "betweenKw": { "type": "keyword", "tokenType": "BETWEEN", "text": "BETWEEN", "raw": "BETWEEN" }, "expr1": [ { "type": "literal", "text": "'2022-11-28 00:00:00'" }, { "type": "keyword", "tokenType": "AND", "text": "AND", "raw": "AND" }, { "type": "literal", "text": "'2022-11-28 23:59:59'" } ], "andKw": { "type": "keyword", "tokenType": "AND", "text": "AND", "raw": "AND" }, "expr2": [ { "type": "keyword", "tokenType": "RESERVED_KEYWORD", "text": "EXISTS", "raw": "EXISTS" } ] }, { "type": "parenthesis", "children": [ { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_SELECT", "text": "SELECT", "raw": "SELECT" }, "children": [ { "type": "literal", "text": "1" } ] }, { "type": "clause", "nameKw": { "type": "keyword", "tokenType": "RESERVED_CLAUSE", "text": "FROM", "raw": "FROM" }, "children": [ { "type": "identifier", "text": "DUAL" } ] } ], "openParen": "(", "closeParen": ")" } ] } ], "hasSemicolon": false } ] ]

Usage

• How are you calling / using the library?
  • From sql-formatter-org.github.io
• What SQL language(s) does this apply to?
  • All
• Which SQL Formatter version are you using?
  • Latest

[nene]

Thanks for reporting, unfortunately haven't had time to tackle this problem.

The actual culprit here is this sort of expression:

... BETWEEN ... AND ... AND ...

The parser is getting confused whether it should parse it as:

(... BETWEEN ... AND ...) AND ...
... BETWEEN (... AND ...) AND ...
... BETWEEN ... AND (... AND ...)

a workaround for now is to supply your own extra parenthesis, like:

WHERE
  (SYSDATE BETWEEN '2022-11-28 00:00:00' AND '2022-11-28 23:59:59')
  AND (SELECT 1 FROM DUAL)

[nene]

This long-standing bug is now finally fixed in 12.1.1 release.