Skip to content

: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.

18.1 KB
Raw Blame
//! ClickHouse person-scan SQL: the byte-frozen renderers for the boundary and range scans over the
//! `person` table. Depends on `domain`; never on `store`.
//!
//! Both queries group by person with `team_id` equality-fixed, and range predicates are defined in
//! ClickHouse's UUID order. Every column reference is table-qualified because the output alias is
//! itself named `id`: ClickHouse binds a bare `id` in `WHERE`, `GROUP BY`, or `ORDER BY` to that
//! alias rather than to the column, so an unqualified `id >= toUUID(…)` compares `String` against
//! `UUID` (no supertype, so the query cannot execute) and an unqualified `ORDER BY id` streams
//! boundaries in text order while the range predicates read them back in UUID order.
//! `optimize_aggregation_in_order = 1` keeps both aggregations streaming; the client's
//! external-group-by spill guard backstops a silent optimizer fallback.
//!
//! The range scan reads the ReplacingMergeTree's latest row per person (`argMax(…, version)`). Its
//! horizon appears twice on purpose: the `p.id IN (…)` prefilter keeps the `_timestamp` minmax skip
//! index usable (a bare `HAVING` would decompress every historical version of every person), while
//! the `HAVING` clause states the update-recency semantics against the group, so `argMax` still sees
//! all versions of the surviving ids. The prefilter's set holds one chunk's range of ids, and the
//! client caps it with `max_bytes_in_set`.
//!
//! The boundary query only sizes the chunks, since the ranges tile the whole UUID space and each
//! range scan applies the exact predicate. So it filters versions by `_timestamp` without collapsing
//! them, counting deleted persons toward the stride, and builds no whole-team id set. Only the
//! boundary query orders its output; the range scan's fold is order-independent, and an unneeded
//! `ORDER BY` would materialize the whole chunk result.
//!
//! The range scan may also carry a key-presence filter, which drops a person whose latest blob is a
//! JSON object holding none of the keys the run's conditions read — a person the fold would prune
//! anyway, at the cost of transferring and decoding their whole property bag first. See
//! [`key_presence_predicate`] for why it admits every non-object blob.

use cohort_core::filters::TeamId;

use crate::clickhouse::sql::{key_list, MAX_GET_QUERY_BYTES};
use crate::domain::{PersonRange, ProjectedKeys, UtcMillis};

/// The rendered ceiling one scan may reach, leaving 2 KB of margin below the GET limit.
///
/// Conservative in the safe direction: the `?` → `??` escaping in
/// [`clickhouse_string_literal`](super::sql::clickhouse_string_literal) is undone by the client's
/// template parser, so the query that reaches the server is never longer than the text measured
/// here.
const MAX_RENDERED_SCAN_BYTES: usize = MAX_GET_QUERY_BYTES - 2048;

/// What became of a run's key filter on one chunk, as a bounded metric label.
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum ScanFilterOutcome {
    /// The scan drops object rows carrying none of the run's keys.
    KeyPresence,
    /// The run asked for no filter: the healer policy, or a condition the VM has to decide.
    None,
    /// The rendered filter would have pushed the query past the readonly ceiling.
    TooLong,
}

impl ScanFilterOutcome {
    pub const ALL: [Self; 3] = [Self::KeyPresence, Self::None, Self::TooLong];

    pub const fn as_str(self) -> &'static str {
        match self {
            Self::KeyPresence => "key_presence",
            Self::None => "none",
            Self::TooLong => "too_long",
        }
    }
}

/// The rendered person scan's inputs, constructed only from already-proven types.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct PersonScanSpec {
    team_id: TeamId,
    scan_since_ms: i64,
    range: PersonRange,
    key_filter: Option<ProjectedKeys>,
    filter_outcome: ScanFilterOutcome,
}

impl PersonScanSpec {
    /// `key_filter` names the top-level `properties` keys a person must carry to be worth scanning;
    /// `None` scans the whole range.
    ///
    /// The size guard lives here rather than in the renderer, which renders once to measure and
    /// drops the filter if it does not fit. [`person_scan_sql`] then stays a pure rendering of a
    /// spec already proven to fit, at the cost of one extra render per chunk.
    pub fn new(
        team_id: TeamId,
        scan_since: UtcMillis,
        range: PersonRange,
        key_filter: Option<ProjectedKeys>,
    ) -> Self {
        let filter_outcome = match &key_filter {
            Some(_) => ScanFilterOutcome::KeyPresence,
            None => ScanFilterOutcome::None,
        };
        let spec = Self {
            team_id,
            scan_since_ms: scan_since.as_i64(),
            range,
            key_filter,
            filter_outcome,
        };
        if person_scan_sql(&spec).len() <= MAX_RENDERED_SCAN_BYTES {
            return spec;
        }
        Self {
            key_filter: None,
            filter_outcome: ScanFilterOutcome::TooLong,
            ..spec
        }
    }

    pub const fn filter_outcome(&self) -> ScanFilterOutcome {
        self.filter_outcome
    }
}

pub fn person_boundaries_sql(team_id: TeamId, scan_since: UtcMillis) -> String {
    format!(
        "SELECT toString(p.id) AS id\nFROM person AS p\nWHERE p.team_id = {team} AND p._timestamp >= fromUnixTimestamp64Milli({since})\nGROUP BY p.id\nORDER BY p.id\nSETTINGS optimize_aggregation_in_order = 1",
        team = team_id.0,
        since = scan_since.as_i64(),
    )
}

pub fn person_scan_sql(spec: &PersonScanSpec) -> String {
    format!(
        "SELECT toString(p.id) AS id, argMax(p.properties, p.version) AS properties\nFROM person AS p\nWHERE p.team_id = {team} AND {outer_range} AND p.id IN (\n    SELECT recent.id FROM person AS recent\n    WHERE recent.team_id = {team} AND {prefilter_range}\n      AND recent._timestamp >= fromUnixTimestamp64Milli({since})\n)\nGROUP BY p.id\nHAVING argMax(p.is_deleted, p.version) = 0 AND max(p._timestamp) >= fromUnixTimestamp64Milli({since}){key_filter}\nSETTINGS optimize_aggregation_in_order = 1",
        team = spec.team_id.0,
        outer_range = range_predicate("p", spec.range),
        // The prefilter carries the same range, against its own table reference, so the primary key
        // prunes it too.
        prefilter_range = range_predicate("recent", spec.range),
        since = spec.scan_since_ms,
        key_filter = spec
            .key_filter
            .as_ref()
            .map(key_presence_predicate)
            .unwrap_or_default(),
    )
}

/// Drop object blobs carrying none of `keys`.
///
/// Costs two server-side JSON parses per scanned group, `JSONType` and `JSONExtractKeys`, against
/// the scan's `max_execution_time` budget. Cheap next to the transfer and per-row evaluation it
/// removes, but it is new ClickHouse work and belongs in the rollout measurement.
///
/// Every *non*-object blob is admitted, deliberately. `JSONType` reports `Null` for a missing,
/// empty, literal-`null` or malformed blob — and also for a well-formed document holding a number
/// ClickHouse cannot represent, which `serde_json` parses and whose keys the VM then reads. Dropping
/// the `Null` class would therefore drop genuine members, so the filter admits a strict superset of
/// what the seeder can emit and exactness stays with the VM.
///
/// The aggregate is spelled out twice rather than referencing the `properties` output alias: a bare
/// column name in `HAVING` binds to that alias, which is the mistake
/// `no_column_reference_can_resolve_to_the_output_alias` exists to catch. ClickHouse computes one
/// `argMax` state for the repeated expression.
fn key_presence_predicate(keys: &ProjectedKeys) -> String {
    let key_list = key_list(keys);
    format!(
        "\n   AND (JSONType(argMax(p.properties, p.version)) != 'Object' OR hasAny(JSONExtractKeys(argMax(p.properties, p.version)), [{key_list}]))"
    )
}

/// One table reference's range predicate. `lo` is always rendered, because band 0 carries the nil
/// UUID and `id >= nil` is a tautology under any byte ordering, so the predicate shape stays
/// uniform across bands.
fn range_predicate(table: &str, range: PersonRange) -> String {
    let hi_predicate = range
        .hi()
        .map(|hi| format!(" AND {table}.id < toUUID('{hi}')"))
        .unwrap_or_default();
    format!(
        "{table}.id >= toUUID('{lo}'){hi}",
        lo = range.lo(),
        hi = hi_predicate,
    )
}

#[cfg(test)]
mod tests {
    use uuid::Uuid;

    use super::*;

    const SINCE: UtcMillis = UtcMillis::new(1_780_000_000_000);

    fn range(lo: Uuid, hi: Option<Uuid>) -> PersonRange {
        PersonRange::new(lo, hi).unwrap()
    }

    fn scan_spec(range: PersonRange, key_filter: Option<ProjectedKeys>) -> PersonScanSpec {
        PersonScanSpec::new(TeamId(2), SINCE, range, key_filter)
    }

    fn keys(names: &[&str]) -> ProjectedKeys {
        ProjectedKeys::new(names.iter().map(|name| (*name).to_owned()).collect())
            .expect("the test key sets are non-empty")
    }

    /// The boundary query spans a whole team, so it must not build an `IN (SELECT …)` set.
    #[test]
    fn boundaries_sql_is_byte_frozen() {
        assert_eq!(
            person_boundaries_sql(TeamId(2), SINCE),
            "SELECT toString(p.id) AS id\nFROM person AS p\nWHERE p.team_id = 2 AND p._timestamp >= fromUnixTimestamp64Milli(1780000000000)\nGROUP BY p.id\nORDER BY p.id\nSETTINGS optimize_aggregation_in_order = 1"
        );
    }

    #[test]
    fn scan_sql_is_byte_frozen_for_every_range_shape() {
        let boundary_a = Uuid::parse_str("01928aaa-bbbb-cccc-dddd-eeeeeeeeeeee").unwrap();
        let boundary_b = Uuid::parse_str("7f000000-0000-0000-0000-000000000001").unwrap();

        // Band 0: nil lo (tautology) with a bounded hi.
        assert_eq!(
            person_scan_sql(&scan_spec(range(Uuid::nil(), Some(boundary_a)), None)),
            "SELECT toString(p.id) AS id, argMax(p.properties, p.version) AS properties\nFROM person AS p\nWHERE p.team_id = 2 AND p.id >= toUUID('00000000-0000-0000-0000-000000000000') AND p.id < toUUID('01928aaa-bbbb-cccc-dddd-eeeeeeeeeeee') AND p.id IN (\n    SELECT recent.id FROM person AS recent\n    WHERE recent.team_id = 2 AND recent.id >= toUUID('00000000-0000-0000-0000-000000000000') AND recent.id < toUUID('01928aaa-bbbb-cccc-dddd-eeeeeeeeeeee')\n      AND recent._timestamp >= fromUnixTimestamp64Milli(1780000000000)\n)\nGROUP BY p.id\nHAVING argMax(p.is_deleted, p.version) = 0 AND max(p._timestamp) >= fromUnixTimestamp64Milli(1780000000000)\nSETTINGS optimize_aggregation_in_order = 1"
        );

        // Interior band: both bounds present.
        assert_eq!(
            person_scan_sql(&scan_spec(range(boundary_a, Some(boundary_b)), None)),
            "SELECT toString(p.id) AS id, argMax(p.properties, p.version) AS properties\nFROM person AS p\nWHERE p.team_id = 2 AND p.id >= toUUID('01928aaa-bbbb-cccc-dddd-eeeeeeeeeeee') AND p.id < toUUID('7f000000-0000-0000-0000-000000000001') AND p.id IN (\n    SELECT recent.id FROM person AS recent\n    WHERE recent.team_id = 2 AND recent.id >= toUUID('01928aaa-bbbb-cccc-dddd-eeeeeeeeeeee') AND recent.id < toUUID('7f000000-0000-0000-0000-000000000001')\n      AND recent._timestamp >= fromUnixTimestamp64Milli(1780000000000)\n)\nGROUP BY p.id\nHAVING argMax(p.is_deleted, p.version) = 0 AND max(p._timestamp) >= fromUnixTimestamp64Milli(1780000000000)\nSETTINGS optimize_aggregation_in_order = 1"
        );

        // Last band: unbounded high.
        assert_eq!(
            person_scan_sql(&scan_spec(range(boundary_b, None), None)),
            "SELECT toString(p.id) AS id, argMax(p.properties, p.version) AS properties\nFROM person AS p\nWHERE p.team_id = 2 AND p.id >= toUUID('7f000000-0000-0000-0000-000000000001') AND p.id IN (\n    SELECT recent.id FROM person AS recent\n    WHERE recent.team_id = 2 AND recent.id >= toUUID('7f000000-0000-0000-0000-000000000001')\n      AND recent._timestamp >= fromUnixTimestamp64Milli(1780000000000)\n)\nGROUP BY p.id\nHAVING argMax(p.is_deleted, p.version) = 0 AND max(p._timestamp) >= fromUnixTimestamp64Milli(1780000000000)\nSETTINGS optimize_aggregation_in_order = 1"
        );
    }

    /// The filter is appended to the `HAVING` clause, before `SETTINGS`, and spells the aggregate
    /// out twice rather than naming the `properties` output alias. Frozen byte for byte because the
    /// predicate's exact text is what `tests/ch_person_key_filter.rs` measures against a real server.
    #[test]
    fn scan_sql_is_byte_frozen_with_the_key_presence_filter() {
        let boundary = Uuid::parse_str("01928aaa-bbbb-cccc-dddd-eeeeeeeeeeee").unwrap();
        assert_eq!(
            person_scan_sql(&scan_spec(
                range(Uuid::nil(), Some(boundary)),
                Some(keys(&["email", "plan"])),
            )),
            "SELECT toString(p.id) AS id, argMax(p.properties, p.version) AS properties\nFROM person AS p\nWHERE p.team_id = 2 AND p.id >= toUUID('00000000-0000-0000-0000-000000000000') AND p.id < toUUID('01928aaa-bbbb-cccc-dddd-eeeeeeeeeeee') AND p.id IN (\n    SELECT recent.id FROM person AS recent\n    WHERE recent.team_id = 2 AND recent.id >= toUUID('00000000-0000-0000-0000-000000000000') AND recent.id < toUUID('01928aaa-bbbb-cccc-dddd-eeeeeeeeeeee')\n      AND recent._timestamp >= fromUnixTimestamp64Milli(1780000000000)\n)\nGROUP BY p.id\nHAVING argMax(p.is_deleted, p.version) = 0 AND max(p._timestamp) >= fromUnixTimestamp64Milli(1780000000000)\n   AND (JSONType(argMax(p.properties, p.version)) != 'Object' OR hasAny(JSONExtractKeys(argMax(p.properties, p.version)), ['email', 'plan']))\nSETTINGS optimize_aggregation_in_order = 1"
        );
    }

    /// A key is customer text. The single quote is the one escape whose failure would be an
    /// injection rather than a wrong answer, and a bare `?` is swallowed by the client's own
    /// template parser before the SQL is sent.
    #[test]
    fn key_literals_are_escaped_for_both_the_server_and_the_client_template() {
        let rendered = person_scan_sql(&scan_spec(
            range(Uuid::nil(), None),
            Some(keys(&["quote' OR 1 = 1 --", "back\\slash", "why?fields"])),
        ));
        let predicate = rendered
            .split_once("OR hasAny(")
            .expect("the filter renders")
            .1;
        assert!(
            predicate.starts_with(
                "JSONExtractKeys(argMax(p.properties, p.version)), ['back\\\\slash', 'quote\\' OR 1 = 1 --', 'why??fields'])"
            ),
            "key literals escaped as: {predicate}"
        );
    }

    /// The `cohort_seeder` profile runs `readonly=2`, which refuses a query over 8192 bytes: a key
    /// list long enough to cross that must cost the filter, not every chunk of the run.
    #[test]
    fn an_oversized_key_list_drops_the_filter_rather_than_the_query() {
        let short = scan_spec(range(Uuid::nil(), None), Some(keys(&["email"])));
        assert_eq!(short.filter_outcome(), ScanFilterOutcome::KeyPresence);
        assert!(person_scan_sql(&short).contains("hasAny("));

        let long: Vec<String> = (0..500)
            .map(|index| format!("property_number_{index}"))
            .collect();
        let long = ProjectedKeys::new(long.into_iter().collect()).unwrap();
        let spec = scan_spec(range(Uuid::nil(), None), Some(long));
        assert_eq!(spec.filter_outcome(), ScanFilterOutcome::TooLong);
        let rendered = person_scan_sql(&spec);
        assert!(
            !rendered.contains("hasAny("),
            "the filter survived the guard"
        );
        assert!(rendered.len() <= MAX_RENDERED_SCAN_BYTES);

        assert_eq!(
            scan_spec(range(Uuid::nil(), None), None).filter_outcome(),
            ScanFilterOutcome::None
        );
    }

    /// The byte-frozen tests above compare each query against a copy of itself, so they accept any
    /// SQL the renderer emits, including SQL no ClickHouse can execute. This one states the property
    /// that made the shipped query unexecutable: an unqualified column reference binds to the
    /// `AS id` output alias, turning `id >= toUUID(…)` into `String >= UUID` (no supertype) and
    /// `ORDER BY id` into a text-ordered boundary stream the UUID-ordered range predicates cannot
    /// tile.
    #[test]
    fn no_column_reference_can_resolve_to_the_output_alias() {
        let boundary = Uuid::parse_str("01928aaa-bbbb-cccc-dddd-eeeeeeeeeeee").unwrap();
        let mut rendered = vec![person_boundaries_sql(TeamId(2), SINCE)];
        // Both predicate shapes: a bounded range and the final unbounded one.
        for hi in [Some(boundary), None] {
            for filter in [None, Some(keys(&["email", "plan"]))] {
                rendered.push(person_scan_sql(&scan_spec(range(Uuid::nil(), hi), filter)));
            }
        }

        for sql in &rendered {
            assert_eq!(
                unqualified_column_references(sql),
                Vec::<&str>::new(),
                "unqualified column reference in:\n{sql}"
            );
        }
    }

    const PERSON_COLUMNS: [&str; 6] = [
        "id",
        "team_id",
        "properties",
        "version",
        "is_deleted",
        "_timestamp",
    ];

    /// Every `person` column name in `sql` that is neither `<table>.`-prefixed nor the `AS <name>`
    /// alias being defined, so it resolves against the query's scope instead of a named table.
    fn unqualified_column_references(sql: &str) -> Vec<&str> {
        let mut identifiers = Vec::new();
        let mut start = None;
        for (index, character) in sql.char_indices() {
            match (start, character.is_ascii_alphanumeric() || character == '_') {
                (None, true) => start = Some(index),
                (Some(begin), false) => {
                    identifiers.push((begin, index));
                    start = None;
                }
                _ => {}
            }
        }
        if let Some(begin) = start {
            identifiers.push((begin, sql.len()));
        }

        identifiers
            .into_iter()
            .filter(|(begin, end)| {
                PERSON_COLUMNS.contains(&&sql[*begin..*end])
                    && !sql[..*begin].ends_with('.')
                    && !sql[..*begin].ends_with("AS ")
            })
            .map(|(begin, end)| &sql[begin..end])
            .collect()
    }
}