Repository: tobymao/sqlglot

Issue:
`REGEXP_I_LIKE` method doesn't exist in ClickHouse
**Fully reproducible code snippet**

sqlglot converts regexp ilike to `REGEXP_I_LIKE` in ClickHouse which doesn't exist.

```py
import sqlglot

sql = "'thomas' ~* '.*thomas.*'"
print(sqlglot.parse_one(sql, read='postgres').sql(dialect='clickhouse'))
#> REGEXP_I_LIKE('thomas', '.*thomas.*')
```

The the best option of an equivalent I've found is something like `f"match('{haystack}', '{pattern}?i')"` to use the [`match`](https://clickhouse.com/docs/en/sql-reference/functions/string-search-functions#match) function.

<details>
<summary>Confirmation the function doesn't exist</summary>

There's no such `REGEXP_I_LIKE` method in Clickhouse, so no documentation to link to, but here's the output if I run that query against CH:

```
SELECT REGEXP_I_LIKE('thomas', '.*thomas.*')

Query id: f38f80b0-59a7-4146-b33c-e345078480ee


0 rows in set. Elapsed: 0.447 sec. 

Received exception from server (version 23.8.1):
Code: 46. DB::Exception: Received from localhost:9000. DB::Exception: Unknown function REGEXP_I_LIKE. Maybe you meant: ['REGEXP_REPLACE']: While processing REGEXP_I_LIKE('thomas', '.*thomas.*'). (UNKNOWN_FUNCTION)
```

</details>


Discussion:

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 7f69dd96..2bd5cbad 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -632,6 +632,13 @@ class BigQuery(Dialect):
             "within",
         }
 
+        def eq_sql(self, expression: exp.EQ) -> str:
+            # Operands of = cannot be NULL in BigQuery
+            if isinstance(expression.left, exp.Null) or isinstance(expression.right, exp.Null):
+                return "NULL"
+
+            return self.binary(expression, "=")
+
         def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
             parent = expression.parent
 
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index e9d93266..c702f1df 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -418,6 +418,11 @@ class ClickHouse(Dialect):
             "NAMED COLLECTION",
         }
 
+        def regexpilike_sql(self, expression: exp.RegexpILike) -> str:
+            # Manually add a flag to make the search case-insensitive
+            regex = self.func("CONCAT", "'(?i)'", expression.expression)
+            return f"match({self.format_args(expression.this, regex)})"
+
         def datatype_sql(self, expression: exp.DataType) -> str:
             # String is the standard ClickHouse type, every other variant is just an alias.
             # Additionally, any supplied length parameter will be ignored.
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index b94b1e13..9ce8c4e2 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -4877,7 +4877,7 @@ class RegexpLike(Binary, Func):
     arg_types = {"this": True, "expression": True, "flag": False}
 
 
-class RegexpILike(Func):
+class RegexpILike(Binary, Func):
     arg_types = {"this": True, "expression": True, "flag": False}
 
 
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 8de76caf..e3409977 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -2952,6 +2952,7 @@ class Parser(metaclass=_Parser):
             cube = None
             totals = None
 
+            index = self._index
             with_ = self._match(TokenType.WITH)
             if self._match(TokenType.ROLLUP):
                 rollup = with_ or self._parse_wrapped_csv(self._parse_column)
@@ -2966,6 +2967,8 @@ class Parser(metaclass=_Parser):
                 elements["totals"] = True  # type: ignore
 
             if not (grouping_sets or rollup or cube or totals):
+                if with_:
+                    self._retreat(index)
                 break
 
         return self.expression(exp.Group, **elements)  # type: ignore


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 3cf95a78..da18c97c 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -138,6 +138,13 @@ class TestBigQuery(Validator):
         self.validate_all('x <> """"""', write={"bigquery": "x <> ''"})
         self.validate_all("x <> ''''''", write={"bigquery": "x <> ''"})
         self.validate_all("CAST(x AS DATETIME)", read={"": "x::timestamp"})
+        self.validate_all(
+            "NULL",
+            read={
+                "duckdb": "NULL = a",
+                "postgres": "a = NULL",
+            },
+        )
         self.validate_all(
             "SELECT '\\n'",
             read={
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 948c00e1..fa04da3f 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -85,6 +85,18 @@ class TestClickhouse(Validator):
             "CREATE MATERIALIZED VIEW test_view (id UInt8) TO db.table1 AS SELECT * FROM test_data"
         )
 
+        self.validate_all(
+            "SELECT match('ThOmAs', CONCAT('(?i)', 'thomas'))",
+            read={
+                "postgres": "SELECT 'ThOmAs' ~* 'thomas'",
+            },
+        )
+        self.validate_all(
+            "SELECT match('ThOmAs', CONCAT('(?i)', x)) FROM t",
+            read={
+                "postgres": "SELECT 'ThOmAs' ~* x FROM t",
+            },
+        )
         self.validate_all(
             "SELECT '\\0'",
             read={
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 22bede4b..3121cb08 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -732,3 +732,8 @@ class TestPostgres(Validator):
         self.validate_all(
             "VAR_POP(x)", read={"": "VARIANCE_POP(x)"}, write={"postgres": "VAR_POP(x)"}
         )
+
+    def test_regexp_binary(self):
+        """See https://github.com/tobymao/sqlglot/pull/2404 for details."""
+        self.assertIsInstance(parse_one("'thomas' ~ '.*thomas.*'", read="postgres"), exp.Binary)
+        self.assertIsInstance(parse_one("'thomas' ~* '.*thomas.*'", read="postgres"), exp.Binary)
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index f182febf..83986e59 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -6,6 +6,9 @@ class TestRedshift(Validator):
     dialect = "redshift"
 
     def test_redshift(self):
+        self.validate_identity(
+            "CREATE OR REPLACE VIEW v1 AS SELECT id, AVG(average_metric1) AS m1, AVG(average_metric2) AS m2 FROM t GROUP BY id WITH NO SCHEMA BINDING"
+        )
         self.validate_all(
             "SELECT APPROXIMATE COUNT(DISTINCT y)",
             read={