Repository: tobymao/sqlglot

Issue:
Parser error for DuckDB FROM-first syntax if subquery is in brackets
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**:
```sql
CREATE TABLE bar AS (
    FROM foo
    SELECT a, b
)
```
```sql
FROM (
    FROM foo
    SELECT a, b
)
```
```sql
WITH
    bar AS (
        FROM (
            FROM foo
            SELECT a, b
        )
    )

FROM bar
```

While these **do not** raise an error.

Not using `FROM`-first:
```sql
FROM (
    SELECT a, b
    FROM foo
)
```
```sql
CREATE TABLE bar AS (
    SELECT a, b
    FROM foo
)
```

Not surrounded in brackets:
```sql
CREATE TABLE bar AS
    FROM foo
    SELECT a, b
```

"Top-level" CTE statement:
```sql
WITH
    bar AS (
        FROM (
            FROM foo
            SELECT a, b
        )
    )

FROM bar
```

**Fully reproducible code snippet**

```python
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


Discussion hints:

Solution patch:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 0e525e7a87..4a62f7d47b 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3068,9 +3068,14 @@ def _parse_select(
                     is_unpivot=self._prev.token_type == TokenType.UNPIVOT
                 )
             elif self._match(TokenType.FROM):
-                this = exp.select("*").from_(
-                    t.cast(exp.From, self._parse_from(skip_from_token=True))
-                )
+                from_ = self._parse_from(skip_from_token=True)
+                # Support parentheses for duckdb FROM-first syntax
+                select = self._parse_select()
+                if select:
+                    select.set("from", from_)
+                    this = select
+                else:
+                    this = exp.select("*").from_(t.cast(exp.From, from_))
             else:
                 this = (
                     self._parse_table()


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 62db38007f..ea83fd0f98 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -1434,3 +1434,23 @@ def test_simplified_pivot_unpivot(self):
         self.validate_identity(
             "SELECT * FROM (UNPIVOT monthly_sales ON COLUMNS(* EXCLUDE (empid, dept)) INTO NAME month VALUE sales) AS unpivot_alias"
         )
+
+    def test_from_first_with_parentheses(self):
+        self.validate_all(
+            "CREATE TABLE t1 AS (FROM t2 SELECT foo1, foo2)",
+            write={
+                "duckdb": "CREATE TABLE t1 AS (SELECT foo1, foo2 FROM t2)",
+            },
+        )
+        self.validate_all(
+            "FROM (FROM t1 SELECT foo1, foo2)",
+            write={
+                "duckdb": "SELECT * FROM (SELECT foo1, foo2 FROM t1)",
+            },
+        )
+        self.validate_all(
+            "WITH t1 AS (FROM (FROM t2 SELECT foo1, foo2)) FROM t1",
+            write={
+                "duckdb": "WITH t1 AS (SELECT * FROM (SELECT foo1, foo2 FROM t2)) SELECT * FROM t1",
+            },
+        )