Repository: duckdb/duckdb (language: python)

Issue:
Eatoni v1.1.0 does not work with pandas API as it did earlier
### What happens?

Duckdb v1.1.0 introduces a new error:
```
CatalogException: Catalog Error: Table with name df does not exist!
```

### To Reproduce

We have a `utils.duckdb.py` the contents of which are:
```python
import duckdb
import time
import pandas

import logging, os
_logs = logging.getLogger(os.path.relpath(__file__, start=os.getcwd()))

with duckdb.connect(database=':memory:') as con:
    output = con.sql(
        f"""
        INSTALL httpfs;
        LOAD httpfs;
        CREATE PERSISTENT SECRET IF NOT EXISTS (
            TYPE S3,
            PROVIDER CREDENTIAL_CHAIN
        );
    """).fetchall()
    _logs.info(f'Loaded extensions and secrets {output}')


def connect_db(
    db: str=':memory:',
    max_retries: int=20,
    max_retry_duration: int=300,
    start_retry_time: int=10,
    read_only: bool=False,
    temp_directory: str='./'
) -> duckdb.DuckDBPyConnection:
    """
    Connects to a duckDB database and returns the connection object.

    Args:
        db (str, optional): The path to the database file. Defaults to ':memory:', in-memory database.
        max_retries (int, optional): The maximum number of connection retries. Defaults to 20.
        max_retry_duration (int, optional): The maximum duration (in seconds) for connection retries. Defaults to 300.
        start_retry_time (int, optional): The initial wait time (in seconds) between connection retries. Defaults to 10.
        read_only (bool, optional): Specifies if the connection should be read-only. Defaults to `False`
        temp_directory (str, optional): The temporary directory path. Defaults to './', the current directory

    Returns:
        duckdb.Connection: The connection object to the duckDB database.

    Raises:
        Exception: If an error occurs during the connection process.

    NOTE:
        If connection fails, ensure that we have permission to rwx on the db file using: sudo chmod -R a+rwx ~/db_folder

    Example:
        ```python
        with connect_db(db=':memory:', read_only=False) as con:
            con.execute("CREATE TABLE test (a INTEGER)")
        ```
    """
    con = None
    retries = 0
    successful = False

    if db == ':memory:':
        read_only = False

    while not successful and retries <= max_retries:
        try:
            con = duckdb.connect(database=db, read_only=read_only)
            con.execute(
                f"""
                SET old_implicit_casting=true;
                SET http_keep_alive=false;
                SET temp_directory = "{temp_directory}";"""
            )
            successful = True
            _logs.info(f'duckDB connection successful with {db}, read_only={read_only}')
        except Exception as e:
            if isinstance(e, duckdb.IOException):
                _logs.warning(f"Database is busy. We'll wait {db}: {e}")
                time.sleep(min(start_retry_time * retries, max_retry_duration))
            else:
                # Raise the original exception
                raise e
        finally:
            retries += 1
    if con is None:
        _logs.error(f'Failed to establish connection with {db}')
    return con


def fetch_query(
    query: str,
    db: str=':memory:',
    read_only: bool=True,
    temp_directory: str='./'
) -> pandas.DataFrame:
    """Executes the given query on the specified DuckDB database and returns the result as a pandas DataFrame.

    Uses [connect_db][utils.duckdb.connect_db] for connection

    Parameters:
        query (str): The SQL query to execute.
        db (str, optional): The path to the DuckDB database file. Defaults to ':memory:'.
        read_only (bool, optional): Specifies whether the database should be opened in read-only mode. Defaults to `True` except when `db = ':memory:'`
        temp_directory (str, optional): The path to the directory where temporary files will be stored. Defaults to './'.

    Returns:
        pandas.DataFrame: The result of the query as a pandas DataFrame.

    Example:
        ```python
        df = fetch_query(
            query = "SELECT * FROM test",
            db = ':memory:',
            read_only = False
        )
        ```
    """
    with connect_db(
        db = db,
        read_only = read_only,
        temp_directory = temp_directory
    ) as con:
        return con.execute(query).fetchdf()
```


This works for all duckdb >= 0:
```python
import duckdb
import pandas
print(duckdb.sql("SELECT version()").df())
df = pandas.DataFrame.from_dict({'a': [42]})
results = duckdb.sql("SELECT * FROM df").df()
print(results)
```

This works for all duckdb v<=1.0.0 but not for v==1.1.0:
```python
from utils.duckdb import *
import pandas
print(duckdb.sql("SELECT version()").df())
df = pandas.DataFrame.from_dict({'a': [42]})
results = fetch_query("SELECT * FROM df")
print(results)
```
The error thrown in v1.1.0 is:
```console
---------------------------------------------------------------------------
CatalogException                          Traceback (most recent call last)
Cell In[8], line 12
      8 df = pandas.DataFrame.from_dict({'a': [42]})
     10 # query the Pandas DataFrame "df"
     11 # Note: duckdb.sql connects to the default in-memory database connection
---> 12 results = fetch_query("SELECT * FROM df")
     13 print(results)

File ~/utils/duckdb.py:138, in fetch_query(query, db, read_only, temp_directory)
    110 def fetch_query(
    111     query: str,
    112     db: str=':memory:',
    113     read_only: bool=True,
    114     temp_directory: str='./'
    115 ) -> pandas.DataFrame:
    116     """Executes the given query on the specified DuckDB database and returns the result as a pandas DataFrame.
    117 
    118     Uses [connect_db][utils.duckdb.connect_db] for connection
   (...)
    136         ```
    137     """
--> 138     with connect_db(
    139         db = db,
    140         read_only = read_only,
...

CatalogException: Catalog Error: Table with name df does not exist!
Did you mean "pg_depend"?
LINE 1: SELECT * FROM df
                      ^
```

However it works if I replace the line:
```python
from utils.duckdb import *
```
with the functions definitions in `utils.duckdb.py`. Why does this happen in the new version?

### OS:

x86

### DuckDB Version:

1.1.0

### DuckDB Client:

Python 3.12

### Full Name:

Rajiv Patki

### Affiliation:

Jodaro

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Yes

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [x] Yes, I have


Discussion / hints:
This is expected, we used to scan the entire frame lineage for the `df` variable, but we switched it to behave like Python itself and only scan the current frame.

You would need to either provide the `df` to the `fetch_query` scope or register `df` to the duckdb connection before querying

See: <https://github.com/duckdb/duckdb/pull/12425>
Apologies but this does not work. Method 1 below works but Method 2 still throws error even if the dataframe is passed as an argument to the function:
```console
CatalogException: Catalog Error: Table with name df does not exist!
```
in 
```python
from utils.duckdb import *
import pandas
print(duckdb.sql("SELECT version() as duckdb_version"))
df = pandas.DataFrame.from_dict({'a': [42]})

# Method 1
with connect_db() as con:
    print(con.sql("SELECT * FROM df").df())

# Method 2
print(fetch_query("SELECT * FROM df", pandas_df = [df], read_only = False))
```
you are using `select * from df`, it's not called `df`, it's called `pandas_df`

Fix patch:
diff --git a/tools/pythonpkg/src/pyconnection.cpp b/tools/pythonpkg/src/pyconnection.cpp
index 9f3c92a0f91c..74b22e699856 100644
--- a/tools/pythonpkg/src/pyconnection.cpp
+++ b/tools/pythonpkg/src/pyconnection.cpp
@@ -1945,6 +1945,10 @@ shared_ptr<DuckDBPyConnection> DuckDBPyConnection::Connect(const py::object &dat
 	config.AddExtensionOption("python_enable_replacements",
 	                          "Whether variables visible to the current stack should be used for replacement scans.",
 	                          LogicalType::BOOLEAN, Value::BOOLEAN(true));
+	config.AddExtensionOption(
+	    "python_scan_all_frames",
+	    "If set, restores the old behavior of scanning all preceding frames to locate the referenced variable.",
+	    LogicalType::BOOLEAN, Value::BOOLEAN(false));
 	if (!DuckDBPyConnection::IsJupyter()) {
 		config_dict["duckdb_api"] = Value("python");
 	} else {
diff --git a/tools/pythonpkg/src/python_replacement_scan.cpp b/tools/pythonpkg/src/python_replacement_scan.cpp
index bc52bd4f4809..8d1610534789 100644
--- a/tools/pythonpkg/src/python_replacement_scan.cpp
+++ b/tools/pythonpkg/src/python_replacement_scan.cpp
@@ -194,25 +194,38 @@ static unique_ptr<TableRef> ReplaceInternal(ClientContext &context, const string
 		return nullptr;
 	}
 
+	lookup_result = context.TryGetCurrentSetting("python_scan_all_frames", result);
+	D_ASSERT((bool)lookup_result);
+	auto scan_all_frames = result.GetValue<bool>();
+
 	py::gil_scoped_acquire acquire;
-	auto current_frame = py::module::import("inspect").attr("currentframe")();
-
-	auto local_dict = py::cast<py::dict>(current_frame.attr("f_locals"));
-	// search local dictionary
-	if (local_dict) {
-		auto result = TryReplacement(local_dict, table_name, context, current_frame);
-		if (result) {
-			return result;
+	py::object current_frame = py::module::import("inspect").attr("currentframe")();
+
+	bool has_locals = false;
+	bool has_globals = false;
+	do {
+		py::object local_dict_p = current_frame.attr("f_locals");
+		has_locals = !py::none().is(local_dict_p);
+		if (has_locals) {
+			// search local dictionary
+			auto local_dict = py::cast<py::dict>(local_dict_p);
+			auto result = TryReplacement(local_dict, table_name, context, current_frame);
+			if (result) {
+				return result;
+			}
 		}
-	}
-	// search global dictionary
-	auto global_dict = py::cast<py::dict>(current_frame.attr("f_globals"));
-	if (global_dict) {
-		auto result = TryReplacement(global_dict, table_name, context, current_frame);
-		if (result) {
-			return result;
+		py::object global_dict_p = current_frame.attr("f_globals");
+		has_globals = !py::none().is(global_dict_p);
+		if (has_globals) {
+			auto global_dict = py::cast<py::dict>(global_dict_p);
+			// search global dictionary
+			auto result = TryReplacement(global_dict, table_name, context, current_frame);
+			if (result) {
+				return result;
+			}
 		}
-	}
+		current_frame = current_frame.attr("f_back");
+	} while (scan_all_frames && (has_locals || has_globals));
 	return nullptr;
 }
 


Test patch:
diff --git a/tools/pythonpkg/tests/fast/test_replacement_scan.py b/tools/pythonpkg/tests/fast/test_replacement_scan.py
index 6a837b363e90..5255c8cac902 100644
--- a/tools/pythonpkg/tests/fast/test_replacement_scan.py
+++ b/tools/pythonpkg/tests/fast/test_replacement_scan.py
@@ -121,10 +121,6 @@ def inner_func(duckdb_cursor):
                 # We set the depth to look for local variables to 0 so it's never found
                 duckdb_cursor.sql("select * from df")
             duckdb_cursor.execute("set python_enable_replacements=true")
-            with pytest.raises(duckdb.CatalogException, match='Table with name df does not exist'):
-                # We set the depth to look for local variables to 1 so it's still not found because it wasn't defined in this function
-                duckdb_cursor.sql("select * from df")
-            duckdb_cursor.execute("set python_enable_replacements=true")
             with pytest.raises(duckdb.CatalogException, match='Table with name df does not exist'):
                 # Here it's still not found, because it's not visible to this frame
                 duckdb_cursor.sql("select * from df")
@@ -138,6 +134,23 @@ def inner_func(duckdb_cursor):
 
         inner_func(duckdb_cursor)
 
+    def test_scan_local_unlimited(self, duckdb_cursor):
+        df = pd.DataFrame({'a': [1, 2, 3]})
+
+        def inner_func(duckdb_cursor):
+            duckdb_cursor.execute("set python_enable_replacements=true")
+            with pytest.raises(duckdb.CatalogException, match='Table with name df does not exist'):
+                # We set the depth to look for local variables to 1 so it's still not found because it wasn't defined in this function
+                duckdb_cursor.sql("select * from df")
+            duckdb_cursor.execute("set python_scan_all_frames=true")
+            # Now we can find 'df' because we also scan the previous frame(s)
+            rel = duckdb_cursor.sql("select * from df")
+
+            res = rel.fetchall()
+            assert res == [(1,), (2,), (3,)]
+
+        inner_func(duckdb_cursor)
+
     def test_replacement_scan_relapi(self):
         con = duckdb.connect()
         pyrel1 = con.query('from (values (42), (84), (120)) t(i)')