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.

50.0 KB
Raw Blame
from datetime import datetime
from typing import Any

import time_machine
from posthog.test.base import NonAtomicBaseTest
from unittest.mock import Mock, patch

from django.test import SimpleTestCase, override_settings

from parameterized import param, parameterized
from rest_framework.exceptions import APIException

from posthog.schema import (
    AssistantFunnelsQuery,
    AssistantHogQLQuery,
    AssistantRetentionEventsNode,
    AssistantRetentionFilter,
    AssistantRetentionQuery,
    AssistantStickinessQuery,
    AssistantTrendsEventsNode,
    AssistantTrendsQuery,
    ChartDisplayType,
    DataVisualizationNode,
    EventsNode,
    FunnelsQuery,
    HogQLQuery,
    LifecycleQuery,
    PathsFilter,
    PathsQuery,
    PathsV2Filter,
    PathsV2Query,
    RetentionFilter,
    RetentionQuery,
    StickinessQuery,
    TrendsFilter,
    TrendsQuery,
)

from posthog.hogql.constants import DEFAULT_POSTHOG_AI_RETURNED_ROWS
from posthog.hogql.errors import (
    ExposedHogQLError,
    NotImplementedError as HogQLNotImplementedError,
    ParsingError,
    QueryError,
    ResolutionError,
    SyntaxError as HogQLSyntaxError,
)

from posthog.clickhouse.client.limit import ConcurrencyLimitExceeded
from posthog.clickhouse.query_tagging import Feature, Product, get_query_tags, tags_context
from posthog.errors import CHQueryErrorIllegalTypeOfArgument, ExposedCHQueryError, InternalCHQueryError
from posthog.exceptions import ClickHouseAtCapacity, QueryRanConcurrently
from posthog.models import Organization, Team, User

from ee.hogai.context.insight.context import InsightContext
from ee.hogai.context.insight.format.sql import SQLResultsFormatter
from ee.hogai.context.insight.query_executor import (
    AssistantQueryExecutor,
    execute_and_format_query,
    get_example_prompt,
    hogql_error_code,
    is_supported_query,
)
from ee.hogai.tool_errors import MaxToolFatalError, MaxToolRetryableError, MaxToolTransientError
from ee.hogai.utils.query import validate_assistant_query


def _exposed_from(cause: Exception) -> ExposedHogQLError:
    error = ExposedHogQLError("HogQL error")
    error.__cause__ = cause
    return error


class TestHogQLErrorCode(SimpleTestCase):
    @parameterized.expand(
        [
            ("unknown_table", QueryError("Unknown table `evnts`."), "hogql_unknown_table"),
            (
                "internal_unknown_table_type",
                ResolutionError("Unknown table type in LazyTableResolver: X"),
                "hogql_resolution_error",
            ),
            ("not_implemented", HogQLNotImplementedError("No support for X"), "hogql_not_implemented"),
            ("other_internal", ParsingError("Parsing failed"), "hogql_internal_error"),
            ("not_hogql", ValueError("Unable to resolve field: x"), None),
        ]
    )
    def test_codes_hold_no_caller_input(self, _name: str, error: Exception, expected_code: str | None) -> None:
        self.assertEqual(hogql_error_code(error), expected_code)


class TestSQLResultBudgets(SimpleTestCase):
    @parameterized.expand(["table", "fallback", "warnings"])
    async def test_sql_result_budget_reaches_formatting_and_fallback(self, kind: str) -> None:
        response: dict[str, Any] = {"results": [["x" * 2000] for _ in range(100)]}
        if kind != "fallback":
            response["columns"] = ["example"]
        if kind == "warnings":
            response["warnings"] = [{"type": "warehouse_sync", "message": "Sync is delayed. " * 100}]
        context = InsightContext(
            team=Team(id=1, organization=Organization()),
            user=User(),
            query=HogQLQuery(query="SELECT 1"),
            max_sql_result_chars=2000,
        )
        with (
            patch("ee.hogai.context.insight.query_executor.process_query_dict", return_value=response),
            patch("ee.hogai.context.insight.query_executor.capture_exception"),
        ):
            result = await context.execute_and_format(prompt_template="{{{results}}}", include_prompt_framing=False)
        self.assertLessEqual(len(result), 2000)
        self.assertIn("SQL result preview", result)
        self.assertEqual(response["results"][0][0], "x" * 2000)
        if kind == "fallback":
            self.assertIn("JSON shown may be cut off", result)

    def test_synchronous_execution_keeps_sql_budget(self) -> None:
        runner = AssistantQueryExecutor(
            team=Team(id=1, organization=Organization()),
            user=User(),
            utc_now_datetime=datetime.now(),
            max_sql_result_chars=SQLResultsFormatter.MAX_RESULT_CHARS,
        )
        response = {"columns": ["example"], "results": [["x" * 2000] for _ in range(300)]}
        with patch("ee.hogai.context.insight.query_executor.process_query_dict", return_value=response):
            result, used_fallback = runner.run_and_format_query(HogQLQuery(query="SELECT 1"))
        self.assertLessEqual(len(result), SQLResultsFormatter.MAX_RESULT_CHARS)
        self.assertFalse(used_fallback)


class TestAssistantQueryExecutor(NonAtomicBaseTest):
    CLASS_DATA_LEVEL_SETUP = False

    def setUp(self):
        super().setUp()
        with time_machine.travel("2025-01-20T12:00:00Z", tick=False):
            self.query_runner = AssistantQueryExecutor(self.team, datetime.now(), user=self.user)

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_run_and_format_query_trends(self, mock_process_query):
        """Test successful execution and formatting of trends query"""
        mock_process_query.return_value = {
            "results": [{"data": [1, 2, 3], "label": "test", "days": ["2025-01-01", "2025-01-02", "2025-01-03"]}]
        }

        query = AssistantTrendsQuery(series=[])
        result, used_fallback = await self.query_runner.arun_and_format_query(query)

        self.assertIsInstance(result, str)
        self.assertFalse(used_fallback)
        self.assertIn("Date|test", result)
        mock_process_query.assert_called_once()

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_run_and_format_query_funnels(self, mock_process_query):
        """Test successful execution and formatting of funnels query"""
        mock_process_query.return_value = {
            "results": [
                {
                    "action_id": "test",
                    "name": "test",
                    "order": 0,
                    "count": 100,
                    "average_conversion_time": None,
                    "median_conversion_time": None,
                }
            ]
        }

        query = AssistantFunnelsQuery(series=[])
        result, used_fallback = await self.query_runner.arun_and_format_query(query)

        self.assertIsInstance(result, str)
        self.assertFalse(used_fallback)
        self.assertIn("Metric|test", result)
        mock_process_query.assert_called_once()

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_run_and_format_query_retention(self, mock_process_query):
        """Test successful execution and formatting of retention query"""
        mock_process_query.return_value = {
            "results": [{"date": "2025-01-01", "label": "Day 0", "values": [{"count": 100}]}]
        }

        query = AssistantRetentionQuery(
            retentionFilter=AssistantRetentionFilter(
                targetEntity=AssistantRetentionEventsNode(id="event"),
                returningEntity=AssistantRetentionEventsNode(id="event"),
            )
        )
        result, used_fallback = await self.query_runner.arun_and_format_query(query)

        self.assertIsInstance(result, str)
        self.assertFalse(used_fallback)
        self.assertIn("Date|Number of persons on date", result)
        mock_process_query.assert_called_once()

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_run_and_format_query_sql(self, mock_process_query):
        """Test successful execution and formatting of SQL query"""
        mock_process_query.return_value = {"results": [{"count": 100}, {"count": 200}], "columns": ["count"]}

        query = AssistantHogQLQuery(query="SELECT count() FROM events")
        result, used_fallback = await self.query_runner.arun_and_format_query(query)

        self.assertIsInstance(result, str)
        self.assertFalse(used_fallback)
        self.assertIn("count\n100\n200", result)
        mock_process_query.assert_called_once()

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_arun_format_and_capture_hands_back_the_response(self, mock_process_query):
        response = {"results": [{"count": 100}], "columns": ["count"]}
        mock_process_query.return_value = response

        query = AssistantHogQLQuery(query="SELECT count() FROM events")
        result = await self.query_runner.arun_format_and_capture(query)

        self.assertIsInstance(result.formatted, str)
        self.assertFalse(result.fallback_used)
        self.assertEqual(result.response, response)

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_run_and_format_query_data_visualization_sql(self, mock_process_query):
        mock_process_query.return_value = {"results": [{"count": 100}, {"count": 200}], "columns": ["count"]}

        query = DataVisualizationNode(source=HogQLQuery(query="SELECT count() FROM events"))
        result, used_fallback = await self.query_runner.arun_and_format_query(query)

        self.assertIsInstance(result, str)
        self.assertFalse(used_fallback)
        self.assertIn("count\n100\n200", result)
        mock_process_query.assert_called_once()

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_run_and_format_query_with_fallback_info_no_fallback(self, mock_process_query):
        """Test run_and_format_query_with_fallback_info returns fallback info"""
        mock_process_query.return_value = {
            "results": [{"data": [1, 2, 3], "label": "test", "days": ["2025-01-01", "2025-01-02", "2025-01-03"]}]
        }

        query = AssistantTrendsQuery(series=[])
        result, used_fallback = await self.query_runner.arun_and_format_query(query)

        self.assertIsInstance(result, str)
        self.assertFalse(used_fallback)
        self.assertIn("Date|test", result)

    @patch("ee.hogai.context.insight.query_executor.capture_exception")
    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_run_and_format_query_with_fallback_on_compression_error(self, mock_process_query, mock_capture):
        """Test fallback to JSON when compression fails and capture_exception is called"""
        mock_process_query.return_value = {"results": [{"invalid": "data"}]}

        # Use a query that will cause compression to fail
        query = AssistantTrendsQuery(series=[])

        with patch(
            "ee.hogai.context.insight.format.TrendsResultsFormatter.format",
            side_effect=Exception("Compression failed"),
        ):
            result, used_fallback = await self.query_runner.arun_and_format_query(query)

        self.assertIsInstance(result, str)
        self.assertTrue(used_fallback)
        # Should be JSON formatted
        self.assertIn('{"invalid":"data"}', result)
        # Should capture the exception for non-NotImplementedError
        mock_capture.assert_called_once()

    @patch("ee.hogai.context.insight.query_executor.capture_exception")
    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_run_and_format_query_with_fallback_on_not_implemented_error_no_capture(
        self, mock_process_query, mock_capture
    ):
        """Test fallback to JSON when NotImplementedError is raised - should NOT capture exception"""
        mock_process_query.return_value = {"results": [{"path": "data"}]}

        query = AssistantTrendsQuery(series=[])

        with patch.object(
            self.query_runner,
            "_compress_results",
            side_effect=NotImplementedError("Unsupported query type"),
        ):
            result, used_fallback = await self.query_runner.arun_and_format_query(query)

        self.assertIsInstance(result, str)
        self.assertTrue(used_fallback)
        # Should NOT capture NotImplementedError
        mock_capture.assert_not_called()

    @patch("ee.hogai.context.insight.query_executor.capture_exception")
    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_paths_v2_query_degrades_to_json_fallback(self, mock_process_query: Mock, mock_capture: Mock) -> None:
        # The assistant has no PathsV2Query formatter yet, so a journeys insight must degrade to
        # the raw-JSON fallback instead of erroring.
        results = {
            "steps": [
                {
                    "stepIndex": 0,
                    "rows": [{"item": {"event": "$pageview", "label": "/home"}, "count": 2}],
                    "otherCount": 0,
                    "dropOffCount": 1,
                }
            ],
            "edges": [],
            "prefixes": [],
        }
        mock_process_query.return_value = {"results": results}

        result = await execute_and_format_query(self.team, PathsV2Query(pathsV2Filter=PathsV2Filter()), user=self.user)

        self.assertIn("stepIndex", result)
        self.assertIn("/home", result)
        mock_capture.assert_not_called()

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_run_and_format_query_handles_api_exception(self, mock_process_query):
        """Test handling of APIException"""

        mock_process_query.side_effect = APIException("API error message")

        query = AssistantTrendsQuery(series=[])

        with self.assertRaises(MaxToolFatalError) as context:
            await self.query_runner.arun_and_format_query(query)

        self.assertEqual(str(context.exception), "API error message")

    @parameterized.expand(
        [
            ("outage", ExposedHogQLError("Warehouse connection failed"), "internal", "hogql_error"),
            ("unresolved_field", QueryError("Unable to resolve field: plan"), "validation", "hogql_unresolved_field"),
            ("syntax", HogQLSyntaxError("mismatched input 'FORM'"), "validation", "hogql_syntax_error"),
            (
                "wrapped_cause",
                _exposed_from(QueryError("Unable to resolve field: plan")),
                "validation",
                "hogql_unresolved_field",
            ),
        ]
    )
    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_run_and_format_query_handles_exposed_hogql_error(
        self, _name, error, expected_type, expected_code, mock_process_query
    ):
        mock_process_query.side_effect = error

        with self.assertRaises(MaxToolRetryableError) as context:
            await self.query_runner.arun_and_format_query(AssistantHogQLQuery(query="SELECT plan FROM events"))

        self.assertEqual(str(context.exception), str(error))
        self.assertEqual(context.exception.error_type, expected_type)
        self.assertEqual(context.exception.error_code, expected_code)

    @parameterized.expand(
        [
            ("unknown", ExposedCHQueryError("ClickHouse error"), "ClickHouse error", "internal", "unknown_exception"),
            (
                "argument_type",
                CHQueryErrorIllegalTypeOfArgument("Expected an integer argument", code=43),
                "Expected an integer argument",
                "validation",
                "illegal_type_of_argument",
            ),
            (
                "bad_arguments",
                ExposedCHQueryError("This function requires two arguments", code=36),
                "This function requires two arguments",
                "validation",
                "bad_arguments",
            ),
            (
                "storage_error",
                ExposedCHQueryError("Storage read failed", code=499),
                "Storage read failed",
                "internal",
                "s3_error",
            ),
            (
                "server_error",
                ExposedCHQueryError("Server failure", code=99999, code_name="caller-supplied-name"),
                "Server failure",
                "internal",
                "unknown_exception",
            ),
        ]
    )
    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_run_and_format_query_handles_exposed_ch_query_error(
        self, _name, error, expected_message, expected_type, expected_code, mock_process_query
    ):
        mock_process_query.side_effect = error

        query = AssistantTrendsQuery(series=[])

        with self.assertRaises(MaxToolRetryableError) as context:
            await self.query_runner.arun_and_format_query(query)

        self.assertEqual(str(context.exception), expected_message)
        self.assertEqual(context.exception.error_type, expected_type)
        self.assertEqual(context.exception.error_code, expected_code)
        self.assertEqual(context.exception.retry_hint, " You may retry with adjusted inputs.")

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_run_and_format_query_handles_generic_exception(self, mock_process_query):
        """Test handling of generic exceptions"""
        mock_process_query.side_effect = ValueError("Some other error")

        query = AssistantTrendsQuery(series=[])

        with self.assertRaises(Exception) as context:
            await self.query_runner.arun_and_format_query(query)

        # The underlying error text must be surfaced, not collapsed to an opaque generic message —
        # that opacity is what left callers unable to diagnose failures like invalid-UTF-8 results.
        self.assertIn("There was an unknown error running this query", str(context.exception))
        self.assertIn("Some other error", str(context.exception))

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_run_and_format_query_truncates_long_error(self, mock_process_query):
        mock_process_query.side_effect = ValueError("x" * 1000)

        query = AssistantTrendsQuery(series=[])

        with self.assertRaises(Exception) as context:
            await self.query_runner.arun_and_format_query(query)

        message = str(context.exception)
        self.assertIn("(truncated)", message)
        self.assertLess(len(message), 700)

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_run_and_format_query_surfaces_error_in_response_dict(self, mock_process_query):
        # A failed query (e.g. a direct-SQL timeout) can return a structurally-valid response with an
        # `error` field and empty `results` instead of raising. That must surface as an error, not be
        # formatted as a header-only table indistinguishable from "zero rows matched".
        mock_process_query.return_value = {
            "results": [],
            "columns": ["count"],
            "error": "Query has hit the max execution time before completing.",
        }

        query = AssistantHogQLQuery(query="SELECT count() FROM events")

        with self.assertRaises(MaxToolRetryableError) as context:
            await self.query_runner.arun_and_format_query(query)

        self.assertEqual(str(context.exception), "Query has hit the max execution time before completing.")
        self.assertEqual(context.exception.error_type, "internal")
        self.assertEqual(context.exception.error_code, "query_result_error")
        self.assertEqual(context.exception.retry_hint, " You may retry with adjusted inputs.")

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    @patch("ee.hogai.context.insight.query_executor.get_query_status")
    async def test_async_query_polling_success(self, mock_get_query_status, mock_process_query):
        """Test successful async query polling"""
        # Initial response with incomplete query
        mock_process_query.return_value = {"query_status": {"id": "test-query-id", "complete": False}}

        # Mock polling responses
        mock_get_query_status.side_effect = [
            Mock(model_dump=lambda mode: {"id": "test-query-id", "complete": False}),  # Still running
            Mock(
                model_dump=lambda mode: {
                    "id": "test-query-id",
                    "complete": True,
                    "results": {"results": [{"data": [1], "label": "test", "days": ["2025-01-01"]}]},
                }
            ),  # Complete
        ]

        query = AssistantTrendsQuery(series=[])

        with patch("ee.hogai.context.insight.query_executor.asyncio.sleep") as mock_sleep:
            result, used_fallback = await self.query_runner.arun_and_format_query(query)

        self.assertIsInstance(result, str)
        self.assertFalse(used_fallback)
        self.assertIn("Date|test", result)
        self.assertEqual(mock_get_query_status.call_count, 2)
        self.assertEqual(mock_sleep.call_count, 2)

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    @patch("ee.hogai.context.insight.query_executor.get_query_status")
    async def test_async_query_polling_timeout(self, mock_get_query_status, mock_process_query):
        """Test async query polling timeout"""
        # Initial response with incomplete query
        mock_process_query.return_value = {"query_status": {"id": "test-query-id", "complete": False}}

        # Mock polling to always return incomplete
        mock_get_query_status.return_value = Mock(model_dump=lambda mode: {"id": "test-query-id", "complete": False})

        query = AssistantTrendsQuery(series=[])

        with patch("ee.hogai.context.insight.query_executor.asyncio.sleep"):
            with self.assertRaises(Exception) as context:
                await self.query_runner.arun_and_format_query(query)

        self.assertIn("Query hasn't completed in time", str(context.exception))

    @parameterized.expand(
        [
            ("validation", "Unknown field: missing_column", None, "Unknown field: missing_column", None),
            ("timeout", "Query timed out", "error", "Query timed out", None),
            param(
                "memory_limit",
                "Query memory limit exceeded",
                "clickhouse_memory_limit_exceeded",
                "Query memory limit exceeded",
                "clickhouse_memory_limit_exceeded",
                expected_error_type="memory_limit",
            ),
            ("warehouse_connection", "Warehouse connection failed", None, "Warehouse connection failed", None),
            (
                "known_server_code",
                None,
                "TOO_MANY_PARTS",
                "The database had a temporary problem while it ran this query. "
                "Wait a few minutes, then run the query again. If the problem continues, contact support.",
                "too_many_parts",
            ),
            (
                "storage_failure",
                None,
                "s3_error",
                "PostHog couldn't read from storage while running this query. Wait a few minutes, "
                "then run the query again. If the problem continues, contact support.",
                "s3_error",
            ),
            (
                "unrecognized_code",
                "Query input is invalid",
                '{"property":"synthetic-private-value"}',
                "Query input is invalid",
                None,
            ),
        ]
    )
    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    @patch("ee.hogai.context.insight.query_executor.get_query_status")
    async def test_async_query_polling_with_error(
        self,
        _name: str,
        error_message: str | None,
        error_code: str | None,
        expected_message: str,
        expected_code: str | None,
        mock_get_query_status: Mock,
        mock_process_query: Mock,
        *,
        expected_error_type: str = "internal",
    ) -> None:
        mock_process_query.return_value = {"query_status": {"id": "test-query-id", "complete": False}}

        mock_get_query_status.return_value = Mock(
            model_dump=lambda mode: {
                "id": "test-query-id",
                "complete": True,
                "error": True,
                "error_message": error_message,
                "error_code": error_code,
            }
        )

        query = AssistantTrendsQuery(series=[])

        with patch("ee.hogai.context.insight.query_executor.asyncio.sleep"):
            with self.assertRaises(MaxToolRetryableError) as context:
                await self.query_runner.arun_and_format_query(query)

        self.assertEqual(str(context.exception), expected_message)
        self.assertEqual(context.exception.retry_hint, " You may retry with adjusted inputs.")
        self.assertEqual(context.exception.error_type, expected_error_type)
        self.assertEqual(context.exception.error_code, expected_code)

    @parameterized.expand(
        [
            (
                "UNKNOWN_IDENTIFIER",
                None,
                "A column in this query doesn't exist in the data. Check the column names. "
                "If the query uses a view, check that the view still matches its source table. "
                "Look up the table columns in `system.information_schema.columns`. "
                "In a join, prefix each column with its table alias.",
            ),
            (
                "unknown_identifier",
                "Check the column selected by this view.",
                "Check the column selected by this view. "
                "Look up the table columns in `system.information_schema.columns`. "
                "In a join, prefix each column with its table alias.",
            ),
            ("unsupported_method", None, "ClickHouse rejected the query with error UNSUPPORTED_METHOD."),
            ("unsupported_method", "Use a supported query method.", "Use a supported query method."),
            (
                "bad_arguments",
                "This function requires an integer argument.",
                "This function requires an integer argument.",
            ),
            (
                "illegal_type_of_argument",
                "Expected an integer argument",
                "Expected an integer argument",
            ),
        ]
    )
    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    @patch("ee.hogai.context.insight.query_executor.get_query_status")
    async def test_async_query_error_code_decides_what_the_agent_reads(
        self, error_code, error_message, expected_message, mock_get_query_status, mock_process_query
    ):
        mock_process_query.return_value = {"query_status": {"id": "test-query-id", "complete": False}}
        mock_get_query_status.return_value = Mock(
            model_dump=lambda mode: {
                "id": "test-query-id",
                "complete": True,
                "error": True,
                "error_message": error_message,
                "error_code": error_code,
            }
        )
        with patch("ee.hogai.context.insight.query_executor.asyncio.sleep"):
            with self.assertRaises(MaxToolRetryableError) as context:
                await self.query_runner.arun_and_format_query(AssistantHogQLQuery(query="SELECT 1"))
        self.assertEqual(str(context.exception), expected_message)
        self.assertEqual(context.exception.error_type, "validation")
        self.assertEqual(context.exception.error_code, error_code.lower())
        self.assertEqual(context.exception.retry_hint, " You may retry with adjusted inputs.")

    @parameterized.expand(
        [
            (code, code_name, name, expected_message, expected_exception)
            for code, name, expected_message, expected_exception in [
                (
                    47,
                    "unknown_identifier",
                    "A column in this query doesn't exist in the data. Check the column names. "
                    "If the query uses a view, check that the view still matches its source table. "
                    "Look up the table columns in `system.information_schema.columns`. "
                    "In a join, prefix each column with its table alias.",
                    MaxToolRetryableError,
                ),
                (
                    1,
                    "unsupported_method",
                    "ClickHouse rejected the query with error UNSUPPORTED_METHOD.",
                    MaxToolRetryableError,
                ),
                (
                    62,
                    "syntax_error",
                    "There was an unknown error running this query: Code: 62.\nstored-secret",
                    Exception,
                ),
            ]
            for code_name in [name, None]
        ]
    )
    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_internal_clickhouse_query_rejection(
        self, code, code_name, expected_code, expected_message, expected_exception, mock_process_query
    ):
        mock_process_query.side_effect = InternalCHQueryError("stored-secret", code=code, code_name=code_name)
        with self.assertRaises(expected_exception) as context:
            await self.query_runner.arun_and_format_query(AssistantHogQLQuery(query="SELECT 1"))
        self.assertIs(type(context.exception), expected_exception)
        self.assertEqual(str(context.exception), expected_message)
        if isinstance(context.exception, MaxToolRetryableError):
            self.assertEqual(context.exception.error_type, "validation")
            self.assertEqual(context.exception.error_code, expected_code)
            self.assertEqual(context.exception.retry_hint, " You may retry with adjusted inputs.")

    @parameterized.expand(
        [
            ("keeper_exception", "keeper_exception"),
            ("syntax_error", "syntax_error"),
            ("UNKNOWN_EXCEPTION", "unknown_exception"),
            (None, None),
        ]
    )
    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    @patch("ee.hogai.context.insight.query_executor.get_query_status")
    async def test_async_server_fault_stays_unknown(
        self, error_code, expected_code, mock_get_query_status, mock_process_query
    ):
        mock_process_query.return_value = {"query_status": {"id": "test-query-id", "complete": False}}
        mock_get_query_status.return_value = Mock(
            model_dump=lambda mode: {
                "id": "test-query-id",
                "complete": True,
                "error": True,
                "error_message": None,
                "error_code": error_code,
            }
        )
        with patch("ee.hogai.context.insight.query_executor.asyncio.sleep"):
            with self.assertRaises(MaxToolRetryableError) as context:
                await self.query_runner.arun_and_format_query(AssistantHogQLQuery(query="SELECT 1"))
        self.assertEqual(str(context.exception), "The query failed because of an internal error")
        self.assertEqual(context.exception.error_type, "internal")
        self.assertEqual(context.exception.error_code, expected_code)

    @parameterized.expand(
        [
            ("at_capacity", "clickhouse_at_capacity", ClickHouseAtCapacity.default_detail, None, "rate_limited"),
            ("ran_concurrently", "query_ran_concurrently", QueryRanConcurrently.default_detail, None, "api_5xx"),
            ("concurrency_limit_without_message", "clickhouse_at_capacity", None, None, "rate_limited"),
            ("direct_capacity", None, ClickHouseAtCapacity.default_detail, ClickHouseAtCapacity(), "rate_limited"),
            ("direct_shared_failure", None, QueryRanConcurrently.default_detail, QueryRanConcurrently(), "api_5xx"),
            (
                "direct_concurrency_limit",
                None,
                str(ConcurrencyLimitExceeded()),
                ConcurrencyLimitExceeded(),
                "rate_limited",
            ),
        ]
    )
    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    @patch("ee.hogai.context.insight.query_executor.get_query_status")
    async def test_temporary_query_failures_ask_the_agent_to_wait(
        self,
        _name: str,
        error_code: str | None,
        error_message: str | None,
        raised_error: Exception | None,
        expected_error_type: str,
        mock_get_query_status: Mock,
        mock_process_query: Mock,
    ) -> None:
        mock_process_query.side_effect = raised_error
        mock_process_query.return_value = {"query_status": {"id": "test-query-id", "complete": False}}
        mock_get_query_status.return_value = Mock(
            model_dump=lambda mode: {
                "id": "test-query-id",
                "complete": True,
                "error": True,
                "error_message": error_message,
                "error_code": error_code,
            }
        )

        with patch("ee.hogai.context.insight.query_executor.asyncio.sleep"):
            with self.assertRaises(MaxToolTransientError) as context:
                await self.query_runner.arun_and_format_query(AssistantTrendsQuery(series=[]))

        self.assertEqual(context.exception.error_type, expected_error_type)
        self.assertEqual(context.exception.retry_hint, " You may retry this operation once without changes.")
        self.assertIn(
            error_message if raised_error is not None else error_message or "too busy", str(context.exception)
        )

    @override_settings(TEST=False)
    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_execution_mode_in_production(self, mock_process_query):
        """Test that production uses correct execution mode"""
        mock_process_query.return_value = {"results": [{"data": [1], "label": "test", "days": ["2025-01-01"]}]}

        query = AssistantTrendsQuery(series=[])
        result, used_fallback = await self.query_runner.arun_and_format_query(query)

        # Check that the execution mode was set correctly (not CALCULATE_BLOCKING_ALWAYS which is test mode)
        call_args = mock_process_query.call_args
        self.assertIn("execution_mode", call_args.kwargs)
        # In production it should be RECENT_CACHE_CALCULATE_ASYNC_IF_STALE
        from posthog.hogql_queries.query_runner import ExecutionMode

        self.assertEqual(call_args.kwargs["execution_mode"], ExecutionMode.RECENT_CACHE_CALCULATE_ASYNC_IF_STALE)

    async def test_compress_results_full_ui_queries(self):
        """Test _compress_results works with full UI query types by casting to assistant types"""
        # Test TrendsQuery -> AssistantTrendsQuery casting
        trends_query = TrendsQuery(series=[])
        response = {"results": [{"data": [1], "label": "test", "days": ["2025-01-01"]}]}
        result = await self.query_runner._compress_results(trends_query, response)
        self.assertIn("Date|test", result)

        # Test FunnelsQuery -> AssistantFunnelsQuery casting
        funnels_query = FunnelsQuery(series=[])
        funnels_response: dict[str, Any] = {
            "results": [
                {
                    "action_id": "test",
                    "name": "test",
                    "order": 0,
                    "count": 100,
                    "average_conversion_time": None,
                    "median_conversion_time": None,
                }
            ]
        }
        result = await self.query_runner._compress_results(funnels_query, funnels_response)
        self.assertIn("Metric|test", result)

        # Test RetentionQuery -> AssistantRetentionQuery casting
        retention_query = RetentionQuery(retentionFilter=RetentionFilter())
        response = {"results": [{"date": "2025-01-01", "label": "Day 0", "values": [{"count": 100}]}]}
        result = await self.query_runner._compress_results(retention_query, response)
        self.assertIn("Date|Number of persons on date", result)

        # Test HogQLQuery -> AssistantHogQLQuery casting
        hogql_query = HogQLQuery(query="SELECT 1")
        hogql_response: dict[str, Any] = {"results": [{"count": 100}], "columns": ["count"]}
        result = await self.query_runner._compress_results(hogql_query, hogql_response)
        self.assertIn("count\n100", result)

    async def test_compress_results_stickiness_query(self):
        query = StickinessQuery(series=[EventsNode(event="$pageview")])
        response: dict[str, Any] = {
            "results": [
                {
                    "count": 500,
                    "data": [200, 150, 100],
                    "days": [1, 2, 3],
                    "labels": ["1 day", "2 days", "3 days"],
                    "label": "$pageview",
                    "order": 0,
                }
            ]
        }
        result = await self.query_runner._compress_results(query, response)
        self.assertIn("Interval|$pageview", result)
        self.assertIn("1 day|200", result)

    async def test_compress_results_lifecycle_query(self):
        query = LifecycleQuery(series=[EventsNode(event="$pageview")])
        response: dict[str, Any] = {
            "results": [
                {
                    "data": [46.0, 38.0],
                    "days": ["2025-01-20", "2025-01-21"],
                    "labels": ["20-Jan", "21-Jan"],
                    "label": "$pageview - new",
                    "status": "new",
                    "order": 0,
                },
                {
                    "data": [120.0, 105.0],
                    "days": ["2025-01-20", "2025-01-21"],
                    "labels": ["20-Jan", "21-Jan"],
                    "label": "$pageview - returning",
                    "status": "returning",
                    "order": 0,
                },
                {
                    "data": [15.0, 22.0],
                    "days": ["2025-01-20", "2025-01-21"],
                    "labels": ["20-Jan", "21-Jan"],
                    "label": "$pageview - resurrecting",
                    "status": "resurrecting",
                    "order": 0,
                },
                {
                    "data": [-30.0, -45.0],
                    "days": ["2025-01-20", "2025-01-21"],
                    "labels": ["20-Jan", "21-Jan"],
                    "label": "$pageview - dormant",
                    "status": "dormant",
                    "order": 0,
                },
            ]
        }
        result = await self.query_runner._compress_results(query, response)
        self.assertIn("Date|New|Returning|Resurrecting|Dormant", result)
        self.assertIn("2025-01-20|46|120|15|-30", result)

    async def test_compress_results_boxplot_data(self):
        query = TrendsQuery(
            series=[EventsNode(event="$pageview")],
            trendsFilter=TrendsFilter(display=ChartDisplayType.BOX_PLOT),
        )
        response: dict[str, Any] = {
            "results": [
                {
                    "day": "2025-01-20",
                    "label": "Day 1",
                    "min": 1.2,
                    "p25": 5.5,
                    "median": 12.3,
                    "p75": 25.8,
                    "max": 100.4,
                    "mean": 18.7,
                    "series_label": "$pageview",
                    "series_index": 0,
                },
            ],
        }
        result = await self.query_runner._compress_results(query, response)
        self.assertIn("Date|Min|P25|Median|P75|Max|Mean", result)
        self.assertIn("2025-01-20|1.2|5.5|12.3|25.8|100.4|18.7", result)
        self.assertNotIn("No data recorded", result)

    async def test_compress_results_boxplot_data_none_falls_back_to_trends(self):
        query = TrendsQuery(series=[EventsNode(event="$pageview")])
        response: dict[str, Any] = {
            "results": [{"data": [1], "label": "test", "days": ["2025-01-01"]}],
        }
        result = await self.query_runner._compress_results(query, response)
        self.assertIn("Date|test", result)

    def test_is_supported_query_includes_new_types(self):
        self.assertTrue(is_supported_query(StickinessQuery(series=[])))
        self.assertTrue(is_supported_query(AssistantStickinessQuery(series=[])))
        self.assertTrue(is_supported_query(LifecycleQuery(series=[EventsNode(event="$pageview")])))

    def test_get_example_prompt_stickiness(self):
        from ee.hogai.context.insight.prompts import STICKINESS_EXAMPLE_PROMPT

        self.assertEqual(get_example_prompt(StickinessQuery(series=[])), STICKINESS_EXAMPLE_PROMPT)
        self.assertEqual(get_example_prompt(AssistantStickinessQuery(series=[])), STICKINESS_EXAMPLE_PROMPT)

    def test_get_example_prompt_lifecycle(self):
        from ee.hogai.context.insight.prompts import LIFECYCLE_EXAMPLE_PROMPT

        self.assertEqual(
            get_example_prompt(LifecycleQuery(series=[EventsNode(event="$pageview")])), LIFECYCLE_EXAMPLE_PROMPT
        )

    def test_get_example_prompt_boxplot(self):
        from ee.hogai.context.insight.prompts import BOX_PLOT_EXAMPLE_PROMPT

        query = TrendsQuery(series=[], trendsFilter=TrendsFilter(display=ChartDisplayType.BOX_PLOT))
        self.assertEqual(get_example_prompt(query), BOX_PLOT_EXAMPLE_PROMPT)

    def test_get_example_prompt_regular_trends_not_boxplot(self):
        from ee.hogai.context.insight.prompts import TRENDS_EXAMPLE_PROMPT

        query = TrendsQuery(series=[])
        self.assertEqual(get_example_prompt(query), TRENDS_EXAMPLE_PROMPT)

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_response_dict_handling(self, mock_process_query):
        """Test that response is handled correctly whether it's a dict or model"""
        # Test with dict response
        mock_process_query.return_value = {"results": [{"data": [1], "label": "test", "days": ["2025-01-01"]}]}

        query = AssistantTrendsQuery(series=[])
        result, used_fallback = await self.query_runner.arun_and_format_query(query)
        self.assertIn("Date|test", result)

        # Test with model response that has model_dump method
        mock_response = Mock()
        mock_response.model_dump.return_value = {"results": [{"data": [2], "label": "test2", "days": ["2025-01-02"]}]}
        mock_process_query.return_value = mock_response

        result, used_fallback = await self.query_runner.arun_and_format_query(query)
        self.assertIn("Date|test2", result)

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_outer_tags_propagate_across_sync_boundary(self, mock_process_query):
        """Outer-context query tags must survive the database_sync_to_async thread switch."""
        captured_tags: dict[str, Any] = {}

        def fake_process_query_dict(*args, **kwargs):
            # Captured from inside the threaded sync function — i.e. across the async-to-sync boundary
            captured_tags.update(get_query_tags().model_dump(exclude_none=True))
            return {"results": []}

        mock_process_query.side_effect = fake_process_query_dict

        query = AssistantTrendsQuery(series=[])

        # Tags set in the outer async context — these would be lost without the snapshot/replay logic
        with tags_context(feature=Feature.MCP, scene="my-scene"):
            await self.query_runner.arun_and_format_query(query, insight_id=42)

        # Outer-context tags propagated through the sync boundary
        self.assertEqual(captured_tags.get("scene"), "my-scene")
        # Outer feature wins over the default POSTHOG_AI fallback in arun_and_format_query
        self.assertEqual(captured_tags.get("feature"), Feature.MCP.value)
        # Tags applied by arun_and_format_query also propagate
        self.assertEqual(captured_tags.get("product"), Product.MAX_AI.value)
        self.assertEqual(captured_tags.get("team_id"), self.team.pk)
        self.assertEqual(captured_tags.get("insight_id"), 42)

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_default_feature_when_no_outer_feature(self, mock_process_query):
        """When no outer feature is set, arun_and_format_query falls back to POSTHOG_AI."""
        captured_tags: dict[str, Any] = {}

        def fake_process_query_dict(*args, **kwargs):
            captured_tags.update(get_query_tags().model_dump(exclude_none=True))
            return {"results": []}

        mock_process_query.side_effect = fake_process_query_dict

        query = AssistantTrendsQuery(series=[])
        await self.query_runner.arun_and_format_query(query)

        self.assertEqual(captured_tags.get("feature"), Feature.POSTHOG_AI.value)
        self.assertEqual(captured_tags.get("product"), Product.MAX_AI.value)


class TestAssistantQueryExecutorAsync(NonAtomicBaseTest):
    CLASS_DATA_LEVEL_SETUP = False

    def setUp(self):
        super().setUp()
        with time_machine.travel("2025-01-20T12:00:00Z", tick=False):
            self.query_runner = AssistantQueryExecutor(self.team, datetime.now(), user=self.user)

    async def test_runs_in_async_context(self):
        """Test successful execution and formatting of funnels query"""
        query = AssistantTrendsQuery(series=[AssistantTrendsEventsNode(name="event")])
        result, used_fallback = await self.query_runner.arun_and_format_query(query)
        self.assertIsInstance(result, str)
        self.assertFalse(used_fallback)

    async def test_sql_query_default_limit_is_applied(self):
        query = AssistantHogQLQuery(query="SELECT arrayJoin(range(1, 100001))")
        result, used_fallback = await self.query_runner.arun_and_format_query(query)
        self.assertFalse(used_fallback)
        lines = result.strip().split("\n")
        data_rows = len(lines) - 1  # subtract header
        self.assertEqual(data_rows, DEFAULT_POSTHOG_AI_RETURNED_ROWS)


class TestExecuteAndFormatQuery(NonAtomicBaseTest):
    """Tests for the execute_and_format_query function"""

    CLASS_DATA_LEVEL_SETUP = False

    def setUp(self):
        super().setUp()
        with time_machine.travel("2025-01-20T12:00:00Z", tick=False):
            self.query_runner = AssistantQueryExecutor(self.team, datetime.now(), user=self.user)

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_includes_insight_schema_for_trends_query(self, mock_process_query):
        """Verify insight schema is included for TrendsQuery"""
        mock_process_query.return_value = {
            "results": [{"data": [1, 2, 3], "label": "test", "days": ["2025-01-01", "2025-01-02", "2025-01-03"]}]
        }

        query = AssistantTrendsQuery(series=[AssistantTrendsEventsNode(name="$pageview")])
        result = await execute_and_format_query(self.team, query, user=self.user)

        # Verify schema section is present
        self.assertIn("```json", result)
        # Verify it contains query configuration
        self.assertIn("kind", result)

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_insight_schema_excludes_unset_fields(self, mock_process_query):
        """Verify that unset fields are excluded from the schema (exclude_unset=True)"""
        mock_process_query.return_value = {"results": [{"data": [1], "label": "test", "days": ["2025-01-01"]}]}

        query = AssistantTrendsQuery(series=[AssistantTrendsEventsNode(name="$pageview")])
        result = await execute_and_format_query(self.team, query, user=self.user)

        self.assertIn("kind", result)
        self.assertNotIn("breakdownFilter", result)

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_insight_schema_excludes_none_values(self, mock_process_query):
        """Verify that None values are excluded from the schema (exclude_none=True)"""
        mock_process_query.return_value = {"results": [{"data": [1], "label": "test", "days": ["2025-01-01"]}]}

        # Create query with dateRange (which might have None values for date_from/date_to if not set)
        query = AssistantTrendsQuery(series=[AssistantTrendsEventsNode(name="$pageview")], breakdownFilter=None)
        result = await execute_and_format_query(self.team, query, user=self.user)

        # The schema should not contain null values
        self.assertNotIn("null", result)
        self.assertNotIn("breakdownFilter", result)

    @patch("ee.hogai.context.insight.query_executor.process_query_dict")
    async def test_excludes_sql_schema(self, mock_process_query):
        """Verify that None values are excluded from the schema (exclude_none=True)"""
        mock_process_query.return_value = {"results": []}

        # Create query with dateRange (which might have None values for date_from/date_to if not set)
        query = AssistantHogQLQuery(query="SELECT 1")
        result = await execute_and_format_query(self.team, query, user=self.user)

        # The schema should not be present
        self.assertNotIn("SELECT 1", result)

    async def test_compress_results_formats_paths_query(self):
        paths_query = PathsQuery(pathsFilter=PathsFilter(includeEventTypes=["$pageview"]))
        response = {
            "results": [
                {"source": "1_/home", "target": "2_/pricing", "value": 150, "average_conversion_time": 150.0},
                {"source": "1_/home", "target": "2_/docs", "value": 80, "average_conversion_time": 75.5},
            ]
        }

        result = await self.query_runner._compress_results(paths_query, response)
        self.assertIn("Source|Target|Users|Avg. conversion time", result)
        self.assertIn("1_/home|2_/pricing|150|2m 30s", result)


class TestValidateAssistantQuery(SimpleTestCase):
    """Tests for the validate_assistant_query function"""

    CLASS_DATA_LEVEL_SETUP = False

    def test_validates_assistant_trends_query(self):
        """Test that assistant-specific queries are validated via AssistantSupportedQueryRoot."""
        query_dict = {"kind": "TrendsQuery", "series": []}
        result = validate_assistant_query(query_dict)
        self.assertIsInstance(result, AssistantTrendsQuery)

    def test_validates_paths_query_via_fallback(self):
        """Test that PathsQuery is validated via QuerySchemaRoot fallback."""
        query_dict = {
            "kind": "PathsQuery",
            "pathsFilter": {"includeEventTypes": ["$pageview"]},
        }
        result = validate_assistant_query(query_dict)
        self.assertIsInstance(result, PathsQuery)

    def test_validates_funnels_query(self):
        """Test that FunnelsQuery can be validated."""
        query_dict = {"kind": "FunnelsQuery", "series": []}
        result = validate_assistant_query(query_dict)
        self.assertIsInstance(result, AssistantFunnelsQuery)