bug: unbound table error even after setting default_backend

[nextchamp-saqib]

What happened?

Executing a query with ibis.<backend>.execute() throws an error even if default_backend is set

Code to replicate the issue:

import ibis
from ibis import _
import pandas as pd

con = ibis.duckdb.connect()
data = {
    "id": ["A", "A", "B", "B", "C", "C"],
    "date": ["2021-01-01", "2021-01-02", "2021-01-01", "2021-01-02", "2021-01-01", "2021-01-02"],
    "product": ["apple", "banana", "apple", "banana", "apple", "banana"],
    "sales": [10, 20, 30, 40, 50, 60],
}
con.create_table("test", pd.DataFrame(data))

schema = {
    "id": "string",
    "date": "string",
    "product": "string",
    "sales": "int64",
}

t = ibis.table(name="test", schema=schema)
ibis.set_backend(con) # <------------------ default backend set
q = (
    t.select(t)
    .aggregate(total_sales=_.sales.sum(), by=[_.date, _.product])
    .pivot_wider(
        id_cols=["date"],
        names_from="product",
        values_from=["total_sales"],
        values_agg="sum",
    )
)

What version of ibis are you using?

8.0.0

What backend(s) are you using, if any?

DuckDB

Relevant log output

{
	"name": "IbisError",
	"message": "Expression contains unbound tables and therefore cannot be executed. Use ibis.<backend>.execute(expr) or assign a backend instance to `ibis.options.default_backend`.",
	"stack": "---------------------------------------------------------------------------
IbisError                                 Traceback (most recent call last)
Cell In[33], line 24
     21 t = ibis.table(name=\"test\", schema=schema)
     22 ibis.set_backend(con)
     23 q = (
---> 24     t.select(t)
     25     .aggregate(total_sales=_.sales.sum(), by=[_.date, _.product])
     26     .pivot_wider(
     27         id_cols=[\"date\"],
     28         names_from=\"product\",
     29         values_from=[\"total_sales\"],
     30         values_agg=\"sum\",
     31     )
     32 )
     34 names = q.select(\"product\").distinct()
     35 names = con.execute(names)[\"product\"].tolist()

File ~/frappe/bench-v13/env/lib/python3.10/site-packages/ibis/expr/types/relations.py:4053, in Table.pivot_wider(self, id_cols, names_from, names_prefix, names_sep, names_sort, names, values_from, values_fill, values_agg)
   4049     values_agg = values_agg.resolve
   4051 if names is None:
   4052     # no names provided, compute them from the data
-> 4053     names = self.select(names_from).distinct().execute()
   4054 else:
   4055     if not (columns := [col.get_name() for col in names_from.expand(self)]):

File ~/frappe/bench-v13/env/lib/python3.10/site-packages/ibis/expr/types/core.py:324, in Expr.execute(self, limit, timecontext, params, **kwargs)
    297 def execute(
    298     self,
    299     limit: int | str | None = \"default\",
   (...)
    302     **kwargs: Any,
    303 ):
    304     \"\"\"Execute an expression against its backend if one exists.
    305 
    306     Parameters
   (...)
    322         Keyword arguments
    323     \"\"\"
--> 324     return self._find_backend(use_default=True).execute(
    325         self, limit=limit, timecontext=timecontext, params=params, **kwargs
    326     )

File ~/frappe/bench-v13/env/lib/python3.10/site-packages/ibis/expr/types/core.py:279, in Expr._find_backend(self, use_default)
    277 if not backends:
    278     if has_unbound:
--> 279         raise IbisError(
    280             \"Expression contains unbound tables and therefore cannot \"
    281             \"be executed. Use ibis.<backend>.execute(expr) or \"
    282             \"assign a backend instance to \"
    283             \"`ibis.options.default_backend`.\"
    284         )
    285     default = _default_backend() if use_default else None
    286     if default is None:

IbisError: Expression contains unbound tables and therefore cannot be executed. Use ibis.<backend>.execute(expr) or assign a backend instance to `ibis.options.default_backend`."
}

Code of Conduct

• I agree to follow this project's Code of Conduct

[jcrist]

Thanks for the excellent reproducible example - this is indeed a bug. For now you should be able to change your code to get a bound table instead of an unbound table doing either:

...
t = con.create_table("test", pd.DataFrame(data))

# OR

t = con.table("test")  # retrieve the table `test` later on
...

[cpcloud]

I'm not sure this is a bug.

An UnboundTable doesn't magically turn into a bound table when you set the default backend.

Do we really want to start trying to turn all unbound tables into bound tables simply because the default backend was set?

[jcrist]

Yeah, I realized that after commenting. I think the only thing to do here is update the error message to not provide incorrect advice (done in #8695).