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.
Bugs in parsing `DATE_TRUNC` function with the timezone parameter in BigQuery
When using `DATE_TRUNC(timestamp_value, timestamp_granularity[, time_zone])` in BigQuery, the `time_zone` argument failed to parse correctly.

For example:
```sql
-- input
SELECT DATE_TRUNC(TIMESTAMP '2024-01-01 12:00:00', MONTH, 'UTC+8')

-- output
SELECT DATE_TRUNC(CAST('2024-01-01 12:00:00' AS TIMESTAMP), MONTH)
```
The timezone `UTC+8` is missing.

The Python script is as follows::
```python
sql = "SELECT DATE_TRUNC(TIMESTAMP '2024-01-01 12:00:00', MONTH, 'UTC+8')"
ast = parse_one(sql, dialect="bigquery")
print(ast.sql(dialect="bigquery"))
# output: SELECT DATE_TRUNC(CAST('2024-01-01 12:00:00' AS TIMESTAMP), MONTH)
```


Discussion:

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 3a717088..6198aa97 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -427,6 +427,7 @@ class BigQuery(Dialect):
             "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 BigQuery(Dialect):
             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 8d5a21db..2224a6e7 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -957,15 +957,16 @@ class DuckDB(Dialect):
         @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 68410c31..48ff8f9a 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -165,6 +165,7 @@ LANGUAGE js AS
         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 d26ea700..b59ac9fa 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -785,6 +785,12 @@ class TestDuckDB(Validator):
                 "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)")