Feat(presto): wrap md5 string arguments in to_utf8 (#3732)

georgesittas · web-flow · commit 820d66430bb2 · 2024-07-03T18:53:21.000+03:00
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
@@ -478,9 +478,6 @@ class Generator(generator.Generator):
                 [transforms.remove_within_group_for_percentiles]
             ),
             exp.Xor: bool_xor_sql,
-            exp.MD5: lambda self, e: self.func(
-                "LOWER", self.func("TO_HEX", self.func("MD5", self.sql(e, "this")))
-            ),
             exp.MD5Digest: rename_func("MD5"),
             exp.SHA: rename_func("SHA1"),
             exp.SHA2: sha256_sql,
@@ -547,6 +544,19 @@ class Generator(generator.Generator):
             "with",
         }
 
+        def md5_sql(self, expression: exp.MD5) -> str:
+            this = expression.this
+
+            if not this.type:
+                from sqlglot.optimizer.annotate_types import annotate_types
+
+                this = annotate_types(this)
+
+            if this.is_type(*exp.DataType.TEXT_TYPES):
+                this = exp.Encode(this=this, charset=exp.Literal.string("utf-8"))
+
+            return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this))))
+
         def strtounix_sql(self, expression: exp.StrToUnix) -> str:
             # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one.
             # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
@@ -581,6 +581,13 @@ def test_unicode_string(self):
             )
 
     def test_presto(self):
+        self.assertEqual(
+            exp.func("md5", exp.func("concat", exp.cast("x", "text"), exp.Literal.string("s"))).sql(
+                dialect="presto"
+            ),
+            "LOWER(TO_HEX(MD5(TO_UTF8(CONCAT(CAST(x AS VARCHAR), CAST('s' AS VARCHAR))))))",
+        )
+
         with self.assertLogs(helper_logger):
             self.validate_all(
                 "SELECT COALESCE(ELEMENT_AT(MAP_FROM_ENTRIES(ARRAY[(51, '1')]), id), quantity) FROM my_table",
