Repository: tobymao/sqlglot
Pull request: feat!: Support MEDIAN() function
Fixes #4315

This PR adds support for `MEDIAN(<expr>)` across major dialects (DuckDB, Spark3, Databricks, Snowflake, Redshift, Clickhouse, Oracle). For the dialects that don't support it, the transpilation remains as `PERCENTILE_CONT(<expr>, 0.5)` which is a best-effort attempt.

Note: BigQuery supports `PERCENTILE_CONT(...)` only as a window function

Docs
---------
- [Oracle MEDIAN](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/MEDIAN.html) | [DuckDB](https://duckdb.org/docs/sql/functions/aggregates.html#medianx) | [Clickhouse MEDIAN/QUANTILE](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/quantile#quantile) | [Redshift MEDIAN](https://docs.aws.amazon.com/redshift/latest/dg/r_MEDIAN.html) | [Spark/Databricks MEDIAN](https://docs.databricks.com/en/sql/language-manual/functions/median.html) | [Snowflake MEDIAN](https://docs.snowflake.com/en/sql-reference/functions/median) 

- [BigQuery PERCENTILE_CONT](https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions#percentile_cont) | [Postgres PERCENTILE_CONT](https://www.postgresql.org/docs/9.4/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE)



Problem statement:
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);
               ^
```

Hints from issue discussion:
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)'
```
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.
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.
Yep, makes sense.

Commit message: feat: Add support for MEDIAN() function

Patch to review:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 7d86075d12..d3700066be 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(),
         }
 
@@ -950,6 +951,9 @@ class Generator(generator.Generator):
             exp.Map: lambda self, e: _lower_func(var_map_sql(self, e)),
             exp.Nullif: rename_func("nullIf"),
             exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
+            exp.PercentileCont: lambda self, e: self.sql(
+                exp.Quantile(this=e.this, quantile=e.expression)
+            ),
             exp.Pivot: no_pivot_sql,
             exp.Quantile: _quantile_sql,
             exp.RegexpLike: lambda self, e: self.func("match", e.this, e.expression),
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 391d4603be..ceb68bc185 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,
@@ -494,6 +491,7 @@ class Generator(generator.Generator):
         STAR_EXCEPT = "EXCLUDE"
         PAD_FILL_PATTERN_IS_REQUIRED = True
         ARRAY_CONCAT_IS_VAR_LEN = False
+        SUPPORTS_MEDIAN = True
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 982907d077..561a9e590a 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -324,6 +324,7 @@ class Generator(generator.Generator):
         SUPPORTS_SELECT_INTO = True
         TZ_TO_WITH_TIME_ZONE = True
         QUERY_HINT_SEP = " "
+        SUPPORTS_MEDIAN = True
 
         TYPE_MAPPING = {
             **generator.Generator.TYPE_MAPPING,
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 ea5680d963..23daab6856 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/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..c0e30b55c6 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 = False
+
     # 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)
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..4567a8ada0 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -2984,3 +2984,36 @@ def test_escaped_identifier_delimiter(self):
                         "tsql": 'SELECT 1 AS [x"]',
                     },
                 )
+
+    def test_median(self):
+        for suffix in (
+            "",
+            " OVER ()",
+        ):
+            self.validate_all(
+                f"PERCENTILE_CONT(x, 0.5){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={
+                    "": f"PERCENTILE_CONT(x, 0.5){suffix}",
+                },
+            )
+            self.validate_all(
+                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"quantile(0.5)(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_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",


Code review comments:
diff_hunk: @@ -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 = False
path: sqlglot/generator.py
text: @user1:
Let's flip this and only set it to false for those that don't support it, so the base dialect includes it.