:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
import copy
from dataclasses import dataclass, field
from enum import Enum
from typing import Any, Optional
from posthog.schema import HogQLQuery, HogQLQueryModifiers
from posthog.hogql import ast
from posthog.hogql.context import HogQLContext
from posthog.hogql.functions.aggregations import COMBINATORS
from posthog.hogql.functions.mapping import find_hogql_aggregation
from posthog.hogql.modifiers import create_default_modifiers_for_team
from posthog.hogql.parser import parse_select
from posthog.hogql.placeholders import find_placeholders
from posthog.hogql.printer import prepare_and_print_ast
from posthog.hogql.timings import HogQLTimings
from posthog.hogql.visitor import CloningVisitor, TraversingVisitor, clone_expr
from posthog.dataclasses import frozen
from posthog.exceptions_capture import capture_exception
from posthog.hogql_queries.query_runner import get_query_runner
from posthog.hogql_queries.utils.breakdowns import BREAKDOWN_NULL_STRING_LABEL, BREAKDOWN_OTHER_STRING_LABEL
from posthog.models import User
from posthog.models.team import Team
from posthog.schema_migrations.upgrade import upgrade
ENDPOINT_BREAKDOWN_LIMIT = 10_000
class MaterializationNotSupportedError(ValueError):
"""Raised when a query uses a construct materialization can't transform.
This is a user-input limitation (a 400), not a server fault — callers should surface
it as a validation error rather than letting it bubble up as an unhandled exception.
"""
class VariableInHavingClauseError(MaterializationNotSupportedError):
"""Raised when a variable is used in a HAVING clause, which is not supported for materialization."""
class VariableComparedToPlaceholderExprError(MaterializationNotSupportedError):
"""Raised when a variable is compared against an expression that itself reads a variable.
That expression would be the materialized key column, which has to be fixed at
materialization time, so it can't depend on a per-request value.
"""
def inject_series_index(query_ast: ast.SelectQuery | ast.SelectSetQuery) -> None:
"""Add a __series_index literal column to each sub-query in a UNION ALL.
For single SelectQuery: adds ``0 AS __series_index``.
For SelectSetQuery (multi-series UNION ALL): adds ``N AS __series_index``
to each sub-query so the materialized table can distinguish series.
"""
if isinstance(query_ast, ast.SelectQuery):
_add_series_index_to_select(query_ast, 0)
elif isinstance(query_ast, ast.SelectSetQuery):
all_queries = query_ast.select_queries()
for i, sub_query in enumerate(all_queries):
if isinstance(sub_query, ast.SelectQuery):
_add_series_index_to_select(sub_query, i)
def _add_series_index_to_select(query: ast.SelectQuery, index: int) -> None:
"""Add ``{index} AS __series_index`` to a single SELECT query."""
series_col = ast.Alias(alias="__series_index", expr=ast.Constant(value=index))
query.select = [*list(query.select or []), series_col]
if query.group_by is not None:
query.group_by = [*list(query.group_by), ast.Field(chain=["__series_index"])]
def _print_materialized_hogql(
query: ast.Expr,
team: Team,
modifiers: HogQLQueryModifiers | None = None,
*,
user: User | None = None,
bypass_warehouse_access_control: bool = False,
) -> str:
"""Like to_printed_hogql, but without the implicit top-level row cap — a materialized table must hold every row."""
return prepare_and_print_ast(
clone_expr(query),
dialect="hogql",
context=HogQLContext(
team_id=team.pk,
user=user,
enable_select_queries=True,
limit_top_select=False,
modifiers=create_default_modifiers_for_team(team, modifiers),
bypass_warehouse_access_control=bypass_warehouse_access_control,
),
pretty=True,
)[0]
def convert_insight_query_to_hogql(
query: dict[str, Any],
team: Team,
*,
user: User | None = None,
bypass_warehouse_access_control: bool = False,
) -> dict[str, Any]:
query_kind = query.get("kind")
if query_kind == "HogQLQuery":
return query
# Stored endpoint snapshots may predate the current query schema
query = upgrade(query)
query_runner = get_query_runner(
query=query,
team=team,
timings=HogQLTimings(),
modifiers=HogQLQueryModifiers(),
user=user,
)
combined_query_ast = query_runner.to_query()
# Only inject __series_index for types that have a corresponding transformer
SERIES_INDEX_QUERY_TYPES = {"TrendsQuery", "LifecycleQuery", "RetentionQuery"}
if query_kind in SERIES_INDEX_QUERY_TYPES:
inject_series_index(combined_query_ast)
hogql_string = _print_materialized_hogql(
combined_query_ast,
team,
query_runner.modifiers,
user=user,
bypass_warehouse_access_control=bypass_warehouse_access_control,
)
result = HogQLQuery(query=hogql_string, modifiers=query_runner.modifiers).model_dump()
if "variables" in query:
result["variables"] = query["variables"]
return result
@dataclass
class MaterializableVariable:
"""Info about a variable that can be materialized"""
variable_id: str
code_name: str
column_chain: list[str]
column_expression: str
operator: ast.CompareOperationOp = ast.CompareOperationOp.Eq
column_ast: Optional[ast.Expr] = None
value_wrapper_fns: Optional[list[str]] = None
cte_name: Optional[str] = None # CTE containing the variable; None = top-level query
bucket_fn: Optional[str] = None # e.g. "toStartOfDay" for range variables on timestamp
# Plans for propagating this variable's column through each downstream CTE.
# Keyed by downstream CTE name. Populated by the analyzer; consumed by the transformer.
downstream_plans: dict[str, "DownstreamCTEPlan"] = field(default_factory=dict)
@dataclass
class VariableUsageInWhere:
"""Details of how a variable is used in a WHERE clause"""
column_chain: list[str]
column_expression: str
operator: ast.CompareOperationOp
column_ast: Optional[ast.Expr] = None
value_wrapper_fns: Optional[list[str]] = None
in_or: bool = False # True when this usage sits inside an OR within the WHERE clause
@frozen
class VariableWhereUsages:
"""Every supported WHERE usage of one variable, and whether it also appears in WHERE in a form the transform can't lift."""
usages: list[tuple[Optional[str], VariableUsageInWhere]]
seen_unmatched: bool
RANGE_OPS = frozenset(
{
ast.CompareOperationOp.GtEq,
ast.CompareOperationOp.Gt,
ast.CompareOperationOp.Lt,
ast.CompareOperationOp.LtEq,
}
)
@dataclass(frozen=True)
class AggregateReaggregation:
"""Defines how a base aggregate function is re-aggregated after bucketed materialization."""
reaggregate_fn: str # function to apply at read time (e.g., "sum" for count)
# Base functions that CAN be re-aggregated, and how.
# A combined function (e.g., sumIf) inherits from its base (sum).
REAGGREGATABLE_BASE_FUNCTIONS: dict[str, AggregateReaggregation] = {
"count": AggregateReaggregation(reaggregate_fn="sum"),
"sum": AggregateReaggregation(reaggregate_fn="sum"),
"min": AggregateReaggregation(reaggregate_fn="min"),
"max": AggregateReaggregation(reaggregate_fn="max"),
}
def _strip_combinators(func_name: str) -> str | None:
"""Strip ClickHouse combinator suffixes to find the base aggregate function.
Uses the COMBINATORS registry from posthog.hogql.functions.aggregations.
Returns the base function name (lowercased), or None if no match found.
Examples:
"sumIf" -> "sum"
"countArrayIf" -> "count"
"uniqMerge" -> "uniq" (but uniq is not in REAGGREGATABLE_BASE_FUNCTIONS)
"count" -> "count"
"""
name_lower = func_name.lower()
if name_lower in REAGGREGATABLE_BASE_FUNCTIONS:
return name_lower
sorted_suffixes = sorted(COMBINATORS.keys(), key=len, reverse=True)
def strip_recursive(name: str) -> str:
for suffix in sorted_suffixes:
if name.endswith(suffix.lower()) and len(name) > len(suffix):
return strip_recursive(name[: -len(suffix)])
return name
base = strip_recursive(name_lower)
return base if base != name_lower or base in REAGGREGATABLE_BASE_FUNCTIONS else None
def get_reaggregation(func_name: str) -> AggregateReaggregation | None:
"""Look up how to re-aggregate a (possibly combined) aggregate function.
Returns AggregateReaggregation if the function can be re-aggregated, None otherwise.
Handles ClickHouse combinators by stripping suffixes to find the base function.
Examples:
"count" -> AggregateReaggregation(reaggregate_fn="sum")
"sumIf" -> AggregateReaggregation(reaggregate_fn="sum")
"avg" -> None (not re-aggregatable)
"uniqArrayIf" -> None (base "uniq" not in registry)
"""
base = _strip_combinators(func_name)
if base is None:
return None
return REAGGREGATABLE_BASE_FUNCTIONS.get(base)
def _is_aggregate(func_name: str) -> bool:
sorted_suffixes = sorted(COMBINATORS.keys(), key=len, reverse=True)
name = func_name
while find_hogql_aggregation(name) is None:
suffix = next((s for s in sorted_suffixes if name.lower().endswith(s.lower()) and len(name) > len(s)), None)
if suffix is None:
return False
name = name[: -len(suffix)]
return True
SUPPORTED_MATERIALIZATION_OPS = frozenset(
{
ast.CompareOperationOp.Eq,
ast.CompareOperationOp.GtEq,
ast.CompareOperationOp.Gt,
ast.CompareOperationOp.Lt,
ast.CompareOperationOp.LtEq,
ast.CompareOperationOp.Like,
ast.CompareOperationOp.ILike,
ast.CompareOperationOp.NotLike,
ast.CompareOperationOp.NotILike,
}
)
def analyze_variables_for_materialization(
hogql_query: dict[str, Any],
bucket_overrides: dict[str, str] | None = None,
) -> tuple[bool, str, list[MaterializableVariable]]:
"""
Check if query variables can be materialized.
Each variable must be used in a WHERE clause with a supported operator
(=, >=, >, <, <=). Multiple variables are supported.
Returns:
(can_materialize, reason, variable_infos)
"""
query_str = hogql_query.get("query")
if not query_str:
return False, "No query string found", []
try:
ast_node = parse_select(query_str)
except Exception as e:
capture_exception(e)
return False, "Failed to parse query.", []
finder = VariablePlaceholderFinder()
finder.visit(ast_node)
if not finder.variable_placeholders:
return False, "No variables found", []
result_vars, reason = _collect_materializable_variables(
ast_node, finder.variable_placeholders, hogql_query.get("variables", {})
)
if reason is not None:
return False, reason, []
# Detect range variables and set bucket_fn for bucketed materialization.
# Single-bound ranges (e.g., just >= start) are supported — we materialize all data
# bucketed and filter at read time with the user's value.
_detect_range_variables(result_vars, bucket_overrides=bucket_overrides)
# Whole-query gates, in the order their messages should win. Each returns the reason
# the query as a whole can't be materialized, or None.
rejection = _reaggregation_rejection(ast_node, result_vars) or _cte_join_rejection(ast_node, result_vars)
if rejection is not None:
return False, rejection, []
# Kept out of the gate chain because it also writes each CTE-bound variable's
# downstream_plans, which the transformer later reads.
rejection = _plan_cte_propagation(ast_node, result_vars) or _find_alias_conflict(ast_node, result_vars)
if rejection is not None:
return False, rejection, []
return True, "OK", result_vars
def _collect_materializable_variables(
ast_node: ast.SelectQuery | ast.SelectSetQuery,
placeholders: list[ast.Placeholder],
variables_dict: dict[str, Any],
) -> tuple[list[MaterializableVariable], Optional[str]]:
"""One MaterializableVariable per distinct variable, or the first reason one can't be built."""
result_vars: list[MaterializableVariable] = []
seen_code_names: set[str] = set()
for placeholder in placeholders:
if not placeholder.chain or len(placeholder.chain) < 2:
return [], "Invalid variable placeholder format"
code_name = str(placeholder.chain[1])
if code_name in seen_code_names:
continue
seen_code_names.add(code_name)
var, reason = _materializable_variable(ast_node, placeholder, code_name, variables_dict)
if var is None:
return [], reason
result_vars.append(var)
return result_vars, None
def _materializable_variable(
ast_node: ast.SelectQuery | ast.SelectSetQuery,
placeholder: ast.Placeholder,
code_name: str,
variables_dict: dict[str, Any],
) -> tuple[Optional[MaterializableVariable], Optional[str]]:
"""Build one variable's materialization info, or the reason it can't be materialized."""
where_usages, reason = _variable_usages(ast_node, placeholder)
if reason is not None:
return None, reason
all_usages = where_usages.usages
cte_names = {cte for cte, _ in all_usages}
reason = _usage_rejection(where_usages) or _context_rejection(cte_names)
if reason is not None:
return None, reason
cte_name = next(iter(cte_names))
# Use the first usage for column info (all usages of the same variable should be consistent)
variable_usage = all_usages[0][1]
if variable_usage.operator not in SUPPORTED_MATERIALIZATION_OPS:
return None, f"Unsupported operator {variable_usage.operator}, supported: =, >=, >, <, <="
variable_id = next(
(var_id for var_id, var_data in variables_dict.items() if var_data.get("code_name") == code_name),
None,
)
if not variable_id:
return None, "Variable metadata not found"
return (
MaterializableVariable(
variable_id=variable_id,
code_name=code_name,
column_chain=variable_usage.column_chain,
column_expression=variable_usage.column_expression,
operator=variable_usage.operator,
column_ast=variable_usage.column_ast,
value_wrapper_fns=variable_usage.value_wrapper_fns,
cte_name=cte_name,
),
None,
)
def _variable_usages(
ast_node: ast.SelectQuery | ast.SelectSetQuery, placeholder: ast.Placeholder
) -> tuple[VariableWhereUsages, Optional[str]]:
"""Every WHERE usage of one variable, or the reason its usage can't be read."""
no_usages = VariableWhereUsages(usages=[], seen_unmatched=False)
try:
return find_all_variable_usages(ast_node, placeholder), None
except VariableInHavingClauseError:
return no_usages, "Variable used in HAVING clause are not supported for materialization."
except VariableComparedToPlaceholderExprError:
return (
no_usages,
"Variable compared against an expression containing another variable is not supported for materialization",
)
except ValueError as e:
capture_exception(e)
return no_usages, "Invalid variable usage in WHERE clause."
def _usage_rejection(where_usages: VariableWhereUsages) -> Optional[str]:
"""Reasons a variable's WHERE usages can't collapse into one materialized key column."""
all_usages = where_usages.usages
# A variable nested in an OR can't be lifted into a single materialized key column:
# removing its predicate (as the transform does) would change the result set.
if any(usage.in_or for _, usage in all_usages):
return "Variables in OR conditions are not supported for materialization"
# The transform removes the whole predicate and filters `column <op> value` at read time,
# so any other use of the variable would be silently dropped.
if where_usages.seen_unmatched:
return (
"Variable must be compared directly to a column, for example column = {variables.name}. "
"Using a variable inside another function or expression in WHERE is not supported for materialization."
)
if not all_usages:
return "Variable not used in WHERE clause"
# A variable compared against more than one column has no single bucket key to
# materialize on (the transform would silently keep only the first usage).
distinct_columns = {
tuple(usage.column_chain) if usage.column_chain else usage.column_expression for _, usage in all_usages
}
if len(distinct_columns) > 1:
return "Variable compared against multiple columns is not supported for materialization"
return None
def _context_rejection(cte_names: set[Optional[str]]) -> Optional[str]:
"""Reject a variable used in more than one place (two CTEs, or a CTE and the top level)."""
if len(cte_names) <= 1:
return None
if None in cte_names:
return "Variable used in both CTE and top-level query is not yet supported"
return "Variable used in multiple CTEs is not yet supported"
def _reaggregation_rejection(
ast_node: ast.SelectQuery | ast.SelectSetQuery, result_vars: list[MaterializableVariable]
) -> Optional[str]:
"""With range variables the table is bucketed, so every SELECT aggregate must re-aggregate."""
if not any(v.bucket_fn is not None for v in result_vars):
return None
if not isinstance(ast_node, ast.SelectQuery):
return None
for expr in ast_node.select or []:
agg_name = _extract_aggregate_name(expr)
if agg_name and get_reaggregation(agg_name) is None:
return f"Aggregate function '{agg_name}' cannot be re-aggregated for range variable materialization"
return None
def _cte_join_rejection(
ast_node: ast.SelectQuery | ast.SelectSetQuery, result_vars: list[MaterializableVariable]
) -> Optional[str]:
"""CTE variables + top-level JOINs produce wrong results.
Removing a CTE's WHERE changes its row cardinality, which changes JOIN
output. Filtering after materialization can't recover the original semantics
(e.g. LEFT JOIN non-matches get NULL for the variable column and are lost).
"""
if not any(v.cte_name is not None for v in result_vars):
return None
if isinstance(ast_node, ast.SelectQuery) and _has_joins(ast_node):
return "CTE variables with JOINs in the top-level query are not supported for materialization"
return None
def _plan_cte_propagation(
ast_node: ast.SelectQuery | ast.SelectSetQuery, result_vars: list[MaterializableVariable]
) -> Optional[str]:
"""Fill in each CTE-bound variable's downstream plans, or return why they can't be built.
For every CTE-bound variable, classify each downstream CTE so the transformer can
rewrite SELECT / GROUP BY / WHERE to carry the variable column from the
variable-carrying CTE to the terminal CTE.
"""
if not any(v.cte_name is not None for v in result_vars):
return None
if not isinstance(ast_node, ast.SelectQuery) or not ast_node.ctes:
return None
ctes = ast_node.ctes
graph = _build_cte_read_graph(ast_node)
all_code_names = [v.code_name for v in result_vars if v.cte_name is not None]
for var in result_vars:
if var.cte_name is None:
continue
downstream = _downstream_ctes(graph, var.cte_name)
propagating = downstream | {var.cte_name}
if _top_level_reads_propagating_cte_outside_from(ast_node, propagating):
return (
"Scalar subquery in top-level query references a CTE downstream of "
"the variable-carrying CTE; the transformer cannot rewrite it to "
"carry the variable column. Move the reference to the top-level FROM."
)
plans: dict[str, DownstreamCTEPlan] = {}
for d_cte_name in _topological_order(graph, downstream):
plan = _classify_downstream_cte(d_cte_name, ctes[d_cte_name].expr, propagating, all_code_names)
if plan.reject_reason:
return plan.reject_reason
plans[d_cte_name] = plan
var.downstream_plans = plans
return None
def _top_level_reads_propagating_cte_outside_from(ast_node: ast.SelectQuery, propagating: set[str]) -> bool:
"""Whether the top-level query reads a propagating CTE anywhere but its own FROM chain.
Propagation adds a column + GROUP BY to propagating CTEs, breaking any top-level scalar
usage of them (`WHERE x = (SELECT col FROM cte)` ends up returning N rows × 2 cols). No
correct rewrite exists — ClickHouse doesn't support correlated subqueries. Hide the outer
WITH so the finder doesn't treat propagating CTEs as shadowed by their own definitions.
"""
original_ctes = ast_node.ctes
ast_node.ctes = None
try:
return _body_has_non_top_from_propagating_reference(ast_node, propagating)
finally:
ast_node.ctes = original_ctes
def _alias_conflict_reason(code_name: str) -> str:
"""Single source of truth for the variable/alias-conflict message, so the analyzer's
rejection reason and the transformer's exception always read identically."""
return (
f"Variable '{code_name}' conflicts with an existing SELECT alias for a different expression. "
f"Rename the variable or the conflicting column alias."
)
def _variable_column_expr(var: MaterializableVariable) -> ast.Expr:
"""Expression a variable contributes to SELECT/GROUP BY, without aliasing.
Uses the original AST expression when available (e.g. function calls like
toDate(timestamp) that can't be reconstructed from column_chain). Always returns a fresh
copy so SELECT and GROUP BY don't share nodes. Wraps with bucket_fn when set.
"""
if var.column_ast is not None:
base = CloningVisitor().visit(var.column_ast)
else:
base = ast.Field(chain=list(var.column_chain))
if var.bucket_fn:
return ast.Call(name=var.bucket_fn, args=[base])
return base
def _select_alias_conflict(select: ast.SelectQuery, vars_in_context: list[MaterializableVariable]) -> Optional[str]:
"""code_name of the first variable colliding with a SELECT alias bound to a *different*
expression, or None. Shared by the analyzer (pre-flight reject) and the transformer
(enable-time backstop) so both agree on what's unmaterializable."""
existing_aliases = {
alias.alias: alias.expr.to_hogql() for alias in select.select or [] if isinstance(alias, ast.Alias)
}
for var in vars_in_context:
existing = existing_aliases.get(var.code_name)
if existing is not None and existing != _variable_column_expr(var).to_hogql():
return var.code_name
return None
class _AliasConflictFinder(TraversingVisitor):
def __init__(self, contexts: dict[Optional[str], list[MaterializableVariable]]) -> None:
super().__init__()
self._contexts = contexts
self._current_cte_name: Optional[str] = None
self.conflict: Optional[str] = None
def visit_select_query(self, node: ast.SelectQuery) -> None:
for cte_name, cte in (node.ctes or {}).items():
prev_cte = self._current_cte_name
self._current_cte_name = cte_name
self.visit(cte.expr)
self._current_cte_name = prev_cte
original_ctes = node.ctes
node.ctes = None
try:
super().visit_select_query(node)
finally:
node.ctes = original_ctes
vars_in_context = self._contexts.get(self._current_cte_name)
if self.conflict is None and vars_in_context:
self.conflict = _select_alias_conflict(node, vars_in_context)
def _find_alias_conflict(
ast_node: ast.SelectQuery | ast.SelectSetQuery, result_vars: list[MaterializableVariable]
) -> Optional[str]:
"""Pre-flight equivalent of the transformer's per-context alias-conflict check: group
variables by their context and run the shared conflict check on each.
A variable whose code_name collides with a SELECT alias for a different expression
can't be materialized (the table would need two columns of that name). This is the one
limitation only the transform could detect, so reject it here — sharing the check with
the transformer — instead of letting enable_materialization fail mid-transform.
"""
contexts: dict[Optional[str], list[MaterializableVariable]] = {}
for var in result_vars:
contexts.setdefault(var.cte_name, []).append(var)
finder = _AliasConflictFinder(contexts)
finder.visit(ast_node)
return _alias_conflict_reason(finder.conflict) if finder.conflict is not None else None
def _has_joins(node: ast.SelectQuery) -> bool:
"""Check if a SelectQuery has any JOINs (next_join on select_from)."""
return node.select_from is not None and node.select_from.next_join is not None
# ---------------------------------------------------------------------------
# CTE variable propagation: graph helpers + downstream shape classifier
# ---------------------------------------------------------------------------
#
# When a variable lives inside a CTE and downstream CTEs read from it,
# the materialized terminal table must still carry the variable column so the
# read-time filter can slice rows per-variable-value. These helpers walk the
# CTE reference graph, classify each downstream CTE's shape, and return a
# plan object the transformer uses to rewrite SELECT / GROUP BY / WHERE.
# Join types between propagating CTEs that preserve per-value row identity
# under a CROSS-JOIN + equi-predicate rewrite. Outer joins break this (see
# product_data reasoning in the plan doc), so they're explicitly rejected.
_SAFE_PROPAGATION_JOIN_TYPES = frozenset({None, "JOIN", "INNER JOIN", "CROSS JOIN"})
class DownstreamCTEShape(Enum):
PROJECTION = "projection"
AGGREGATION = "aggregation"
DISTINCT = "distinct"
MULTI_JOIN = "multi_join"
UNION_ALL = "union_all"
@frozen
class PropagatingSource:
"""A propagating CTE read by a downstream CTE, under the name it is read as.
Both fields are names of the same shape, so they are keyword-only to stop a
read alias and a CTE name swapping places at a call site.
"""
alias: str
cte_name: str
@frozen
class DownstreamCTEPlan:
"""How the transformer should propagate a variable through a downstream CTE."""
cte_name: str
shape: DownstreamCTEShape
# Every propagating CTE read in this CTE.
# For UNION_ALL, this is left empty; each leg carries its own nested plan.
propagating_sources: list[PropagatingSource] = field(default_factory=list)
leg_plans: list["DownstreamCTEPlan"] = field(default_factory=list)
reject_reason: Optional[str] = None
class _CTEReferenceCollector(TraversingVisitor):
"""Collect names of sibling CTEs referenced anywhere in an expression subtree.
A nested ``WITH`` defining the same name shadows the outer sibling, so references
inside the nested scope are excluded.
"""
def __init__(self, known_ctes: set[str]):
super().__init__()
self._known_stack: list[set[str]] = [known_ctes]
self.referenced: set[str] = set()
def visit_select_query(self, node: ast.SelectQuery) -> None:
shadowed = (set(node.ctes.keys()) & self._known_stack[-1]) if node.ctes else set()
if shadowed:
self._known_stack.append(self._known_stack[-1] - shadowed)
try:
super().visit_select_query(node)
finally:
self._known_stack.pop()
else:
super().visit_select_query(node)
def visit_join_expr(self, node: ast.JoinExpr):
if isinstance(node.table, ast.Field) and len(node.table.chain) == 1:
name = str(node.table.chain[0])
if name in self._known_stack[-1]:
self.referenced.add(name)
super().visit_join_expr(node)
def _build_cte_read_graph(node: ast.SelectQuery) -> dict[str, set[str]]:
"""Map each CTE name to the sibling CTE names its body references (transitively via subtree walk)."""
if not node.ctes:
return {}
cte_names = set(node.ctes.keys())
graph: dict[str, set[str]] = {}
for name, cte in node.ctes.items():
collector = _CTEReferenceCollector(cte_names - {name})
collector.visit(cte.expr)
graph[name] = collector.referenced
return graph
def _reads_from(graph: dict[str, set[str]], source: str, target: str, visited: set[str]) -> bool:
if source in visited:
return False
visited.add(source)
direct = graph.get(source, set())
if target in direct:
return True
return any(_reads_from(graph, ref, target, visited) for ref in direct)
def _downstream_ctes(graph: dict[str, set[str]], start: str) -> set[str]:
"""Return every CTE that transitively reads from ``start`` (excludes ``start`` itself)."""
return {name for name in graph if name != start and _reads_from(graph, name, start, set())}
def _topological_order(graph: dict[str, set[str]], subset: set[str]) -> list[str]:
"""Order CTEs so each appears after any CTE it reads from (within ``subset``)."""
result: list[str] = []
visited: set[str] = set()
def visit(n: str) -> None:
if n in visited or n not in subset:
return
visited.add(n)
for dep in graph.get(n, set()):
visit(dep)
result.append(n)
for n in subset:
visit(n)
return result
def _normalize_join_type(join_type: Optional[str]) -> Optional[str]:
"""Strip a leading ``GLOBAL `` prefix so classification keys match the base join type."""
return join_type.removeprefix("GLOBAL ") if join_type is not None else None
def _select_column_name(expr: ast.Expr) -> Optional[str]:
"""The name a SELECT expression emits — alias, or a single-segment field chain."""
if isinstance(expr, ast.Alias):
return expr.alias
if isinstance(expr, ast.Field) and len(expr.chain) == 1:
return str(expr.chain[0])
return None
def _collect_propagating_sources_top_level(
select_from: Optional[ast.JoinExpr],
propagating: set[str],
) -> tuple[list[PropagatingSource], Optional[str]]:
"""Walk the top-level ``select_from`` chain, collecting propagating CTE refs.
Returns ``(sources, reject_reason)``. ``reject_reason`` is non-None if any
JoinExpr in the chain references a propagating CTE via an unsupported join type.
"""
sources: list[PropagatingSource] = []
cur = select_from
while cur is not None:
if isinstance(cur.table, ast.Field) and len(cur.table.chain) == 1:
cte_name = str(cur.table.chain[0])
if cte_name in propagating:
join_type = _normalize_join_type(cur.join_type)
if join_type not in _SAFE_PROPAGATION_JOIN_TYPES:
return (
[],
f"CTE variable propagation requires CROSS/INNER joins between propagating CTEs; "
f"{cur.join_type} not supported",
)
sources.append(PropagatingSource(alias=cur.alias or cte_name, cte_name=cte_name))
cur = cur.next_join
return sources, None
class _NestedPropagatingReferenceFinder(TraversingVisitor):
"""Follow only FROM chains, so a propagating name used elsewhere does not count.
Unlike the other finders here, this one ignores nested ``WITH`` shadowing, so a local
CTE that reuses a propagating name is still reported. That over-rejects rather than
letting an unpropagated query through.
"""
def __init__(self, propagating: set[str]) -> None:
super().__init__()
self.propagating = propagating
self.found = False
def visit_select_query(self, n: ast.SelectQuery) -> None:
if n.select_from is not None:
self.visit(n.select_from)
def visit_select_set_query(self, n: ast.SelectSetQuery) -> None:
for leg in n.select_queries():
self.visit(leg)
def visit_join_expr(self, n: ast.JoinExpr) -> None:
if isinstance(n.table, ast.Field) and len(n.table.chain) == 1:
if str(n.table.chain[0]) in self.propagating:
self.found = True
return
if isinstance(n.table, ast.SelectQuery | ast.SelectSetQuery):
self.visit(n.table)
if n.next_join is not None:
self.visit(n.next_join)
def _has_nested_propagating_reference(node: Optional[ast.Expr], propagating: set[str]) -> bool:
"""Detect a propagating-CTE reference inside a subquery (e.g. ``FROM (SELECT * FROM prop)``)."""
if node is None:
return False
finder = _NestedPropagatingReferenceFinder(propagating)
finder.visit(node)
return finder.found
def _select_from_has_nested_reference(
select_from: Optional[ast.JoinExpr],
propagating: set[str],
) -> bool:
"""Return True if any join in the top chain uses a subquery that references a propagating CTE."""
cur = select_from
while cur is not None:
if isinstance(cur.table, ast.SelectQuery | ast.SelectSetQuery):
if _has_nested_propagating_reference(cur.table, propagating):
return True
cur = cur.next_join
return False
class _NonTopPropagatingReferenceFinder(TraversingVisitor):
"""Flag a propagating-CTE reference outside the top-level FROM chain.
Top-level FROM references are valid propagation sources (handled separately).
Anywhere else — scalar subquery in WHERE/SELECT, JOIN ON, LIMIT BY — would need
a correlated subquery to carry the variable column, which the transformer can't
produce, so the caller must reject. Nested ``WITH`` shadowing an outer name is
honored.
"""
def __init__(self, propagating: set[str], top_chain_join_ids: set[int]) -> None:
super().__init__()
self._propagating_stack: list[set[str]] = [propagating]
self.top_chain_join_ids = top_chain_join_ids
self.found = False
def visit(self, node: ast.AST | None) -> None:
if self.found or node is None:
return
super().visit(node)
def visit_select_query(self, node: ast.SelectQuery) -> None:
shadowed = (set(node.ctes.keys()) & self._propagating_stack[-1]) if node.ctes else set()
if shadowed:
self._propagating_stack.append(self._propagating_stack[-1] - shadowed)
try:
super().visit_select_query(node)
finally:
self._propagating_stack.pop()
else:
super().visit_select_query(node)
def visit_join_expr(self, n: ast.JoinExpr) -> None:
if self.found:
return
if id(n) not in self.top_chain_join_ids:
if (
isinstance(n.table, ast.Field)
and len(n.table.chain) == 1
and str(n.table.chain[0]) in self._propagating_stack[-1]
):
self.found = True
return
super().visit_join_expr(n)
def _body_has_non_top_from_propagating_reference(
cte: ast.SelectQuery,
propagating: set[str],
) -> bool:
"""Return True if any part of ``cte`` other than its top-level ``select_from`` chain
references a propagating CTE."""
top_chain_join_ids: set[int] = set()
cur: Optional[ast.JoinExpr] = cte.select_from
while cur is not None:
top_chain_join_ids.add(id(cur))
cur = cur.next_join
finder = _NonTopPropagatingReferenceFinder(propagating, top_chain_join_ids)
finder.visit(cte)
return finder.found
def _emits_column(select_query: ast.SelectQuery, column_name: str) -> bool:
return any(_select_column_name(expr) == column_name for expr in select_query.select or [])
def _rejected_plan(
cte_name: str,
reason: str,
shape: DownstreamCTEShape = DownstreamCTEShape.PROJECTION,
) -> DownstreamCTEPlan:
return DownstreamCTEPlan(cte_name=cte_name, shape=shape, reject_reason=reason)
def _classify_downstream_cte(
cte_name: str,
cte_expr: ast.Expr,
propagating: set[str],
code_names: list[str],
) -> DownstreamCTEPlan:
"""Classify a downstream CTE's shape; produce a plan or a rejection reason."""
if isinstance(cte_expr, ast.SelectSetQuery):
return _classify_union_cte(cte_name, cte_expr, propagating, code_names)
if not isinstance(cte_expr, ast.SelectQuery):
return _rejected_plan(cte_name, f"Unsupported CTE body type: {type(cte_expr).__name__}")
sources, reject = _propagating_sources_or_rejection(cte_name, cte_expr, propagating, code_names)
if reject is not None:
return _rejected_plan(cte_name, reject)
return DownstreamCTEPlan(
cte_name=cte_name,
shape=_downstream_shape(cte_expr, sources),
propagating_sources=sources,
)
def _classify_union_cte(
cte_name: str,
cte_expr: ast.SelectSetQuery,
propagating: set[str],
code_names: list[str],
) -> DownstreamCTEPlan:
"""Classify each leg of a set query; every leg has to carry the variable column itself."""
if any(node.set_operator != "UNION ALL" for node in cte_expr.subsequent_select_queries):
return _rejected_plan(
cte_name,
"Only UNION ALL is supported for propagation across set operations",
DownstreamCTEShape.UNION_ALL,
)
leg_plans: list[DownstreamCTEPlan] = []
for i, leg in enumerate(cte_expr.select_queries()):
leg_name = f"{cte_name}#leg{i}"
if not isinstance(leg, ast.SelectQuery):
leg_plan = _rejected_plan(leg_name, "nested set queries are not supported")
else:
leg_plan = _classify_downstream_cte(leg_name, leg, propagating, code_names)
# Unreachable today: a SELECT leg with no source is already rejected one level down.
# Kept because a leg the transformer can't source the column from would silently emit
# rows without it, and that failure is invisible until the read-time filter drops data.
if not leg_plan.reject_reason and not leg_plan.propagating_sources:
leg_plan = _rejected_plan(leg_name, "leg has no propagating CTE source")
if leg_plan.reject_reason:
return _rejected_plan(
cte_name,
f"Variable propagation failed on UNION leg: {leg_plan.reject_reason}",
DownstreamCTEShape.UNION_ALL,
)
leg_plans.append(leg_plan)
return DownstreamCTEPlan(cte_name=cte_name, shape=DownstreamCTEShape.UNION_ALL, leg_plans=leg_plans)
def _propagating_sources_or_rejection(
cte_name: str,
cte_expr: ast.SelectQuery,
propagating: set[str],
code_names: list[str],
) -> tuple[list[PropagatingSource], Optional[str]]:
"""The propagating CTEs this SELECT reads, or the first reason it can't carry the column."""
if cte_expr.window_exprs:
return [], "Window functions in downstream CTEs of a variable CTE are not yet supported for materialization"
sources, reject = _collect_propagating_sources_top_level(cte_expr.select_from, propagating)
if reject:
return [], reject
if _select_from_has_nested_reference(cte_expr.select_from, propagating):
return (
[],
"CTE variable propagation requires top-level FROM references; nested subquery reference not supported",
)
if _body_has_non_top_from_propagating_reference(cte_expr, propagating):
return [], (
"CTE variable propagation requires top-level FROM references; "
"scalar subquery reading from a propagating CTE not supported"
)
if not sources:
return [], "Downstream CTE does not read from any propagating CTE"
collision = next((code_name for code_name in code_names if _emits_column(cte_expr, code_name)), None)
if collision is not None:
return [], f"Variable code_name '{collision}' collides with existing column in downstream CTE {cte_name}"
return sources, None
def _downstream_shape(cte_expr: ast.SelectQuery, sources: list[PropagatingSource]) -> DownstreamCTEShape:
"""Pick the rewrite shape from the CTE's own structure."""
if len(sources) >= 2:
return DownstreamCTEShape.MULTI_JOIN
if cte_expr.distinct:
return DownstreamCTEShape.DISTINCT
if cte_expr.group_by or _select_has_aggregate(cte_expr):
return DownstreamCTEShape.AGGREGATION
return DownstreamCTEShape.PROJECTION
class _AggregateFinder(TraversingVisitor):
def __init__(self) -> None:
super().__init__()
self.found = False
def visit_call(self, n: ast.Call) -> None:
if _is_aggregate(n.name):
self.found = True
return
super().visit_call(n)
def _select_has_aggregate(node: ast.SelectQuery) -> bool:
"""Check if any SELECT expression uses an aggregate function (sum, count, avg, etc.)."""
finder = _AggregateFinder()
for expr in node.select or []:
finder.visit(expr)
if finder.found:
return True
return False
class VariablePlaceholderFinder(TraversingVisitor):
"""Find all variable placeholders in the AST"""
def __init__(self):
super().__init__()
self.variable_placeholders: list[ast.Placeholder] = []
def visit_placeholder(self, node: ast.Placeholder):
if node.chain and node.chain[0] == "variables":
self.variable_placeholders.append(node)
def _contains_placeholder(node: ast.Expr) -> bool:
found = find_placeholders(node)
return bool(found.has_filters or found.placeholder_fields or found.placeholder_expressions)
def find_variable_in_where(
ast_node: ast.SelectQuery | ast.SelectSetQuery, placeholder: ast.Placeholder
) -> Optional[VariableUsageInWhere]:
"""
Walk AST to find where the placeholder is used in WHERE clause.
Returns column being compared and operator.
"""
if not isinstance(ast_node, ast.SelectQuery):
return None
finder = VariableInWhereFinder(placeholder)
finder.visit(ast_node)
return finder.result
def find_all_variable_usages(
ast_node: ast.SelectQuery | ast.SelectSetQuery, placeholder: ast.Placeholder
) -> VariableWhereUsages:
"""Find all usages of a variable in WHERE clauses, including inside CTEs.
Each usage is a (cte_name, usage) tuple, where cte_name is None for the top-level query.
"""
if not isinstance(ast_node, ast.SelectQuery):
return VariableWhereUsages(usages=[], seen_unmatched=False)
finder = VariableInWhereFinder(placeholder)
finder.visit(ast_node)
return VariableWhereUsages(usages=finder.all_results, seen_unmatched=finder.seen_unmatched_in_where)
class VariableInWhereFinder(TraversingVisitor):
"""Find how a variable is used in WHERE clause, including inside CTEs."""
def __init__(self, target_placeholder: ast.Placeholder):
super().__init__()
self.target = target_placeholder
self.all_results: list[tuple[Optional[str], VariableUsageInWhere]] = []
self.seen_unmatched_in_where = False
self.in_where = False
self._removable_compare_ids: set[int] = set()
self._or_depth = 0
self._current_cte_name: Optional[str] = None
@property
def result(self) -> Optional[VariableUsageInWhere]:
"""Backward-compat: return first match's usage."""
return self.all_results[0][1] if self.all_results else None
def visit_select_query(self, node: ast.SelectQuery):
if node.having:
finder = VariablePlaceholderFinder()
finder.visit(node.having)
if any(p.chain == self.target.chain for p in finder.variable_placeholders):
raise VariableInHavingClauseError()
# Visit CTEs first (they're part of this SelectQuery)
if node.ctes:
for cte_name, cte in node.ctes.items():
prev_cte = self._current_cte_name
self._current_cte_name = cte_name
self.visit(cte.expr)
self._current_cte_name = prev_cte
if node.where:
prev_in_where, prev_or_depth, prev_removable = self.in_where, self._or_depth, self._removable_compare_ids
self.in_where, self._or_depth = True, 0
# Mirrors MaterializationTransformer._remove_variable_from_where: it only removes a
# comparison that is the whole WHERE or a direct term of a top-level AND.
terms = node.where.exprs if isinstance(node.where, ast.And) else [node.where]
self._removable_compare_ids = {id(term) for term in terms if isinstance(term, ast.CompareOperation)}
self.visit(node.where)
self.in_where, self._or_depth, self._removable_compare_ids = prev_in_where, prev_or_depth, prev_removable
def visit_or(self, node: ast.Or):
# Only OR nesting within a WHERE clause matters for materialization.
track = self.in_where
if track:
self._or_depth += 1
for expr in node.exprs:
self.visit(expr)
if track:
self._or_depth -= 1
def visit_compare_operation(self, node: ast.CompareOperation):
if not self.in_where:
return
field_side = None
variable_side = None
if self._contains_target_placeholder(node.right):
field_side = node.left
variable_side = node.right
elif self._contains_target_placeholder(node.left):
field_side = node.right
variable_side = node.left
if not isinstance(field_side, ast.Field | ast.Call):
# Flag without descending: a full visit would record comparisons inside subqueries as usages here.
finder = VariablePlaceholderFinder()
finder.visit(node)
if any(p.chain == self.target.chain for p in finder.variable_placeholders):
self.seen_unmatched_in_where = True
return
if isinstance(field_side, ast.Call) and _contains_placeholder(field_side):
raise VariableComparedToPlaceholderExprError()
# Only single-argument wrappers can be reapplied to the value at read time. An OR is left
# to record its usage, so the OR rejection message wins.
is_removable = self._or_depth > 0 or id(node) in self._removable_compare_ids
if not is_removable or not self._is_wrapped_target(variable_side):
self.seen_unmatched_in_where = True
return
wrapper_fns = self._extract_wrapper_fns(variable_side)
if isinstance(field_side, ast.Field):
column_chain = [str(item) for item in field_side.chain]
self.all_results.append(
(
self._current_cte_name,
VariableUsageInWhere(
column_chain=column_chain,
column_expression=".".join(column_chain),
operator=node.op,
value_wrapper_fns=wrapper_fns,
in_or=self._or_depth > 0,
),
)
)
elif isinstance(field_side, ast.Call):
column_chain = self._extract_column_chain_from_call(field_side)
self.all_results.append(
(
self._current_cte_name,
VariableUsageInWhere(
column_chain=column_chain,
column_expression=".".join(column_chain) if column_chain else str(field_side),
operator=node.op,
column_ast=field_side if not column_chain else None,
value_wrapper_fns=wrapper_fns,
in_or=self._or_depth > 0,
),
)
)
def visit_placeholder(self, node: ast.Placeholder):
if self.in_where and node.chain == self.target.chain:
self.seen_unmatched_in_where = True
def _is_wrapped_target(self, node: Optional[ast.Expr]) -> bool:
"""Whether the node is the target placeholder, optionally inside single-argument calls."""
while isinstance(node, ast.Call) and len(node.args) == 1:
node = node.args[0]
return isinstance(node, ast.Placeholder) and node.chain == self.target.chain
def _contains_target_placeholder(self, node: ast.Expr) -> bool:
"""Check if an expression is or contains the target placeholder (e.g. inside toDate(...))."""
if isinstance(node, ast.Placeholder) and node.chain == self.target.chain:
return True
if isinstance(node, ast.Call):
return any(self._contains_target_placeholder(arg) for arg in node.args)
return False
@staticmethod
def _extract_wrapper_fns(node: Optional[ast.Expr]) -> Optional[list[str]]:
"""Extract the chain of wrapping function names from outermost to innermost.
For toDate(toStartOfMonth({variables.x})), returns ["toDate", "toStartOfMonth"].
"""
fns: list[str] = []
current = node
while isinstance(current, ast.Call) and len(current.args) == 1:
fns.append(current.name)
current = current.args[0]
return fns or None
def _extract_column_chain_from_call(self, call: ast.Call) -> list[str]:
if call.name == "JSONExtractString" and len(call.args) >= 2:
if isinstance(call.args[0], ast.Field) and isinstance(call.args[1], ast.Constant):
field_chain = [str(item) for item in call.args[0].chain]
return [*field_chain, str(call.args[1].value)]
return []
SUPPORTED_BUCKET_FUNCTIONS: dict[str, str] = {
"minute": "toStartOfMinute",
"fifteen_minutes": "toStartOfFifteenMinutes",
"hour": "toStartOfHour",
"day": "toStartOfDay",
"week": "toStartOfWeek",
"month": "toStartOfMonth",
}
def _is_property_column(column_chain: list[str]) -> bool:
"""Check if a column chain references a properties field (e.g. properties.price)."""
return "properties" in column_chain
def _detect_range_variables(
variables: list[MaterializableVariable],
bucket_overrides: dict[str, str] | None = None,
) -> None:
"""Detect range variables and set bucket_fn for bucketed materialization.
Any variable with a range operator on a plain column (no column_ast)
gets bucket_fn set. For single-bound ranges, we materialize all data
bucketed and filter at read time with the user's value.
Properties columns (e.g. properties.price) are skipped unless the user
explicitly provides a bucket_override — bucket functions like toStartOfDay
only make sense on DateTime columns, not on string/numeric properties.
"""
for var in variables:
if var.column_ast is not None:
continue
if var.operator not in RANGE_OPS:
continue
col_key = ".".join(var.column_chain) if var.column_chain else var.column_expression
if bucket_overrides and col_key in bucket_overrides:
override = bucket_overrides[col_key]
if override not in SUPPORTED_BUCKET_FUNCTIONS:
raise ValueError(
f"Unsupported bucket override '{override}'. Supported: {list(SUPPORTED_BUCKET_FUNCTIONS.keys())}"
)
var.bucket_fn = SUPPORTED_BUCKET_FUNCTIONS[override]
elif not _is_property_column(var.column_chain):
var.bucket_fn = "toStartOfDay"
def _extract_aggregate_name(expr: ast.Expr) -> Optional[str]:
"""Extract the aggregate function name from a SELECT expression, if any.
Returns lowercased names so downstream lookups (REAGGREGATABLE_BASE_FUNCTIONS,
get_reaggregation) match regardless of source casing. Both count(DISTINCT x) and
countDistinct(x) (in any case) collapse to the camelCase sentinel "countDistinct"
so they stay out of REAGGREGATABLE_BASE_FUNCTIONS — merging distinct counts via
sum would double-count across partitions.
"""
if isinstance(expr, ast.Alias):
return _extract_aggregate_name(expr.expr)
if isinstance(expr, ast.Call):
name_lower = expr.name.lower()
if name_lower == "count" and getattr(expr, "distinct", False):
return "countDistinct"
if name_lower == "countdistinct":
return "countDistinct"
if _is_aggregate(expr.name):
return name_lower
return None
@frozen
class MaterializedColumn:
"""A column in the materialized table with metadata for read-time re-aggregation."""
expr: ast.Expr
is_aggregate: bool
name: str # the column name the endpoint declares, which a materialized read must keep
reaggregate_fn: Optional[str] = None # e.g. "sum" for count/sum, "min" for min, etc.
@property
def labeled_expr(self) -> ast.Expr:
if isinstance(self.expr, ast.Field) and self.expr.chain == [self.name]:
return self.expr
return ast.Alias(alias=self.name, expr=self.expr)
def _declared_name(expr: ast.Expr) -> str:
return expr.alias if isinstance(expr, ast.Alias) else expr.to_hogql()
def transform_select_for_materialized_table(
select_exprs: list[ast.Expr], output_names: list[str]
) -> list[MaterializedColumn]:
"""
Transform SELECT expressions to reference pre-computed columns in materialized table.
`output_names` are the names the materialized table stores, in SELECT order. When their
count does not match `select_exprs`, each column falls back to its alias or HogQL text.
Returns list of MaterializedColumn with re-aggregation metadata.
Examples, given the stored column each expression reads and the name it declares:
- count() -> reads "count()", name "count()", is_aggregate=True, reaggregate_fn="sum"
- count() as total -> reads "total", name "total", is_aggregate=True, reaggregate_fn="sum"
- toStartOfDay(timestamp) as date -> reads "date", name "date", is_aggregate=False
- toStartOfDay(timestamp) -> reads "toStartOfDay(toTimeZone(timestamp, 'UTC'))", name "toStartOfDay(timestamp)"
- e.event -> reads "event", name "event", is_aggregate=False
- properties.foo.bar -> reads "foo__bar", name "foo__bar", is_aggregate=False
"""
stored_names = (
output_names if len(output_names) == len(select_exprs) else [_declared_name(expr) for expr in select_exprs]
)
result: list[MaterializedColumn] = []
for expr, stored_name in zip(select_exprs, stored_names):
agg_name = _extract_aggregate_name(expr)
is_agg = agg_name is not None
reagg = get_reaggregation(agg_name) if agg_name else None
reaggregate_fn = reagg.reaggregate_fn if reagg else None
result.append(
MaterializedColumn(
expr=ast.Field(chain=[stored_name]),
is_aggregate=is_agg,
name=stored_name if isinstance(expr, ast.Field) else _declared_name(expr),
reaggregate_fn=reaggregate_fn,
)
)
return result
def transform_query_for_materialization(
hogql_query: dict[str, Any],
variable_infos: MaterializableVariable | list[MaterializableVariable],
team: Team,
bucket_overrides: dict[str, str] | None = None,
*,
user: User | None = None,
bypass_warehouse_access_control: bool = False,
) -> dict[str, Any]:
"""
Transform query by:
1. Removing WHERE clauses with variables
2. Adding variable columns to SELECT (aliased by code_name) and GROUP BY (deduplicated)
Example (single):
Before: SELECT count(), date FROM events WHERE event = {variables.event_name} GROUP BY date
After: SELECT count(), date, event AS event_name FROM events GROUP BY date, event
Example (multi, same column):
Before: SELECT count() FROM events WHERE hour >= {variables.start} AND hour < {variables.end}
After: SELECT count(), hour AS start, hour AS end FROM events GROUP BY hour
If bucket_overrides is provided, re-runs range pair detection with the overrides
so that bucket_fn on the variable_infos is updated before the transform.
"""
if isinstance(variable_infos, MaterializableVariable):
variable_infos = [variable_infos]
variable_infos = copy.deepcopy(variable_infos)
# Re-apply range variable detection with overrides if provided
if bucket_overrides:
# Reset existing bucket_fn values so detection can re-apply with overrides
for v in variable_infos:
if v.bucket_fn is not None:
v.bucket_fn = None
_detect_range_variables(variable_infos, bucket_overrides=bucket_overrides)
query_str = hogql_query.get("query")
if not query_str:
raise ValueError("No query string found")
parsed_ast = parse_select(query_str)
transformer = MaterializationTransformer(variable_infos)
transformed_ast = transformer.visit(parsed_ast)
transformed_query_str = _print_materialized_hogql(
transformed_ast,
team,
user=user,
bypass_warehouse_access_control=bypass_warehouse_access_control,
)
return {
**hogql_query,
"query": transformed_query_str,
"variables": {},
}
class MaterializationTransformer(CloningVisitor):
"""AST transformer that removes variable WHERE clauses and adds columns to SELECT and GROUP BY.
Each variable gets an aliased column in SELECT (aliased by code_name).
GROUP BY is deduplicated by column_chain to handle same-column range variables
(e.g., hour >= start AND hour < end → GROUP BY hour, not GROUP BY hour, hour).
CTE-aware: when variables live in a CTE, the CTE gets the column addition + WHERE removal,
and the top-level query gets a passthrough column from the CTE.
"""
def __init__(self, variable_infos: list[MaterializableVariable]):
super().__init__()
self.variable_infos = variable_infos
self._current_cte_name: Optional[str] = None
def visit_select_query(self, node: ast.SelectQuery):
new_ctes = self._process_ctes(node)
# Visit the select query itself (without re-visiting CTEs)
original_ctes = node.ctes
node.ctes = None
new_node = super().visit_select_query(node)
node.ctes = original_ctes # Restore original
new_node.ctes = new_ctes
# Add variable columns + remove variable WHERE clauses for current context
vars_for_context = self._vars_for_current_context()
if vars_for_context:
self._add_variable_columns(new_node, vars_for_context)
# Top-level query: add passthrough columns for CTE variables
cte_vars = [v for v in self.variable_infos if v.cte_name is not None]
if self._current_cte_name is None and cte_vars:
self._add_cte_passthrough_columns(new_node, cte_vars)
return new_node
def _process_ctes(self, node: ast.SelectQuery) -> Optional[dict[str, ast.CTE]]:
"""Process CTEs with proper context tracking, returning transformed CTE dict."""
if not node.ctes:
return None
new_ctes: dict[str, ast.CTE] = {}
for cte_name, cte in node.ctes.items():
prev_cte = self._current_cte_name
self._current_cte_name = cte_name
new_expr = self.visit(cte.expr)
self._current_cte_name = prev_cte
# After the CTE body has been cloned/visited, propagate any variable
# whose plan targets this CTE. Definition order is also topological,
# so upstream CTEs have already been rewritten to emit code_name.
for var in self.variable_infos:
if var.cte_name is None:
continue
plan = var.downstream_plans.get(cte_name)
if plan is None:
continue
new_expr = self._apply_downstream_plan(new_expr, var, plan)
new_ctes[cte_name] = ast.CTE(name=cte_name, expr=new_expr, cte_type=cte.cte_type)
return new_ctes
def _apply_downstream_plan(
self,
cte_expr: ast.Expr,
var: MaterializableVariable,
plan: DownstreamCTEPlan,
) -> ast.Expr:
"""Rewrite a cloned downstream CTE body so it emits ``var.code_name``."""
if isinstance(cte_expr, ast.SelectSetQuery):
if plan.shape != DownstreamCTEShape.UNION_ALL or not plan.leg_plans:
return cte_expr
new_initial = self._apply_downstream_plan(cte_expr.initial_select_query, var, plan.leg_plans[0])
assert isinstance(new_initial, (ast.SelectQuery, ast.SelectSetQuery))
new_subsequent: list[ast.SelectSetNode] = []
for i, node in enumerate(cte_expr.subsequent_select_queries):
leg_plan = plan.leg_plans[i + 1]
rewritten = self._apply_downstream_plan(node.select_query, var, leg_plan)
assert isinstance(rewritten, (ast.SelectQuery, ast.SelectSetQuery))
new_subsequent.append(ast.SelectSetNode(select_query=rewritten, set_operator=node.set_operator))
return ast.SelectSetQuery(
initial_select_query=new_initial,
subsequent_select_queries=new_subsequent,
limit=cte_expr.limit,
offset=cte_expr.offset,
limit_percent=cte_expr.limit_percent,
limit_with_ties=cte_expr.limit_with_ties,
)
if not isinstance(cte_expr, ast.SelectQuery):
return cte_expr
sources = plan.propagating_sources
if not sources:
return cte_expr
first_alias = sources[0].alias
# Add `first_alias.code_name AS code_name` to SELECT.
select_addition = ast.Alias(
alias=var.code_name,
expr=ast.Field(chain=[first_alias, var.code_name]),
)
cte_expr.select = [*list(cte_expr.select or []), select_addition]
if plan.shape == DownstreamCTEShape.AGGREGATION:
group_by_field = ast.Field(chain=[var.code_name])
if cte_expr.group_by:
cte_expr.group_by = [*list(cte_expr.group_by), group_by_field]
else:
cte_expr.group_by = [group_by_field]
if plan.shape == DownstreamCTEShape.MULTI_JOIN and len(sources) > 1:
for source in sources[1:]:
predicate = ast.CompareOperation(
op=ast.CompareOperationOp.Eq,
left=ast.Field(chain=[first_alias, var.code_name]),
right=ast.Field(chain=[source.alias, var.code_name]),
)
cte_expr.where = self._append_and(cte_expr.where, predicate)
return cte_expr
@staticmethod
def _append_and(existing: Optional[ast.Expr], new_predicate: ast.Expr) -> ast.Expr:
"""AND-flatten ``new_predicate`` into ``existing``, collapsing nested ``ast.And``."""
if existing is None:
return new_predicate
if isinstance(existing, ast.And):
return ast.And(exprs=[*list(existing.exprs), new_predicate])
return ast.And(exprs=[existing, new_predicate])
def _add_variable_columns(self, node: ast.SelectQuery, vars_for_context: list[MaterializableVariable]) -> None:
"""Add aliased variable columns to SELECT, update GROUP BY, and remove variable WHERE clauses."""
# Backstop: pre-flight analysis normally rejects this first (analyze_variables_for_materialization),
# so reaching here means a pre-flight gap. Surfaced as a 400, not a server fault.
conflict = _select_alias_conflict(node, vars_for_context)
if conflict is not None:
raise MaterializationNotSupportedError(_alias_conflict_reason(conflict))
# A code_name already aliased here is — after the conflict check — the same expression
# already exposed under that name, so skip it; add the rest.
existing_alias_names = {expr.alias for expr in node.select or [] if isinstance(expr, ast.Alias)}
vars_to_add = [var for var in vars_for_context if var.code_name not in existing_alias_names]
select_additions = [self._create_column_field(var) for var in vars_to_add]
if select_additions:
node.select = [*list(node.select or []), *select_additions]
if vars_to_add and (node.group_by is not None or _select_has_aggregate(node)):
self._add_group_by(node, vars_to_add)
if node.where:
node.where = self._remove_variable_from_where(node.where)
def _add_cte_passthrough_columns(self, node: ast.SelectQuery, cte_vars: list[MaterializableVariable]) -> None:
"""Add passthrough columns + GROUP BY at top level for CTE-resident variables."""
passthrough_additions: list[ast.Expr] = [ast.Field(chain=[var.code_name]) for var in cte_vars]
if node.select:
node.select = [*list(node.select), *passthrough_additions]
else:
node.select = passthrough_additions
if node.group_by is not None or _select_has_aggregate(node):
self._add_group_by(node, cte_vars, use_field_ref=True)
def _vars_for_current_context(self) -> list[MaterializableVariable]:
"""Return variables that apply to the current CTE/top-level context."""
return [v for v in self.variable_infos if v.cte_name == self._current_cte_name]
def _add_group_by(
self,
node: ast.SelectQuery,
vars_to_add: list[MaterializableVariable],
use_field_ref: bool = False,
) -> None:
"""Add unique columns to GROUP BY, deduplicating by column_chain."""
existing_keys: set[str] = set()
if node.group_by:
for expr in node.group_by:
if isinstance(expr, ast.Field):
existing_keys.add(".".join(str(c) for c in expr.chain))
seen_keys: set[str] = set()
group_by_additions: list[ast.Expr] = []
for var in vars_to_add:
dedup_key = ".".join(var.column_chain) if var.column_chain else var.column_expression
if use_field_ref:
dedup_key = var.code_name
if dedup_key not in seen_keys and dedup_key not in existing_keys:
seen_keys.add(dedup_key)
if use_field_ref:
group_by_additions.append(ast.Field(chain=[var.code_name]))
else:
group_by_additions.append(_variable_column_expr(var))
if node.group_by:
node.group_by = [*list(node.group_by), *group_by_additions]
elif group_by_additions:
node.group_by = group_by_additions
def _create_column_field(self, var: MaterializableVariable) -> ast.Expr:
return ast.Alias(
alias=var.code_name,
expr=_variable_column_expr(var),
)
def _remove_variable_from_where(self, where_node: Optional[ast.Expr]) -> Optional[ast.Expr]:
if where_node is None:
return None
if isinstance(where_node, ast.CompareOperation):
return None if self._is_variable_comparison(where_node) else where_node
if isinstance(where_node, ast.And):
filtered_exprs = [
expr
for expr in where_node.exprs
if not (isinstance(expr, ast.CompareOperation) and self._is_variable_comparison(expr))
]
if not filtered_exprs:
return None
if len(filtered_exprs) == 1:
return filtered_exprs[0]
return ast.And(exprs=filtered_exprs)
if isinstance(where_node, ast.Or):
raise MaterializationNotSupportedError("Variables in OR conditions not supported")
return where_node
def _is_variable_comparison(self, node: ast.CompareOperation) -> bool:
return any(self._expr_contains_variable(side) for side in (node.left, node.right))
def _expr_contains_variable(self, node: ast.Expr) -> bool:
if isinstance(node, ast.Placeholder) and node.chain and node.chain[0] == "variables":
return True
if isinstance(node, ast.Call):
return any(self._expr_contains_variable(arg) for arg in node.args)
return False
def prepare_insight_query_for_endpoint(query: dict) -> dict:
"""Override breakdown_limit to surface all values; keep breakdown_hide_other_aggregation=False so the
'Other' bucket appears in results if the limit is ever exceeded."""
breakdown_filter = query.get("breakdownFilter")
if not breakdown_filter:
return query
return {
**query,
"breakdownFilter": {
**breakdown_filter,
"breakdown_hide_other_aggregation": False,
"breakdown_limit": ENDPOINT_BREAKDOWN_LIMIT,
},
}
def replace_breakdown_sentinels_in_query(hogql_query: dict) -> dict:
"""Strip internal sentinel string literals from HogQL so S3-stored data is clean. The 'other' sentinel
is embedded in the HogQL by the query builder (in if() and ORDER BY expressions) regardless of
breakdown_hide_other_aggregation, which only affects post-processing."""
query_text = hogql_query.get("query")
if not query_text or not isinstance(query_text, str):
return hogql_query
replacements = {
f"'{BREAKDOWN_NULL_STRING_LABEL}'": "''",
f"'{BREAKDOWN_OTHER_STRING_LABEL}'": "'Other'",
}
for old, new in replacements.items():
query_text = query_text.replace(old, new)
return {**hogql_query, "query": query_text}
def _has_variable_placeholders(hogql_query: dict[str, Any]) -> bool:
query_str = hogql_query.get("query")
if not isinstance(query_str, str) or not query_str:
return False
try:
ast_node = parse_select(query_str)
except Exception:
return False
finder = VariablePlaceholderFinder()
finder.visit(ast_node)
return bool(finder.variable_placeholders)
def build_endpoint_hogql(
insight_query: dict,
team: Team,
bucket_overrides: dict[str, str] | None = None,
*,
user: User | None = None,
bypass_warehouse_access_control: bool = False,
) -> dict:
"""Run the full endpoint conversion pipeline: prepare → convert insight to HogQL → apply variable
materialization plan → strip breakdown sentinels. Pure function; no DB side effects."""
mat_query = prepare_insight_query_for_endpoint(insight_query)
hogql_query = convert_insight_query_to_hogql(
mat_query,
team,
user=user,
bypass_warehouse_access_control=bypass_warehouse_access_control,
)
if insight_query.get("variables"):
can_materialize, reason, variable_infos = analyze_variables_for_materialization(
insight_query, bucket_overrides=bucket_overrides
)
# Untransformed placeholders can't resolve at materialization time, so the run would fail.
if not can_materialize and _has_variable_placeholders(insight_query):
raise MaterializationNotSupportedError(reason)
if can_materialize and variable_infos:
hogql_query = transform_query_for_materialization(
hogql_query,
variable_infos,
team,
bucket_overrides=bucket_overrides,
user=user,
bypass_warehouse_access_control=bypass_warehouse_access_control,
)
return replace_breakdown_sentinels_in_query(hogql_query)