Repository: tobymao/sqlglot

Issue:
sqlglot removes options from the regexp_replace function
Hey, I have a problem. Sqlglot removes options from the regexp_replace function when translating from a Postgres script to a duckdb one.

**Fully reproducible code snippet**
```
sqlglot.transpile("select regexp_replace('mr .', '[^a-zA-Z]', '', 'g')", read="postgres", write="duckdb")[0]
"SELECT REGEXP_REPLACE('mr .', '[^a-zA-Z]', '')"
```

**Official Documentation**
DuckdDb regexp_replace https://duckdb.org/docs/sql/functions/regular_expressions.html#using-regexp_replace
Postgres: https://www.postgresql.org/docs/current/functions-matching.html#:~:text=The%20regexp_replace%20function%20provides%20substitution,be%20given%20in%20any%20case.)

Discussion:

Patch:
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 2e04aca3..cc1d6208 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -903,9 +903,7 @@ def regexp_extract_sql(self: Generator, expression: exp.RegexpExtract) -> str:
 
 
 def regexp_replace_sql(self: Generator, expression: exp.RegexpReplace) -> str:
-    bad_args = list(
-        filter(expression.args.get, ("position", "occurrence", "parameters", "modifiers"))
-    )
+    bad_args = list(filter(expression.args.get, ("position", "occurrence", "modifiers")))
     if bad_args:
         self.unsupported(f"REGEXP_REPLACE does not support the following arg(s): {bad_args}")
 
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 770dad2f..bdc42186 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -32,7 +32,7 @@ from sqlglot.dialects.dialect import (
     trim_sql,
     ts_or_ds_add_cast,
 )
-from sqlglot.helper import seq_get
+from sqlglot.helper import is_int, seq_get
 from sqlglot.parser import binary_range_parser
 from sqlglot.tokens import TokenType
 
@@ -204,6 +204,29 @@ def _json_extract_sql(
     return _generate
 
 
+def _build_regexp_replace(args: t.List) -> exp.RegexpReplace:
+    # The signature of REGEXP_REPLACE is:
+    # regexp_replace(source, pattern, replacement [, start [, N ]] [, flags ])
+    #
+    # Any one of `start`, `N` and `flags` can be column references, meaning that
+    # unless we can statically see that the last argument is a non-integer string
+    # (eg. not '0'), then it's not possible to construct the correct AST
+    if len(args) > 3:
+        last = args[-1]
+        if not is_int(last.name):
+            if not last.type or last.is_type(exp.DataType.Type.UNKNOWN, exp.DataType.Type.NULL):
+                from sqlglot.optimizer.annotate_types import annotate_types
+
+                last = annotate_types(last)
+
+            if last.is_type(*exp.DataType.TEXT_TYPES):
+                regexp_replace = exp.RegexpReplace.from_arg_list(args[:-1])
+                regexp_replace.set("modifiers", last)
+                return regexp_replace
+
+    return exp.RegexpReplace.from_arg_list(args)
+
+
 class Postgres(Dialect):
     INDEX_OFFSET = 1
     TYPED_DIVISION = True
@@ -321,6 +344,7 @@ class Postgres(Dialect):
             "MAKE_TIME": exp.TimeFromParts.from_arg_list,
             "MAKE_TIMESTAMP": exp.TimestampFromParts.from_arg_list,
             "NOW": exp.CurrentTimestamp.from_arg_list,
+            "REGEXP_REPLACE": _build_regexp_replace,
             "TO_CHAR": build_formatted_time(exp.TimeToStr, "postgres"),
             "TO_TIMESTAMP": _build_to_timestamp,
             "UNNEST": exp.Explode.from_arg_list,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index af1e1bc0..5005519e 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5567,7 +5567,6 @@ class RegexpReplace(Func):
         "replacement": False,
         "position": False,
         "occurrence": False,
-        "parameters": False,
         "modifiers": False,
     }
 
diff --git a/sqlglot/lineage.py b/sqlglot/lineage.py
index f4a3dec5..5c28e97d 100644
--- a/sqlglot/lineage.py
+++ b/sqlglot/lineage.py
@@ -8,6 +8,7 @@ from dataclasses import dataclass, field
 from sqlglot import Schema, exp, maybe_parse
 from sqlglot.errors import SqlglotError
 from sqlglot.optimizer import Scope, build_scope, find_all_in_scope, normalize_identifiers, qualify
+from sqlglot.optimizer.scope import ScopeType
 
 if t.TYPE_CHECKING:
     from sqlglot.dialects.dialect import DialectType
@@ -255,7 +256,12 @@ def to_node(
         source = scope.sources.get(table)
 
         if isinstance(source, Scope):
-            selected_node, _ = scope.selected_sources.get(table, (None, None))
+            reference_node_name = None
+            if source.scope_type == ScopeType.DERIVED_TABLE and table not in source_names:
+                reference_node_name = table
+            elif source.scope_type == ScopeType.CTE:
+                selected_node, _ = scope.selected_sources.get(table, (None, None))
+                reference_node_name = selected_node.name if selected_node else None
             # The table itself came from a more specific scope. Recurse into that one using the unaliased column name.
             to_node(
                 c.name,
@@ -264,7 +270,7 @@ def to_node(
                 scope_name=table,
                 upstream=node,
                 source_name=source_names.get(table) or source_name,
-                reference_node_name=selected_node.name if selected_node else None,
+                reference_node_name=reference_node_name,
                 trim_selects=trim_selects,
             )
         else:


Test patch:
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 7cfba94a..fa8cf60c 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -325,6 +325,13 @@ class TestPostgres(Validator):
             "CAST(x AS BIGINT)",
         )
 
+        self.validate_all(
+            "SELECT REGEXP_REPLACE('mr .', '[^a-zA-Z]', '', 'g')",
+            write={
+                "duckdb": "SELECT REGEXP_REPLACE('mr .', '[^a-zA-Z]', '', 'g')",
+                "postgres": "SELECT REGEXP_REPLACE('mr .', '[^a-zA-Z]', '', 'g')",
+            },
+        )
         self.validate_all(
             "CREATE TABLE t (c INT)",
             read={
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index a328940d..a6c933a8 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -1580,7 +1580,7 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS _flattene
             "REGEXP_REPLACE(subject, pattern, replacement, position, occurrence, parameters)",
             write={
                 "bigquery": "REGEXP_REPLACE(subject, pattern, replacement)",
-                "duckdb": "REGEXP_REPLACE(subject, pattern, replacement)",
+                "duckdb": "REGEXP_REPLACE(subject, pattern, replacement, parameters)",
                 "hive": "REGEXP_REPLACE(subject, pattern, replacement)",
                 "snowflake": "REGEXP_REPLACE(subject, pattern, replacement, position, occurrence, parameters)",
                 "spark": "REGEXP_REPLACE(subject, pattern, replacement, position)",
diff --git a/tests/test_lineage.py b/tests/test_lineage.py
index cbadf7bb..3e17f956 100644
--- a/tests/test_lineage.py
+++ b/tests/test_lineage.py
@@ -300,6 +300,7 @@ class TestLineage(unittest.TestCase):
         self.assertEqual(node.name, "a")
         node = node.downstream[0]
         self.assertEqual(node.name, "cte.a")
+        self.assertEqual(node.reference_node_name, "cte")
         node = node.downstream[0]
         self.assertEqual(node.name, "z.a")
 
@@ -338,6 +339,27 @@ class TestLineage(unittest.TestCase):
         node = a.downstream[0]
         self.assertEqual(node.name, "foo.a")
 
+        # Select from derived table
+        node = lineage(
+            "a",
+            "SELECT a FROM (SELECT a FROM x) subquery",
+        )
+        self.assertEqual(node.name, "a")
+        self.assertEqual(len(node.downstream), 1)
+        node = node.downstream[0]
+        self.assertEqual(node.name, "subquery.a")
+        self.assertEqual(node.reference_node_name, "subquery")
+
+        node = lineage(
+            "a",
+            "SELECT a FROM (SELECT a FROM x)",
+        )
+        self.assertEqual(node.name, "a")
+        self.assertEqual(len(node.downstream), 1)
+        node = node.downstream[0]
+        self.assertEqual(node.name, "_q_0.a")
+        self.assertEqual(node.reference_node_name, "_q_0")
+
     def test_lineage_cte_union(self) -> None:
         query = """
         WITH dataset AS (