Repository: tobymao/sqlglot

Issue:
Expected that round-tripping (same dialect) fixes unescaped backslashes?
I'm not sure if this is a bug but it seemed strange as it can change the semantics of a query.

In all the dialects I tried, round-tripping through sqlglot will add an extra backslash to an unescaped backslash in a string literal. For example:

```python
from sqlglot import transpile

original_query = r"""
select 
name as raw_name,
REGEXP_REPLACE(name, ' [vV]\d+$','') as unescaped_backslash,
REGEXP_REPLACE(name, ' [vV]\\d+$','') as escaped_backslash,
from values ('foo'), ('bar v1') as t (name)
"""
print("original query", original_query)

(roundtrip,) = transpile(original_query, pretty=True, read="snowflake", identity=True)
print("roundtrip", roundtrip)
```

Note the use of raw Python strings here: I found at least one prior issue (#2236) which seemed to stem from confusion about escaping of Python strings, but in the original context I encountered this these were not Python string literals (the query was read from a file).

Now it seems that in some dialects `original_query` is not syntactically valid at all - BigQuery for example reports "Illegal escape sequence: \d at [3:28]". However in some dialects like Snowflake `original_query` is syntactically valid but has different semantics than the fixed form. [Here](https://docs.snowflake.com/en/sql-reference/functions-regexp#label-regexp-escape-character-caveats) are the Snowflake docs for specifying regexes in single-quoted strings which call out that the backslash sequence `\d` must be escaped `\\d` and round-tripping conceals the bug in the original SQL with the unescaped backslash.

Discussion:
@VaggelisD: Sorry for the incorrect mentions, feel free to ignore.@georgesittas: As you say, the original query here is probably poorly written, relying on unspecified behavior around `\d`. If you wanted to match a `d`, you could omit the backslash altogether. Even though this is accepted by Snowflake, I don't think it's worth tackling right now, because it'd change certain properties in how escapes are handled and the complexity looks unjustified. Happy to continue discussing, but I'm gonna close this for now.

<img width="1034" alt="Screenshot 2024-11-05 at 12 16 48 PM" src="https://github.com/user-attachments/assets/1deb6d31-a526-464b-b197-e199a89c1c86">


Solution patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 3a71708820..6198aa97a1 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -427,6 +427,7 @@ class Parser(parser.Parser):
             "DATE_TRUNC": lambda args: exp.DateTrunc(
                 unit=exp.Literal.string(str(seq_get(args, 1))),
                 this=seq_get(args, 0),
+                zone=seq_get(args, 2),
             ),
             "DATETIME": _build_datetime,
             "DATETIME_ADD": build_date_delta_with_interval(exp.DatetimeAdd),
@@ -732,7 +733,9 @@ class Generator(generator.Generator):
             exp.DateSub: date_add_interval_sql("DATE", "SUB"),
             exp.DatetimeAdd: date_add_interval_sql("DATETIME", "ADD"),
             exp.DatetimeSub: date_add_interval_sql("DATETIME", "SUB"),
-            exp.DateTrunc: lambda self, e: self.func("DATE_TRUNC", e.this, e.text("unit")),
+            exp.DateTrunc: lambda self, e: self.func(
+                "DATE_TRUNC", e.this, e.text("unit"), e.args.get("zone")
+            ),
             exp.FromTimeZone: lambda self, e: self.func(
                 "DATETIME", self.func("TIMESTAMP", e.this, e.args.get("zone")), "'UTC'"
             ),
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 8d5a21dbcd..2224a6e72c 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -957,15 +957,16 @@ def arraytostring_sql(self, expression: exp.ArrayToString) -> str:
         @unsupported_args("position", "occurrence")
         def regexpextract_sql(self, expression: exp.RegexpExtract) -> str:
             group = expression.args.get("group")
+            params = expression.args.get("parameters")
 
-            # Do not render group if it's the default value for this dialect
-            if group and group.name == str(self.dialect.REGEXP_EXTRACT_DEFAULT_GROUP):
+            # Do not render group if there is no following argument,
+            # and it's the default value for this dialect
+            if (
+                not params
+                and group
+                and group.name == str(self.dialect.REGEXP_EXTRACT_DEFAULT_GROUP)
+            ):
                 group = None
-
             return self.func(
-                "REGEXP_EXTRACT",
-                expression.this,
-                expression.expression,
-                group,
-                expression.args.get("parameters"),
+                "REGEXP_EXTRACT", expression.this, expression.expression, group, params
             )


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 68410c3152..48ff8f9a33 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -165,6 +165,7 @@ def test_bigquery(self):
         self.validate_identity("SELECT * FROM foo.bar.25ab c", "SELECT * FROM foo.bar.`25ab` AS c")
         self.validate_identity("x <> ''")
         self.validate_identity("DATE_TRUNC(col, WEEK(MONDAY))")
+        self.validate_identity("DATE_TRUNC(col, MONTH, 'UTC+8')")
         self.validate_identity("SELECT b'abc'")
         self.validate_identity("SELECT AS STRUCT 1 AS a, 2 AS b")
         self.validate_identity("SELECT DISTINCT AS STRUCT 1 AS a, 2 AS b")
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index d26ea70096..b59ac9fa42 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -785,6 +785,12 @@ def test_duckdb(self):
                 "snowflake": "SELECT REGEXP_SUBSTR(a, 'pattern', 1, 1, 'i', 2) FROM t",
             },
         )
+        self.validate_identity(
+            "SELECT REGEXP_EXTRACT(a, 'pattern', 0)",
+            "SELECT REGEXP_EXTRACT(a, 'pattern')",
+        )
+        self.validate_identity("SELECT REGEXP_EXTRACT(a, 'pattern', 0, 'i')")
+        self.validate_identity("SELECT REGEXP_EXTRACT(a, 'pattern', 1, 'i')")
 
         self.validate_identity("SELECT ISNAN(x)")