Join behavior inconsistency with duckdb 2.0

Abierto
  • ¿Algo no está bien?
#733 2 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 1 día

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
3/5
Tiempo estimado
1-2 días
Aptitud para principiantes
68/100
Tipo de issue
Error
Claridad
Bien especificado
Estado de actividad
Activo
Stack tecnológico
pandas, python
Área
databases

Línea de trabajo

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.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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
Lenguaje dominante
Python
Estrellas
203
Forks
124
Merge medio
13 h 36 min
PR fusionados (30 d)
61

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de duckdb/duckdb-python

Todos los issues de duckdb/duckdb-python

Issues similares

Más issues de Python

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.