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.

25.5 KB
Raw Blame
import datetime
from collections.abc import Iterable
from datetime import timedelta
from time import sleep

import pytest
import time_machine
from posthog.test.base import BaseTest, ClickhouseTestMixin, _create_event, get_index_from_explain
from unittest import TestCase
from unittest.mock import patch

from parameterized import parameterized
from tenacity import retry, stop_after_attempt, wait_exponential

from posthog.clickhouse.client import sync_execute
from posthog.clickhouse.materialized_columns import TablesWithMaterializedColumns
from posthog.conftest import create_clickhouse_tables
from posthog.constants import GROUP_TYPES_LIMIT
from posthog.models.event.sql import EVENTS_DATA_TABLE
from posthog.models.property import PropertyName, TableColumn
from posthog.settings import CLICKHOUSE_DATABASE

from ee.clickhouse.materialized_columns.columns import (
    MATERIALIZATION_VALID_TABLES,
    MaterializedColumn,
    MaterializedColumnDetails,
    _clear_materialized_columns_cache,
    backfill_materialized_columns,
    drop_column,
    get_bloom_filter_index_name,
    get_bloom_filter_lower_index_name,
    get_enabled_materialized_columns,
    get_materialized_columns,
    get_minmax_index_name,
    get_ngram_lower_index_name,
    materialize,
    update_column_is_disabled,
)

EVENTS_TABLE_DEFAULT_MATERIALIZED_COLUMNS = [f"$group_{i}" for i in range(GROUP_TYPES_LIMIT)] + [
    "$session_id",
    "$window_id",
]


class TestMaterializedColumnDetails(TestCase):
    def test_column_comment_formats(self):
        old_format_comment = "column_materializer::foo"
        old_format_details = MaterializedColumnDetails.from_column_comment(old_format_comment)
        assert old_format_details == MaterializedColumnDetails(
            "properties",  # the default
            "foo",
            is_disabled=False,
        )
        # old comment format is implicitly upgraded to the newer format when serializing
        assert old_format_details.as_column_comment() == "column_materializer::properties::foo"

        new_format_comment = "column_materializer::person_properties::bar"
        new_format_details = MaterializedColumnDetails.from_column_comment(new_format_comment)
        assert new_format_details == MaterializedColumnDetails(
            "person_properties",
            "bar",
            is_disabled=False,
        )
        assert new_format_details.as_column_comment() == new_format_comment

        new_format_disabled_comment = "column_materializer::person_properties::bar::disabled"
        new_format_disabled_details = MaterializedColumnDetails.from_column_comment(new_format_disabled_comment)
        assert new_format_disabled_details == MaterializedColumnDetails(
            "person_properties",
            "bar",
            is_disabled=True,
        )
        assert new_format_disabled_details.as_column_comment() == new_format_disabled_comment

        with pytest.raises(ValueError):
            MaterializedColumnDetails.from_column_comment("bad-prefix::property")

        with pytest.raises(ValueError):
            MaterializedColumnDetails.from_column_comment("bad-prefix::column::property")

        with pytest.raises(ValueError):
            MaterializedColumnDetails.from_column_comment("column_materializer::column::property::enabled")


class TestMaterializedColumns(ClickhouseTestMixin, BaseTest):
    @classmethod
    def setUpClass(cls) -> None:
        cls.recreate_database()
        super().setUpClass()

    def tearDown(self):
        self.recreate_database()
        super().tearDown()

    @staticmethod
    def recreate_database() -> None:
        # Dropping the database removes materialized columns behind the metadata cache's back.
        for table in MATERIALIZATION_VALID_TABLES:
            _clear_materialized_columns_cache(table)
        sync_execute(f"DROP DATABASE {CLICKHOUSE_DATABASE} SYNC")
        sync_execute(f"CREATE DATABASE {CLICKHOUSE_DATABASE}")
        create_clickhouse_tables()

    def test_unknown_table(self):
        assert {} == get_materialized_columns("some weird string")

    def test_get_columns_default(self):
        assert sorted([property_name for property_name, _ in get_materialized_columns("events")]) == sorted(
            EVENTS_TABLE_DEFAULT_MATERIALIZED_COLUMNS
        )
        assert sorted(get_materialized_columns("person")) == sorted([])

    def test_caching_and_materializing(self):
        base_time = datetime.datetime.fromisoformat("2020-01-04T13:01:01Z")
        with time_machine.travel(base_time, tick=False):
            materialize("events", "$foo", create_minmax_index=True)
            materialize("events", "$bar", create_minmax_index=True)
            materialize("person", "$zeta", create_minmax_index=True)

            assert sorted(
                [
                    property_name
                    for property_name, _ in get_enabled_materialized_columns("events", use_cache=True).keys()
                ]
            ) == sorted(["$foo", "$bar", *EVENTS_TABLE_DEFAULT_MATERIALIZED_COLUMNS])
            assert sorted(get_enabled_materialized_columns("person", use_cache=True).keys()) == sorted(
                [("$zeta", "properties")]
            )

            materialize("events", "abc", create_minmax_index=True)

            assert sorted(
                [
                    property_name
                    for property_name, _ in get_enabled_materialized_columns("events", use_cache=True).keys()
                ]
            ) == sorted(["$foo", "$bar", *EVENTS_TABLE_DEFAULT_MATERIALIZED_COLUMNS])

        # The cache is updated in the background, so we need to poll for cache update with retry
        # otherwise this flakes
        @retry(wait=wait_exponential(multiplier=0.5, min=0.5, max=2), stop=stop_after_attempt(5))
        def check_cache_updated():
            assert sorted(
                [
                    property_name
                    for property_name, _ in get_enabled_materialized_columns("events", use_cache=True).keys()
                ]
            ) == sorted(["$foo", "$bar", "abc", *EVENTS_TABLE_DEFAULT_MATERIALIZED_COLUMNS])

        with time_machine.travel(base_time + timedelta(minutes=59), tick=False):
            check_cache_updated()

    @patch("secrets.choice", return_value="X")
    def test_materialized_column_naming(self, mock_choice):
        assert materialize("events", "$foO();--sqlinject", create_minmax_index=True).name == "mat_$foO_____sqlinject"

        mock_choice.return_value = "Y"
        assert (
            materialize("events", "$foO();ääsqlinject", create_minmax_index=True).name == "mat_$foO_____sqlinject_YYYY"
        )

        mock_choice.return_value = "Z"
        assert (
            materialize("events", "$foO_____sqlinject", create_minmax_index=True).name == "mat_$foO_____sqlinject_ZZZZ"
        )

        assert materialize("person", "SoMePrOp", create_minmax_index=True).name == "pmat_SoMePrOp"

    def test_backfilling_data(self):
        sync_execute("ALTER TABLE events DROP COLUMN IF EXISTS mat_prop")
        sync_execute("ALTER TABLE events DROP COLUMN IF EXISTS mat_another")

        _create_event(
            event="some_event",
            distinct_id="1",
            team=self.team,
            timestamp="2020-01-01 00:00:00",
            properties={"prop": 1},
        )
        _create_event(
            event="some_event",
            distinct_id="1",
            team=self.team,
            timestamp="2021-05-02 00:00:00",
            properties={"prop": 2, "another": 5},
        )
        _create_event(
            event="some_event",
            distinct_id="1",
            team=self.team,
            timestamp="2021-05-03 00:00:00",
            properties={"prop": 3},
        )
        _create_event(
            event="another_event",
            distinct_id="1",
            team=self.team,
            timestamp="2021-05-04 00:00:00",
        )
        _create_event(
            event="third_event",
            distinct_id="1",
            team=self.team,
            timestamp="2021-05-05 00:00:00",
            properties={"prop": 4},
        )
        _create_event(
            event="fourth_event",
            distinct_id="1",
            team=self.team,
            timestamp="2021-05-06 00:00:00",
            properties={"another": 6},
        )

        columns = [
            materialize("events", "prop", create_minmax_index=True),
            materialize("events", "another", create_minmax_index=True),
        ]

        assert self._count_materialized_rows("mat_prop") == 0
        assert self._count_materialized_rows("mat_another") == 0

        with time_machine.travel("2021-05-10T14:00:01Z", tick=False):
            backfill_materialized_columns(
                "events",
                columns,
                timedelta(days=50),
                test_settings={"mutations_sync": "0"},
            )

        _create_event(
            event="fifth_event",
            distinct_id="1",
            team=self.team,
            timestamp="2021-05-07 00:00:00",
            properties={"another": 7},
        )

        iterations = 0
        while self._get_count_of_mutations_running() > 0 and iterations < 100:
            sleep(0.1)
            iterations += 1

        assert self._count_materialized_rows("mat_prop") >= 4
        assert self._count_materialized_rows("mat_another") >= 4

        assert sync_execute("SELECT mat_prop, mat_another FROM events ORDER BY timestamp") == [
            ("1", ""),
            ("2", "5"),
            ("3", ""),
            ("", ""),
            ("4", ""),
            ("", "6"),
            ("", "7"),
        ]

    def test_column_types(self):
        columns = [
            materialize("events", "myprop", create_minmax_index=True),
            materialize("events", "myprop_nullable", create_minmax_index=True, is_nullable=True),
            materialize(
                "events",
                "myprop_float",
                create_minmax_index=True,
                is_nullable=True,
                column_type="Nullable(Float64)",
            ),
        ]

        expr_nonnullable = "replaceRegexpAll(JSONExtractRaw(properties, 'myprop'), '^\"|\"$', '')"
        expr_nullable = "JSONExtract(properties, 'myprop_nullable', 'Nullable(String)')"
        expr_float = "JSONExtract(properties, 'myprop_float', 'Nullable(Float64)')"

        backfill_materialized_columns("events", columns, timedelta(days=50))
        assert self._get_column_types("mat_myprop") == ("String", "DEFAULT", expr_nonnullable)
        assert self._get_column_types("mat_myprop_nullable") == ("Nullable(String)", "DEFAULT", expr_nullable)
        assert self._get_column_types("mat_myprop_float") == ("Nullable(Float64)", "DEFAULT", expr_float)

    def _count_materialized_rows(self, column):
        return sync_execute(
            """
            SELECT sum(rows)
            FROM system.parts_columns
            WHERE database = %(database)s
              AND table = %(table)s
              AND column = %(column)s
        """,
            {
                "database": CLICKHOUSE_DATABASE,
                "table": EVENTS_DATA_TABLE(),
                "column": column,
            },
        )[0][0]

    def _get_count_of_mutations_running(self) -> int:
        return sync_execute(
            """
            SELECT count(*)
            FROM system.mutations
            WHERE is_done = 0
        """
        )[0][0]

    def _get_column_types(self, column: str):
        return sync_execute(
            """
            SELECT type, default_kind, default_expression
            FROM system.columns
            WHERE database = %(database)s AND table = %(table)s AND name = %(column)s
            """,
            {
                "database": CLICKHOUSE_DATABASE,
                "table": EVENTS_DATA_TABLE(),
                "column": column,
            },
        )[0]

    def test_lifecycle(self):
        table: TablesWithMaterializedColumns = "events"
        property_names = ["foo", "bar"]
        source_column: TableColumn = "properties"

        # create materialized columns
        materialized_columns = {}
        for property_name in property_names:
            materialized_columns[property_name] = materialize(
                table, property_name, table_column=source_column, create_minmax_index=True
            ).name

        assert set(property_names) == materialized_columns.keys()

        # ensure they exist everywhere
        for property_name, destination_column in materialized_columns.items():
            key = (property_name, source_column)
            assert get_materialized_columns(table)[key].name == destination_column
            assert MaterializedColumn.get(table, destination_column) == MaterializedColumn(
                destination_column,
                MaterializedColumnDetails(source_column, property_name, is_disabled=False),
                is_nullable=False,
                column_type="String",
                has_minmax_index=True,
            )

        # disable them and ensure updates apply as needed
        update_column_is_disabled(table, materialized_columns.values(), is_disabled=True)
        for property_name, destination_column in materialized_columns.items():
            key = (property_name, source_column)
            assert get_materialized_columns(table)[key].name == destination_column
            assert MaterializedColumn.get(table, destination_column) == MaterializedColumn(
                destination_column,
                MaterializedColumnDetails(source_column, property_name, is_disabled=True),
                is_nullable=False,
                column_type="String",
                has_minmax_index=True,
            )

        # re-enable them and ensure updates apply as needed
        update_column_is_disabled(table, materialized_columns.values(), is_disabled=False)
        for property_name, destination_column in materialized_columns.items():
            key = (property_name, source_column)
            assert get_materialized_columns(table)[key].name == destination_column
            assert MaterializedColumn.get(table, destination_column) == MaterializedColumn(
                destination_column,
                MaterializedColumnDetails(source_column, property_name, is_disabled=False),
                is_nullable=False,
                column_type="String",
                has_minmax_index=True,
            )

        # drop them and ensure updates apply as needed
        drop_column(table, materialized_columns.values())
        for property_name, destination_column in materialized_columns.items():
            key = (property_name, source_column)
            assert key not in get_materialized_columns(table)
            with pytest.raises(ValueError):
                MaterializedColumn.get(table, destination_column)

    def _get_latest_mutation_id(self, table: str) -> str:
        [(mutation_id,)] = sync_execute(
            """
            SELECT max(mutation_id)
            FROM system.mutations
            WHERE
                database = currentDatabase()
                AND table = %(table)s
            """,
            {"table": table},
        )
        return mutation_id

    def _get_mutations_since_id(self, table: str, id: str) -> Iterable[str]:
        return [
            command
            for (command,) in sync_execute(
                """
                SELECT command
                FROM system.mutations
                WHERE
                    database = currentDatabase()
                    AND table = %(table)s
                    AND mutation_id > %(mutation_id)s
                ORDER BY mutation_id
                """,
                {"table": table, "mutation_id": id},
            )
        ]

    def test_drop_optimized_no_index(self):
        table: TablesWithMaterializedColumns = (
            "person"  # little bit easier than events because no shard awareness needed
        )
        property: PropertyName = "myprop"
        source_column: TableColumn = "properties"

        destination_column = materialize(table, property, table_column=source_column, create_minmax_index=False)

        latest_mutation_id_before_drop = self._get_latest_mutation_id(table)

        drop_column(table, destination_column.name)

        mutations_ran = self._get_mutations_since_id(table, latest_mutation_id_before_drop)
        assert not any("DROP INDEX" in mutation for mutation in mutations_ran)

    def test_drop_optimized_no_column(self):
        table: TablesWithMaterializedColumns = (
            "person"  # little bit easier than events because no shard awareness needed
        )
        property: PropertyName = "myprop"
        source_column: TableColumn = "properties"

        # create the materialized column
        destination_column = materialize(table, property, table_column=source_column, create_minmax_index=False)

        sync_execute(f"ALTER TABLE {table} DROP COLUMN {destination_column.name}", settings={"alter_sync": 1})

        latest_mutation_id_before_drop = self._get_latest_mutation_id(table)

        drop_column(table, destination_column.name)

        mutations_ran = self._get_mutations_since_id(table, latest_mutation_id_before_drop)
        assert not any("DROP COLUMN" in mutation for mutation in mutations_ran)

    def test_minmax_index_usage(self):
        property_name = "numeric_prop"
        _create_event(
            team=self.team,
            event="test_event",
            distinct_id="user1",
            properties={property_name: "100"},
        )

        column = materialize("events", property_name, create_minmax_index=True)
        index_name = get_minmax_index_name(column.name)

        # Verify index appears in EXPLAIN for range query
        query = f"SELECT count() FROM {EVENTS_DATA_TABLE()} WHERE {column.name} > '50'"
        index_info = get_index_from_explain(query, index_name)
        assert index_info is not None, f"MinMax index {index_name} should appear in EXPLAIN output"

    def test_bloom_filter_index_usage(self):
        property_name = "category_prop"
        _create_event(
            team=self.team,
            event="test_event",
            distinct_id="user1",
            properties={property_name: "category_a"},
        )

        column = materialize("events", property_name, create_bloom_filter_index=True)
        index_name = get_bloom_filter_index_name(column.name)

        # Verify index appears in EXPLAIN for equality query
        query = f"SELECT count() FROM {EVENTS_DATA_TABLE()} WHERE {column.name} = 'category_a'"
        index_info = get_index_from_explain(query, index_name)
        assert index_info is not None, f"Bloom filter index {index_name} should appear in EXPLAIN output"

    def test_ngram_lower_index_usage_non_nullable(self):
        property_name = "text_prop"
        _create_event(
            team=self.team,
            event="test_event",
            distinct_id="user1",
            properties={property_name: "Hello World Text"},
        )

        column = materialize("events", property_name, create_ngram_lower_index=True, is_nullable=False)
        index_name = get_ngram_lower_index_name(column.name)

        # Verify index appears in EXPLAIN for case-insensitive LIKE query
        query = f"SELECT count() FROM {EVENTS_DATA_TABLE()} WHERE lower({column.name}) LIKE '%world%'"
        index_info = get_index_from_explain(query, index_name)
        assert index_info is not None, f"N-gram index {index_name} should appear in EXPLAIN output"

    def test_ngram_lower_index_usage_nullable(self):
        property_name = "text_prop"
        _create_event(
            team=self.team,
            event="test_event",
            distinct_id="user1",
            properties={property_name: "Hello World Text"},
        )

        column = materialize("events", property_name, create_ngram_lower_index=True, is_nullable=True)
        index_name = get_ngram_lower_index_name(column.name)

        # Verify index appears in EXPLAIN for case-insensitive LIKE query
        query = f"SELECT count() FROM {EVENTS_DATA_TABLE()} WHERE lower(coalesce({column.name}, '')) LIKE '%world%'"
        index_info = get_index_from_explain(query, index_name)
        assert index_info is not None, f"N-gram index {index_name} should appear in EXPLAIN output"

    @parameterized.expand([("non_nullable", False), ("nullable", True)])
    def test_bloom_filter_lower_index_usage(self, _name, is_nullable):
        property_name = "ci_category_prop"
        _create_event(
            team=self.team,
            event="test_event",
            distinct_id="user1",
            properties={property_name: "Category_A"},
        )

        column = materialize("events", property_name, create_bloom_filter_lower_index=True, is_nullable=is_nullable)
        index_name = get_bloom_filter_lower_index_name(column.name)

        # The index expression is lower(coalesce(col, '')) for nullable columns, lower(col) otherwise
        indexed_expr = f"lower(coalesce({column.name}, ''))" if is_nullable else f"lower({column.name})"
        query = f"SELECT count() FROM {EVENTS_DATA_TABLE()} WHERE {indexed_expr} = 'category_a'"
        index_info = get_index_from_explain(query, index_name)
        assert index_info is not None, f"Bloom filter lower index {index_name} should appear in EXPLAIN output"

    def test_get_all_returns_index_flags(self):
        """Test that get_materialized_columns returns correct index flags from ClickHouse."""
        prop_all = "prop_all"
        prop_minmax = "prop_minmax"
        prop_bf = "prop_bf"
        prop_ngram_lower = "prop_ngram_lower"
        prop_bf_lower = "prop_bf_lower"
        prop_none = "prop_none"

        _create_event(
            team=self.team,
            event="test_event",
            distinct_id="user1",
            properties={
                prop_all: prop_all,
                prop_minmax: prop_minmax,
                prop_bf: prop_bf,
                prop_ngram_lower: prop_ngram_lower,
                prop_bf_lower: prop_bf_lower,
                prop_none: prop_none,
            },
        )

        # Create column with all index types
        materialize(
            "events",
            prop_all,
            create_minmax_index=True,
            create_bloom_filter_index=True,
            create_ngram_lower_index=True,
            create_bloom_filter_lower_index=True,
        )
        materialize(
            "events",
            prop_minmax,
            create_minmax_index=True,
            create_bloom_filter_index=False,
            create_ngram_lower_index=False,
            create_bloom_filter_lower_index=False,
        )
        materialize(
            "events",
            prop_bf,
            create_bloom_filter_index=True,
            create_ngram_lower_index=False,
            create_minmax_index=False,
            create_bloom_filter_lower_index=False,
        )
        materialize(
            "events",
            prop_ngram_lower,
            create_bloom_filter_index=False,
            create_ngram_lower_index=True,
            create_minmax_index=False,
            create_bloom_filter_lower_index=False,
        )
        materialize(
            "events",
            prop_bf_lower,
            create_bloom_filter_index=False,
            create_ngram_lower_index=False,
            create_minmax_index=False,
            create_bloom_filter_lower_index=True,
        )
        materialize(
            "events",
            prop_none,
            create_bloom_filter_index=False,
            create_ngram_lower_index=False,
            create_minmax_index=False,
            create_bloom_filter_lower_index=False,
        )

        cols = get_materialized_columns("events")
        mat_col_all = cols.get((prop_all, "properties"))
        mat_col_minmax = cols.get((prop_minmax, "properties"))
        mat_col_bf = cols.get((prop_bf, "properties"))
        mat_col_ngram_lower = cols.get((prop_ngram_lower, "properties"))
        mat_col_bf_lower = cols.get((prop_bf_lower, "properties"))
        mat_col_none = cols.get((prop_none, "properties"))

        assert mat_col_all is not None
        assert mat_col_minmax is not None
        assert mat_col_bf is not None
        assert mat_col_ngram_lower is not None
        assert mat_col_bf_lower is not None
        assert mat_col_none is not None

        # Verify index flags: (has_minmax, has_bloom_filter, has_ngram_lower, has_bloom_filter_lower)
        assert (
            mat_col_all.has_minmax_index,
            mat_col_all.has_bloom_filter_index,
            mat_col_all.has_ngram_lower_index,
            mat_col_all.has_bloom_filter_lower_index,
        ) == (True, True, True, True)
        assert (
            mat_col_minmax.has_minmax_index,
            mat_col_minmax.has_bloom_filter_index,
            mat_col_minmax.has_ngram_lower_index,
            mat_col_minmax.has_bloom_filter_lower_index,
        ) == (True, False, False, False)
        assert (
            mat_col_bf.has_minmax_index,
            mat_col_bf.has_bloom_filter_index,
            mat_col_bf.has_ngram_lower_index,
            mat_col_bf.has_bloom_filter_lower_index,
        ) == (False, True, False, False)
        assert (
            mat_col_ngram_lower.has_minmax_index,
            mat_col_ngram_lower.has_bloom_filter_index,
            mat_col_ngram_lower.has_ngram_lower_index,
            mat_col_ngram_lower.has_bloom_filter_lower_index,
        ) == (False, False, True, False)
        assert (
            mat_col_bf_lower.has_minmax_index,
            mat_col_bf_lower.has_bloom_filter_index,
            mat_col_bf_lower.has_ngram_lower_index,
            mat_col_bf_lower.has_bloom_filter_lower_index,
        ) == (False, False, False, True)
        assert (
            mat_col_none.has_minmax_index,
            mat_col_none.has_bloom_filter_index,
            mat_col_none.has_ngram_lower_index,
            mat_col_none.has_bloom_filter_lower_index,
        ) == (False, False, False, False)