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.

11.6 KB
Raw Blame
from typing import Any

from django.test import SimpleTestCase

from parameterized import parameterized

from posthog.schema import AssistantHogQLQuery

from .. import NULL_MARKER, TRUNCATED_MARKER, SQLResultsFormatter


class TestSQLResultsFormatter(SimpleTestCase):
    @parameterized.expand([511, 512, 513])
    def test_preview_at_total_budget_boundary(self, total_chars: int) -> None:
        rows = [{"a": "x" * 250, "b": "y" * (total_chars - 255)}]
        formatter = SQLResultsFormatter(AssistantHogQLQuery(query="SELECT 1"), rows, ["a", "b"], max_result_chars=512)
        output = formatter.format()
        self.assertLessEqual(len(output), 512)
        if total_chars <= 512:
            self.assertEqual(len(output), total_chars)
            self.assertNotIn("SQL result preview", output)
        else:
            self.assertIn("Showing 0 of 1 returned rows", output)

    @parameterized.expand(["text", "json", "unicode"])
    def test_bounded_preview_shortens_cells_and_omits_complete_rows(self, kind: str) -> None:
        value: str | dict[str, str] = "x" * 20000
        if kind == "json":
            value = {"example": "x" * 20000}
        elif kind == "unicode":
            value = "🦔" * 20000
        rows = [{"id": i, "value": value} for i in range(100)]
        formatter = SQLResultsFormatter(
            AssistantHogQLQuery(query="SELECT 1"), rows, ["id", "value"], max_result_chars=2000
        )
        result = formatter.format()
        table, notice = result.rsplit("\n", 1)
        shown_rows = table.splitlines()[1:]
        self.assertLessEqual(len(result), 2000)
        self.assertEqual(len(shown_rows), 3)
        self.assertTrue(all(len(row.split("|", 1)[1]) <= 500 for row in shown_rows))
        self.assertIn("Showing 3 of 100 returned rows", notice)
        self.assertIn("Some cells were shortened", notice)
        self.assertIn("not evidence of missing data", notice)
        self.assertEqual(rows[0]["value"], value)

    @parameterized.expand(["header", "row"])
    def test_bounded_preview_omits_oversized_table_structure(self, kind: str) -> None:
        columns = ["x" * 600] if kind == "header" else ["a", "b"]
        rows = [{"a": "x" * 500, "b": "y" * 500}]
        formatter = SQLResultsFormatter(AssistantHogQLQuery(query="SELECT 1"), rows, columns, max_result_chars=512)
        result = formatter.format()
        self.assertLessEqual(len(result), 512)
        self.assertIn("Showing 0 of 1 returned rows", result)
        self.assertIn("too wide to show", result)

    @parameterized.expand([0, 1, 2])
    def test_bounded_preview_preserves_small_results(self, count: int) -> None:
        rows = [{"id": i, "value": None} for i in range(count)]
        query = AssistantHogQLQuery(query="SELECT 1")
        self.assertEqual(
            SQLResultsFormatter(query, rows, ["id", "value"], max_result_chars=512).format(),
            SQLResultsFormatter(query, rows, ["id", "value"]).format(),
        )

    def test_format_basic(self):
        query = AssistantHogQLQuery(query="SELECT 1")
        results = [
            {"column1": "value1", "column2": "value2"},
            {"column1": "value3", "column2": "value4"},
        ]
        columns = ["column1", "column2"]

        formatter = SQLResultsFormatter(query, results, columns)
        expected = "column1|column2\nvalue1|value2\nvalue3|value4"
        self.assertEqual(formatter.format(), expected)

    def test_format_empty_results(self):
        query = AssistantHogQLQuery(query="SELECT 1")
        results: list[Any] = []
        columns = ["column1", "column2"]

        formatter = SQLResultsFormatter(query, results, columns)
        expected = "column1|column2"
        self.assertEqual(formatter.format(), expected)

    def test_format_single_column(self):
        query = AssistantHogQLQuery(query="SELECT count()")
        results = [{"count": 42}, {"count": 100}]
        columns = ["count"]

        formatter = SQLResultsFormatter(query, results, columns)
        expected = "count\n42\n100"
        self.assertEqual(formatter.format(), expected)

    def test_format_with_none_values(self):
        query = AssistantHogQLQuery(query="SELECT id, name")
        results: list[Any] = [
            {"id": 1, "name": "test"},
            {"id": 2, "name": None},
        ]
        columns = ["id", "name"]

        formatter = SQLResultsFormatter(query, results, columns)
        expected = f"id|name\n1|test\n2|{NULL_MARKER}"
        self.assertEqual(formatter.format(), expected)

    def test_format_none_is_distinguishable_from_the_string_none(self):
        query = AssistantHogQLQuery(query="SELECT id, name")
        results: list[Any] = [
            {"id": 1, "name": None},
            {"id": 2, "name": "None"},
        ]
        columns = ["id", "name"]

        formatter = SQLResultsFormatter(query, results, columns)
        expected = f"id|name\n1|{NULL_MARKER}\n2|None"
        self.assertEqual(formatter.format(), expected)

    def test_format_none_is_distinguishable_from_a_literal_marker_value(self):
        query = AssistantHogQLQuery(query="SELECT id, name")
        results: list[Any] = [
            {"id": 1, "name": None},
            {"id": 2, "name": NULL_MARKER},
        ]
        columns = ["id", "name"]

        formatter = SQLResultsFormatter(query, results, columns)
        expected = f'id|name\n1|{NULL_MARKER}\n2|"{NULL_MARKER}"'
        self.assertEqual(formatter.format(), expected)

    def test_format_with_numeric_values(self):
        query = AssistantHogQLQuery(query="SELECT count, avg")
        results = [
            {"count": 100, "avg": 15.5},
            {"count": 200, "avg": 25.75},
        ]
        columns = ["count", "avg"]

        formatter = SQLResultsFormatter(query, results, columns)
        expected = "count|avg\n100|15.5\n200|25.75"
        self.assertEqual(formatter.format(), expected)

    def test_format_truncates_large_dict(self):
        query = AssistantHogQLQuery(query="SELECT properties")
        large_dict = {"key_" + str(i): "value_" + str(i) * 50 for i in range(20)}
        results = [{"properties": large_dict}]
        columns = ["properties"]

        formatter = SQLResultsFormatter(query, results, columns)
        output = formatter.format()

        self.assertIn(TRUNCATED_MARKER, output)
        self.assertTrue(formatter.has_truncated_values)
        # The cell content should be truncated
        cell_content = output.split("\n")[1]
        self.assertLess(len(cell_content), len(str(large_dict)))

    def test_format_truncates_large_list(self):
        query = AssistantHogQLQuery(query="SELECT items")
        large_list = ["item_" + str(i) * 50 for i in range(20)]
        results = [{"items": large_list}]
        columns = ["items"]

        formatter = SQLResultsFormatter(query, results, columns)
        output = formatter.format()

        self.assertIn(TRUNCATED_MARKER, output)
        self.assertTrue(formatter.has_truncated_values)

    def test_format_does_not_truncate_small_dict(self):
        query = AssistantHogQLQuery(query="SELECT properties")
        small_dict = {"key": "value", "another": "data"}
        results = [{"properties": small_dict}]
        columns = ["properties"]

        formatter = SQLResultsFormatter(query, results, columns)
        output = formatter.format()

        self.assertNotIn(TRUNCATED_MARKER, output)
        self.assertFalse(formatter.has_truncated_values)
        self.assertIn(str(small_dict), output)

    def test_format_does_not_truncate_small_list(self):
        query = AssistantHogQLQuery(query="SELECT items")
        small_list = ["a", "b", "c"]
        results = [{"items": small_list}]
        columns = ["items"]

        formatter = SQLResultsFormatter(query, results, columns)
        output = formatter.format()

        self.assertNotIn(TRUNCATED_MARKER, output)
        self.assertFalse(formatter.has_truncated_values)
        self.assertIn(str(small_list), output)

    def test_format_truncation_at_boundary(self):
        query = AssistantHogQLQuery(query="SELECT data")
        # Create a dict that's exactly at the boundary
        boundary_dict = {"x": "y" * (SQLResultsFormatter.MAX_CELL_LENGTH - 10)}
        results = [{"data": boundary_dict}]
        columns = ["data"]

        formatter = SQLResultsFormatter(query, results, columns)
        output = formatter.format()

        # Should be truncated since str(boundary_dict) > MAX_CELL_LENGTH
        if len(str(boundary_dict)) > SQLResultsFormatter.MAX_CELL_LENGTH:
            self.assertIn(TRUNCATED_MARKER, output)
            self.assertTrue(formatter.has_truncated_values)
        else:
            self.assertNotIn(TRUNCATED_MARKER, output)
            self.assertFalse(formatter.has_truncated_values)

    def test_format_truncates_stringified_json_dict(self):
        query = AssistantHogQLQuery(query="SELECT json_data")
        # Stringified JSON object
        large_json_str = '{"key_' + '0": "' + "x" * 600 + '"}'
        results = [{"json_data": large_json_str}]
        columns = ["json_data"]

        formatter = SQLResultsFormatter(query, results, columns)
        output = formatter.format()

        self.assertIn(TRUNCATED_MARKER, output)
        self.assertTrue(formatter.has_truncated_values)

    def test_format_truncates_stringified_json_array(self):
        query = AssistantHogQLQuery(query="SELECT json_data")
        # Stringified JSON array
        large_json_str = '["' + "x" * 600 + '"]'
        results = [{"json_data": large_json_str}]
        columns = ["json_data"]

        formatter = SQLResultsFormatter(query, results, columns)
        output = formatter.format()

        self.assertIn(TRUNCATED_MARKER, output)
        self.assertTrue(formatter.has_truncated_values)

    def test_format_does_not_truncate_small_stringified_json(self):
        query = AssistantHogQLQuery(query="SELECT json_data")
        small_json_str = '{"key": "value"}'
        results = [{"json_data": small_json_str}]
        columns = ["json_data"]

        formatter = SQLResultsFormatter(query, results, columns)
        output = formatter.format()

        self.assertNotIn(TRUNCATED_MARKER, output)
        self.assertFalse(formatter.has_truncated_values)
        self.assertIn(small_json_str, output)

    def test_format_does_not_truncate_long_regular_string(self):
        query = AssistantHogQLQuery(query="SELECT description")
        # Long string that doesn't look like JSON
        long_string = "x" * 600
        results = [{"description": long_string}]
        columns = ["description"]

        formatter = SQLResultsFormatter(query, results, columns)
        output = formatter.format()

        # Regular strings should NOT be truncated
        self.assertNotIn(TRUNCATED_MARKER, output)
        self.assertFalse(formatter.has_truncated_values)
        self.assertIn(long_string, output)

    def test_no_truncation_when_max_cell_length_is_none(self):
        query = AssistantHogQLQuery(query="SELECT properties")
        large_dict = {"key_" + str(i): "value_" + str(i) * 50 for i in range(20)}
        results = [{"properties": large_dict}]
        columns = ["properties"]

        formatter = SQLResultsFormatter(query, results, columns, max_cell_length=None)
        output = formatter.format()

        self.assertNotIn(TRUNCATED_MARKER, output)
        self.assertFalse(formatter.has_truncated_values)
        self.assertIn(str(large_dict), output)

    def test_custom_max_cell_length(self):
        query = AssistantHogQLQuery(query="SELECT data")
        results = [{"data": {"a": "x" * 100}}]
        columns = ["data"]

        formatter = SQLResultsFormatter(query, results, columns, max_cell_length=50)
        output = formatter.format()

        self.assertIn(TRUNCATED_MARKER, output)
        self.assertTrue(formatter.has_truncated_values)