[BUG] JSQLParser 5.4-SNAPSHOT : PostgreSQL : SEARCH BREADTH FIRST BY not supported

[tomershay]

SEARCH BREADTH FIRST BY is part of the SQL 99 extensions specification, and I believe it was implemented by PostgreSQL in v14.

https://sql-99.readthedocs.io/en/latest/chapters/32.html#recursive-unions

Sample query:

WITH RECURSIVE team_hierarchy AS (
    SELECT employee_id, first_name, manager_id, ARRAY[employee_id] AS path
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.first_name, e.manager_id, th.path || e.employee_id
    FROM employees e
    INNER JOIN team_hierarchy th ON e.manager_id = th.employee_id
)
SEARCH BREADTH FIRST BY employee_id SET order_col
SELECT
    employee_id,
    first_name,
    path,
    order_col
FROM team_hierarchy
ORDER BY order_col;

Error:

ParseException: Encountered: <S_IDENTIFIER> / "SEARCH", at line 10, column 1, in lexical state DEFAULT.
Was expecting one of these terminals within expansion starting at 974:21:
    <K_COMMA> (inside 4375:5) ...