Repository: tobymao/sqlglot

Issue:
duckdb `median` parses as `percentile_cont` which produces `quantile_cont`, making some median calls invalid
DuckDB `median` is not equivalent to `quantile_cont`.

`median` allows string inputs, while `quantile_cont` does not:

```
v1.1.2 f680b7d08f
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D select median('a');
┌─────────────┐
│ median('a') │
│   varchar   │
├─────────────┤
│ a           │
└─────────────┘
D select quantile_cont('a', 0.5);
Binder Error: No function matches the given name and argument types 'quantile_cont(VARCHAR, DECIMAL(2,1))'. You might need to add explicit type casts.
        Candidate functions:
        quantile_cont(DECIMAL, DOUBLE) -> DECIMAL
        quantile_cont(DECIMAL, DOUBLE[]) -> DECIMAL
        quantile_cont(TINYINT, DOUBLE) -> TINYINT
        quantile_cont(TINYINT, DOUBLE[]) -> TINYINT
        quantile_cont(SMALLINT, DOUBLE) -> SMALLINT
        quantile_cont(SMALLINT, DOUBLE[]) -> SMALLINT
        quantile_cont(INTEGER, DOUBLE) -> INTEGER
        quantile_cont(INTEGER, DOUBLE[]) -> INTEGER
        quantile_cont(BIGINT, DOUBLE) -> BIGINT
        quantile_cont(BIGINT, DOUBLE[]) -> BIGINT
        quantile_cont(HUGEINT, DOUBLE) -> HUGEINT
        quantile_cont(HUGEINT, DOUBLE[]) -> HUGEINT
        quantile_cont(FLOAT, DOUBLE) -> FLOAT
        quantile_cont(FLOAT, DOUBLE[]) -> FLOAT
        quantile_cont(DOUBLE, DOUBLE) -> DOUBLE
        quantile_cont(DOUBLE, DOUBLE[]) -> DOUBLE
        quantile_cont(DATE, DOUBLE) -> DATE
        quantile_cont(DATE, DOUBLE[]) -> DATE
        quantile_cont(TIMESTAMP, DOUBLE) -> TIMESTAMP
        quantile_cont(TIMESTAMP, DOUBLE[]) -> TIMESTAMP
        quantile_cont(TIME, DOUBLE) -> TIME
        quantile_cont(TIME, DOUBLE[]) -> TIME
        quantile_cont(TIMESTAMP WITH TIME ZONE, DOUBLE) -> TIMESTAMP WITH TIME ZONE
        quantile_cont(TIMESTAMP WITH TIME ZONE, DOUBLE[]) -> TIMESTAMP WITH TIME ZONE
        quantile_cont(TIME WITH TIME ZONE, DOUBLE) -> TIME WITH TIME ZONE
        quantile_cont(TIME WITH TIME ZONE, DOUBLE[]) -> TIME WITH TIME ZONE

LINE 1: select quantile_cont('a', 0.5);
               ^
```

Discussion:
@cpcloud: Here's the sqlglot parsing and emitting:

```
In [1]: import sqlglot as sg

In [2]: sg.parse_one("select median(x)", read="duckdb").sql('duckdb')
Out[2]: 'SELECT QUANTILE_CONT(x, 0.5)'
```@georgesittas: For context, I think this was based on DuckDB's [docs](https://duckdb.org/docs/sql/functions/aggregates.html#medianx) for `median(x)`. Will need to investigate a bit to understand what's happening.@cpcloud: The issue is that continuous quantiles don't make sense for string inputs, and thus aren't defined.

DuckDB's median isn't strictly defined as `quantile_cont(x, 0.5)` since it supports strings.@georgesittas: Yep, makes sense.

Solution patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 7d86075d12..fa86e3ac66 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -431,6 +431,7 @@ class Parser(parser.Parser):
             **parser.Parser.FUNCTION_PARSERS,
             "ARRAYJOIN": lambda self: self.expression(exp.Explode, this=self._parse_expression()),
             "QUANTILE": lambda self: self._parse_quantile(),
+            "MEDIAN": lambda self: self._parse_quantile(),
             "COLUMNS": lambda self: self._parse_columns(),
         }
 
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 391d4603be..a8006cca72 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -374,9 +374,6 @@ class Parser(parser.Parser):
             "LIST_VALUE": lambda args: exp.Array(expressions=args),
             "MAKE_TIME": exp.TimeFromParts.from_arg_list,
             "MAKE_TIMESTAMP": _build_make_timestamp,
-            "MEDIAN": lambda args: exp.PercentileCont(
-                this=seq_get(args, 0), expression=exp.Literal.number(0.5)
-            ),
             "QUANTILE_CONT": exp.PercentileCont.from_arg_list,
             "QUANTILE_DISC": exp.PercentileDisc.from_arg_list,
             "REGEXP_EXTRACT": build_regexp_extract,
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index db4c997286..af759d10c8 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -460,6 +460,7 @@ class Generator(generator.Generator):
         WITH_PROPERTIES_PREFIX = "TBLPROPERTIES"
         PARSE_JSON_NAME = None
         PAD_FILL_PATTERN_IS_REQUIRED = True
+        SUPPORTS_MEDIAN = False
 
         EXPRESSIONS_WITHOUT_NESTED_CTES = {
             exp.Insert,
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 2485e14ed2..1404f0fc32 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -704,6 +704,7 @@ class Generator(generator.Generator):
         PAD_FILL_PATTERN_IS_REQUIRED = True
         WRAP_DERIVED_VALUES = False
         VARCHAR_REQUIRES_SIZE = True
+        SUPPORTS_MEDIAN = False
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index a94bccbdb4..ef3bd3e09b 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -471,6 +471,7 @@ class Generator(generator.Generator):
         CAN_IMPLEMENT_ARRAY_ANY = True
         COPY_HAS_INTO_KEYWORD = False
         ARRAY_CONCAT_IS_VAR_LEN = False
+        SUPPORTS_MEDIAN = False
 
         SUPPORTED_JSON_PATH_PARTS = {
             exp.JSONPathKey,
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index 2173d3e5ee..6e4053a6d2 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -317,6 +317,7 @@ class Generator(generator.Generator):
         PARSE_JSON_NAME = "JSON_PARSE"
         PAD_FILL_PATTERN_IS_REQUIRED = True
         EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
+        SUPPORTS_MEDIAN = False
 
         PROPERTIES_LOCATION = {
             **generator.Generator.PROPERTIES_LOCATION,
diff --git a/sqlglot/dialects/redshift.py b/sqlglot/dialects/redshift.py
index 4462ea95a7..1430f1e63a 100644
--- a/sqlglot/dialects/redshift.py
+++ b/sqlglot/dialects/redshift.py
@@ -157,6 +157,7 @@ class Generator(Postgres.Generator):
         ARRAY_CONCAT_IS_VAR_LEN = False
         SUPPORTS_CONVERT_TIMEZONE = True
         EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
+        SUPPORTS_MEDIAN = True
 
         # Redshift doesn't have `WITH` as part of their with_properties so we remove it
         WITH_PROPERTIES_PREFIX = " "
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 8ed193dae5..0924fd5387 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -331,9 +331,6 @@ class Parser(parser.Parser):
             "LEN": lambda args: exp.Length(this=seq_get(args, 0), binary=True),
             "LENGTH": lambda args: exp.Length(this=seq_get(args, 0), binary=True),
             "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,
@@ -770,6 +767,7 @@ class Generator(generator.Generator):
         ARRAY_CONCAT_IS_VAR_LEN = False
         SUPPORTS_CONVERT_TIMEZONE = True
         EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
+        SUPPORTS_MEDIAN = True
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
diff --git a/sqlglot/dialects/spark.py b/sqlglot/dialects/spark.py
index d0ed525c12..6c4f194897 100644
--- a/sqlglot/dialects/spark.py
+++ b/sqlglot/dialects/spark.py
@@ -136,6 +136,7 @@ class Generator(Spark2.Generator):
         SUPPORTS_TO_NUMBER = True
         PAD_FILL_PATTERN_IS_REQUIRED = False
         SUPPORTS_CONVERT_TIMEZONE = True
+        SUPPORTS_MEDIAN = True
 
         TYPE_MAPPING = {
             **Spark2.Generator.TYPE_MAPPING,
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index 026988091d..68deeb062d 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -138,6 +138,7 @@ class Generator(generator.Generator):
         SUPPORTS_TABLE_ALIAS_COLUMNS = False
         SUPPORTS_TO_NUMBER = False
         EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
+        SUPPORTS_MEDIAN = False
 
         SUPPORTED_JSON_PATH_PARTS = {
             exp.JSONPathKey,
diff --git a/sqlglot/dialects/teradata.py b/sqlglot/dialects/teradata.py
index 6c7b4c5f67..f015c12227 100644
--- a/sqlglot/dialects/teradata.py
+++ b/sqlglot/dialects/teradata.py
@@ -71,6 +71,9 @@ class Teradata(Dialect):
     }
 
     class Tokenizer(tokens.Tokenizer):
+        # Tested each of these and they work, although there is no
+        # Teradata documentation explicitly mentioning them.
+        HEX_STRINGS = [("X'", "'"), ("x'", "'"), ("0x", "")]
         # https://docs.teradata.com/r/Teradata-Database-SQL-Functions-Operators-Expressions-and-Predicates/March-2017/Comparison-Operators-and-Functions/Comparison-Operators/ANSI-Compliance
         # https://docs.teradata.com/r/SQL-Functions-Operators-Expressions-and-Predicates/June-2017/Arithmetic-Trigonometric-Hyperbolic-Operators/Functions
         KEYWORDS = {
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 19dd979d41..9a9073a411 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -6135,6 +6135,10 @@ class MD5Digest(Func):
     _sql_names = ["MD5_DIGEST"]
 
 
+class Median(AggFunc):
+    pass
+
+
 class Min(AggFunc):
     arg_types = {"this": True, "expressions": False}
     is_var_len_args = True
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index e9ebf8fdd9..5456549331 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -433,6 +433,9 @@ class Generator(metaclass=_Generator):
     # Whether CONVERT_TIMEZONE() is supported; if not, it will be generated as exp.AtTimeZone
     SUPPORTS_CONVERT_TIMEZONE = False
 
+    # Whether MEDIAN(expr) is supported; if not, it will be generated as PERCENTILE_CONT(expr, 0.5)
+    SUPPORTS_MEDIAN = True
+
     # The name to generate for the JSONPath expression. If `None`, only `this` will be generated
     PARSE_JSON_NAME: t.Optional[str] = "PARSE_JSON"
 
@@ -4455,3 +4458,11 @@ def string_sql(self, expression: exp.String) -> str:
             )
 
         return self.sql(exp.cast(this, exp.DataType.Type.VARCHAR))
+
+    def median_sql(self, expression: exp.Median):
+        if not self.SUPPORTS_MEDIAN:
+            return self.sql(
+                exp.PercentileCont(this=expression.this, expression=exp.Literal.number(0.5))
+            )
+
+        return self.function_fallback_sql(expression)


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index d99a4ef4a1..b3a2ddcf50 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -428,8 +428,13 @@ def test_clickhouse(self):
         )
         self.validate_all(
             "SELECT quantile(0.5)(a)",
-            read={"duckdb": "SELECT quantile(a, 0.5)"},
-            write={"clickhouse": "SELECT quantile(0.5)(a)"},
+            read={
+                "duckdb": "SELECT quantile(a, 0.5)",
+                "clickhouse": "SELECT median(a)",
+            },
+            write={
+                "clickhouse": "SELECT quantile(0.5)(a)",
+            },
         )
         self.validate_all(
             "SELECT quantiles(0.5, 0.4)(a)",
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index e7533773f8..43644a0e41 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -2984,3 +2984,30 @@ def test_escaped_identifier_delimiter(self):
                         "tsql": 'SELECT 1 AS [x"]',
                     },
                 )
+
+    def test_median(self):
+        for suffix in (
+            "",
+            " OVER ()",
+        ):
+            self.validate_all(
+                f"MEDIAN(x){suffix}",
+                read={
+                    "snowflake": f"MEDIAN(x){suffix}",
+                    "duckdb": f"MEDIAN(x){suffix}",
+                    "spark": f"MEDIAN(x){suffix}",
+                    "databricks": f"MEDIAN(x){suffix}",
+                    "redshift": f"MEDIAN(x){suffix}",
+                    "oracle": f"MEDIAN(x){suffix}",
+                },
+                write={
+                    "snowflake": f"MEDIAN(x){suffix}",
+                    "duckdb": f"MEDIAN(x){suffix}",
+                    "spark": f"MEDIAN(x){suffix}",
+                    "databricks": f"MEDIAN(x){suffix}",
+                    "redshift": f"MEDIAN(x){suffix}",
+                    "oracle": f"MEDIAN(x){suffix}",
+                    "clickhouse": f"MEDIAN(x){suffix}",
+                    "postgres": f"PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+                },
+            )
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 1e65be6489..bc24a5e8f7 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -752,14 +752,6 @@ def test_duckdb(self):
                 "snowflake": "SELECT PERCENTILE_DISC(q) WITHIN GROUP (ORDER BY x) FROM t",
             },
         )
-        self.validate_all(
-            "SELECT MEDIAN(x) FROM t",
-            write={
-                "duckdb": "SELECT QUANTILE_CONT(x, 0.5) FROM t",
-                "postgres": "SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) FROM t",
-                "snowflake": "SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) FROM t",
-            },
-        )
 
         with self.assertRaises(UnsupportedError):
             transpile(
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index bd0d6c3a5a..52b04eae84 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -388,7 +388,7 @@ def test_hexadecimal_literal(self):
             "sqlite": "SELECT x'CC'",
             "starrocks": "SELECT x'CC'",
             "tableau": "SELECT 204",
-            "teradata": "SELECT 204",
+            "teradata": "SELECT X'CC'",
             "trino": "SELECT X'CC'",
             "tsql": "SELECT 0xCC",
         }
@@ -409,7 +409,7 @@ def test_hexadecimal_literal(self):
             "sqlite": "SELECT x'0000CC'",
             "starrocks": "SELECT x'0000CC'",
             "tableau": "SELECT 204",
-            "teradata": "SELECT 204",
+            "teradata": "SELECT X'0000CC'",
             "trino": "SELECT X'0000CC'",
             "tsql": "SELECT 0x0000CC",
         }
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 1effb27962..e0c67b2b55 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -524,7 +524,6 @@ def test_snowflake(self):
             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={
@@ -534,15 +533,6 @@ def test_snowflake(self):
                     "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",
diff --git a/tests/dialects/test_teradata.py b/tests/dialects/test_teradata.py
index 466f5d5877..8951ebe929 100644
--- a/tests/dialects/test_teradata.py
+++ b/tests/dialects/test_teradata.py
@@ -32,6 +32,10 @@ def test_teradata(self):
             },
         )
 
+        self.validate_identity("SELECT 0x1d", "SELECT X'1d'")
+        self.validate_identity("SELECT X'1D'", "SELECT X'1D'")
+        self.validate_identity("SELECT x'1d'", "SELECT X'1d'")
+
         self.validate_identity(
             "RENAME TABLE emp TO employee", check_command_warning=True
         ).assert_is(exp.Command)