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.

40.1 KB
Raw Blame
from __future__ import annotations

import ast
from textwrap import dedent

import pytest

from marimo._ast.sql_visitor import (
    SQLDefs,
    SQLRef,
    SQLVisitor,
    find_sql_defs,
    find_sql_refs,
)
from marimo._dependencies.dependencies import DependencyManager

HAS_DUCKDB = DependencyManager.duckdb.has()
HAS_SQLGLOT = DependencyManager.sqlglot.has()


def test_execute_with_string_literal() -> None:
    source_code = "db.execute('SELECT * FROM users')"
    tree = ast.parse(source_code)
    visitor = SQLVisitor()
    visitor.visit(tree)
    assert visitor.get_sqls() == ["SELECT * FROM users"]


def test_sql_with_string_literal() -> None:
    source_code = "db.sql('UPDATE users SET name = \\'Alice\\' WHERE id = 1')"
    tree = ast.parse(source_code)
    visitor = SQLVisitor()
    visitor.visit(tree)
    assert visitor.get_sqls() == [
        "UPDATE users SET name = 'Alice' WHERE id = 1"
    ]


def test_execute_with_f_string() -> None:
    source_code = 'db.execute(f"SELECT * FROM users WHERE name = {name}")'
    tree = ast.parse(source_code)
    visitor = SQLVisitor()
    visitor.visit(tree)
    assert visitor.get_sqls() == ["SELECT * FROM users WHERE name = 1"]


def test_no_sql_calls() -> None:
    source_code = "print('Hello, world!')"
    tree = ast.parse(source_code)
    visitor = SQLVisitor()
    visitor.visit(tree)
    assert visitor.get_sqls() == []


def test_sql_with_multiple_arguments() -> None:
    source_code = (
        "db.sql('SELECT * FROM users', 'This should not be captured')"
    )
    tree = ast.parse(source_code)
    visitor = SQLVisitor()
    visitor.visit(tree)
    assert visitor.get_sqls() == ["SELECT * FROM users"]


def test_multiple_sql_calls() -> None:
    source_code = dedent(
        """
        a = db.sql('SELECT * FROM users', 'This should not be captured')
        b = db.sql('ALTER TABLE users ADD COLUMN name TEXT')
        c = db.sql('UPDATE users SET name = \\'Alice\\' WHERE id = 1')
        """
    )
    tree = ast.parse(source_code)
    visitor = SQLVisitor()
    visitor.visit(tree)
    assert visitor.get_sqls() == [
        "SELECT * FROM users",
        "ALTER TABLE users ADD COLUMN name TEXT",
        "UPDATE users SET name = 'Alice' WHERE id = 1",
    ]


def test_sql_with_variable() -> None:
    source_code = dedent(
        """
      var = f"SELECT * FROM users WHERE name = {name}"
      db.sql(var)
    """
    )
    tree = ast.parse(source_code)
    visitor = SQLVisitor()
    visitor.visit(tree)
    assert visitor.get_sqls() == []


@pytest.mark.skipif(not HAS_DUCKDB, reason="Missing DuckDB")
class TestFindSQLDefs:
    @staticmethod
    def test_find_sql_defs_simple() -> None:
        sql = "CREATE TABLE test_table (id INT, name VARCHAR(255));"
        assert find_sql_defs(sql) == SQLDefs(
            tables=[SQLRef(table="test_table")]
        )

        sql = "CREATE VIEW test_view (id INT, name VARCHAR(255));"
        assert find_sql_defs(sql) == SQLDefs(views=[SQLRef(table="test_view")])

    @staticmethod
    def test_find_sql_defs_multiple() -> None:
        sql = """
        CREATE TABLE table1 (id INT);
        CREATE TABLE table2 (name VARCHAR(255));
        """
        assert find_sql_defs(sql) == SQLDefs(
            tables=[
                SQLRef(table="table1"),
                SQLRef(table="table2"),
            ],
        )

        sql = """
        CREATE VIEW table1 (id INT);
        CREATE VIEW table2 (name VARCHAR(255));
        """
        assert find_sql_defs(sql) == SQLDefs(
            views=[
                SQLRef(table="table1"),
                SQLRef(table="table2"),
            ],
        )

    @staticmethod
    def test_find_sql_defs_with_comments() -> None:
        sql = """
        CREATE TABLE
        -- This is a comment
        IF NOT EXISTS
        -- This is another comment
        table1 (id INT);
        -- This is a comment
        CREATE TABLE table2 (name VARCHAR(255));
        """
        assert find_sql_defs(sql) == SQLDefs(
            tables=[
                SQLRef(table="table1"),
                SQLRef(table="table2"),
            ],
        )

        sql = """
        CREATE VIEW
        -- This is a comment
        IF NOT EXISTS
        -- This is another comment
        table1 (id INT);
        -- This is a comment
        CREATE VIEW table2 (name VARCHAR(255));
        """
        assert find_sql_defs(sql) == SQLDefs(
            views=[
                SQLRef(table="table1"),
                SQLRef(table="table2"),
            ],
        )

    @staticmethod
    def test_find_sql_defs_with_or_replace() -> None:
        sql = "CREATE OR REPLACE TABLE test_table (id INT);"
        assert find_sql_defs(sql) == SQLDefs(
            tables=[SQLRef(table="test_table")]
        )

        sql = "CREATE OR REPLACE VIEW test_view (id INT);"
        assert find_sql_defs(sql) == SQLDefs(views=[SQLRef(table="test_view")])

    @staticmethod
    def test_find_sql_defs_unicode_comment_select() -> None:
        assert find_sql_defs("-- комментарий\nSELECT 1 AS x") == SQLDefs()

    @staticmethod
    @pytest.mark.parametrize("text", ["тест", "测试", "🚀"])
    @pytest.mark.parametrize(
        "prefix", ["-- {text}\n", "/* {text} */", "SELECT '{text}';"]
    )
    def test_find_sql_defs_after_unicode(prefix: str, text: str) -> None:
        prefix = prefix.format(text=text)
        assert (
            find_sql_defs(prefix + "SELECT a FROM t WHERE x = 1") == SQLDefs()
        )
        assert find_sql_defs(
            prefix + "CREATE TABLE t(id INT); CREATE VIEW v AS SELECT * FROM t"
        ) == SQLDefs(tables=[SQLRef(table="t")], views=[SQLRef(table="v")])

    @staticmethod
    @pytest.mark.parametrize("quote", ["", '"'])
    def test_find_sql_defs_unicode_identifiers(quote: str) -> None:
        catalog = f"{quote}каталог{quote}"
        schema = f"{quote}схема{quote}"
        table = f"{quote}表{quote}"
        view = f"{quote}представление{quote}"
        sql = (
            f"CREATE SCHEMA {catalog}.{schema};"
            f"CREATE TABLE {catalog}.{schema}.{table}(id INT);"
            f"CREATE VIEW {catalog}.{schema}.{view} AS SELECT '🚀';"
        )
        assert find_sql_defs(sql) == SQLDefs(
            tables=[SQLRef(table="表", catalog="каталог", schema="схема")],
            views=[
                SQLRef(
                    table="представление", catalog="каталог", schema="схема"
                )
            ],
            schemas=["схема"],
            reffed_catalogs=["каталог", "каталог", "каталог"],
            reffed_schemas=["схема", "схема"],
        )

    @staticmethod
    @pytest.mark.parametrize("quote", ["'", '"', "e'"])
    @pytest.mark.parametrize("alias", ["", ' AS "каталог🚀"'])
    def test_find_sql_defs_unicode_attach(quote: str, alias: str) -> None:
        sql = f"ATTACH {quote}данные数据库.db{quote[-1]}{alias}"
        assert find_sql_defs(sql) == SQLDefs(
            catalogs=["каталог🚀" if alias else "данные数据库"]
        )

    @staticmethod
    def test_find_sql_defs_temporary() -> None:
        sql = "CREATE TEMPORARY TABLE temp_table (id INT);"
        assert find_sql_defs(sql) == SQLDefs(
            tables=[SQLRef(table="temp_table")],
        )

        sql = "CREATE TEMPORARY VIEW temp_table (id INT);"
        assert find_sql_defs(sql) == SQLDefs(
            views=[SQLRef(table="temp_table")],
        )

    @staticmethod
    def test_find_sql_defs_if_not_exists() -> None:
        sql = "CREATE TABLE IF NOT EXISTS new_table (id INT);"
        assert find_sql_defs(sql) == SQLDefs(
            tables=[SQLRef(table="new_table")],
        )

        sql = "CREATE VIEW IF NOT EXISTS new_table (id INT);"
        assert find_sql_defs(sql) == SQLDefs(views=[SQLRef(table="new_table")])

    @staticmethod
    def test_find_sql_defs_complex() -> None:
        sql = """
        CREATE TABLE table1 (id INT);
        CREATE OR REPLACE TEMPORARY TABLE IF NOT EXISTS table2 (name VARCHAR(255));
        CREATE TABLE table3 (date DATE);
        """
        assert find_sql_defs(sql) == SQLDefs(
            tables=[
                SQLRef(table="table1"),
                SQLRef(table="table2"),
                SQLRef(table="table3"),
            ],
        )

        sql = """
        CREATE VIEW table1 (id INT);
        CREATE OR REPLACE TEMPORARY VIEW IF NOT EXISTS table2 (name VARCHAR(255));
        CREATE VIEW table3 (date DATE);
        """
        assert find_sql_defs(sql) == SQLDefs(
            views=[
                SQLRef(table="table1"),
                SQLRef(table="table2"),
                SQLRef(table="table3"),
            ],
        )

    @staticmethod
    def test_find_sql_defs_no_create() -> None:
        sql = "SELECT * FROM existing_table;"
        assert find_sql_defs(sql) == SQLDefs()

    @staticmethod
    def test_find_sql_defs_case_insensitive() -> None:
        sql = "create TABLE Test_Table (id INT);"
        assert find_sql_defs(sql) == SQLDefs(
            tables=[SQLRef(table="test_table")],
        )

        sql = "create VIEW Test_Table (id INT);"
        assert find_sql_defs(sql) == SQLDefs(
            views=[SQLRef(table="test_table")]
        )

    @staticmethod
    @pytest.mark.parametrize(
        "query",
        [
            "",
            "   ",
            ";",
        ],
    )
    def test_find_sql_defs_empty_input(
        query: str,
    ) -> None:
        assert find_sql_defs(query) == SQLDefs()

    @staticmethod
    @pytest.mark.parametrize(
        "query",
        [
            """
            -- This is a comment
            CREATE TABLE my_table (
                my_column INT, -- Inline comment
                my_other_column INT
            )
            """,
            """
            /* Multi-line
            comment */
            CREATE OR REPLACE TABLE my_table (
                my_column INT,
                my_other_column INT
            )
            """,
            """
            CREATE TABLE IF NOT EXISTS my_table
            -- Comment before AS
            AS
            /* Comment
            before SELECT */
            SELECT * FROM read_csv()
            """,
            """
            CREATE TEMPORARY TABLE my_table AS
            -- Comment in the middle
            SELECT * FROM existing_table
            """,
            """
            -- Comment at the start
            CREATE OR REPLACE TEMP TABLE IF NOT EXISTS my_table (
                id INT, -- Comment after column
                name VARCHAR
            ) -- Comment at the end
            """,
            """
            CREATE/* after CREATE */TABLE/* after TABLE */
            IF/* after IF */NOT/* after NOT */EXISTS
            -- Before the table name
            my_table (id INT)
            """,
            """
            CREATE -- Before OR
            OR /* Before REPLACE */ REPLACE
            /* Before TEMP */ TEMP -- Before TABLE
            TABLE /* Before the table name */ my_table (id INT)
            """,
        ],
    )
    def test_find_sql_defs_many_comments(
        query: str,
    ) -> None:
        assert find_sql_defs(query) == SQLDefs(
            tables=[SQLRef(table="my_table")],
        )

    @staticmethod
    def test_find_sql_defs_weird_names() -> None:
        sql = r"""
        CREATE TABLE "my--table" (
            "column/*with*/comment" INT,
            "another--column" VARCHAR
        );

        CREATE TABLE my_table_with_select AS
        SELECT *
        FROM (
            VALUES
            ('a', 1),
            ('b', 2)
        ) AS t("col--1", "col--2");

        CREATE TABLE "my/*weird*/table" (id INT);

        CREATE TABLE "with a space" (id INT);

        CREATE TABLE 'single-quotes' (id INT);
        CREATE TABLE e'escaped\ntable' (id INT);
        """
        assert find_sql_defs(sql) == SQLDefs(
            tables=[
                SQLRef(table="my--table"),
                SQLRef(table="my_table_with_select"),
                SQLRef(table="my/*weird*/table"),
                SQLRef(table="with a space"),
                SQLRef(table="single-quotes"),
                SQLRef(table=r"escaped\ntable"),
            ],
        )

    @staticmethod
    def test_find_created_database() -> None:
        sql = "ATTACH 'Chinook.sqlite';"
        assert find_sql_defs(sql) == SQLDefs(
            catalogs=["Chinook"],
        )

        sql = "ATTACH 'Chinook.sqlite' AS my_db;"
        assert find_sql_defs(sql) == SQLDefs(
            catalogs=["my_db"],
        )
        sql = "ATTACH DATABASE 'Chinook.sqlite';"
        assert find_sql_defs(sql) == SQLDefs(
            catalogs=["Chinook"],
        )

        sql = "ATTACH DATABASE IF NOT EXISTS 'Chinook.sqlite';"
        assert find_sql_defs(sql) == SQLDefs(
            catalogs=["Chinook"],
        )

        sql = "ATTACH DATABASE IF NOT EXISTS 'Chinook.sqlite' AS my_db;"
        assert find_sql_defs(sql) == SQLDefs(
            catalogs=["my_db"],
        )

    @staticmethod
    def test_find_sql_defs_attach_with_colon() -> None:
        sql = "ATTACH 'md:my_db'"
        assert find_sql_defs(sql) == SQLDefs(
            catalogs=["my_db"],
        )

    @staticmethod
    def test_find_sql_defs_with_catalog() -> None:
        sql = """
        CREATE TABLE my_catalog.my_table (id INT);
        """
        assert find_sql_defs(sql) == SQLDefs(
            tables=[SQLRef(table="my_table", schema="my_catalog")],
            reffed_catalogs=["my_catalog"],
        )

    @staticmethod
    def test_find_sql_defs_create_or_replace_with_catalog() -> None:
        sql = """
        CREATE OR REPLACE TABLE my_db.my_table as (SELECT 42);
        """
        assert find_sql_defs(sql) == SQLDefs(
            tables=[SQLRef(table="my_table", schema="my_db")],
            reffed_catalogs=["my_db"],
        )

    @staticmethod
    @pytest.mark.parametrize(
        "sql",
        [
            "CREATE TABLE my_catalog.my_schema.my_table (id INT);",
            """
            CREATE TABLE my_catalog /* Before the schema */ . my_schema
            -- Before the table
            . my_table (id INT);
            """,
        ],
    )
    def test_find_sql_defs_with_catalog_and_schema(sql: str) -> None:
        assert find_sql_defs(sql) == SQLDefs(
            tables=[
                SQLRef(
                    table="my_table", catalog="my_catalog", schema="my_schema"
                )
            ],
            reffed_catalogs=["my_catalog"],
            reffed_schemas=["my_schema"],
        )

    @staticmethod
    def test_find_sql_defs_with_catalog_and_main() -> None:
        sql = """
        CREATE TABLE my_catalog.main.my_table (id INT);
        """
        assert find_sql_defs(sql) == SQLDefs(
            tables=[
                SQLRef(table="my_table", catalog="my_catalog", schema="main")
            ],
            reffed_catalogs=["my_catalog"],
            reffed_schemas=[],  # main not included, since that is the default
        )

    @staticmethod
    def test_find_sql_defs_create_schema() -> None:
        sql = """
        CREATE SCHEMA my_catalog.my_schema;
        """
        assert find_sql_defs(sql) == SQLDefs(
            schemas=["my_schema"],
            reffed_catalogs=["my_catalog"],
        )

    @staticmethod
    def test_find_sql_defs_with_in_memory_catalog_and_schema() -> None:
        sql = """
        CREATE TABLE memory.main.my_table (id INT);
        """
        assert find_sql_defs(sql) == SQLDefs(
            tables=[SQLRef(table="my_table", catalog="memory", schema="main")],
        )

    @staticmethod
    def test_find_sql_defs_with_in_memory_catalog() -> None:
        sql = """
        CREATE TABLE memory.my_table (id INT);
        """
        assert find_sql_defs(sql) == SQLDefs(
            tables=[SQLRef(table="my_table", schema="memory")],
        )

    @staticmethod
    def test_find_sql_defs_with_temp_table() -> None:
        sql = """
        CREATE TEMP TABLE my_temp_table (id INT);
        """
        assert find_sql_defs(sql) == SQLDefs(
            tables=[SQLRef(table="my_temp_table")],
        )

    @staticmethod
    def test_find_sql_defs_with_if_not_exists() -> None:
        sql = """
        CREATE TABLE IF NOT EXISTS my_table (id INT);
        """
        assert find_sql_defs(sql) == SQLDefs(
            tables=[SQLRef(table="my_table")],
        )


@pytest.mark.skipif(
    HAS_DUCKDB, reason="Test requires DuckDB to be unavailable"
)
def test_find_sql_defs_duckdb_not_available() -> None:
    assert find_sql_defs("CREATE TABLE test (id INT);") == SQLDefs()


class TestSQLRefsConvertName:
    def test_simple(self) -> None:
        ref = SQLRef(table="test_table")
        assert ref.qualified_name == "test_table"

    def test_with_catalog(self) -> None:
        ref = SQLRef(table="test_table", catalog="test_catalog")
        assert ref.qualified_name == "test_catalog.test_table"

    def test_with_schema(self) -> None:
        ref = SQLRef(table="test_table", schema="test_schema")
        assert ref.qualified_name == "test_schema.test_table"

    def test_with_catalog_and_schema(self) -> None:
        ref = SQLRef(
            table="test_table", catalog="test_catalog", schema="test_schema"
        )
        assert ref.qualified_name == "test_catalog.test_schema.test_table"

    def test_with_catalog_and_table(self) -> None:
        ref = SQLRef(table="test_table", catalog="test_catalog")
        assert ref.qualified_name == "test_catalog.test_table"


@pytest.mark.skipif(not HAS_SQLGLOT, reason="Missing sqlglot")
class TestFindSQLRefs:
    def test_simple(self) -> None:
        sql = "SELECT * FROM test_table;"
        assert find_sql_refs(sql) == {SQLRef(table="test_table")}

    def test_multiple(self) -> None:
        sql = """
        SELECT * FROM table1;
        SELECT * FROM table2;
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="table1"),
            SQLRef(table="table2"),
        }

    def test_without_duplicates(self) -> None:
        sql = """
        SELECT * FROM table1;
        SELECT * FROM table2;
        SELECT * FROM table1;
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="table1"),
            SQLRef(table="table2"),
        }

    def test_with_function(self) -> None:
        sql = """
        SELECT *, embedding(text) as text_embedding
        FROM prompts;
        """
        assert find_sql_refs(sql) == {SQLRef(table="prompts")}

    def test_with_schema(self) -> None:
        sql = "SELECT * FROM schema1.table1;"
        assert find_sql_refs(sql) == {SQLRef(table="table1", schema="schema1")}

    def test_with_catalog(self) -> None:
        sql = "SELECT * FROM catalog1.schema1.table1;"
        assert find_sql_refs(sql) == {
            SQLRef(table="table1", schema="schema1", catalog="catalog1")
        }

    def test_memory_catalog(self) -> None:
        sql = "SELECT * FROM memory.main.table1;"
        assert find_sql_refs(sql) == {
            SQLRef(table="table1", schema="main", catalog="memory")
        }

    def test_with_multiple_tables(self) -> None:
        sql = "SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;"
        assert find_sql_refs(sql) == {
            SQLRef(table="table1"),
            SQLRef(table="table2"),
        }

    def test_with_join_schema(self) -> None:
        sql = "SELECT * FROM schema1.table1 JOIN schema2.table2 ON table1.id = table2.id;"
        assert find_sql_refs(sql) == {
            SQLRef(table="table1", schema="schema1"),
            SQLRef(table="table2", schema="schema2"),
        }

    def test_with_subquery(self) -> None:
        sql = "SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);"
        assert find_sql_refs(sql) == {
            SQLRef(table="table2"),
            SQLRef(table="table1"),
        }

    def test_with_subquery_2(self) -> None:
        sql = """
        SELECT * FROM (
            SELECT * FROM inner_table
        ) t;
        """
        assert find_sql_refs(sql) == {SQLRef(table="inner_table")}

    def test_with_cte(self) -> None:
        sql = """
        WITH cte AS (
            SELECT * FROM source_table
        )
        SELECT * FROM cte;
        """
        assert find_sql_refs(sql) == {SQLRef(table="source_table")}

    def test_with_union(self) -> None:
        sql = """
        SELECT * FROM table1
        UNION
        SELECT * FROM table2;
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="table1"),
            SQLRef(table="table2"),
        }

    def test_with_quoted_names(self) -> None:
        sql = """
        SELECT * FROM "My Table"
        JOIN "Weird.Name" ON "My Table".id = "Weird.Name".id;
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="My Table"),
            SQLRef(table="Weird.Name"),
        }

    def test_with_multiple_ctes(self) -> None:
        sql = """
        WITH
            cte1 AS (SELECT * FROM table1),
            cte2 AS (SELECT * FROM table2),
            cte3 AS (SELECT * FROM cte1 JOIN cte2)
        SELECT * FROM cte3;
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="table1"),
            SQLRef(table="table2"),
        }

    def test_with_nested_joins(self) -> None:
        sql = """
        SELECT * FROM t1
        JOIN (t2 JOIN t3 ON t2.id = t3.id)
        ON t1.id = t2.id;
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="t1"),
            SQLRef(table="t2"),
            SQLRef(table="t3"),
        }

    def test_with_lateral_join(self) -> None:
        sql = """
        SELECT * FROM employees,
        LATERAL (SELECT * FROM departments WHERE departments.id = employees.dept_id) dept;
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="departments"),
            SQLRef(table="employees"),
        }

    def test_with_schema_switching(self) -> None:
        sql = """
        SELECT * FROM schema1.table1
        JOIN schema2.table2 ON schema1.table1.id = schema2.table2.id;
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="table1", schema="schema1"),
            SQLRef(table="table2", schema="schema2"),
        }

    def test_with_complex_subqueries(self) -> None:
        sql = """
        SELECT * FROM (
            SELECT * FROM (
                SELECT * FROM deeply.nested.table
            ) t1
            JOIN another_table
        ) t2;
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="table", schema="nested", catalog="deeply"),
            SQLRef(table="another_table"),
        }

    def test_nested_intersect(self) -> None:
        sql = """
        SELECT * FROM table1
        WHERE id IN (
            SELECT id FROM table2
            UNION
            SELECT id FROM table3
            INTERSECT
            SELECT id FROM table4
        );
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="table2"),
            SQLRef(table="table3"),
            SQLRef(table="table4"),
            SQLRef(table="table1"),
        }

    def test_with_alias(self) -> None:
        sql = "SELECT * FROM employees AS e;"
        assert find_sql_refs(sql) == {SQLRef(table="employees")}

    def test_comment(self) -> None:
        sql = """
        -- comment
        SELECT * FROM table1;
        -- comment
        """
        assert find_sql_refs(sql) == {SQLRef(table="table1")}

    def test_ddl(self) -> None:
        # we are not referencing any table hence no refs
        sql = "CREATE TABLE t1 (id int);"
        assert find_sql_refs(sql) == set()

    def test_ddl_with_reference(self) -> None:
        sql = """
        CREATE TABLE table2 AS
        WITH x AS (
            SELECT * from table1
        )
        SELECT * FROM x;
        """
        assert find_sql_refs(sql) == {SQLRef(table="table1")}

    def test_update(self) -> None:
        sql = "UPDATE my_schema.table1 SET id = 1"
        assert find_sql_refs(sql) == {
            SQLRef(table="table1", schema="my_schema")
        }

    def test_insert(self) -> None:
        sql = "INSERT INTO my_schema.table1 (id INT) VALUES (1,2);"
        assert find_sql_refs(sql) == {
            SQLRef(table="table1", schema="my_schema")
        }

    def test_delete(self) -> None:
        sql = "DELETE FROM my_schema.table1 WHERE true;"
        assert find_sql_refs(sql) == {
            SQLRef(table="table1", schema="my_schema")
        }

    def test_multi_dml(self) -> None:
        sql = """
        INSERT INTO table1 (id INT) VALUES (1,2);
        DELETE FROM table2 WHERE true;
        UPDATE table3 SET id = 1;
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="table1"),
            SQLRef(table="table2"),
            SQLRef(table="table3"),
        }

    def test_multiple_selects_in_update(self) -> None:
        sql = """
        UPDATE schema1.table1
        SET table1.column1 = (
            SELECT table2.column2 FROM schema2.table2
        ),
        table1.column3 = (
            SELECT table3.column3 FROM table3
        )
        WHERE EXISTS (
            SELECT 1 FROM table2
        )
        AND table1.column4 IN (
            SELECT table4.column4 FROM table4
        );
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="table1", schema="schema1"),
            SQLRef(table="table2", schema="schema2"),
            SQLRef(table="table3"),
            SQLRef(table="table2"),  # Table2 comes from main schema
            SQLRef(table="table4"),
        }

    def test_select_in_insert(self) -> None:
        sql = """
        INSERT INTO table1 (column1, column2)
        SELECT column1, column2 FROM table2
        WHERE column3 = 'value';
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="table1"),
            SQLRef(table="table2"),
        }

    def test_select_in_delete(self) -> None:
        sql = """
        DELETE FROM table1
        WHERE column1 IN (
            SELECT column1 FROM table2
            WHERE column2 = 'value'
        );
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="table1"),
            SQLRef(table="table2"),
        }

    def test_invalid_sql(self) -> None:
        sql = "SELECT * FROM"
        assert find_sql_refs(sql) == set()

    def test_dml_with_subquery(self) -> None:
        # first
        sql = """
        insert into table1 (column1) select distinct column1 from table2 order by random();
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="table1"),
            SQLRef(table="table2"),
        }

        # second
        sql = """
        update table3 set column1=(select column2 from table1 t where t.column1=table3.column1);
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="table3"),
            SQLRef(table="table1"),
        }

        # combined
        sql = """
        insert into table1 (column1) select distinct column1 from table2 order by random();
        update table3 set column1=(select column2 from table1 t where t.column1=table3.column1);
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="table1"),
            SQLRef(table="table2"),
            SQLRef(table="table3"),
        }

    def test_with_regex(self) -> None:
        # regex and replacement
        sql = """
        SELECT name, REGEXP_REPLACE(name, '^.*/', '') regex_name
        FROM pdb.database
        LIMIT 10;
        """
        assert find_sql_refs(sql) == {SQLRef(table="database", schema="pdb")}

    def test_read_file_and_urls(self) -> None:
        sql = "SELECT * FROM 'file.csv'"
        assert find_sql_refs(sql) == set()

        sql = "SELECT * FROM 'https://example.com/file.csv'"
        assert find_sql_refs(sql) == set()

        sql = "SELECT * FROM read_csv('/dev/stdin')"
        assert find_sql_refs(sql) == set()

    def test_describe_table(self) -> None:
        sql = "DESCRIBE test_duck;"
        assert find_sql_refs(sql) == {SQLRef(table="test_duck")}

    def test_summarize_table(self) -> None:
        sql = "SUMMARIZE test_duck;"
        assert find_sql_refs(sql) == {SQLRef(table="test_duck")}

    def test_pivot_table(self) -> None:
        sql = "PIVOT test_duck ON column_name USING sum(value);"
        assert find_sql_refs(sql) == {SQLRef(table="test_duck")}

    def test_unpivot_table(self) -> None:
        sql = "UNPIVOT test_duck ON (col1, col2) INTO NAME column_name VALUE column_value;"
        assert find_sql_refs(sql) == {SQLRef(table="test_duck")}

    def test_describe_with_schema(self) -> None:
        sql = "DESCRIBE my_schema.test_table;"
        assert find_sql_refs(sql) == {
            SQLRef(table="test_table", schema="my_schema")
        }

    def test_summarize_with_catalog_schema(self) -> None:
        sql = "SUMMARIZE my_catalog.my_schema.test_table;"
        assert find_sql_refs(sql) == {
            SQLRef(
                table="test_table", schema="my_schema", catalog="my_catalog"
            )
        }

    def test_analyze_table(self) -> None:
        sql = "ANALYZE test_table;"
        assert find_sql_refs(sql) == {SQLRef(table="test_table")}

    def test_drop_table(self) -> None:
        sql = "DROP TABLE test_table;"
        assert find_sql_refs(sql) == {SQLRef(table="test_table")}

    def test_truncate_table(self) -> None:
        sql = "TRUNCATE test_table;"
        assert find_sql_refs(sql) == {SQLRef(table="test_table")}

    def test_copy_table_to_file(self) -> None:
        sql = "COPY test_table TO 'output.csv';"
        assert find_sql_refs(sql) == {SQLRef(table="test_table")}

    def test_copy_table_from_file(self) -> None:
        sql = "COPY test_table FROM 'input.csv';"
        assert find_sql_refs(sql) == {SQLRef(table="test_table")}

    def test_analyze_with_schema(self) -> None:
        sql = "ANALYZE my_schema.test_table;"
        assert find_sql_refs(sql) == {
            SQLRef(table="test_table", schema="my_schema")
        }

    def test_unnest_with_json_access(self) -> None:
        # Regression test for issue #8080
        # sqlglot's build_scope raises OptimizeError for unnest with JSON access
        # because it generates duplicate empty aliases internally
        sql = "SELECT unnest([1,2,3])->>'$' FROM (SELECT 1)"
        # Should not raise OptimizeError; falls back to direct table extraction
        assert find_sql_refs(sql) == set()

    def test_duplicate_alias_in_subqueries(self) -> None:
        # sqlglot's build_scope raises OptimizeError for duplicate aliases
        # in cross-joined subqueries which is valid DuckDB syntax
        sql = "SELECT * FROM (SELECT 1 as x), (SELECT 2 as x)"
        # Should not raise OptimizeError; falls back to direct table extraction
        assert find_sql_refs(sql) == set()

    def test_duplicate_alias_with_table_refs(self) -> None:
        # Test that table refs are still extracted when OptimizeError occurs
        sql = """
        SELECT * FROM (SELECT * FROM table1),
        (SELECT * FROM table2 as x),
        (SELECT * FROM table3 as x)
        """
        # Should not raise OptimizeError and should extract table refs
        assert find_sql_refs(sql) == {
            SQLRef(table="table1"),
            SQLRef(table="table2"),
            SQLRef(table="table3"),
        }

    def test_cte_with_duplicate_join_aliases(self) -> None:
        # Regression test for issue #9168
        # When two JOIN-ed tables share the same alias and one of them
        # is a CTE, build_scope raises OptimizeError ("Alias already used").
        # The fallback path must still filter out CTE names.
        sql = """
        WITH
            num_exams AS (
                SELECT
                    student_id,
                    exam_type_id,
                    COUNT(*) AS num_exams
                FROM
                    exam_records
                GROUP BY
                    student_id,
                    exam_type_id
            )
        SELECT
            student_id,
            student_name,
            exam_type_id,
            c.class_name,
            COALESCE(c.num_exams, 0) AS num_exams
        FROM
            students
            LEFT JOIN num_exams c USING (student_id)
            JOIN classes c USING (class_id)
        """
        # num_exams is a CTE and should NOT appear as a dependency
        assert find_sql_refs(sql) == {
            SQLRef(table="exam_records"),
            SQLRef(table="students"),
            SQLRef(table="classes"),
        }

    def test_cte_with_duplicate_join_aliases_mixed_case(self) -> None:
        # CTE defined as "Num_Exams" but referenced as "num_exams".
        # SQL identifiers are case-insensitive, so these must match.
        sql = """
        WITH
            Num_Exams AS (
                SELECT student_id, exam_type_id, COUNT(*) AS num_exams
                FROM exam_records
                GROUP BY student_id, exam_type_id
            )
        SELECT
            student_id, student_name, exam_type_id,
            c.class_name, COALESCE(c.num_exams, 0) AS num_exams
        FROM students
            LEFT JOIN num_exams c USING (student_id)
            JOIN classes c USING (class_id)
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="exam_records"),
            SQLRef(table="students"),
            SQLRef(table="classes"),
        }

    def test_cte_with_duplicate_join_aliases_different_aliases(self) -> None:
        # Same query as above but with distinct aliases — should work
        # both before and after the fix (build_scope succeeds here).
        sql = """
        WITH
            num_exams AS (
                SELECT
                    student_id,
                    exam_type_id,
                    COUNT(*) AS num_exams
                FROM
                    exam_records
                GROUP BY
                    student_id,
                    exam_type_id
            )
        SELECT
            student_id,
            student_name,
            exam_type_id,
            cl.class_name,
            COALESCE(ne.num_exams, 0) AS num_exams
        FROM
            students
            LEFT JOIN num_exams ne USING (student_id)
            JOIN classes cl USING (class_id)
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="exam_records"),
            SQLRef(table="students"),
            SQLRef(table="classes"),
        }

    def test_multiple_ctes_with_duplicate_aliases(self) -> None:
        # Multiple CTEs referenced with the same alias in joins
        sql = """
        WITH
            cte1 AS (SELECT id, val FROM table1),
            cte2 AS (SELECT id, val FROM table2)
        SELECT *
        FROM table3
            JOIN cte1 x ON table3.id = x.id
            JOIN cte2 x ON table3.id = x.id
        """
        # Neither cte1 nor cte2 should appear as dependencies
        assert find_sql_refs(sql) == {
            SQLRef(table="table1"),
            SQLRef(table="table2"),
            SQLRef(table="table3"),
        }

    def test_cte_name_matches_real_table_with_duplicate_alias(self) -> None:
        # Edge case: CTE name shadows a real table used elsewhere.
        # The CTE itself still shouldn't be a dependency — only the
        # tables referenced inside and outside it should be.
        sql = """
        WITH
            shared_name AS (SELECT id FROM source_table)
        SELECT *
        FROM base_table
            JOIN shared_name a ON base_table.id = a.id
            JOIN other_table a ON base_table.id = a.id
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="source_table"),
            SQLRef(table="base_table"),
            SQLRef(table="other_table"),
        }

    def test_schema_qualified_table_same_name_as_cte(self) -> None:
        # A schema-qualified table reference should never be filtered,
        # even if its base name matches a CTE in the same query.
        sql = """
        WITH foo AS (SELECT id FROM source)
        SELECT *
        FROM schema1.foo
            JOIN foo a ON schema1.foo.id = a.id
            JOIN bar a ON schema1.foo.id = a.id
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="source"),
            SQLRef(table="foo", schema="schema1"),
            SQLRef(table="bar"),
        }

    def test_nested_subquery_cte_does_not_mask_outer_table(self) -> None:
        # A CTE defined inside a subquery is scoped to that subquery.
        # It must not mask a real table with the same name in the outer
        # query. Duplicate aliases force the OptimizeError fallback.
        sql = """
        SELECT *
        FROM my_table
            JOIN (
                WITH my_table AS (SELECT 1 AS id)
                SELECT * FROM my_table
            ) a ON my_table.id = a.id
            JOIN other_table a ON my_table.id = a.id
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="my_table"),
            SQLRef(table="other_table"),
        }

    def test_dml_with_cte(self) -> None:
        # CTE names should be filtered in DML statements too.
        sql = """
        WITH cte AS (SELECT * FROM source_table)
        INSERT INTO target_table SELECT * FROM cte;
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="source_table"),
            SQLRef(table="target_table"),
        }

    @pytest.mark.parametrize(
        "statement",
        [
            "INSERT INTO items VALUES (1) RETURNING *",
            "UPDATE items SET id = 2 RETURNING *",
            "DELETE FROM items RETURNING *",
        ],
    )
    def test_dml_cte_reference(self, statement: str) -> None:
        sql = f"WITH changed AS ({statement}) SELECT * FROM changed"
        assert find_sql_refs(sql) == {SQLRef(table="items")}

    @pytest.mark.parametrize(
        "statement",
        [
            "INSERT INTO changed (id) VALUES (1) RETURNING *",
            "UPDATE changed SET id = 2 RETURNING *",
            "DELETE FROM changed RETURNING *",
        ],
    )
    def test_dml_cte_name_matches_target(self, statement: str) -> None:
        sql = f"WITH changed AS ({statement}) SELECT * FROM changed"
        assert find_sql_refs(sql) == {SQLRef(table="changed")}

    def test_nested_dml_cte_scope(self) -> None:
        sql = """
        SELECT * FROM changed
        JOIN (
            WITH changed AS (INSERT INTO items VALUES (1) RETURNING *),
                 local_rows AS (SELECT * FROM changed)
            SELECT * FROM local_rows
        ) sub ON changed.id = sub.id
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="changed"),
            SQLRef(table="items"),
        }

    def test_dml_cte_schema_qualified_reference(self) -> None:
        sql = """
        WITH changed AS (DELETE FROM items RETURNING *)
        SELECT * FROM archive.changed a JOIN changed c ON a.id = c.id
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="items"),
            SQLRef(table="changed", schema="archive"),
        }

    def test_dml_cte_reads_table_with_same_name(self) -> None:
        sql = """
        WITH changed AS (
            INSERT INTO items SELECT * FROM changed RETURNING *
        )
        SELECT * FROM changed
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="items"),
            SQLRef(table="changed"),
        }

    def test_dml_cte_with_recursive_source(self) -> None:
        sql = """
        WITH RECURSIVE nums AS (
            SELECT 1 AS id
            UNION ALL
            SELECT id + 1 FROM nums WHERE id < 3
        ), changed AS (INSERT INTO items SELECT * FROM nums RETURNING *)
        SELECT * FROM changed
        """
        assert find_sql_refs(sql) == {SQLRef(table="items")}

    def test_dml_cte_cannot_reference_later_recursive_cte(self) -> None:
        sql = """
        WITH RECURSIVE changed AS (
            INSERT INTO items SELECT * FROM later RETURNING *
        ), later AS (SELECT 1 AS id)
        SELECT * FROM changed
        """
        assert find_sql_refs(sql) == {
            SQLRef(table="items"),
            SQLRef(table="later"),
        }

    def test_multiple_statements_with_optimize_error(self) -> None:
        # Verify that OptimizeError in one statement doesn't affect others.
        # The try/except is inside the loop, so each statement is independent.
        sql = """
        SELECT * FROM table1;
        SELECT * FROM (SELECT 1 as x), (SELECT 2 as x);
        SELECT * FROM table2;
        """
        # table1 and table2 should be extracted normally via build_scope;
        # the middle statement falls back but has no table refs
        assert find_sql_refs(sql) == {
            SQLRef(table="table1"),
            SQLRef(table="table2"),
        }