Parser error for DuckDB FROM-first syntax if subquery is in brackets

[LennartH]

Parsing DuckDB statements where a subquery is wrapped in brackets and uses FROM-first syntax with SELECT can raise the error sqlglot.errors.ParseError: Expecting ). For example for subqueries in SELECT or CREATE TABLE AS statements, but not in CTEs. This needs some examples, so statements like these do raise an error:

CREATE TABLE bar AS (
    FROM foo
    SELECT a, b
)

FROM (
    FROM foo
    SELECT a, b
)

WITH
    bar AS (
        FROM (
            FROM foo
            SELECT a, b
        )
    )

FROM bar

While these do not raise an error.

Not using FROM-first:

FROM (
    SELECT a, b
    FROM foo
)

CREATE TABLE bar AS (
    SELECT a, b
    FROM foo
)

Not surrounded in brackets:

CREATE TABLE bar AS
    FROM foo
    SELECT a, b

"Top-level" CTE statement:

WITH
    bar AS (
        FROM (
            FROM foo
            SELECT a, b
        )
    )

FROM bar

Fully reproducible code snippet

from sqlglot import parse_one

parse_one("""
    FROM (
        FROM foo
        SELECT a, b
    )
""", dialect="duckdb")

Traceback:

Traceback (most recent call last):
  File "/some/path/example.py", line 3, in <module>
    parse_one("""
  File "/some/path/.venv/lib/python3.12/site-packages/sqlglot/__init__.py", line 139, in parse_one
    result = dialect.parse(sql, **opts)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/some/path/.venv/lib/python3.12/site-packages/sqlglot/dialects/dialect.py", line 937, in parse
    return self.parser(**opts).parse(self.tokenize(sql), sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/some/path/.venv/lib/python3.12/site-packages/sqlglot/parser.py", line 1439, in parse
    return self._parse(
           ^^^^^^^^^^^^
  File "/some/path/.venv/lib/python3.12/site-packages/sqlglot/parser.py", line 1508, in _parse
    expressions.append(parse_method(self))
                       ^^^^^^^^^^^^^^^^^^
  File "/some/path/.venv/lib/python3.12/site-packages/sqlglot/parser.py", line 1749, in _parse_statement
    expression = self._parse_set_operations(expression) if expression else self._parse_select()
                                                                           ^^^^^^^^^^^^^^^^^^^^
  File "/some/path/.venv/lib/python3.12/site-packages/sqlglot/parser.py", line 3001, in _parse_select
    from_ = self._parse_from() if self._match(TokenType.FROM, advance=False) else None
            ^^^^^^^^^^^^^^^^^^
  File "/some/path/.venv/lib/python3.12/site-packages/sqlglot/parser.py", line 3314, in _parse_from
    exp.From, comments=self._prev_comments, this=self._parse_table(joins=joins)
                                                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/some/path/.venv/lib/python3.12/site-packages/sqlglot/parser.py", line 3744, in _parse_table
    subquery = self._parse_select(table=True)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/some/path/.venv/lib/python3.12/site-packages/sqlglot/parser.py", line 3080, in _parse_select
    self._match_r_paren()
  File "/some/path/.venv/lib/python3.12/site-packages/sqlglot/parser.py", line 7328, in _match_r_paren
    self.raise_error("Expecting )")
  File "/some/path/.venv/lib/python3.12/site-packages/sqlglot/parser.py", line 1552, in raise_error
    raise error
sqlglot.errors.ParseError: Expecting ). Line 4, Col: 7.
  
    FROM (
        FROM foo
        SELECT a, b
    )

Official Documentation

• https://duckdb.org/docs/sql/query_syntax/from#from-first-syntax

[geooo109]

Thank you @LennartH for pointing it out.
I am working on it.