Repository: tobymao/sqlglot

Issue:
ex.convert(b"\x00\x00\x00\x00\x00\x00\x07\xd3") throws, but should be HexString
The behavior of binary literal's is a bit weird currently:

```python
import sqlglot.expressions as ex
import sqlglot as sg

print(
    repr(sg.parse_one("select 0x00000000000007d3", dialect="tsql"))
)  # binary literal in SQL Server
print(
    sg.parse_one("select 0x00000000000007d3", dialect="tsql").sql("duckdb")
)  # SELECT 2003 --> it's an int in duckdb, a bit unfortunate

import duckdb

duckdb.execute(
    "select '0x00000000000007d3'::blob"
).fetchall()  # correctly gives [(b'0x00000000000007d3',)]

ex.convert(
    b"\x00\x00\x00\x00\x00\x00\x07\xd3"
)  # should be ex.HexString(this="0x00000000000007d3"), I guess
# currently throws

```

Maybe a dedicated BinaryLiteral Type would be good? Not sure how to solve this :)


Discussion:

Patch:
diff --git a/sqlglot/dataframe/sql/functions.py b/sqlglot/dataframe/sql/functions.py
index ca117e13..b4dd2c6d 100644
--- a/sqlglot/dataframe/sql/functions.py
+++ b/sqlglot/dataframe/sql/functions.py
@@ -356,15 +356,15 @@ def coalesce(*cols: ColumnOrName) -> Column:
 
 
 def corr(col1: ColumnOrName, col2: ColumnOrName) -> Column:
-    return Column.invoke_anonymous_function(col1, "CORR", col2)
+    return Column.invoke_expression_over_column(col1, expression.Corr, expression=col2)
 
 
 def covar_pop(col1: ColumnOrName, col2: ColumnOrName) -> Column:
-    return Column.invoke_anonymous_function(col1, "COVAR_POP", col2)
+    return Column.invoke_expression_over_column(col1, expression.CovarPop, expression=col2)
 
 
 def covar_samp(col1: ColumnOrName, col2: ColumnOrName) -> Column:
-    return Column.invoke_anonymous_function(col1, "COVAR_SAMP", col2)
+    return Column.invoke_expression_over_column(col1, expression.CovarSamp, expression=col2)
 
 
 def first(col: ColumnOrName, ignorenulls: t.Optional[bool] = None) -> Column:
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index c13c15ab..a138eb00 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -368,6 +368,9 @@ class Snowflake(Dialect):
                 this=seq_get(args, 0), unit=_map_date_part(seq_get(args, 1))
             ),
             "LISTAGG": exp.GroupConcat.from_arg_list,
+            "MEDIAN": lambda args: exp.PercentileCont(
+                this=seq_get(args, 0), expression=exp.Literal.number(0.5)
+            ),
             "NULLIFZERO": _build_if_from_nullifzero,
             "OBJECT_CONSTRUCT": _build_object_construct,
             "REGEXP_REPLACE": _build_regexp_replace,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 883ab41a..da206544 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5745,6 +5745,10 @@ class Upper(Func):
     _sql_names = ["UPPER", "UCASE"]
 
 
+class Corr(Binary, AggFunc):
+    pass
+
+
 class Variance(AggFunc):
     _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
 
@@ -5753,6 +5757,14 @@ class VariancePop(AggFunc):
     _sql_names = ["VARIANCE_POP", "VAR_POP"]
 
 
+class CovarSamp(Binary, AggFunc):
+    pass
+
+
+class CovarPop(Binary, AggFunc):
+    pass
+
+
 class Week(Func):
     arg_types = {"this": True, "mode": False}
 
@@ -6958,6 +6970,8 @@ def convert(value: t.Any, copy: bool = False) -> Expression:
         return null()
     if isinstance(value, numbers.Number):
         return Literal.number(value)
+    if isinstance(value, bytes):
+        return HexString(this=value.hex())
     if isinstance(value, datetime.datetime):
         datetime_literal = Literal.string(
             (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()


Test patch:
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 4d7d97c5..495159c9 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -435,6 +435,46 @@ WHERE
                 "sqlite": "SELECT MIN(c1), MIN(c2) FROM test",
             },
         )
+        for suffix in (
+            "",
+            " OVER ()",
+        ):
+            self.validate_all(
+                f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+                read={
+                    "snowflake": f"SELECT MEDIAN(x){suffix}",
+                    "postgres": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+                },
+                write={
+                    "": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x NULLS LAST){suffix}",
+                    "duckdb": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+                    "postgres": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+                    "snowflake": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+                },
+            )
+            self.validate_all(
+                f"SELECT MEDIAN(x){suffix}",
+                write={
+                    "": f"SELECT PERCENTILE_CONT(x, 0.5){suffix}",
+                    "duckdb": f"SELECT QUANTILE_CONT(x, 0.5){suffix}",
+                    "postgres": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+                    "snowflake": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+                },
+            )
+            for func in (
+                "CORR",
+                "COVAR_POP",
+                "COVAR_SAMP",
+            ):
+                self.validate_all(
+                    f"SELECT {func}(y, x){suffix}",
+                    write={
+                        "": f"SELECT {func}(y, x){suffix}",
+                        "duckdb": f"SELECT {func}(y, x){suffix}",
+                        "postgres": f"SELECT {func}(y, x){suffix}",
+                        "snowflake": f"SELECT {func}(y, x){suffix}",
+                    },
+                )
         self.validate_all(
             "TO_CHAR(x, y)",
             read={
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index 54bc223c..9365fc4e 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -802,6 +802,7 @@ class TestExpressions(unittest.TestCase):
             ),
             (datetime.date(2022, 10, 1), "DATE_STR_TO_DATE('2022-10-01')"),
             (math.nan, "NULL"),
+            (b"\x00\x00\x00\x00\x00\x00\x07\xd3", "2003"),
         ]:
             with self.subTest(value):
                 self.assertEqual(exp.convert(value).sql(), expected)