Join behavior inconsistency with duckdb 2.0

未关闭
  • 哪里不对？
#733 2 条评论 0 个 reaction 已指派 0 人 在 GitHub 查看

维护者通常 1 天内回复

还没有人认领这个 Issue。

评估

难度
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 分钟
30 天内合并 PR
61

环境准备

  • 没有 Dockerfile 或 Docker Compose 文件
  • 没有 Pull Request 模板
  • 阅读贡献指南

从这里开始

  1. 先读完整个 Issue，再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库，在一个分支上完成修改。
  4. 提交 Pull Request，并在描述里引用这个 Issue 编号。

duckdb/duckdb-python 的其他 Issue

查看 duckdb/duckdb-python 的全部 Issue

相似的 Issue

更多 Python Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。