blaze / blaze/blaze

`by` ignores the join condition

Open
#1,673 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Python
Stars
3.2k
Forks
389
PR merge metrics
No merged PRs in 30d

Description

When a column from a joined table is used in `by`, it is ignoring the join condition.

For example, take the orders table from the blaze docs:

```
create table products (
id integer primary key,
name text
);
create table orders (
id integer primary key,
product_id integer references products(id),
quantity integer
);
```

Using that database as source:

```
>>> from blaze import data, by, merge, compute
>>> url = "postgresql:///testdb"
>>> d = data(url)
```

Using a joined column correctly uses the join condition:

```
>>> print(compute(d.orders.product_id.name))
SELECT products.name
FROM products, orders
WHERE orders.product_id = products.id
```

But using the same in `by` ignore the join condition completely:

```
>>> print(compute(by(d.orders.product_id.name, count=d.orders.id.count())))
SELECT products.name, count(orders.id) AS count
FROM products, orders GROUP BY products.name
```

The correct query is:

```
SELECT products.name, count(orders.id) AS count
FROM products, orders
WHERE orders.product_id = products.id
GROUP BY products.name
```

Contributor guide

No contributing guide indexed for this repository

Research direction

Start by reproducing the issue with the documented products and orders tables, using data, by, and compute against the PostgreSQL URL. Compare the SQL for the direct joined column with the SQL generated by by; done means the grouped query includes the join condition in its WHERE clause.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.