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.

12.5 KB
Raw Blame
from posthog.test.base import NonAtomicBaseTest

from parameterized import parameterized

from posthog.schema import ChartDisplayType, DataVisualizationNode, HogQLQuery

from posthog.sync import database_sync_to_async

from products.product_analytics.backend.facade.models import InsightVariable

from ee.hogai.chat_agent.schema_generator.parsers import PydanticOutputParserException
from ee.hogai.chat_agent.sql.mixins import HogQLGeneratorMixin, SQLSchemaGeneratorOutput


class TestSQLMixins(NonAtomicBaseTest):
    CLASS_DATA_LEVEL_SETUP = False

    def _get_node(self):
        class DummyNode(HogQLGeneratorMixin):
            pass

        node = DummyNode()
        node._team = self.team
        node._user = self.user
        return node

    @property
    def _node(self):
        return self._get_node()

    def _sql_output(self, query: str) -> SQLSchemaGeneratorOutput:
        return SQLSchemaGeneratorOutput(query=DataVisualizationNode(source=HogQLQuery(query=query)))

    async def test_construct_system_prompt(self):
        mixin = self._node
        prompt_template = await mixin._construct_system_prompt()
        prompt = prompt_template.format()
        self.assertIn("<project_schema>", prompt)
        self.assertIn("Table", prompt)
        self.assertIn("<core_memory>", prompt)
        self.assertIn("system.insight_variables", prompt)
        self.assertIn("FROM system.insight_variables", prompt)

    def test_assert_database_is_cached(self):
        mixin = self._node
        database = mixin._get_database()
        self.assertEqual(mixin._database_instance, database)

    def test_parse_output_success_path(self):
        """Test successful parsing in HogQLGeneratorMixin."""
        mixin = self._node

        # Test direct _parse_output method
        test_output = {"query": "SELECT count() FROM events", "name": "", "description": ""}
        result = mixin._parse_output(test_output)

        self.assertIsInstance(result, SQLSchemaGeneratorOutput)
        self.assertEqual(
            result,
            SQLSchemaGeneratorOutput(
                query=DataVisualizationNode(source=HogQLQuery(query="SELECT count() FROM events"))
            ),
        )

    def test_parse_output_with_visualization_settings(self):
        mixin = self._node

        result = mixin._parse_output(
            {
                "query": "SELECT toStartOfDay(timestamp) AS day, count() AS events FROM events GROUP BY day",
                "display": "ActionsLineGraph",
                "x_axis": "day",
                "y_axis": ["events"],
                "series_breakdown_column": None,
                "y_axis_format": "short",
                "y_axis_decimal_places": 0,
                "y_axis_prefix": None,
                "y_axis_suffix": None,
                "show_legend": False,
            }
        )

        self.assertEqual(result.query.display, ChartDisplayType.ACTIONS_LINE_GRAPH)
        self.assertEqual(result.query.chartSettings.xAxis.column, "day")
        self.assertEqual(result.query.chartSettings.yAxis[0].column, "events")
        self.assertEqual(result.query.chartSettings.yAxis[0].settings.formatting.style, "short")

    def test_parse_output_with_none_axis_format_omits_empty_formatting(self):
        mixin = self._node

        result = mixin._parse_output(
            {
                "query": "SELECT toStartOfDay(timestamp) AS day, count() AS events FROM events GROUP BY day",
                "display": "ActionsLineGraph",
                "x_axis": "day",
                "y_axis": ["events"],
                "series_breakdown_column": None,
                "y_axis_format": "none",
                "y_axis_decimal_places": None,
                "y_axis_prefix": None,
                "y_axis_suffix": None,
                "show_legend": False,
            }
        )

        self.assertIsNone(result.query.chartSettings.yAxis[0].settings)

    def test_parse_output_with_none_axis_format_preserves_other_formatting(self):
        mixin = self._node

        result = mixin._parse_output(
            {
                "query": "SELECT toStartOfDay(timestamp) AS day, count() AS latency FROM events GROUP BY day",
                "display": "ActionsLineGraph",
                "x_axis": "day",
                "y_axis": ["latency"],
                "series_breakdown_column": None,
                "y_axis_format": "none",
                "y_axis_decimal_places": 2,
                "y_axis_prefix": None,
                "y_axis_suffix": "ms",
                "show_legend": False,
            }
        )

        formatting = result.query.chartSettings.yAxis[0].settings.formatting
        self.assertEqual(formatting.style, "none")
        self.assertEqual(formatting.decimalPlaces, 2)
        self.assertEqual(formatting.suffix, "ms")

    def test_parse_output_with_empty_query(self):
        """Test parsing with empty query string."""
        mixin = self._node

        test_output = {"query": "", "name": "", "description": ""}
        result = mixin._parse_output(test_output)

        self.assertIsInstance(result, SQLSchemaGeneratorOutput)
        self.assertEqual(result.query.source.query, "")

    def test_parse_output_removes_semicolon(self):
        """Test that semicolons are removed from the end of queries."""
        mixin = self._node

        test_output = {"query": "SELECT count() FROM events;", "name": "", "description": ""}
        result = mixin._parse_output(test_output)

        self.assertIsInstance(result, SQLSchemaGeneratorOutput)
        self.assertEqual(result.query.source.query, "SELECT count() FROM events")

    def test_parse_output_removes_multiple_semicolons(self):
        """Test that multiple semicolons are removed from the end of queries."""
        mixin = self._node

        test_output = {"query": "SELECT count() FROM events;;;", "name": "", "description": ""}
        result = mixin._parse_output(test_output)

        self.assertIsInstance(result, SQLSchemaGeneratorOutput)
        self.assertEqual(result.query.source.query, "SELECT count() FROM events")

    def test_parse_output_preserves_semicolons_in_middle(self):
        """Test that semicolons in the middle of queries are preserved."""
        mixin = self._node

        test_output = {"query": "SELECT 'hello;world' FROM events;", "name": "", "description": ""}
        result = mixin._parse_output(test_output)

        self.assertIsInstance(result, SQLSchemaGeneratorOutput)
        self.assertEqual(result.query.source.query, "SELECT 'hello;world' FROM events")

    async def test_quality_check_output_success_simple_query(self):
        """Test successful quality check with simple valid query."""
        mixin = self._node

        valid_output = self._sql_output("SELECT count() FROM events")

        # Should not raise any exception for valid SQL
        await mixin._quality_check_output(valid_output)

    async def test_quality_check_output_success_with_placeholders(self):
        """Test successful quality check with placeholders."""
        mixin = self._node

        valid_output = self._sql_output("SELECT properties FROM events WHERE {filters}")

        # Should not raise any exception for valid SQL with placeholders
        await mixin._quality_check_output(valid_output)

    async def test_quality_check_output_invalid_syntax_raises_exception(self):
        """Test quality check failure with an invalid table in the SQL."""
        mixin = self._node

        invalid_output = self._sql_output("SELECT * FROM nowhere")

        with self.assertRaises(PydanticOutputParserException) as context:
            await mixin._quality_check_output(invalid_output)

        self.assertEqual(context.exception.llm_output, "SELECT * FROM nowhere")
        self.assertEqual(context.exception.validation_message, "Unknown table `nowhere`.")

    async def test_quality_check_output_empty_query_raises_exception(self):
        """Test quality check failure with empty query."""
        mixin = self._node

        # Create output with None query using model_construct to bypass validation
        empty_output = SQLSchemaGeneratorOutput.model_construct(query=None, name="", description="")  # type: ignore[arg-type]

        with self.assertRaises(PydanticOutputParserException) as context:
            await mixin._quality_check_output(empty_output)

        self.assertEqual(context.exception.llm_output, "")
        self.assertEqual(context.exception.validation_message, "Output is empty")

    async def test_quality_check_output_blank_query_raises_exception(self):
        """Test quality check failure with blank query string."""
        mixin = self._node

        blank_output = self._sql_output("")

        with self.assertRaises(PydanticOutputParserException) as context:
            await mixin._quality_check_output(blank_output)

        self.assertEqual(context.exception.llm_output, "")
        self.assertEqual(context.exception.validation_message, "Output is empty")

    async def test_quality_check_output_no_viable_alternative_error_handling(self):
        """Test that 'no viable alternative' errors get helpful messages."""
        mixin = self._node

        # Create a query that will still trigger the generic "no viable alternative" ANTLR error.
        invalid_syntax_output = self._sql_output("SELECT 1 IS TRUE AS value")

        with self.assertRaises(PydanticOutputParserException) as context:
            await mixin._quality_check_output(invalid_syntax_output)

        # Should replace unhelpful ANTLR error with better message
        self.assertEqual(context.exception.llm_output, "SELECT 1 IS TRUE AS value")
        self.assertIn("query isn't valid HogQL", context.exception.validation_message)

    async def test_quality_check_output_nonexistent_table_raises_exception(self):
        """Test quality check failure with nonexistent table."""
        mixin = self._node

        invalid_table_output = self._sql_output("SELECT count() FROM nonexistent_table")

        with self.assertRaises(PydanticOutputParserException) as context:
            await mixin._quality_check_output(invalid_table_output)

        self.assertEqual(context.exception.llm_output, "SELECT count() FROM nonexistent_table")
        self.assertEqual(context.exception.validation_message, "Unknown table `nonexistent_table`.")

    async def test_quality_check_output_complex_query_with_joins(self):
        """Test quality check success with complex query including joins."""
        mixin = self._node

        complex_output = self._sql_output(
            "SELECT e.event, p.id FROM events e LEFT JOIN persons p ON e.person_id = p.id LIMIT 10"
        )

        # Should not raise any exception for valid complex SQL
        await mixin._quality_check_output(complex_output)

    async def test_quality_check_output_success_with_filters_date_range(self):
        mixin = self._node

        valid_output = self._sql_output("SELECT event FROM events WHERE timestamp >= {filters.dateRange.from}")

        await mixin._quality_check_output(valid_output)

    async def test_quality_check_output_with_unsupported_filter_placeholder(self):
        mixin = self._node

        invalid_output = self._sql_output(
            "SELECT dateTrunc({filters.granularity}, timestamp) FROM events WHERE {filters}"
        )

        with self.assertRaises(PydanticOutputParserException):
            await mixin._quality_check_output(invalid_output)

    async def test_quality_check_unrecognized_placeholder_reports_clean_error(self):
        mixin = self._node

        output = self._sql_output("SELECT {some_other} AS d FROM events")

        with self.assertRaises(PydanticOutputParserException) as context:
            await mixin._quality_check_output(output)

        self.assertIn("some_other", context.exception.validation_message)

    @parameterized.expand(
        [
            ("resolves_to_default_in_string_context", "SELECT coalesce({variables.district_name}, '') AS d", False),
            ("missing_variable_is_reported", "SELECT {variables.nonexistent} AS d", True),
        ]
    )
    async def test_quality_check_resolves_insight_variables(self, _name: str, query: str, should_raise: bool):
        await database_sync_to_async(InsightVariable.objects.create)(
            team=self.team,
            name="District name",
            code_name="district_name",
            type=InsightVariable.Type.STRING,
            default_value="barbaz",
        )
        mixin = self._node
        output = self._sql_output(query)

        if should_raise:
            with self.assertRaises(PydanticOutputParserException):
                await mixin._quality_check_output(output)
        else:
            await mixin._quality_check_output(output)