Join behavior inconsistency with duckdb 2.0

Offen
  • Stimmt etwas nicht?
#733 2 Kommentare 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen

Maintainer antworten meist innerhalb von 1 Tag

Dieses Issue hat noch niemand übernommen.

Bewertung

Schwierigkeit
3/5
Geschätzter Aufwand
1-2 Tage
Anfängerfreundlichkeit
68/100
Issue-Typ
Bug
Klarheit
Klar beschrieben
Aktivitätsstatus
Aktiv
Tech-Stack
pandas, python
Bereich
databases

Rechercherichtung

Start with the relation API implementation reached through duckdb/_relation.py and reproduce the two join forms with the provided registered pandas DataFrames. Trace how equality-expression joins alter relation binding or table qualification, and add a regression test showing that table_one, table_two, and table_three remain selectable. The issue is done when the equality-expression query selects from all three tables without a BinderException.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Beschreibung

needs triage
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
Vorherrschende Sprache
Python
Sterne
203
Forks
124
Ø Merge
13 Std. 36 Min.
Gemergte PRs (30 T.)
61

Entwicklungsumgebung

Erste Schritte

  1. Lesen Sie das ganze Issue und danach den Beitragsleitfaden des Projekts.
  2. Schreiben Sie ins Issue, dass Sie es übernehmen — das erspart doppelte Arbeit.
  3. Forken Sie das Repository und arbeiten Sie in einem Branch.
  4. Öffnen Sie einen Pull Request, der die Issue-Nummer nennt.

Mehr aus duckdb/duckdb-python

Alle Issues in duckdb/duckdb-python

Ähnliche Issues

Weitere Issues zu Python

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.