Repository: tobymao/sqlglot

Issue:
Failed to parse SQL (postgres) with commented out semicolon
```sql
-- COMMENT
-------------------------------------------------------------------------------
TRUNCATE table1
;


-- COMMENT
-------------------------------------------------------------------------------
WITH
    table5 AS (
        SELECT DISTINCT
            col1,
            col2,
            col3
        FROM
            table6 cdep
    ),
    table4 AS (
        SELECT
            col1,
            col2,
            col3 --
        FROM
            table5 ltt
    ),
    table3 AS (
        SELECT
            col1,
            col2,
            col3
        FROM
            table4
    ),
    table2 AS (
        SELECT
            col1,
            col2,
            col3
        FROM
            table3
    )
INSERT INTO
    table1 (
        col1,
        col2,
        col3
    )
SELECT
    col1,
    col2,
    col3
FROM
    table2
;

-- vacuum after
-- VACUUM VERBOSE
-- ANALYZE table1
-- ;
```


The above SQL is a simplified example as I can't post client code.  However, it fails the same way.

I am using read dialect of postgres.

I am first calling `parse(sql_stmt, read=dialect)` with the entire statement expecting to get all of the statements returned as a List.  That returns 3 sub-statements: TruncateTable, Insert, and Semicolon.  The first 2 are correct.  However, the entire 3rd statement (Semicolon) is the commented out lines at the bottom of the full sql statement.

Then I try to parse each one of the sub-statements: `ast = parse_one(sql_stmt, read=dialect)`.  When I get to the 3rd statement (Semicolon), it fails with:

`ParseError("No expression was parsed from '/* vacuum after */ /* VACUUM VERBOSE */ /* ANALYZE table1 */ /* ; */'")`

It seems like when it parses the end of the file, it get's thrown off by the commented out semicolon.

Discussion:
@dbittenbender: Also seeing a ton of log messages like this:

INFO:sqlglot:Applying array index offset (1)
INFO:sqlglot:Applying array index offset (1)
INFO:sqlglot:Applying array index offset (1)
INFO:sqlglot:Applying array index offset (-1)
INFO:sqlglot:Applying array index offset (-1)
INFO:sqlglot:Applying array index offset (-1)

Not sure where it's coming from or if it's relevant@VaggelisD: Hey @dbittenbender,

Regarding the comments, this is expected and is happening for the following reasons:
- For the `parse` example, the comments are appended to the _previous_  semicolon (support introduced [here](https://github.com/tobymao/sqlglot/commit/aa104fd2ccd73a13ca60fa3de3296ed4c007e8da))
- For the `parse_one` example, the failure is correct in that there's indeed no valid expression to be parsed (comments are attached to their nearby expressions, so we need at least 1); What does look faulty is that even if we add a semicolon at the end the parse error is there, so I can add a solution to mitigate that. 

As far as the array indexes are concerned, SQLGlot has logic to canonicalize array indexes between dialects (some are 0-based while others are 1-based etc), but I'd expect to see these during transpilation. Does this lead to incorrect roundtrips in Postgres? Would you be able to find a repro?@dbittenbender: Sorry for the delay in response.  I was away.  Thanks for your help!

I will skip the Semicolon types.  

For the array indexes, it's not causing any problems that I am aware of, I just never saw that many in the logs before so i wasn't sure if it was related.

Solution patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 09e80c4207..06cc145ab6 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -284,6 +284,7 @@ class DuckDB(Dialect):
     SUPPORTS_ORDER_BY_ALL = True
     SUPPORTS_FIXED_SIZE_ARRAYS = True
     STRICT_JSON_PATH_SYNTAX = False
+    NUMBERS_CAN_BE_UNDERSCORE_SEPARATED = True
 
     # https://duckdb.org/docs/sql/introduction.html#creating-a-new-table
     NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 6a4dd1101a..450a86baa6 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1568,8 +1568,12 @@ class CharacterSet(Expression):
     arg_types = {"this": True, "default": False}
 
 
+class RecursiveWithSearch(Expression):
+    arg_types = {"kind": True, "this": True, "expression": True, "using": False}
+
+
 class With(Expression):
-    arg_types = {"expressions": True, "recursive": False}
+    arg_types = {"expressions": True, "recursive": False, "search": False}
 
     @property
     def recursive(self) -> bool:
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 1f0c63c5a0..085e09e148 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1238,8 +1238,10 @@ def with_sql(self, expression: exp.With) -> str:
             if self.CTE_RECURSIVE_KEYWORD_REQUIRED and expression.args.get("recursive")
             else ""
         )
+        search = self.sql(expression, "search")
+        search = f" {search}" if search else ""
 
-        return f"WITH {recursive}{sql}"
+        return f"WITH {recursive}{sql}{search}"
 
     def cte_sql(self, expression: exp.CTE) -> str:
         alias = expression.args.get("alias")
@@ -4267,7 +4269,7 @@ def copy_sql(self, expression: exp.Copy) -> str:
         return f"COPY{this}{kind} {files}{credentials}{params}"
 
     def semicolon_sql(self, expression: exp.Semicolon) -> str:
-        return ""
+        return ";"
 
     def datadeletionproperty_sql(self, expression: exp.DataDeletionProperty) -> str:
         on_sql = "ON" if expression.args.get("on") else "OFF"
@@ -4793,3 +4795,14 @@ def declareitem_sql(self, expression: exp.DeclareItem) -> str:
             kind = f"TABLE {kind}"
 
         return f"{variable} AS {kind}{default}"
+
+    def recursivewithsearch_sql(self, expression: exp.RecursiveWithSearch) -> str:
+        kind = self.sql(expression, "kind")
+        this = self.sql(expression, "this")
+        set = self.sql(expression, "expression")
+        using = self.sql(expression, "using")
+        using = f" USING {using}" if using else ""
+
+        kind_sql = kind if kind == "CYCLE" else f"SEARCH {kind} FIRST BY"
+
+        return f"{kind_sql} {this} SET {set}{using}"
diff --git a/sqlglot/optimizer/qualify_tables.py b/sqlglot/optimizer/qualify_tables.py
index edf78e989d..220b6c9ac4 100644
--- a/sqlglot/optimizer/qualify_tables.py
+++ b/sqlglot/optimizer/qualify_tables.py
@@ -97,12 +97,16 @@ def _qualify(table: exp.Table) -> None:
                     source.alias
                 )
 
-                _qualify(source)
+                if pivots:
+                    if not pivots[0].alias:
+                        pivot_alias = next_alias_name()
+                        pivots[0].set("alias", exp.TableAlias(this=exp.to_identifier(pivot_alias)))
+
+                    # This case corresponds to a pivoted CTE, we don't want to qualify that
+                    if isinstance(scope.sources.get(source.alias_or_name), Scope):
+                        continue
 
-                if pivots and not pivots[0].alias:
-                    pivots[0].set(
-                        "alias", exp.TableAlias(this=exp.to_identifier(next_alias_name()))
-                    )
+                _qualify(source)
 
                 if infer_csv_schemas and schema and isinstance(source.this, exp.ReadCSV):
                     with csv_reader(source.this) as reader:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 8326ae0cfc..47a0fe0f26 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1373,6 +1373,8 @@ class Parser(metaclass=_Parser):
 
     OPERATION_MODIFIERS: t.Set[str] = set()
 
+    RECURSIVE_CTE_SEARCH_KIND = {"BREADTH", "DEPTH", "CYCLE"}
+
     STRICT_CAST = True
 
     PREFIXED_PIVOT_COLUMNS = False
@@ -1543,7 +1545,10 @@ def _parse(
         for i, token in enumerate(raw_tokens):
             if token.token_type == TokenType.SEMICOLON:
                 if token.comments:
-                    chunks.append([token])
+                    if not chunks[-1]:
+                        chunks[-1].append(token)
+                    else:
+                        chunks.append([token])
 
                 if i < total - 1:
                     chunks.append([])
@@ -3174,6 +3179,24 @@ def _parse_select(
 
         return self._parse_set_operations(this) if parse_set_operation else this
 
+    def _parse_recursive_with_search(self) -> t.Optional[exp.RecursiveWithSearch]:
+        self._match_text_seq("SEARCH")
+
+        kind = self._match_texts(self.RECURSIVE_CTE_SEARCH_KIND) and self._prev.text.upper()
+
+        if not kind:
+            return None
+
+        self._match_text_seq("FIRST", "BY")
+
+        return self.expression(
+            exp.RecursiveWithSearch,
+            kind=kind,
+            this=self._parse_id_var(),
+            expression=self._match_text_seq("SET") and self._parse_id_var(),
+            using=self._match_text_seq("USING") and self._parse_id_var(),
+        )
+
     def _parse_with(self, skip_with_token: bool = False) -> t.Optional[exp.With]:
         if not skip_with_token and not self._match(TokenType.WITH):
             return None
@@ -3198,7 +3221,11 @@ def _parse_with(self, skip_with_token: bool = False) -> t.Optional[exp.With]:
             last_comments = self._prev_comments
 
         return self.expression(
-            exp.With, comments=comments, expressions=expressions, recursive=recursive
+            exp.With,
+            comments=comments,
+            expressions=expressions,
+            recursive=recursive,
+            search=self._parse_recursive_with_search(),
         )
 
     def _parse_cte(self) -> t.Optional[exp.CTE]:


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 89c0f4773c..4dcfe3697a 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -314,6 +314,10 @@ def test_duckdb(self):
         self.validate_identity(
             """SELECT '{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }' ->> ['$.family', '$.species']""",
         )
+        self.validate_identity(
+            "SELECT 20_000 AS literal",
+            "SELECT 20000 AS literal",
+        )
         self.validate_identity(
             """SELECT JSON_EXTRACT_STRING('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }', ['$.family', '$.species'])""",
             """SELECT '{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }' ->> ['$.family', '$.species']""",
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index a6061e5347..44f1e397e0 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -1358,3 +1358,13 @@ def test_analyze(self):
         self.validate_identity("ANALYZE TBL(col1, col2)")
         self.validate_identity("ANALYZE VERBOSE SKIP_LOCKED TBL(col1, col2)")
         self.validate_identity("ANALYZE BUFFER_USAGE_LIMIT 1337 TBL")
+
+    def test_recursive_cte(self):
+        for kind in ("BREADTH", "DEPTH"):
+            self.validate_identity(
+                f"WITH RECURSIVE search_tree(id, link, data) AS (SELECT t.id, t.link, t.data FROM tree AS t UNION ALL SELECT t.id, t.link, t.data FROM tree AS t, search_tree AS st WHERE t.id = st.link) SEARCH {kind} FIRST BY id SET ordercol SELECT * FROM search_tree ORDER BY ordercol"
+            )
+
+        self.validate_identity(
+            "WITH RECURSIVE search_graph(id, link, data, depth) AS (SELECT g.id, g.link, g.data, 1 FROM graph AS g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph AS g, search_graph AS sg WHERE g.id = sg.link) CYCLE id SET is_cycle USING path SELECT * FROM search_graph"
+        )
diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py
index fe4130e301..efac82445e 100644
--- a/tests/test_optimizer.py
+++ b/tests/test_optimizer.py
@@ -216,6 +216,28 @@ def test_isolate_table_selects(self):
         )
 
     def test_qualify_tables(self):
+        self.assertEqual(
+            optimizer.qualify_tables.qualify_tables(
+                parse_one(
+                    "WITH cte AS (SELECT * FROM t) SELECT * FROM cte PIVOT(SUM(c) FOR v IN ('x', 'y'))"
+                ),
+                db="db",
+                catalog="catalog",
+            ).sql(),
+            "WITH cte AS (SELECT * FROM catalog.db.t AS t) SELECT * FROM cte AS cte PIVOT(SUM(c) FOR v IN ('x', 'y')) AS _q_0",
+        )
+
+        self.assertEqual(
+            optimizer.qualify_tables.qualify_tables(
+                parse_one(
+                    "WITH cte AS (SELECT * FROM t) SELECT * FROM cte PIVOT(SUM(c) FOR v IN ('x', 'y')) AS pivot_alias"
+                ),
+                db="db",
+                catalog="catalog",
+            ).sql(),
+            "WITH cte AS (SELECT * FROM catalog.db.t AS t) SELECT * FROM cte AS cte PIVOT(SUM(c) FOR v IN ('x', 'y')) AS pivot_alias",
+        )
+
         self.assertEqual(
             optimizer.qualify_tables.qualify_tables(
                 parse_one("select a from b"), catalog="catalog"
diff --git a/tests/test_parser.py b/tests/test_parser.py
index 20967b9de0..c7aaeae310 100644
--- a/tests/test_parser.py
+++ b/tests/test_parser.py
@@ -851,8 +851,20 @@ def test_trailing_comments(self):
             """
         )
 
+        self.assertEqual("".join(e.sql() for e in expressions), "SELECT * FROM x; /* my comment */")
+
+        expressions = parse_one(
+            """
+            -- vacuum after
+            -- VACUUM VERBOSE
+            -- ANALYZE table1
+            -- ;
+            ;
+            """
+        )
         self.assertEqual(
-            ";\n".join(e.sql() for e in expressions), "SELECT * FROM x;\n/* my comment */"
+            expressions.sql(),
+            "; /* vacuum after */ /* VACUUM VERBOSE */ /* ANALYZE table1 */ /* ; */",
         )
 
     def test_parse_prop_eq(self):