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.

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

from __future__ import annotations

from textwrap import dedent

import pytest

from marimo._dependencies.dependencies import DependencyManager
from marimo._sql.parse import (
    SqlParseError,
    SqlParseResult,
    format_query_with_globals,
    parse_sql,
    replace_brackets_with_quotes,
)
from marimo._sql.utils import is_duckdb_v2

HAS_DUCKDB = DependencyManager.duckdb.has()


@pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
@pytest.mark.parametrize(
    ("version", "expected"),
    [("1.5.6", False), ("2.0.0.dev2610011535", True), ("2.0.0", True)],
)
def test_is_duckdb_v2(
    monkeypatch: pytest.MonkeyPatch, version: str, expected: bool
):
    import duckdb

    monkeypatch.setattr(duckdb, "__version__", version)
    assert is_duckdb_v2() is expected


def test_sql_parse_result_with_errors():
    """Test SqlParseResult with errors."""
    error = SqlParseError(
        message="Syntax error", line=1, column=5, severity="error"
    )
    result = SqlParseResult(success=False, errors=[error])

    assert result.success is False
    assert len(result.errors) == 1
    assert result.errors[0].message == "Syntax error"


class TestUnsupportedDialects:
    """Test handling of unsupported SQL dialects."""

    @pytest.mark.parametrize(
        "dialect",
        [
            "postgresql",
            "mysql",
            "sqlite",
            "oracle",
            "sqlserver",
            "bigquery",
            "snowflake",
            "redshift",
            "",
            "unknown_dialect",
        ],
    )
    def test_unsupported_dialects_return_none(self, dialect: str):
        """Test that unsupported dialects return none."""
        result, error = parse_sql("SELECT * FROM table", dialect)
        assert result is None
        assert error == "Unsupported dialect: " + dialect

    def test_dialect_with_whitespace(self):
        """Test dialects with leading/trailing whitespace."""
        result, error = parse_sql("SELECT 1", "  postgresql  ")

        assert result is None
        assert error == "Unsupported dialect: postgresql"

    @pytest.mark.requires("duckdb")
    def test_duckdb_dialect_with_whitespace(self):
        """Test duckdb dialect with leading/trailing whitespace."""
        result, error = parse_sql("SELECT 1", " duckdb ")
        assert result is not None
        assert error is None


@pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
class TestDuckDBValidQueries:
    """Test parsing of valid SQL queries with DuckDB."""

    @pytest.mark.parametrize(
        "query",
        [
            "SELECT 1",
            "SELECT 1 as value",
            "SELECT 'hello' as greeting",
            "SELECT 1, 2, 3",
            "SELECT 1 as a, 2 as b, 3 as c",
            "SELECT 1 UNION SELECT 2",
            "SELECT CASE WHEN 1 > 0 THEN 'positive' ELSE 'zero or negative' END",
            "SELECT COUNT(*)",
            "SELECT SUM(1), AVG(1), MAX(1), MIN(1)",
            "SELECT 1 ORDER BY 1",
            "SELECT 1 LIMIT 10",
        ],
    )
    def test_valid_queries_return_success(self, query: str):
        """Test that valid SQL queries return successful parse results."""
        result, error = parse_sql(query, "duckdb")
        assert result is not None
        assert error is None

        assert isinstance(result, SqlParseResult)
        assert result.success is True
        assert result.errors == []

    @pytest.mark.parametrize(
        "dialect",
        [
            "duckdb",
            "DUCKDB",
            "DuckDB",
            "  duckdb  ",
        ],
    )
    def test_duckdb_dialect_variations(self, dialect: str):
        """Test that various DuckDB dialect strings work."""
        result, error = parse_sql("SELECT * FROM", dialect)
        assert result is not None
        assert error is None

        assert result.success is False

    def test_multiline_valid_query(self):
        """Test parsing multiline valid queries."""
        query = """
        SELECT
            1 as id,
            'test' as name,
            true as active
        ORDER BY id DESC
        """

        result, error = parse_sql(query, "duckdb")
        assert result is not None
        assert error is None

        assert result.success is True
        assert result.errors == []

    def test_query_with_comments(self):
        """Test parsing queries with SQL comments."""
        query = """
        -- This is a comment
        SELECT 1 as test_column; -- End line comment
        """

        result, error = parse_sql(query, "duckdb")
        assert result is not None
        assert error is None

        assert result.success is True
        assert result.errors == []

    def test_complex_valid_query(self):
        """Test parsing a complex but valid query."""
        query = """
        WITH summary AS (
            SELECT
                1 as id,
                100 as total_quantity,
                50.5 as avg_price
        )
        SELECT
            id,
            total_quantity,
            avg_price,
            CASE
                WHEN total_quantity > 100 THEN 'High Volume'
                WHEN total_quantity > 50 THEN 'Medium Volume'
                ELSE 'Low Volume'
            END as volume_category
        FROM summary
        ORDER BY total_quantity DESC
        """

        result, error = parse_sql(query, "duckdb")
        assert result is not None
        assert error is None

        assert result.success is True
        assert result.errors == []


@pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
class TestDuckDBInvalidQueries:
    """Test parsing of invalid SQL queries with DuckDB."""

    @pytest.mark.parametrize(
        ("query", "expected_error_keywords"),
        [
            ("SELECT * FRM table", ["syntax", "error"]),
            ("SELECT (", ["syntax", "error"]),
            ("SELECT * FROM", ["syntax", "error"]),
            ("SELECT ,", ["syntax", "error"]),
            ("SELEC * FROM table", ["syntax", "error"]),
            ("SELECT * FROM table WHERE", ["syntax", "error"]),
            ("SELECT * FROM table ORDER", ["syntax", "error"]),
            ("INSERT INTO", ["syntax", "error"]),
            ("UPDATE users SET", ["syntax", "error"]),
            ("DELETE FROM", ["syntax", "error"]),
            # Unclosed parentheses
            ("SELECT COUNT(*", ["syntax", "error"]),
            # Unclosed quotes
            # ("SELECT 'unclosed string", ["syntax", "error"]), # DuckDB does not raise errors for this case
            # # Invalid function call
            ("SELECT COUNT(DISTINCT FROM users)", ["syntax", "error"]),
            # # Malformed CASE statement
            ("SELECT CASE WHEN FROM table", ["syntax", "error"]),
            # # Incomplete JOIN
            ("SELECT * FROM table1 JOIN", ["syntax", "error"]),
        ],
    )
    def test_invalid_queries_return_errors(
        self, query: str, expected_error_keywords: list[str]
    ):
        """Test that invalid SQL queries return error results."""
        result, error = parse_sql(query, "duckdb")

        assert isinstance(result, SqlParseResult)
        assert result.success is False
        assert len(result.errors) > 0

        error = result.errors[0]
        assert isinstance(error, SqlParseError)
        assert error.severity == "error"
        assert error.line > 0
        assert error.column >= 0

        # Check that error message contains expected keywords
        error_msg_lower = error.message.lower()
        assert any(
            keyword in error_msg_lower for keyword in expected_error_keywords
        )


@pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
class TestErrorPositionCalculation:
    """Test that error positions (line and column) are calculated correctly."""

    def assert_line_column(
        self,
        result: SqlParseResult | None,
        parse_error: str | None,
        expected_line: int,
        expected_column: int,
    ):
        assert result is not None
        assert parse_error is None

        assert result.success is False
        assert len(result.errors) == 1
        error = result.errors[0]

        assert error.line == expected_line
        assert error.column == expected_column

    def test_single_line_error_position(self):
        """Test position calculation for single-line queries."""
        query = "SELECT * FRM table"  # Error at position of "FRM"

        result, error = parse_sql(query, "duckdb")
        self.assert_line_column(result, error, 1, 13)

    @pytest.mark.xfail(reason="DuckDB does not raise errors for this case")
    def test_multiline_error_position(self):
        """Test position calculation for multiline queries."""
        query = """SELECT name,
        email,
        FRM users"""  # Error on line 3

        result, error = parse_sql(query, "duckdb")
        self.assert_line_column(result, error, 3, 0)

    def test_error_at_beginning_of_line(self):
        """Test position calculation when error is at beginning of line."""
        query = """SELECT *
FRM table"""

        result, error = parse_sql(query, "duckdb")
        self.assert_line_column(result, error, 2, 4)

    def test_error_with_leading_whitespace(self):
        """Test position calculation with leading whitespace."""
        query = """    SELECT *
    FRM table"""

        result, error = parse_sql(query, "duckdb")
        assert result is not None

        assert result.success is False
        assert len(result.errors) == 1

        error = result.errors[0]
        assert error.line == 2

    @pytest.mark.xfail(
        reason="DuckDB does not raise errors for invalid syntax"
    )
    def test_error_position_after_newlines(self):
        """Test position calculation with multiple newlines."""
        query = """
        SELECT name
        FROM users
        WHERE invalid_syntax"""

        result, error = parse_sql(query, "duckdb")
        assert result is not None
        assert error is None

        assert result.success is False
        assert len(result.errors) == 1

        error = result.errors[0]
        assert error.line == 3
        assert error.column >= 0

    @pytest.mark.parametrize(
        "query",
        [
            "SELECT 'é'; SELECT * FRM users",
            "SELECT '😀'; SELECT * FRM users",
            "SELECT 'é😀';\nSELECT * FRM users",
            "SELECT 'é😀', {value};\nSELECT * FRM users",
            "SELECT 'é😀', {value} FRM users",
            "SELECT 'é😀', {value} FRM users WHERE id = {other}",
            "SELECT 'é😀'\nFRM users",
        ],
    )
    def test_unicode_error_position(self, query: str):
        result, error = parse_sql(query, "duckdb")
        prefix = query[: query.index("users")]
        expected_line = prefix.count("\n") + 1
        line_prefix = prefix.rsplit("\n", 1)[-1]
        expected_column = len(line_prefix.encode("utf-16-le")) // 2
        self.assert_line_column(result, error, expected_line, expected_column)

    def test_error_position_after_offset(self):
        """Test position calculation with offset."""
        query = """SELECT id FRM users"""
        result, error = parse_sql(query, "duckdb")

        expected_line = 1
        expected_column = 14
        self.assert_line_column(result, error, expected_line, expected_column)

        query_with_brackets = "SELECT {id} FRM users"
        result, error = parse_sql(query_with_brackets, "duckdb")
        # Add 2 for the brackets
        self.assert_line_column(
            result, error, expected_line, expected_column + 2
        )

        # Multiple brackets
        query_with_multiple_vars = "SELECT id, name FRM users"
        result, error = parse_sql(query_with_multiple_vars, "duckdb")

        expected_line = 1
        expected_column = 20
        self.assert_line_column(result, error, expected_line, expected_column)

        query_multiple_vars_brackets = "SELECT {id}, {name} FRM users"
        result, error = parse_sql(query_multiple_vars_brackets, "duckdb")
        self.assert_line_column(
            result,
            error,
            expected_line,
            expected_column + (2 * 2),  # 2 for each bracket
        )

    def test_offset_after_position(self):
        query = "SELECT * FRM users WHERE id = id"
        result, error = parse_sql(query, "duckdb")
        expected_column = 13
        expected_line = 1
        self.assert_line_column(result, error, expected_line, expected_column)

        query_with_brackets = "SELECT * FRM users WHERE id = {id}"
        result, error = parse_sql(query_with_brackets, "duckdb")
        # No change since the error is before the brackets
        self.assert_line_column(result, error, expected_line, expected_column)

        # Multiple variables with brackets
        query_multiple_vars_brackets = (
            "SELECT * FRM users WHERE id = id and name = name"
        )
        result, error = parse_sql(query_multiple_vars_brackets, "duckdb")
        self.assert_line_column(result, error, expected_line, expected_column)

        query_multiple_vars_brackets_with_brackets = (
            "SELECT * FRM users WHERE id = {id} and name = {name}"
        )
        result, error = parse_sql(
            query_multiple_vars_brackets_with_brackets, "duckdb"
        )
        self.assert_line_column(result, error, expected_line, expected_column)

    def test_mixed_position_brackets(self):
        query = "SELECT id FRM users WHERE name = name"
        result, error = parse_sql(query, "duckdb")
        expected_line = 1
        expected_column = 14
        self.assert_line_column(result, error, expected_line, expected_column)

        query_with_brackets = "SELECT {id} FRM users WHERE name = {name}"
        result, error = parse_sql(query_with_brackets, "duckdb")

        self.assert_line_column(
            result,
            error,
            expected_line,
            # Only accounts for brackets before the errors
            expected_column + 2,
        )

    def test_multiline_brackets_before_error(self):
        query = """SELECT id
FRM users"""
        result, error = parse_sql(query, "duckdb")
        expected_line = 2
        expected_column = 4
        self.assert_line_column(result, error, expected_line, expected_column)

        query_with_brackets = """SELECT {id}
FRM users"""
        result, error = parse_sql(query_with_brackets, "duckdb")
        self.assert_line_column(result, error, expected_line, expected_column)

    def test_brackets_on_error_line(self):
        # Brackets on error line
        query_error_line = """SELECT name,
id FRM users"""
        result, error = parse_sql(query_error_line, "duckdb")
        expected_line = 2
        expected_column = 7
        self.assert_line_column(result, error, expected_line, expected_column)

        query_error_line_with_brackets = """SELECT {name},
{id} FRM users"""
        result, error = parse_sql(query_error_line_with_brackets, "duckdb")
        self.assert_line_column(
            result, error, expected_line, expected_column + 2
        )

    @pytest.mark.parametrize(
        "query",
        [
            "SELECT {a}, {b}, {c}, {d}, {e};\nSELECT {value} FRM users",
            "SELECT 'é😀', {a},\n{id} FRM users",
            "SELECT {a}, {b}, {c}, {d}, {e} FRM users, {other}",
            "SELECT {a\n} FRM users",
            "SELECT {a\n+ b} FRM users",
            "SELECT {a\n}, {b} FRM users",
            "SELECT {a\n+ 😀} FRM users",
        ],
    )
    def test_bracket_positions_after_replacements(self, query: str):
        result, error = parse_sql(query, "duckdb")
        prefix = query[: query.index("users")]
        line_prefix = prefix.rsplit("\n", 1)[-1]
        self.assert_line_column(
            result,
            error,
            prefix.count("\n") + 1,
            len(line_prefix.encode("utf-16-le")) // 2,
        )

    def test_multiline_brackets_after_error(self):
        query = """SELECT *
FRM users WHERE name = name"""
        result, error = parse_sql(query, "duckdb")
        expected_line = 2
        expected_column = 4
        self.assert_line_column(result, error, expected_line, expected_column)

        query_with_brackets = """SELECT *
FRM users WHERE name = {name}"""
        result, error = parse_sql(query_with_brackets, "duckdb")
        # No change since the error is after the brackets
        self.assert_line_column(result, error, expected_line, expected_column)


@pytest.mark.skipif(not HAS_DUCKDB, reason="DuckDB not installed")
class TestEdgeCases:
    """Test edge cases and boundary conditions."""

    def test_empty_query(self):
        """Test parsing empty query."""
        result, error = parse_sql("", "duckdb")
        assert result is not None
        assert error is None

        assert isinstance(result, SqlParseResult)
        assert result.success is True

    def test_whitespace_only_query(self):
        """Test parsing query with only whitespace."""
        result, error = parse_sql("   \n  \t  ", "duckdb")
        assert result is not None
        assert error is None

        assert isinstance(result, SqlParseResult)
        assert result.success is True
        assert result.errors == []

    def test_query_with_semicolon(self):
        """Test parsing query with trailing semicolon."""
        result, _error = parse_sql("SELECT 1;", "duckdb")
        assert result is not None

        assert result.success is True
        assert result.errors == []

    def test_multiple_statements(self):
        """Test parsing multiple SQL statements."""
        query = "SELECT 1; SELECT 2;"

        result, _error = parse_sql(query, "duckdb")

        # DuckDB might handle this differently, just ensure we get a result
        assert isinstance(result, SqlParseResult)

    def test_very_long_query(self):
        """Test parsing a very long query."""
        # Create a query with many columns
        columns = ", ".join([f"{i} as col_{i}" for i in range(100)])
        query = f"SELECT {columns}"

        result, error = parse_sql(query, "duckdb")
        assert error is None

        assert result is not None
        assert result.success is True
        assert result.errors == []

    def test_query_with_unicode(self):
        """Test parsing query with Unicode characters."""
        query = "SELECT 'Hello 世界' as greeting"

        result, error = parse_sql(query, "duckdb")
        assert error is None

        assert result is not None
        assert result.success is True
        assert result.errors == []

    def test_query_with_special_characters(self):
        """Test parsing query with various special characters."""
        query = "SELECT 'test@#$%^&*()' as special_chars"

        result, error = parse_sql(query, "duckdb")
        assert error is None

        assert result is not None
        assert result.success is True
        assert result.errors == []

    def test_deeply_nested_query(self):
        """Test parsing deeply nested subqueries."""
        query = """
        SELECT * FROM (
            SELECT * FROM (
                SELECT * FROM (
                    SELECT 1 as nested_value
                ) inner_query
            ) middle_query
        ) outer_query
        """

        result, error = parse_sql(query, "duckdb")
        assert error is None

        assert result is not None
        assert result.success is True
        assert result.errors == []

    def test_just_comments(self):
        """Test parsing query with just comments."""
        for query in [
            """
            -- This is a comment
            /* This is a comment */
            """,
            """-- SELECT 1""",
            """/* SELECT 1 */""",
            """
            -- This is a comment
            SELECT 1 as test_column; -- End line comment
            """,
            """
            /* This is a comment */
            SELECT 1 as test_column; /* End line comment */
            """,
        ]:
            result, _error = parse_sql(query, "duckdb")
            assert result is not None
            assert result.success is True
            assert result.errors == []


@pytest.mark.skipif(HAS_DUCKDB, reason="DuckDB is installed")
def test_fails_gracefully_no_duckdb():
    """Test that the function fails gracefully if DuckDB is not installed."""
    result, error = parse_sql("SELECT 1", "duckdb")
    assert result is None
    assert error is not None


class TestReplaceBracketsWithQuotes:
    """Test the replace_brackets_with_quotes function."""

    def test_basic_replacement(self):
        """Test basic bracket replacement."""
        sql = "SELECT {id} FROM users"
        result_sql, offset_record = replace_brackets_with_quotes(sql)

        assert result_sql == "SELECT '{id}' FROM users"
        assert offset_record == {7: 2}

    def test_already_quoted_single(self):
        """Test that already single-quoted brackets are not modified."""
        sql = "SELECT {id}, '{name}' FROM users"
        result_sql, offset_record = replace_brackets_with_quotes(sql)

        assert result_sql == "SELECT '{id}', '{name}' FROM users"
        assert offset_record == {7: 2}

    def test_already_quoted_double(self):
        """Test that already double-quoted brackets are not modified."""
        sql = 'SELECT {id}, "{name}" FROM users'
        result_sql, offset_record = replace_brackets_with_quotes(sql)

        assert result_sql == "SELECT '{id}', \"{name}\" FROM users"
        assert offset_record == {7: 2}

    def test_multiple_brackets(self):
        """Test multiple unquoted brackets."""
        sql = "SELECT {id}, {name}, {age} FROM users"
        result_sql, offset_record = replace_brackets_with_quotes(sql)

        assert result_sql == "SELECT '{id}', '{name}', '{age}' FROM users"
        assert offset_record == {7: 2, 13: 2, 21: 2}

    def test_mixed_quoted_and_unquoted(self):
        """Test mix of quoted and unquoted brackets."""
        sql = "SELECT {id}, '{name}', {age}, \"{city}\" FROM users"
        result_sql, offset_record = replace_brackets_with_quotes(sql)

        assert (
            result_sql
            == "SELECT '{id}', '{name}', '{age}', \"{city}\" FROM users"
        )
        assert offset_record == {7: 2, 23: 2}

    def test_no_brackets(self):
        """Test SQL with no brackets."""
        sql = "SELECT id, name FROM users"
        result_sql, offset_record = replace_brackets_with_quotes(sql)

        assert result_sql == "SELECT id, name FROM users"
        assert offset_record == {}

    def test_empty_brackets(self):
        """Test SQL with empty brackets."""
        sql = "SELECT {} FROM users"
        result_sql, offset_record = replace_brackets_with_quotes(sql)

        assert result_sql == "SELECT '{}' FROM users"
        assert offset_record == {7: 2}

    def test_multiple_bracket_in_quotes(self):
        sql = "SELECT '{id} {name}' FROM users"
        result_sql, offset_record = replace_brackets_with_quotes(sql)

        assert result_sql == "SELECT '{id} {name}' FROM users"
        assert offset_record == {}

    def test_escaped_quotes_in_strings(self):
        """Test that escaped quotes in strings are handled correctly."""
        sql = "SELECT 'O\\'Brien', {id} FROM users"
        result_sql, offset_record = replace_brackets_with_quotes(sql)

        assert result_sql == "SELECT 'O\\'Brien', '{id}' FROM users"
        assert offset_record == {19: 2}

    def test_complex_nested_quotes(self):
        """Test complex nested quote scenarios."""
        sql = "SELECT '{id}', \"{name}\", {status} FROM users WHERE name = 'John\\'s'"
        result_sql, offset_record = replace_brackets_with_quotes(sql)

        assert (
            result_sql
            == "SELECT '{id}', \"{name}\", '{status}' FROM users WHERE name = 'John\\'s'"
        )
        assert offset_record == {25: 2}

    def test_multiline(self):
        """Test multiline query."""
        sql = """
        SELECT
        {id}, {name}, {age}
        FROM users
        WHERE name = 'John\\'s'
        """
        result_sql, offset_record = replace_brackets_with_quotes(sql)

        assert (
            result_sql
            == """
        SELECT
        '{id}', '{name}', '{age}'
        FROM users
        WHERE name = 'John\\'s'
        """
        )
        assert offset_record == {24: 2, 30: 2, 38: 2}

        sql = """SELECT
{id}
FROM users"""
        result_sql, offset_record = replace_brackets_with_quotes(sql)

        assert (
            result_sql
            == """SELECT
'{id}'
FROM users"""
        )
        assert offset_record == {7: 2}

        sql = dedent("""
        SELECT
            {id}
        FROM users
        """)
        result_sql, offset_record = replace_brackets_with_quotes(sql)

        assert result_sql == dedent("""
        SELECT
            '{id}'
        FROM users
        """)
        assert offset_record == {12: 2}

    def test_insert_json(self):
        query = "INSERT INTO users VALUES (1, '{\"id\": 1}')"
        result_sql, offset_record = replace_brackets_with_quotes(query)

        assert result_sql == "INSERT INTO users VALUES (1, '{\"id\": 1}')"
        assert offset_record == {}

    def test_brackets_inside_quotes(self):
        # Brackets inside single quotes should not be replaced
        query = "SELECT '{id}' FROM users"
        result_sql, offset_record = replace_brackets_with_quotes(query)
        assert result_sql == "SELECT '{id}' FROM users"
        assert offset_record == {}

        # Brackets inside double quotes should not be replaced
        query = 'SELECT "{id}" FROM users'
        result_sql, offset_record = replace_brackets_with_quotes(query)
        assert result_sql == 'SELECT "{id}" FROM users'
        assert offset_record == {}

    def test_multiple_brackets_on_same_line(self):
        query = "SELECT {id}, {name}, {age} FROM users"
        result_sql, offset_record = replace_brackets_with_quotes(query)
        assert result_sql == "SELECT '{id}', '{name}', '{age}' FROM users"
        assert offset_record == {7: 2, 13: 2, 21: 2}

    @pytest.mark.xfail(reason="Nested brackets are not supported")
    def test_nested_brackets(self):
        query = "SELECT {id_{nested}} FROM users"
        result_sql, offset_record = replace_brackets_with_quotes(query)
        assert result_sql == "SELECT '{id_{nested}}' FROM users"
        assert offset_record == {7: 2}

    def test_brackets_with_escaped_quotes(self):
        # Brackets inside a quoted string with escaped quotes
        query = "SELECT '{id}\\'s' FROM users"
        result_sql, offset_record = replace_brackets_with_quotes(query)
        assert result_sql == "SELECT '{id}\\'s' FROM users"
        assert offset_record == {}

    def test_brackets_at_start_and_end(self):
        query = "{id} FROM users WHERE name = {name}"
        result_sql, offset_record = replace_brackets_with_quotes(query)
        assert result_sql == "'{id}' FROM users WHERE name = '{name}'"
        assert offset_record == {0: 2, 29: 2}

    def test_brackets_with_special_characters(self):
        query = "SELECT {id_1$-foo} FROM users"
        result_sql, offset_record = replace_brackets_with_quotes(query)
        assert result_sql == "SELECT '{id_1$-foo}' FROM users"
        assert offset_record == {7: 2}

    def test_brackets_in_comment(self):
        # Brackets in SQL comments should be replaced, as comments are not parsed
        query = "SELECT id -- {comment}\nFROM users"
        result_sql, offset_record = replace_brackets_with_quotes(query)
        assert result_sql == "SELECT id -- '{comment}'\nFROM users"
        assert offset_record == {13: 2}

    def test_adjacent_brackets(self):
        query = "SELECT {id}{name}{age} FROM users"
        result_sql, offset_record = replace_brackets_with_quotes(query)
        assert result_sql == "SELECT '{id}''{name}''{age}' FROM users"
        assert offset_record == {7: 2, 11: 2, 17: 2}


class TestFormatQueryWithGlobals:
    """Test the format_query_with_globals function."""

    def test_basic_substitution(self):
        """Test basic variable substitution."""
        query = "SELECT {column} FROM users"
        result = format_query_with_globals(query, {"column": "id"})
        assert result == "SELECT id FROM users"

    def test_multiple_substitutions(self):
        """Test multiple variable substitutions."""
        query = "SELECT {col1}, {col2} FROM {table}"
        result = format_query_with_globals(
            query, {"col1": "id", "col2": "name", "table": "users"}
        )
        assert result == "SELECT id, name FROM users"

    def test_missing_variable_quoted(self):
        """Test that missing variables are returned with quotes."""
        query = "SELECT {missing} FROM users"
        result = format_query_with_globals(query, {})
        assert result == "SELECT 'missing' FROM users"

    def test_mixed_present_and_missing(self):
        """Test mix of present and missing variables."""
        query = "SELECT {present}, {missing} FROM users"
        result = format_query_with_globals(query, {"present": "id"})
        assert result == "SELECT id, 'missing' FROM users"

    def test_no_brackets_returns_unchanged(self):
        """Test query without brackets returns unchanged."""
        query = "SELECT * FROM users"
        result = format_query_with_globals(query, {"unused": "value"})
        assert result == "SELECT * FROM users"

    def test_empty_query(self):
        """Test empty query."""
        result = format_query_with_globals("", {})
        assert result == ""

    def test_empty_globals(self):
        """Test with empty globals dict - missing keys get quoted."""
        query = "SELECT {var} FROM users"
        result = format_query_with_globals(query, {})
        assert result == "SELECT 'var' FROM users"

    def test_quoted_brackets_ignored(self):
        """Test that brackets inside single quotes are NOT substituted."""
        query = "SELECT '{id}' FROM users"
        result = format_query_with_globals(query, {"id": "REPLACED"})
        assert result == "SELECT '{id}' FROM users"

    def test_double_quoted_brackets_substituted(self):
        """Test that brackets inside double quotes ARE substituted (identifiers)."""
        query = 'SELECT "{col}" FROM users'
        result = format_query_with_globals(query, {"col": "my_column"})
        assert result == 'SELECT "my_column" FROM users'

    def test_double_quoted_missing_key(self):
        """Test that missing keys in double quotes get quoted."""
        query = 'SELECT "{col}" FROM users'
        result = format_query_with_globals(query, {})
        assert result == "SELECT \"'col'\" FROM users"

    def test_mixed_quoted_and_unquoted(self):
        """Test mix of quoted and unquoted brackets."""
        query = "SELECT '{literal}', {variable} FROM users"
        result = format_query_with_globals(query, {"variable": "id"})
        assert result == "SELECT '{literal}', id FROM users"

    def test_numeric_value(self):
        """Test numeric values in globals."""
        query = "SELECT * FROM users WHERE id = {id}"
        result = format_query_with_globals(query, {"id": 42})
        assert result == "SELECT * FROM users WHERE id = 42"

    def test_string_value_not_auto_quoted(self):
        """Test that string values are inserted as-is (caller handles quoting)."""
        query = "SELECT * FROM users WHERE name = {name}"
        result = format_query_with_globals(query, {"name": "'Alice'"})
        assert result == "SELECT * FROM users WHERE name = 'Alice'"

    def test_escaped_quotes_in_strings(self):
        """Test brackets near escaped quotes."""
        query = "SELECT 'O\\'Brien', {id} FROM users"
        result = format_query_with_globals(query, {"id": "123"})
        assert result == "SELECT 'O\\'Brien', 123 FROM users"

    def test_json_in_quoted_string(self):
        """Test JSON-like content in quoted strings is preserved."""
        query = "INSERT INTO t VALUES ('{\"id\": 1}', {value})"
        result = format_query_with_globals(query, {"value": "42"})
        assert result == "INSERT INTO t VALUES ('{\"id\": 1}', 42)"

    def test_empty_brackets(self):
        """Test empty bracket expression."""
        query = "SELECT {} FROM users"
        result = format_query_with_globals(query, {"": "value"})
        assert result == "SELECT value FROM users"

    def test_empty_brackets_missing_key(self):
        """Test empty bracket expression with no matching key."""
        query = "SELECT {} FROM users"
        result = format_query_with_globals(query, {})
        assert result == "SELECT '' FROM users"

    def test_adjacent_brackets(self):
        """Test adjacent bracket expressions."""
        query = "SELECT {a}{b} FROM users"
        result = format_query_with_globals(query, {"a": "1", "b": "2"})
        assert result == "SELECT 12 FROM users"

    def test_multiline_query(self):
        """Test multiline query."""
        query = dedent("""
            SELECT {col}
            FROM {table}
            WHERE id = {id}
        """)
        result = format_query_with_globals(
            query, {"col": "name", "table": "users", "id": "1"}
        )
        expected = dedent("""
            SELECT name
            FROM users
            WHERE id = 1
        """)
        assert result == expected

    def test_missing_key_handler(self):
        """Test missing key handler."""
        query = "SELECT {col} FROM {table}"
        result_double_quote = format_query_with_globals(
            query, {"col": "id"}, lambda key: f'"{key}"'
        )
        assert result_double_quote == 'SELECT id FROM "table"'

        result_with_uppercase = format_query_with_globals(
            query, {"col": "id"}, lambda key: key.upper()
        )
        assert result_with_uppercase == "SELECT id FROM TABLE"


@pytest.mark.requires("duckdb")
@pytest.mark.parametrize("query", ["SELECT 1", "SELECT FROM"])
def test_parse_releases_connection_result(query: str) -> None:
    import duckdb

    result, error = parse_sql(query, "duckdb")
    assert error is None
    assert result is not None
    assert result.success == (query == "SELECT 1")
    assert duckdb.sql("SELECT 42").fetchall() == [(42,)]