Join behavior inconsistency with duckdb 2.0

オープン
  • 何か問題がありますか？
#733 コメント 2 件 リアクション 0 件 担当者 0 名 GitHub で見る

メンテナーはふだん 1 日以内に返信

まだ誰も着手していません。

評価

難易度
3/5
見積もり時間
1〜2日
初心者へのやさしさ
68/100
issue の種類
バグ
明瞭さ
明確に書かれている
活発さ
活発
技術スタック
pandas, python
領域
databases

調査の方向性

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.

索引モデルが issue の本文から書いたものです。

説明

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
主要言語
Python
スター
203
フォーク
124
平均マージ
13時間 36分
マージ済み PR（30日）
61

環境構築

はじめの一歩

  1. issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
  2. 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
  3. リポジトリをフォークし、ブランチを切って変更します。
  4. issue 番号を参照したプルリクエストを送ります。

duckdb/duckdb-python のほかの issue

duckdb/duckdb-python の issue をすべて見る

似ている issue

Python の issue をもっと見る

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。