`by` ignores the join condition
- 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