Join behavior inconsistency with duckdb 2.0

Aberta
  • Algo errado?
#733 2 comentários 0 reações 0 responsáveis Ver no GitHub

Mantenedores costumam responder em até 1 dia

Ninguém assumiu esta issue ainda.

Avaliação

Dificuldade
3/5
Tempo estimado
1-2 dias
Facilidade para iniciantes
68/100
Tipo de issue
Bug
Clareza
Claramente especificada
Status de atividade
Ativa
Stack de tecnologia
pandas, python
Domínio
databases

Direção de pesquisa

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.

Escrita pelo modelo de indexação a partir do texto da issue.

Descrição

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
Linguagem predominante
Python
Estrelas
203
Forks
124
Merge médio
13h 36min
PRs com merge (30d)
61

Preparar o ambiente

Primeiros passos

  1. Leia a issue inteira e depois o guia de contribuição do projeto.
  2. Comente na issue dizendo que vai assumir — evita que duas pessoas façam o mesmo trabalho.
  3. Faça um fork do repositório e trabalhe em uma branch.
  4. Abra um pull request que referencie o número da issue.

Mais de duckdb/duckdb-python

Todas as issues de duckdb/duckdb-python

Issues semelhantes

Mais issues de Python

Receba novas issues na sua caixa de entrada

Um resumo curto de issues do GitHub para quem está começando.