Repository: tobymao/sqlglot

Issue:
Incorrect StrPosition Code Generation for SQLite
Hi,

Sqlglot parses string position functions into `exp.StrPosition` expressions that can be compiled to target dialects via `sql()`. I believe that both parsing and generation does not work for SQLite. The code assumes SQLite's string position function is `STR_POSITION(...)`, but in reality it is `INSTR(...)`. Is this the expected behavior?

MVE:
```python
import sqlglot

x = parse_one("select instr('asd', 's')", read='sqlite')
print(repr(x)) # parsed as Anonymous

x = parse_one("select str_position('asd', 's')", read='sqlite')
print(repr(x)) # parsed as StrPosition

x = parse_one("select instr('asd', 's')", read='mysql')
print(repr(x)) # parsed as StrPosition

print(x.sql(dialect='sqlite')) # compiled as STR_POSITION
```

Fix:
Adding `INSTR` to SQLite's [function list](https://github.com/tobymao/sqlglot/blob/9eba00dca517efe7df171b09ed916af3ea5e350d/sqlglot/dialects/sqlite.py#L113) just like it is done for [MySQL](https://github.com/tobymao/sqlglot/blob/9eba00dca517efe7df171b09ed916af3ea5e350d/sqlglot/dialects/mysql.py#L303C14-L303C19) should do the trick.

Discussion:
@georgesittas: This transpilation path hasn't been covered yet, so I'd say it's "expected" as in "missing functionality". We welcome PRs if you wanna take a stab at it. Your suggestion makes sense 👍@pruzko: I made a [PR](https://github.com/tobymao/sqlglot/pull/4198).

Also, I'm new to the library and it's absolutely amazing.

Solution patch:
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 1d271f306d..f32f6945d4 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -1052,7 +1052,10 @@ def property_sql(self: Generator, expression: exp.Property) -> str:
 
 
 def str_position_sql(
-    self: Generator, expression: exp.StrPosition, generate_instance: bool = False
+    self: Generator,
+    expression: exp.StrPosition,
+    generate_instance: bool = False,
+    str_position_func_name: str = "STRPOS",
 ) -> str:
     this = self.sql(expression, "this")
     substr = self.sql(expression, "substr")
@@ -1065,7 +1068,7 @@ def str_position_sql(
         this = self.func("SUBSTR", this, position)
         position_offset = f" + {position} - 1"
 
-    return self.func("STRPOS", this, substr, instance) + position_offset
+    return self.func(str_position_func_name, this, substr, instance) + position_offset
 
 
 def struct_extract_sql(self: Generator, expression: exp.StructExtract) -> str:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index ff9042042b..8930735ca3 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -300,7 +300,6 @@ class Parser(parser.Parser):
             "DAYOFMONTH": lambda args: exp.DayOfMonth(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
             "DAYOFWEEK": lambda args: exp.DayOfWeek(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
             "DAYOFYEAR": lambda args: exp.DayOfYear(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
-            "INSTR": lambda args: exp.StrPosition(substr=seq_get(args, 1), this=seq_get(args, 0)),
             "FROM_UNIXTIME": build_formatted_time(exp.UnixToTime, "mysql"),
             "ISNULL": isnull_to_is_null,
             "LOCATE": locate_to_strposition,
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index aa8df3e535..65f640d3a3 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -15,6 +15,7 @@
     no_tablesample_sql,
     no_trycast_sql,
     rename_func,
+    str_position_sql,
 )
 from sqlglot.tokens import TokenType
 
@@ -186,6 +187,7 @@ class Generator(generator.Generator):
                     transforms.eliminate_semi_and_anti_joins,
                 ]
             ),
+            exp.StrPosition: lambda self, e: str_position_sql(self, e, str_position_func_name="INSTR"),
             exp.TableSample: no_tablesample_sql,
             exp.TimeStrToTime: lambda self, e: self.sql(e, "this"),
             exp.TimeToStr: lambda self, e: self.func("STRFTIME", e.args.get("format"), e.this),
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 5f73496730..6060377c79 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -212,6 +212,7 @@ class Parser(metaclass=_Parser):
         ),
         "GLOB": lambda args: exp.Glob(this=seq_get(args, 1), expression=seq_get(args, 0)),
         "HEX": build_hex,
+        "INSTR": lambda args: exp.StrPosition(this=seq_get(args, 0), substr=seq_get(args, 1)),
         "JSON_EXTRACT": build_extract_json_with_path(exp.JSONExtract),
         "JSON_EXTRACT_SCALAR": build_extract_json_with_path(exp.JSONExtractScalar),
         "JSON_EXTRACT_PATH_TEXT": build_extract_json_with_path(exp.JSONExtractScalar),


Test patch:
diff --git a/tests/dialects/test_sqlite.py b/tests/dialects/test_sqlite.py
index f2c98029b7..230c0e8fea 100644
--- a/tests/dialects/test_sqlite.py
+++ b/tests/dialects/test_sqlite.py
@@ -26,6 +26,7 @@ def test_sqlite(self):
             """SELECT item AS "item", some AS "some" FROM data WHERE (item = 'value_1' COLLATE NOCASE) AND (some = 't' COLLATE NOCASE) ORDER BY item ASC LIMIT 1 OFFSET 0"""
         )
         self.validate_identity("SELECT * FROM GENERATE_SERIES(1, 5)")
+        self.validate_identity("SELECT INSTR(haystack, needle)")
 
         self.validate_all("SELECT LIKE(y, x)", write={"sqlite": "SELECT x LIKE y"})
         self.validate_all("SELECT GLOB('*y*', 'xyz')", write={"sqlite": "SELECT 'xyz' GLOB '*y*'"})
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index e88740b85e..1c88952b94 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -350,6 +350,7 @@ def test_function_building(self):
         )
 
         self.assertIsInstance(exp.func("instr", "x", "b", dialect="mysql"), exp.StrPosition)
+        self.assertIsInstance(exp.func("instr", "x", "b", dialect="sqlite"), exp.StrPosition)
         self.assertIsInstance(exp.func("bla", 1, "foo"), exp.Anonymous)
         self.assertIsInstance(
             exp.func("cast", this=exp.Literal.number(5), to=exp.DataType.build("DOUBLE")),