Skip to content

A reactive notebook for Python — run reproducible experiments, query with SQL, execute as a script, deploy as an app, and version with git. Stored as pure Python. All in a modern, AI-native editor.

17.0 KB
Raw Blame
# Copyright 2026 Marimo. All rights reserved.

from __future__ import annotations

import pytest

from marimo._dependencies.dependencies import DependencyManager
from marimo._messaging.errors import MarimoSQLError
from marimo._sql.error_utils import (
    MarimoSQLException,
    _extract_sql_position,
    create_sql_error_from_exception,
    is_sql_parse_error,
)
from marimo._sql.sql import sql

HAS_DUCKDB = DependencyManager.duckdb.has()
HAS_SQLGLOT = DependencyManager.sqlglot.has()
HAS_PANDAS = DependencyManager.pandas.has()
HAS_POLARS = DependencyManager.polars.has()


@pytest.fixture
def duckdb_conn():
    """Per-test in-memory DuckDB connection.

    Each test gets a fresh, isolated connection.
    """
    if not HAS_DUCKDB:
        pytest.skip("DuckDB not installed")
    import duckdb

    conn = duckdb.connect(":memory:")
    try:
        yield conn
    finally:
        conn.close()


class TestDuckDBRuntimeErrors:
    """Test DuckDB errors that occur during SQL execution."""

    @pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
    def test_table_not_found_error(self, duckdb_conn):
        """Test error when referencing a non-existent table."""
        with pytest.raises(MarimoSQLException) as exc_info:
            sql("SELECT * FROM nonexistent_table", engine=duckdb_conn)

        error = exc_info.value
        assert "nonexistent_table" in str(error).lower()
        assert "does not exist" in str(error).lower()
        assert error.sql_statement == "SELECT * FROM nonexistent_table"

    @pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
    def test_column_not_found_error(self, duckdb_conn):
        """Test error when referencing a non-existent column."""
        duckdb_conn.execute(
            "CREATE OR REPLACE TABLE test_error_table (id INTEGER, name TEXT)"
        ).fetchall()

        with pytest.raises(MarimoSQLException) as exc_info:
            sql(
                "SELECT invalid_column FROM test_error_table",
                engine=duckdb_conn,
            )

        error = exc_info.value
        assert "invalid_column" in str(error)
        assert "test_error_table" in error.sql_statement

    @pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
    def test_syntax_error_missing_from(self, duckdb_conn):
        """Test syntax error when FROM keyword is misspelled."""
        with pytest.raises(MarimoSQLException) as exc_info:
            sql("SELECT * FRM test_table", engine=duckdb_conn)

        error = exc_info.value
        assert "syntax error" in str(error).lower()
        assert "SELECT * FRM test_table" in error.sql_statement

    @pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
    def test_syntax_error_malformed_expression(self, duckdb_conn):
        """Test syntax error with malformed SQL expression."""
        with pytest.raises(MarimoSQLException) as exc_info:
            sql("SELECT ( FROM table", engine=duckdb_conn)

        error = exc_info.value
        assert (
            "syntax error" in str(error).lower()
            or "parser error" in str(error).lower()
        )

    @pytest.mark.skipif(
        not HAS_DUCKDB or not (HAS_PANDAS or HAS_POLARS),
        reason="DuckDB/Pandas not installed",
    )
    def test_data_type_error(self, duckdb_conn):
        """Test data type conversion errors."""
        sql(
            "CREATE OR REPLACE TABLE test_type_table (id INTEGER)",
            engine=duckdb_conn,
        )
        sql("INSERT INTO test_type_table VALUES (1)", engine=duckdb_conn)

        with pytest.raises(MarimoSQLException) as exc_info:
            sql(
                "SELECT id / 'invalid_string' FROM test_type_table",
                engine=duckdb_conn,
            )

        error = exc_info.value
        # Error message varies by DuckDB version, just ensure we caught it
        assert len(str(error)) > 0

    @pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
    def test_long_sql_statement_truncation(self, duckdb_conn):
        """Test that long SQL statements are truncated in error messages."""
        long_query = (
            "SELECT "
            + ", ".join([f"col_{i}" for i in range(100)])
            + " FROM nonexistent_table"
        )

        with pytest.raises(MarimoSQLException) as exc_info:
            sql(long_query, engine=duckdb_conn)

        error = exc_info.value
        assert len(error.sql_statement) == len(long_query)


class TestSQLGlotParseErrors:
    """Test SQLGlot parsing errors during static analysis."""

    @pytest.mark.skipif(not HAS_SQLGLOT, reason="SQLGlot not installed")
    def test_malformed_case_statement(self):
        """Test ParseError with malformed CASE statement."""
        from sqlglot import parse_one
        from sqlglot.errors import ParseError

        with pytest.raises(ParseError):
            parse_one("SELECT CASE FROM table")

    @pytest.mark.skipif(not HAS_SQLGLOT, reason="SQLGlot not installed")
    def test_incomplete_cte(self):
        """Test ParseError with incomplete CTE."""
        from sqlglot import parse_one
        from sqlglot.errors import ParseError

        with pytest.raises(ParseError):
            parse_one("WITH cte AS (SELECT * FROM x)")

    @pytest.mark.skipif(not HAS_SQLGLOT, reason="SQLGlot not installed")
    def test_function_argument_errors(self):
        """Test ParseError with incorrect function arguments."""
        from sqlglot import parse_one
        from sqlglot.errors import ParseError

        # Too few arguments for IF function
        with pytest.raises(ParseError):
            parse_one("SELECT IF(a > 0)")

    @pytest.mark.skipif(not HAS_SQLGLOT, reason="SQLGlot not installed")
    def test_empty_query_parse(self):
        """Test ParseError with empty query."""
        from sqlglot import parse_one
        from sqlglot.errors import ParseError

        with pytest.raises(ParseError):
            parse_one("")

    @pytest.mark.skipif(not HAS_SQLGLOT, reason="SQLGlot not installed")
    def test_invalid_select_syntax(self):
        """Test ParseError with invalid SELECT syntax."""
        from sqlglot import parse_one
        from sqlglot.errors import ParseError

        with pytest.raises(ParseError):
            parse_one("SELECT * * FROM table")


class TestErrorUtilityFunctions:
    """Test marimo's SQL error handling utility functions."""

    @pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
    def test_is_sql_parse_error_duckdb(self, duckdb_conn):
        """Test detection of DuckDB parsing errors."""
        with pytest.raises(Exception) as exc_info:
            duckdb_conn.sql("SELECT * FROM nonexistent_table")
        assert is_sql_parse_error(exc_info.value) is True

        with pytest.raises(Exception) as exc_info:
            duckdb_conn.sql("SELECT * FRM invalid_syntax")
        assert is_sql_parse_error(exc_info.value) is True

    @pytest.mark.skipif(not HAS_SQLGLOT, reason="SQLGlot not installed")
    def test_is_sql_parse_error_sqlglot(self):
        """Test detection of SQLGlot parsing errors."""
        from sqlglot import parse_one
        from sqlglot.errors import ParseError

        with pytest.raises(ParseError) as exc_info:
            parse_one("SELECT CASE FROM table")
        assert is_sql_parse_error(exc_info.value) is True

    def test_is_sql_parse_error_non_sql_exception(self):
        """Test that non-SQL exceptions are not detected as SQL errors."""
        regular_exception = ValueError("This is not a SQL error")
        assert is_sql_parse_error(regular_exception) is False

    def test_is_sql_parse_error_marimo_sql_exception(self):
        """Test that MarimoSQLException is detected as SQL error."""
        marimo_sql_exception = MarimoSQLException("SQL error message")
        assert is_sql_parse_error(marimo_sql_exception) is True

    @pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
    def test_create_sql_error_from_exception(self, duckdb_conn):
        """Test conversion of raw exception to MarimoSQLError."""

        class MockCell:
            def __init__(self, sql_statement: str):
                self.sqls = [sql_statement]

        try:
            duckdb_conn.sql("SELECT * FROM nonexistent_table")
        except Exception as e:
            mock_cell = MockCell("SELECT * FROM nonexistent_table")
            error = create_sql_error_from_exception(e, mock_cell)

            assert isinstance(error, MarimoSQLError)
            assert "nonexistent_table" in error.sql_statement
            assert len(error.msg) > 0
            assert "nonexistent_table" in error.msg
            # Hint field should exist (may be None for this error)
            assert hasattr(error, "hint")

    def test_create_sql_error_forwards_position_without_hint(self):
        """Coordinates (sql_line/sql_col) must be forwarded even when hint is
        None — engines may report position without a hint (issue #10987)."""

        class MockCell:
            sqls = ["SELECT * FROM t"]

        exception = MarimoSQLException(
            message="syntax error",
            sql_statement="SELECT * FROM t",
            sql_line=3,
            sql_col=12,
            hint=None,
        )
        error = create_sql_error_from_exception(exception, MockCell())
        assert error.sql_line == 3
        assert error.sql_col == 12
        assert error.sql_statement == "SELECT * FROM t"
        assert error.hint is None

    @pytest.mark.requires("duckdb")
    def test_create_sql_error_long_statement(self, duckdb_conn):
        """Test SQL statement truncation in error creation."""
        long_statement = (
            "SELECT "
            + ", ".join([f"col_{i}" for i in range(100)])
            + " FROM test"
        )

        class MockCell:
            def __init__(self, sql_statement: str):
                self.sqls = [sql_statement]

        try:
            duckdb_conn.sql(long_statement)
        except Exception as e:
            mock_cell = MockCell(long_statement)
            error = create_sql_error_from_exception(e, mock_cell)
            assert len(error.sql_statement) == len(long_statement)


class TestErrorMessageQuality:
    """Test that error messages are actionable and well-formatted."""

    def test_extract_sql_position_duckdb_format(self):
        """Test position extraction from DuckDB error messages."""
        # DuckDB format: "Line 1, Col: 15"
        duckdb_msg = "Parser Error: syntax error at Line 1, Col: 15"
        line, col = _extract_sql_position(duckdb_msg)
        assert line == 0  # 0-based
        assert col == 14  # 0-based

    def test_extract_sql_position_sqlglot_format(self):
        """Test position extraction from SQLGlot error messages."""
        # SQLGlot format variations
        sqlglot_msg = "Parse error at line 2, col 10"
        line, col = _extract_sql_position(sqlglot_msg)
        assert line == 1  # 0-based
        assert col == 9  # 0-based

    def test_extract_sql_position_no_position(self):
        """Test position extraction when no position info available."""
        no_position_msg = "Some generic SQL error without position"
        line, col = _extract_sql_position(no_position_msg)
        assert line is None
        assert col is None

    def test_error_message_cleaning(self):
        """Test that error messages are cleaned of traces."""

        class MockException(Exception):
            def __str__(self):
                return "SQL error message\nTraceback (most recent call last):\n  File..."

        class MockCell:
            sqls = ["SELECT * FROM test"]

        error = create_sql_error_from_exception(MockException(), MockCell())
        # Should only contain the first line, no traceback
        assert "Traceback" in error.msg


class TestIntegrationAndEdgeCases:
    """Test complete error flow and edge cases."""

    @pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
    def test_sql_function_error_flow(self, duckdb_conn):
        """Test complete error flow through mo.sql() function."""
        with pytest.raises(MarimoSQLException) as exc_info:
            sql(
                "SELECT * FROM definitely_nonexistent_table_12345",
                engine=duckdb_conn,
            )

        error = exc_info.value
        assert isinstance(error, MarimoSQLException)
        assert error.sql_statement is not None
        assert len(error.sql_statement) > 0

    def test_empty_sql_statement_error_handling(self):
        """Test error handling with empty SQL statements."""

        class MockCell:
            sqls = []

        mock_exception = Exception("Test error")
        error = create_sql_error_from_exception(mock_exception, MockCell())

        assert error.sql_statement == ""

    def test_cell_without_sqls_attribute(self):
        """Test error handling when cell doesn't have sqls attribute."""

        class MockCellNoSqls:
            pass

        mock_exception = Exception("Test error")
        error = create_sql_error_from_exception(
            mock_exception, MockCellNoSqls()
        )

        assert error.sql_statement == ""

    @pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
    def test_multiple_errors_in_sequence(self, duckdb_conn):
        """Test handling multiple SQL errors in sequence."""
        # First error
        with pytest.raises(MarimoSQLException):
            sql("SELECT * FROM table1_nonexistent", engine=duckdb_conn)

        # Second error should still work
        with pytest.raises(MarimoSQLException):
            sql("SELECT * FROM table2_nonexistent", engine=duckdb_conn)

    @pytest.mark.requires("duckdb")
    def test_error_with_special_characters(self, duckdb_conn):
        """Test error handling with SQL containing special characters."""
        with pytest.raises(MarimoSQLException):
            sql(
                "SELECT * FROM 'table with spaces and quotes'",
                engine=duckdb_conn,
            )

    @pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
    def test_duckdb_hints_preserved(self, duckdb_conn):
        """Test that DuckDB hints like 'Did you mean?' are preserved in error messages."""
        # Create a table to generate "Did you mean?" suggestions
        duckdb_conn.sql(
            "CREATE OR REPLACE TABLE test_hints_table (id INT, name TEXT)"
        )

        with pytest.raises(MarimoSQLException) as exc_info:
            sql(
                "SELECT * FROM test_hint", engine=duckdb_conn
            )  # Missing 's' in table name

        error = exc_info.value
        error_msg = str(error)
        # Check that the main error message is present
        assert "does not exist" in error_msg
        # Check that the hint is properly extracted to the hint field
        assert error.hint is None

    @pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
    def test_column_candidates_preserved(self, duckdb_conn):
        """Test that column candidate hints are preserved in error messages."""
        # Create a table to generate candidate binding suggestions
        duckdb_conn.sql(
            "CREATE OR REPLACE TABLE test_columns (id INT, user_name TEXT, email TEXT)"
        )

        with pytest.raises(MarimoSQLException) as exc_info:
            sql(
                "SELECT fullname FROM test_columns", engine=duckdb_conn
            )  # Wrong column name

        error = exc_info.value
        error_msg = str(error)
        # Check that the main error message is present
        assert "not found" in error_msg
        # Check that the hint is properly extracted to the hint field
        assert error.hint is None

    @pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
    def test_hint_field_in_sql_error_struct(self, duckdb_conn):
        """Test that MarimoSQLError struct properly includes hint field."""
        # Create table for hint generation
        duckdb_conn.sql(
            "CREATE OR REPLACE TABLE hint_test_table (id INT, name TEXT)"
        )

        try:
            duckdb_conn.sql("SELECT * FROM hint_test")  # Missing letters
        except Exception as e:

            class MockCell:
                sqls = ["SELECT * FROM hint_test"]

            error_struct = create_sql_error_from_exception(e, MockCell())

            # Verify the struct has the hint field and it's populated
            assert hasattr(error_struct, "hint")
            assert error_struct.hint is None

    @pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
    def test_multiline_hints_preserved(self, duckdb_conn):
        """Test that multiline hints like function candidates are fully captured."""
        # Create table for multiline hint generation
        duckdb_conn.sql(
            "CREATE OR REPLACE TABLE hint_multiline_table (id INT, name TEXT)"
        )

        try:
            duckdb_conn.sql(
                "SELECT SUBSTRING(name) FROM hint_multiline_table"
            )  # Wrong args
        except Exception as e:

            class MockCell:
                sqls = ["SELECT SUBSTRING(name) FROM hint_multiline_table"]

            error_struct = create_sql_error_from_exception(e, MockCell())

            # Verify multiline hint is captured completely
            assert hasattr(error_struct, "hint")
            assert error_struct.hint is None