alamb commented on pull request #1339:
URL: https://github.com/apache/arrow-datafusion/pull/1339#issuecomment-982930856
> I guess PostgreSQL does some other special processing that isn't included
in the plan. So interesting ticket, I think I should take a look at Postgres.
> Any thoughts? @alamb
❯ explain SELECT * FROM table1 LEFT JOIN table2 ON table1.column1 =
table2.column1 WHERE table2.column1 IS NOT NULL;
Since the predicate is on `table2` and table 2 is not preserved, I don't
think predicates can be pushed down in this case (only predicates on table1).
Perhaps something more like (swapped `pushable_to_right` and
`pushable_to_left`):
```rust
match join_type.unwrap() {
JoinType::Left => pushable_to_right.clear(),
JoinType::Right => pushable_to_left.clear(),
JoinType::Full => {
pushable_to_left.clear();
pushable_to_right.clear()
}
```
Also, I am not sure if you have handled / encountered this, but there is a
(very subtle) semantic difference between
```sql
SELECT * FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column1
WHERE table2.column1 IS NOT NULL;
```
```sql
SELECT * FROM table1 LEFT JOIN table2 ON (table1.column1 = table2.column1
AND table2.column1 IS NOT NULL);
```
Semantically, in SQL, a filter in the `WHERE` clause (first query) happens
*AFTER* all joins, while a filter in the `ON` clause (second query) happens
*during* the join (so if nothing matches a predicate during an outer join for a
particular row on the preserved side, a row is still produced)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
