Repository: tobymao/sqlglot

Issue:
Error when parsing position function call
```
In [6]: import sqlglot as sg

In [7]: print(sg.__version__)
6.2.10

In [8]: sg.parse_one("position('a_b_c', '_') > 0")

...

File /nix/store/9cfygcnsr7d5mw8a4dxrmf0xjfl0qnwl-python3-3.10.6-env/lib/python3.10/site-packages/sqlglot/parser.py:2033, in Parser._parse_position(self)
   2031 if self._match(TokenType.IN):
   2032     string = self._parse_bitwise()
-> 2033 return self.expression(exp.StrPosition, this=string, substr=substr)

UnboundLocalError: local variable 'string' referenced before assignment
```

Discussion:

Patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 34b6df14..6a141a1a 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -1,10 +1,16 @@
 from sqlglot import exp
 from sqlglot.dialects.dialect import Dialect, inline_array_sql, var_map_sql
 from sqlglot.generator import Generator
+from sqlglot.helper import csv
 from sqlglot.parser import Parser, parse_var_map
 from sqlglot.tokens import Tokenizer, TokenType
 
 
+def _lower_func(sql):
+    index = sql.index("(")
+    return sql[:index].lower() + sql[index:]
+
+
 class ClickHouse(Dialect):
     normalize_functions = None
     null_ordering = "nulls_are_last"
@@ -51,9 +57,10 @@ class ClickHouse(Dialect):
         TRANSFORMS = {
             **Generator.TRANSFORMS,
             exp.Array: inline_array_sql,
+            exp.StrPosition: lambda self, e: f"position({csv(self.sql(e, 'this'), self.sql(e, 'substr'), self.sql(e, 'position'))})",
             exp.Final: lambda self, e: f"{self.sql(e, 'this')} FINAL",
-            exp.Map: var_map_sql,
-            exp.VarMap: var_map_sql,
+            exp.Map: lambda self, e: _lower_func(var_map_sql(self, e)),
+            exp.VarMap: lambda self, e: _lower_func(var_map_sql(self, e)),
         }
 
         EXPLICIT_UNION = True
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index fb2d9006..770240af 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -164,6 +164,7 @@ class Snowflake(Dialect):
             exp.StrToTime: lambda self, e: f"TO_TIMESTAMP({self.sql(e, 'this')}, {self.format_time(e)})",
             exp.UnixToTime: _unix_to_time,
             exp.Array: inline_array_sql,
+            exp.StrPosition: rename_func("POSITION"),
             exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'value')}",
         }
 
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 256f45ad..bc6beaa1 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2459,7 +2459,7 @@ class Substring(Func):
 
 
 class StrPosition(Func):
-    arg_types = {"this": True, "substr": True, "position": False}
+    arg_types = {"substr": True, "this": True, "position": False}
 
 
 class StrToDate(Func):
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index c74532f1..1323ae04 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -2044,10 +2044,16 @@ class Parser:
         return self.expression(exp.Cast, this=this, to=to)
 
     def _parse_position(self):
-        substr = self._parse_bitwise()
+        args = self._parse_csv(self._parse_bitwise)
+
         if self._match(TokenType.IN):
-            string = self._parse_bitwise()
-        return self.expression(exp.StrPosition, this=string, substr=substr)
+            args.append(self._parse_bitwise())
+
+        # Note: we're parsing in order needle, haystack, position
+        this = exp.StrPosition.from_arg_list(args)
+        self.validate_expression(this, args)
+
+        return this
 
     def _parse_substring(self):
         # Postgres supports the form: substring(string [from int] [for int])


Test patch:
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 6d7578d6..b103ecda 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -858,15 +858,28 @@ class TestDialect(Validator):
                 "postgres": "STRPOS(x, ' ')",
                 "presto": "STRPOS(x, ' ')",
                 "spark": "LOCATE(' ', x)",
+                "clickhouse": "position(x, ' ')",
+                "snowflake": "POSITION(' ', x)",
             },
         )
         self.validate_all(
-            "STR_POSITION(x, 'a')",
+            "STR_POSITION('a', x)",
             write={
                 "duckdb": "STRPOS(x, 'a')",
                 "postgres": "STRPOS(x, 'a')",
                 "presto": "STRPOS(x, 'a')",
                 "spark": "LOCATE('a', x)",
+                "clickhouse": "position(x, 'a')",
+                "snowflake": "POSITION('a', x)",
+            },
+        )
+        self.validate_all(
+            "POSITION('a', x, 3)",
+            write={
+                "presto": "STRPOS(SUBSTR(x, 3), 'a') + 3 - 1",
+                "spark": "LOCATE('a', x, 3)",
+                "clickhouse": "position(x, 'a', 3)",
+                "snowflake": "POSITION('a', x, 3)",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index 4e8b4af5..acb3be9a 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -452,7 +452,7 @@ class TestHive(Validator):
             "MAP(a, b, c, d)",
             read={
                 "": "VAR_MAP(a, b, c, d)",
-                "clickhouse": "MAP(a, b, c, d)",
+                "clickhouse": "map(a, b, c, d)",
                 "duckdb": "MAP(LIST_VALUE(a, c), LIST_VALUE(b, d))",
                 "hive": "MAP(a, b, c, d)",
                 "presto": "MAP(ARRAY[a, c], ARRAY[b, d])",
@@ -460,7 +460,7 @@ class TestHive(Validator):
             },
             write={
                 "": "MAP(ARRAY(a, c), ARRAY(b, d))",
-                "clickhouse": "MAP(a, b, c, d)",
+                "clickhouse": "map(a, b, c, d)",
                 "duckdb": "MAP(LIST_VALUE(a, c), LIST_VALUE(b, d))",
                 "presto": "MAP(ARRAY[a, c], ARRAY[b, d])",
                 "hive": "MAP(a, b, c, d)",