Repository: tobymao/sqlglot

Issue:
Functions transpiled incorrectly from MySQL to BigQuery
```python
import sqlglot
print(sqlglot.transpile("SELECT 'foo' regexp '.*';", read="mysql", write="bigquery"))
```
This currently transpiles to `SELECT REGEXP_LIKE('foo', '.*')` which is not valid for BigQuery (I guess the correct function would be `REGEXP_CONTAINS`).

Overall, how robust should I expect transpilation to be? Are certain language pairs better supported than others? Are functions more problematic than other constructs?

Discussion:

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 0ab32188..6be68ace 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -110,17 +110,17 @@ class BigQuery(Dialect):
 
         KEYWORDS = {
             **tokens.Tokenizer.KEYWORDS,
+            "BEGIN": TokenType.COMMAND,
+            "BEGIN TRANSACTION": TokenType.BEGIN,
             "CURRENT_DATETIME": TokenType.CURRENT_DATETIME,
             "CURRENT_TIME": TokenType.CURRENT_TIME,
             "GEOGRAPHY": TokenType.GEOGRAPHY,
-            "INT64": TokenType.BIGINT,
             "FLOAT64": TokenType.DOUBLE,
+            "INT64": TokenType.BIGINT,
+            "NOT DETERMINISTIC": TokenType.VOLATILE,
             "QUALIFY": TokenType.QUALIFY,
             "UNKNOWN": TokenType.NULL,
             "WINDOW": TokenType.WINDOW,
-            "NOT DETERMINISTIC": TokenType.VOLATILE,
-            "BEGIN": TokenType.COMMAND,
-            "BEGIN TRANSACTION": TokenType.BEGIN,
         }
         KEYWORDS.pop("DIV")
 
@@ -131,6 +131,7 @@ class BigQuery(Dialect):
             "DATE_ADD": _date_add(exp.DateAdd),
             "DATETIME_ADD": _date_add(exp.DatetimeAdd),
             "DIV": lambda args: exp.IntDiv(this=seq_get(args, 0), expression=seq_get(args, 1)),
+            "REGEXP_CONTAINS": exp.RegexpLike.from_arg_list,
             "TIME_ADD": _date_add(exp.TimeAdd),
             "TIMESTAMP_ADD": _date_add(exp.TimestampAdd),
             "DATE_SUB": _date_add(exp.DateSub),
@@ -183,6 +184,7 @@ class BigQuery(Dialect):
             exp.VolatilityProperty: lambda self, e: f"DETERMINISTIC"
             if e.name == "IMMUTABLE"
             else "NOT DETERMINISTIC",
+            exp.RegexpLike: rename_func("REGEXP_CONTAINS"),
         }
 
         TYPE_MAPPING = {


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 5c5a7713..1d60ec65 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -6,6 +6,11 @@ class TestBigQuery(Validator):
     dialect = "bigquery"
 
     def test_bigquery(self):
+        self.validate_all(
+            "REGEXP_CONTAINS('foo', '.*')",
+            read={"bigquery": "REGEXP_CONTAINS('foo', '.*')"},
+            write={"mysql": "REGEXP_LIKE('foo', '.*')"},
+        ),
         self.validate_all(
             '"""x"""',
             write={