Repository: tobymao/sqlglot

Issue:
Support special syntax of limit & offset for ClickHouse
Hello guys! How are you?

Current version of apache/superset generates sql with `LIMIT 10, 20` instead of `LIMIT 10 OFFSET 20`

```python
from sqlglot import parse_one, Dialects

sql = "SELECT * FROM data LIMIT 10, 20"

print(parse_one(sql, read=Dialects.CLICKHOUSE))
```

We got the such error:
```
sqlglot.errors.ParseError: Expected table name. Line 2, Col: 30.
  
SELECT * FROM data LIMIT 10, 20
```

Can we try to support it?

Discussion:

Patch:
diff --git a/sqlglot/dataframe/sql/functions.py b/sqlglot/dataframe/sql/functions.py
index 7423c650..bc002e5b 100644
--- a/sqlglot/dataframe/sql/functions.py
+++ b/sqlglot/dataframe/sql/functions.py
@@ -807,7 +807,7 @@ def rpad(col: ColumnOrName, len: int, pad: str) -> Column:
 
 
 def repeat(col: ColumnOrName, n: int) -> Column:
-    return Column.invoke_anonymous_function(col, "REPEAT", n)
+    return Column.invoke_expression_over_column(col, glotexp.Repeat, times=lit(n))
 
 
 def split(str: ColumnOrName, pattern: str, limit: t.Optional[int] = None) -> Column:
diff --git a/sqlglot/dialects/spark.py b/sqlglot/dialects/spark.py
index b750b903..572f411b 100644
--- a/sqlglot/dialects/spark.py
+++ b/sqlglot/dialects/spark.py
@@ -71,6 +71,7 @@ class Spark(Hive):
                 length=list_get(args, 1),
             ),
             "APPROX_PERCENTILE": exp.ApproxQuantile.from_arg_list,
+            "IIF": exp.If.from_arg_list,
         }
 
         FUNCTION_PARSERS = {
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index 4baed696..0143e601 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -149,6 +149,10 @@ class TSQL(Dialect):
             "DATENAME": tsql_format_time_lambda(exp.TimeToStr, full_format_mapping=True),
             "DATEPART": tsql_format_time_lambda(exp.TimeToStr),
             "GETDATE": exp.CurrentDate.from_arg_list,
+            "IIF": exp.If.from_arg_list,
+            "LEN": exp.Length.from_arg_list,
+            "REPLICATE": exp.Repeat.from_arg_list,
+            "JSON_VALUE": exp.JSONExtractScalar.from_arg_list,
         }
 
         VAR_LENGTH_DATATYPES = {
@@ -207,4 +211,5 @@ class TSQL(Dialect):
             **Generator.TRANSFORMS,
             exp.DateAdd: lambda self, e: f"DATEADD({self.format_args(e.args.get('unit'), e.expression, e.this)})",
             exp.CurrentDate: rename_func("GETDATE"),
+            exp.If: rename_func("IIF"),
         }
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 147e42b8..bbf0e9bd 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2574,6 +2574,10 @@ class RegexpSplit(Func):
     arg_types = {"this": True, "expression": True}
 
 
+class Repeat(Func):
+    arg_types = {"this": True, "times": True}
+
+
 class Round(Func):
     arg_types = {"this": True, "decimals": False}
 
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 44dfb8a3..b94313a8 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1513,7 +1513,7 @@ class Parser:
         return this
 
     def _parse_offset(self, this=None):
-        if not self._match(TokenType.OFFSET):
+        if not self._match_set((TokenType.OFFSET, TokenType.COMMA)):
             return this
         count = self._parse_number()
         self._match_set((TokenType.ROW, TokenType.ROWS))


Test patch:
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 8659d29b..877b1e49 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -989,6 +989,7 @@ class TestDialect(Validator):
         )
 
     def test_limit(self):
+        self.validate_all("SELECT * FROM data LIMIT 10, 20", write={"sqlite": "SELECT * FROM data LIMIT 10 OFFSET 20"})
         self.validate_all(
             "SELECT x FROM y LIMIT 10",
             write={
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 339d1a61..8605bd1c 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -282,3 +282,6 @@ TBLPROPERTIES (
                 "spark": "SELECT ARRAY_SORT(x)",
             },
         )
+
+    def test_iif(self):
+        self.validate_all("SELECT IIF(cond, 'True', 'False')", write={"spark": "SELECT IF(cond, 'True', 'False')"})
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index d494ec5b..60c41bfe 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -71,6 +71,23 @@ class TestTSQL(Validator):
                 "spark": "LOCATE('sub', 'testsubstring')",
             },
         )
+
+    def test_len(self):
+        self.validate_all("LEN(x)", write={"spark": "LENGTH(x)"})
+
+    def test_replicate(self):
+        self.validate_all("REPLICATE('x', 2)", write={"spark": "REPEAT('x', 2)"})
+
+    def test_isnull(self):
+        self.validate_all("ISNULL(x, y)", write={"spark": "COALESCE(x, y)"})
+
+    def test_jsonvalue(self):
+        self.validate_all(
+            "JSON_VALUE(r.JSON, '$.Attr_INT')",
+            write={"spark": "GET_JSON_OBJECT(r.JSON, '$.Attr_INT')"},
+        )
+
+    def test_datefromparts(self):
         self.validate_all(
             "SELECT DATEFROMPARTS('2020', 10, 01)",
             write={"spark": "SELECT MAKE_DATE('2020', 10, 01)"},
@@ -243,3 +260,12 @@ class TestTSQL(Validator):
                 "spark": "CAST(x AS INT)",
             },
         )
+
+    def test_iif(self):
+        self.validate_identity("SELECT IIF(cond, 'True', 'False')")
+        self.validate_all(
+            "SELECT IIF(cond, 'True', 'False');",
+            write={
+                "spark": "SELECT IF(cond, 'True', 'False')",
+            },
+        )