Skip to content

Commit e0c1933

Browse files
feat(optimizer): annotate types for CovarPop/Samp, PercentileCont/Disc, and WithinGroup [CLAUDE] (tobymao#7656)
* feat(annotate_types): annotate CovarPop/Samp, PercentileCont/Disc, and WithinGroup Registers constant DOUBLE return types for COVAR_POP, COVAR_SAMP, and PERCENTILE_CONT. PERCENTILE_DISC is also registered as DOUBLE by default, but the new _annotate_within_group method overrides this when wrapped in a WITHIN GROUP clause: for PERCENTILE_DISC it reads the type from the ORDER BY sort expression (the engine picks an existing row, so the result type matches the sort column). PERCENTILE_CONT always interpolates and therefore always returns DOUBLE regardless of sort column type. [CLAUDE] Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com> * refactor(annotate_types): extract _type_from_order helper from _annotate_within_group [CLAUDE] Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com> * fix(annotate_types): fix return type of _annotate_within_group and add int-input covariance tests _set_type returns exp.Expr, not the subtype — separate the call from the return to satisfy mypy. Also add COVAR_POP/COVAR_SAMP tests with integer inputs to assert DOUBLE is returned regardless of input type. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com> * fix(annotate_types): PercentileDisc returns sort-expr type, not always DOUBLE Remove exp.PercentileDisc from the unconditional DOUBLE block — it picks an actual row value so its return type matches the sort expression. Add a DuckDB typing override to handle the bare QUANTILE_DISC(col, fraction) form, where `this` is the data column. Add tests covering int and bigint sort columns. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com> * fix(annotate_types): WithinGroup(PercentileDisc) falls back to UNKNOWN when sort type unavailable Previously fell through to _annotate_by_args(e, "this") which would propagate UNKNOWN from the PercentileDisc node itself — explicit is better. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com> * fix(annotate_types): remove spurious UNKNOWN filter in _type_from_order The annotator runs in a single pass so there is no later opportunity to resolve an UNKNOWN sort type. Filtering it out caused _annotate_within_group to set UNKNOWN anyway via the None fallback — with no benefit. Let UNKNOWN propagate naturally; None now means only that no order expression exists. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com> * style(annotate_types): remove redundant comments from _annotate_within_group Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com> * fix(annotate_types): WithinGroup always returns DOUBLE in Hive/Spark/Databricks Empirically verified against Databricks and confirmed via Spark docs examples: PERCENTILE_DISC returns DOUBLE even for integer inputs in these dialects, unlike Snowflake/BigQuery which return the sort expression's type. Override in hive.py propagates to Spark2, Spark, and Databricks via inheritance. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com> * refactor(annotate_types): inline _type_from_order into _annotate_within_group Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com> * test(annotate_functions): add DuckDB PERCENTILE_DISC cases Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com> * style(annotate_types): ruff format Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com> * fix(annotate_types): Hive/Spark WithinGroup annotates by `this` arg [CLAUDE] The Hive override hardcoded WithinGroup as DOUBLE, which silently mistyped non-percentile ordered-set aggregates (e.g. LISTAGG, MODE) that propagate their inner expression's type. Switch to annotate-by-`this` and register PercentileDisc as DOUBLE so the existing percentile cases still return DOUBLE in Spark/Hive/Databricks regardless of the sort-expr type. --------- Co-authored-by: Claude Sonnet 4.6 <noreply@anthropic.com>
1 parent 6084d34 commit e0c1933

5 files changed

Lines changed: 81 additions & 0 deletions

File tree

‎sqlglot/optimizer/annotate_types.py‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1029,6 +1029,16 @@ def _annotate_extract(self, expression: exp.Extract) -> exp.Extract:
10291029
self._set_type(expression, exp.DType.INT)
10301030
return expression
10311031

1032+
def _annotate_within_group(self, expression: exp.WithinGroup) -> exp.WithinGroup:
1033+
if isinstance(expression.this, exp.PercentileDisc):
1034+
order = expression.args.get("expression")
1035+
sort_type = (
1036+
order.expressions[0].this.type if order and order.expressions else exp.DType.UNKNOWN
1037+
)
1038+
self._set_type(expression, sort_type)
1039+
return expression
1040+
return self._annotate_by_args(expression, "this")
1041+
10321042
def _annotate_by_array_element(self, expression: exp.Expr) -> exp.Expr:
10331043
array_arg = expression.this
10341044
if array_arg.type.is_type(exp.DType.ARRAY):

‎sqlglot/typing/__init__.py‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -98,6 +98,8 @@
9898
exp.Asin,
9999
exp.Asinh,
100100
exp.Acos,
101+
exp.CovarPop,
102+
exp.CovarSamp,
101103
exp.Acosh,
102104
exp.ApproxQuantile,
103105
exp.Atan,
@@ -114,6 +116,7 @@
114116
exp.Log,
115117
exp.Pi,
116118
exp.Pow,
119+
exp.PercentileCont,
117120
exp.Quantile,
118121
exp.Radians,
119122
exp.Round,
@@ -369,5 +372,6 @@
369372
},
370373
exp.ToMap: {"annotator": lambda self, e: self._annotate_to_map(e)},
371374
exp.Unnest: {"annotator": lambda self, e: self._annotate_unnest(e)},
375+
exp.WithinGroup: {"annotator": lambda self, e: self._annotate_within_group(e)},
372376
exp.Subquery: {"annotator": lambda self, e: self._annotate_subquery(e)},
373377
}

‎sqlglot/typing/duckdb.py‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -49,6 +49,7 @@
4949
}
5050
},
5151
exp.DateBin: {"annotator": lambda self, e: self._annotate_by_args(e, "expression")},
52+
exp.PercentileDisc: {"annotator": lambda self, e: self._annotate_by_args(e, "this")},
5253
exp.Localtimestamp: {"returns": exp.DType.TIMESTAMP},
5354
exp.ToDays: {"returns": exp.DType.INTERVAL},
5455
exp.TimeFromParts: {"returns": exp.DType.TIME},

‎sqlglot/typing/hive.py‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -52,6 +52,7 @@
5252
exp.Second,
5353
}
5454
},
55+
exp.PercentileDisc: {"returns": exp.DType.DOUBLE},
5556
**{
5657
expr_type: {"annotator": lambda self, e: self._annotate_by_args(e, "this")}
5758
for expr_type in {
@@ -63,6 +64,7 @@
6364
}
6465
},
6566
exp.ApproxQuantile: {"annotator": lambda self, e: self._annotate_by_args(e, "quantile")},
67+
exp.WithinGroup: {"annotator": lambda self, e: self._annotate_by_args(e, "this")},
6668
exp.ArrayIntersect: {"annotator": lambda self, e: self._annotate_by_args(e, "expressions")},
6769
exp.Coalesce: {
6870
"annotator": lambda self, e: self._annotate_by_args(e, "this", "expressions", promote=True)

‎tests/fixtures/optimizer/annotate_functions.sql‎

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6470,3 +6470,67 @@ DOUBLE;
64706470

64716471
NTH_VALUE(tbl.str_col, 2) OVER (ORDER BY tbl.int_col);
64726472
TEXT;
6473+
6474+
--------------------------------------
6475+
-- Aggregate functions
6476+
--------------------------------------
6477+
6478+
COVAR_POP(tbl.double_col, tbl.double_col);
6479+
DOUBLE;
6480+
6481+
COVAR_POP(tbl.int_col, tbl.int_col);
6482+
DOUBLE;
6483+
6484+
COVAR_SAMP(tbl.double_col, tbl.double_col);
6485+
DOUBLE;
6486+
6487+
COVAR_SAMP(tbl.int_col, tbl.int_col);
6488+
DOUBLE;
6489+
6490+
--------------------------------------
6491+
-- Ordered-set aggregate functions
6492+
--------------------------------------
6493+
6494+
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tbl.double_col);
6495+
DOUBLE;
6496+
6497+
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY tbl.double_col);
6498+
DOUBLE;
6499+
6500+
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY tbl.int_col);
6501+
INT;
6502+
6503+
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY tbl.bigint_col);
6504+
BIGINT;
6505+
6506+
# dialect: hive, spark2, spark, databricks
6507+
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY tbl.int_col);
6508+
DOUBLE;
6509+
6510+
# dialect: hive, spark2, spark, databricks
6511+
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY tbl.bigint_col);
6512+
DOUBLE;
6513+
6514+
# dialect: hive, spark2, spark, databricks
6515+
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY tbl.double_col);
6516+
DOUBLE;
6517+
6518+
# dialect: hive, spark2, spark, databricks
6519+
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tbl.int_col);
6520+
DOUBLE;
6521+
6522+
# dialect: duckdb
6523+
QUANTILE_DISC(tbl.int_col, 0.5);
6524+
INT;
6525+
6526+
# dialect: duckdb
6527+
PERCENTILE_DISC(tbl.int_col, 0.5);
6528+
INT;
6529+
6530+
# dialect: duckdb
6531+
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY tbl.int_col);
6532+
INT;
6533+
6534+
# dialect: duckdb
6535+
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY tbl.double_col);
6536+
DOUBLE;

0 commit comments

Comments
 (0)