Repository navigation
Join behavior inconsistency with duckdb 2.0 #733
Description
What happens?
When I join three tables with condition = 'column', I can select from all three joined tables
When I join with a condition table['column'] == table['column'], I can't select from all three tables using their table names
To Reproduce
#!/usr/bin/env python
# coding: utf-8
# In[1]:
import duckdb
import pandas as pd
from duckdb import col
duckdb.__version__
# '2.0.0'
# In[2]:
# Table 1's id1 is a foreign key referenced by both table 2 and table 3
table_1_df = pd.DataFrame({
"id1": [1,2,3,4,5],
"data": [1,2,3,4,5],
})
# Table 2 and table 3 share a data column name, so table references and aliases are needed
table_2_df = pd.DataFrame({
"id2": list("abcde"),
"id1": [1,2,3,4,5],
"data": [1,2,3,4,5],
})
table_3_df = pd.DataFrame({
"id3": list("fghij"),
"id1": [1,2,3,4,5],
"data": [1,2,3,4,5],
})
con = duckdb.connect()
con.register("table_one", table_1_df)
table_1 = con.table("table_one")
con.register("table_two", table_2_df)
table_2 = con.table("table_two")
con.register("table_three", table_3_df)
table_3 = con.table("table_three")
# In[3]:
# When a join USING(id1) query is created, all is well
using_query = (table_1
.join(table_2, condition= 'id1')
.join(table_3, how="left", condition='id1')
)
using_query.select(
col("table_one", "id1"),
col("table_two", "data").alias("data2"),
col("table_three", "data").alias("data3"),
)
# id1
# BIGINT data2
# BIGINT data3
# BIGINT
# 1 1 1
# 2 2 2
# 3 3 3
# 4 4 4
# 5 5 5
# In[4]:
# When a join with an equality expression is used, it does not work
condition_query = (table_1
.join(table_2, condition= lambda left,right: left['id1'] == right['id1'])
.join(table_3, how="left", condition=lambda left,right: left['id1'] == right['id1'])
)
condition_query.select(
col("table_one", "id1"),
col("table_two", "data").alias("data2"),
col("table_three", "data").alias("data3"),
)
# ---------------------------------------------------------------------------
# BinderException Traceback (most recent call last)
# Cell In[4], line 7
# 3 .join(table_2, condition= lambda left,right: left['id1'] == right['id1'])
# 4 .join(table_3, how="left", condition=lambda left,right: left['id1'] == right['id1'])
# 5 )
# 6
# ----> 7 condition_query.select(
# 8 col("table_one", "id1"),
# 9 col("table_two", "data").alias("data2"),
# 10 col("table_three", "data").alias("data3"),
# File ~/develop/jds-sql/.venv/lib/python3.12/site-packages/duckdb/_relation.py:3017, in _closed_as_relation.<locals>.guarded(*args, **kwargs)
# 3015 raise _duckdb.ConnectionException(message) from None
# 3016 except _duckdb.Error as error:
# -> 3017 raise _trimmed(error) from error.__cause__
# BinderException: Binder Error: Referenced table "table_one" not found!
# Candidate tables: "table_three"
OS:
Ubuntu
DuckDB Package Version:
2.0.0
Python Version:
3.12.3
Full Name:
Janet Carson
Affiliation:
Echodyne
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?
- Yes, I have
Did you include all relevant configuration to reproduce the issue?
- Yes, I have
Activity
mohamd764 commentedon Nov 1, 2026
I could reproduce this on 2.0.0. From what I can tell it comes from the lambda condition, not from the number of tables. When
join()gets a function as the condition it "closes" both sides (there's a comment about it aroundsides_closedinduckdb/_relation.py), so the first join result goes into the second join as a single item under its own generated alias. You can see it in the SQL:So
table_oneandtable_twoaren't visible anymore, and the duplicate names get renamed (condition_query.columnsgives['id1', 'data', 'id2', 'id1_1', 'data_1', 'id3', 'id1', 'data']). The two-table lambda join works because nothing is wrapped yet.Workarounds that worked for me on 2.0.0 with your data:
It looks intentional from the code, but it's surprising that the USING form and the lambda form scope names differently, so at least a note in the docs for the function condition would help.
j-carson commentedon Nov 1, 2026
@mohamd764 Thank you so much for your reply! It was absolutely vexing me why some of my complex joins that worked just fine in duckdb 1.5 were not porting to duckdb 2.0. I finally found at least one simple test case and logged it here...
I was just following the examples on the relational API docs which suggest the lambda is the shiny new way to do things
https://duckdb.org/docs/2.0/clients/python/relational_api#join and does not mention any downsides nor the other two methods shown above.
I was basically doing option 2 in the duckdb 1.5 code that I was trying to port over -- so that change would be easy. Option 1 is kind of what I've been aiming for -- trying to reduce the hardcoded sql-strings and using more API's to manage the SQL for me, so I like that one better and will try that. For option 3, it seems to require knowing how duckdb will rename my columns under the covers, which might have a risk of breaking later, so not sure about it right now.