Repository: tobymao/sqlglot

Issue:
Spark exp.RegexpExtract transpile is incorrect
**Before you file an issue**
sqlglot.transpile(sql, read="presto", write="spark")

**Fully reproducible code snippet**
The default value of the group parameter of REGEXP_EXTRACT is different between presto and spark:
- Presto defaults to 0
- Spark defaults to 1

presto sql as following:
```
select regexp_extract('aaaa', 'a|b|c')
```

The sql transpile to spark is as follows:
```
select regexp_extract('aaaa', 'a|b|c')
```

Expected result:
```
select regexp_extract('aaaa', 'a|b|c', 0)
```

## Environmental Verification

When execute in spark sql, output as following:
`SELECT regexp_extract('1a 2b 14m', '(\\d+)([a-z]+)') limit 100`
```
+-------------------------------------------+
|regexp_extract(1a 2b 14m, (\d+)([a-z]+), 1)|
+-------------------------------------------+
| 1|
+-------------------------------------------+

24/09/03 11:43:36 INFO SparkUI: Stopped Spark web UI at http://192.168.15.150:4040
```

And the result of presto execution is:
`SELECT regexp_extract('1a 2b 14m', '(\d+)([a-z]+)') limit 100`
![image](https://github.com/user-attachments/assets/5cad3a5f-c0e5-41be-a65b-c25e623b14b8)


## Doc
[Spark regexp_extract](https://spark.apache.org/docs/latest/api/sql/#regexp_extract) | [Presto](https://prestodb.io/docs/current/functions/regexp.html#regexp_extract-string-pattern-varchar)

Discussion:
@hellozepp: Should Presto Parser add a 0 to the `group`?@VaggelisD: I think this sounds right, we can canonicalize the default value @ parse time for each dialect; I'll also do a pass over other dialects to document their behavior.@hellozepp: @VaggelisD Thanks for the quick fix!

Solution patch:
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 9b2695d753..a319ac1e2f 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -400,6 +400,9 @@ class Dialect(metaclass=_Dialect):
     ARRAY_AGG_INCLUDES_NULLS: t.Optional[bool] = True
     """Whether ArrayAgg needs to filter NULL values."""
 
+    REGEXP_EXTRACT_DEFAULT_GROUP = 0
+    """The default value for the capturing group."""
+
     SET_OP_DISTINCT_BY_DEFAULT: t.Dict[t.Type[exp.Expression], t.Optional[bool]] = {
         exp.Except: True,
         exp.Intersect: True,
@@ -1368,9 +1371,13 @@ def regexp_extract_sql(self: Generator, expression: exp.RegexpExtract) -> str:
     if bad_args:
         self.unsupported(f"REGEXP_EXTRACT does not support the following arg(s): {bad_args}")
 
-    return self.func(
-        "REGEXP_EXTRACT", expression.this, expression.expression, expression.args.get("group")
-    )
+    group = expression.args.get("group")
+
+    # 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):
+        group = None
+
+    return self.func("REGEXP_EXTRACT", expression.this, expression.expression, group)
 
 
 def regexp_replace_sql(self: Generator, expression: exp.RegexpReplace) -> str:
@@ -1690,3 +1697,11 @@ def sequence_sql(self: Generator, expression: exp.GenerateSeries | exp.GenerateD
             start = exp.cast(start, target_type)
 
     return self.func("SEQUENCE", start, end, step)
+
+
+def build_regexp_extract(args: t.List, dialect: DialectType) -> exp.RegexpExtract:
+    return exp.RegexpExtract(
+        this=seq_get(args, 0),
+        expression=seq_get(args, 1),
+        group=seq_get(args, 2) or exp.Literal.number(dialect.REGEXP_EXTRACT_DEFAULT_GROUP),
+    )
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 8ed03138dc..e8f5837680 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -34,6 +34,7 @@
     unit_to_var,
     unit_to_str,
     sha256_sql,
+    build_regexp_extract,
 )
 from sqlglot.helper import seq_get
 from sqlglot.tokens import TokenType
@@ -360,9 +361,7 @@ class Parser(parser.Parser):
             ),
             "QUANTILE_CONT": exp.PercentileCont.from_arg_list,
             "QUANTILE_DISC": exp.PercentileDisc.from_arg_list,
-            "REGEXP_EXTRACT": lambda args: exp.RegexpExtract(
-                this=seq_get(args, 0), expression=seq_get(args, 1), group=seq_get(args, 2)
-            ),
+            "REGEXP_EXTRACT": build_regexp_extract,
             "REGEXP_MATCHES": exp.RegexpLike.from_arg_list,
             "REGEXP_REPLACE": lambda args: exp.RegexpReplace(
                 this=seq_get(args, 0),
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index 57e75c300b..ad6e92586c 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -34,6 +34,7 @@
     var_map_sql,
     sequence_sql,
     property_sql,
+    build_regexp_extract,
 )
 from sqlglot.transforms import (
     remove_unique_constraints,
@@ -194,6 +195,7 @@ class Hive(Dialect):
     SUPPORTS_USER_DEFINED_TYPES = False
     SAFE_DIVISION = True
     ARRAY_AGG_INCLUDES_NULLS = None
+    REGEXP_EXTRACT_DEFAULT_GROUP = 1
 
     # https://spark.apache.org/docs/latest/sql-ref-identifier.html#description
     NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
@@ -308,9 +310,7 @@ class Parser(parser.Parser):
             "MONTH": lambda args: exp.Month(this=exp.TsOrDsToDate.from_arg_list(args)),
             "PERCENTILE": exp.Quantile.from_arg_list,
             "PERCENTILE_APPROX": exp.ApproxQuantile.from_arg_list,
-            "REGEXP_EXTRACT": lambda args: exp.RegexpExtract(
-                this=seq_get(args, 0), expression=seq_get(args, 1), group=seq_get(args, 2)
-            ),
+            "REGEXP_EXTRACT": build_regexp_extract,
             "SEQUENCE": exp.GenerateSeries.from_arg_list,
             "SIZE": exp.ArraySize.from_arg_list,
             "SPLIT": exp.RegexpSplit.from_arg_list,
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index 12db5dc9ef..e837052563 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -29,6 +29,7 @@
     ts_or_ds_add_cast,
     unit_to_str,
     sequence_sql,
+    build_regexp_extract,
 )
 from sqlglot.dialects.hive import Hive
 from sqlglot.dialects.mysql import MySQL
@@ -315,9 +316,7 @@ class Parser(parser.Parser):
                 this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8")
             ),
             "NOW": exp.CurrentTimestamp.from_arg_list,
-            "REGEXP_EXTRACT": lambda args: exp.RegexpExtract(
-                this=seq_get(args, 0), expression=seq_get(args, 1), group=seq_get(args, 2)
-            ),
+            "REGEXP_EXTRACT": build_regexp_extract,
             "REGEXP_REPLACE": lambda args: exp.RegexpReplace(
                 this=seq_get(args, 0),
                 expression=seq_get(args, 1),
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 734a0b2f0f..edb7df1de3 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -327,7 +327,14 @@ class Parser(parser.Parser):
             "NULLIFZERO": _build_if_from_nullifzero,
             "OBJECT_CONSTRUCT": _build_object_construct,
             "REGEXP_REPLACE": _build_regexp_replace,
-            "REGEXP_SUBSTR": exp.RegexpExtract.from_arg_list,
+            "REGEXP_SUBSTR": lambda args: exp.RegexpExtract(
+                this=seq_get(args, 0),
+                expression=seq_get(args, 1),
+                position=seq_get(args, 2),
+                occurrence=seq_get(args, 3),
+                parameters=seq_get(args, 4),
+                group=seq_get(args, 5) or exp.Literal.number(0),
+            ),
             "RLIKE": exp.RegexpLike.from_arg_list,
             "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)),
             "TIMEADD": _build_date_time_add(exp.TimeAdd),
@@ -991,6 +998,12 @@ def regexpextract_sql(self, expression: exp.RegexpExtract) -> str:
             # Other dialects don't support all of the following parameters, so we need to
             # generate default values as necessary to ensure the transpilation is correct
             group = expression.args.get("group")
+
+            # To avoid generating all these default values, we set group to None if
+            # it's 0 (also default value) which doesn't trigger the following chain
+            if group and group.name == "0":
+                group = None
+
             parameters = expression.args.get("parameters") or (group and exp.Literal.string("c"))
             occurrence = expression.args.get("occurrence") or (parameters and exp.Literal.number(1))
             position = expression.args.get("position") or (occurrence and exp.Literal.number(1))


Test patch:
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index 2aca96db3e..136ea605c7 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -765,6 +765,24 @@ def test_hive(self):
                 "presto": "SELECT DATE_TRUNC('MONTH', TRY_CAST(ds AS TIMESTAMP)) AS mm FROM tbl WHERE ds BETWEEN '2023-10-01' AND '2024-02-29'",
             },
         )
+        self.validate_all(
+            "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+            read={
+                "hive": "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+                "spark2": "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+                "spark": "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+                "databricks": "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+            },
+            write={
+                "hive": "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+                "spark2": "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+                "spark": "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+                "databricks": "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+                "presto": "REGEXP_EXTRACT('abc', '(a)(b)(c)', 1)",
+                "trino": "REGEXP_EXTRACT('abc', '(a)(b)(c)', 1)",
+                "duckdb": "REGEXP_EXTRACT('abc', '(a)(b)(c)', 1)",
+            },
+        )
 
     def test_escapes(self) -> None:
         self.validate_identity("'\n'", "'\\n'")
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 3149df75c1..9c61f62c3c 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -1034,6 +1034,25 @@ def test_presto(self):
                 "spark": "SELECT REGEXP_EXTRACT(TO_JSON(FROM_JSON('[[1, 2, 3]]', SCHEMA_OF_JSON('[[1, 2, 3]]'))), '^.(.*).$', 1)",
             },
         )
+        self.validate_all(
+            "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+            read={
+                "presto": "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+                "trino": "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+                "duckdb": "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+                "snowflake": "REGEXP_SUBSTR('abc', '(a)(b)(c)')",
+            },
+            write={
+                "presto": "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+                "trino": "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+                "duckdb": "REGEXP_EXTRACT('abc', '(a)(b)(c)')",
+                "snowflake": "REGEXP_SUBSTR('abc', '(a)(b)(c)')",
+                "hive": "REGEXP_EXTRACT('abc', '(a)(b)(c)', 0)",
+                "spark2": "REGEXP_EXTRACT('abc', '(a)(b)(c)', 0)",
+                "spark": "REGEXP_EXTRACT('abc', '(a)(b)(c)', 0)",
+                "databricks": "REGEXP_EXTRACT('abc', '(a)(b)(c)', 0)",
+            },
+        )
 
     def test_encode_decode(self):
         self.validate_identity("FROM_UTF8(x, y)")
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index fc1c86449b..49f1a4ff73 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -1701,16 +1701,27 @@ def test_regexp_substr(self, logger):
             "REGEXP_SUBSTR(subject, pattern)",
             read={
                 "bigquery": "REGEXP_EXTRACT(subject, pattern)",
+                "snowflake": "REGEXP_EXTRACT(subject, pattern)",
+            },
+            write={
+                "bigquery": "REGEXP_EXTRACT(subject, pattern)",
+                "snowflake": "REGEXP_SUBSTR(subject, pattern)",
+            },
+        )
+        self.validate_all(
+            "REGEXP_SUBSTR(subject, pattern, 1, 1, 'c', 1)",
+            read={
                 "hive": "REGEXP_EXTRACT(subject, pattern)",
-                "presto": "REGEXP_EXTRACT(subject, pattern)",
+                "spark2": "REGEXP_EXTRACT(subject, pattern)",
                 "spark": "REGEXP_EXTRACT(subject, pattern)",
+                "databricks": "REGEXP_EXTRACT(subject, pattern)",
             },
             write={
-                "bigquery": "REGEXP_EXTRACT(subject, pattern)",
                 "hive": "REGEXP_EXTRACT(subject, pattern)",
-                "presto": "REGEXP_EXTRACT(subject, pattern)",
-                "snowflake": "REGEXP_SUBSTR(subject, pattern)",
+                "spark2": "REGEXP_EXTRACT(subject, pattern)",
                 "spark": "REGEXP_EXTRACT(subject, pattern)",
+                "databricks": "REGEXP_EXTRACT(subject, pattern)",
+                "snowflake": "REGEXP_SUBSTR(subject, pattern, 1, 1, 'c', 1)",
             },
         )
         self.validate_all(