You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by GitBox <gi...@apache.org> on 2021/11/06 12:25:04 UTC
[GitHub] [arrow-datafusion] xudong963 opened a new issue #1082: Implement the rest of Set Operators: INTERSECT, EXCEPT, etc
xudong963 opened a new issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082
**Is your feature request related to a problem or challenge? Please describe what you are trying to do.**
- [x] INTERSECT
- [x] INTERSECT ALL
- [ ] EXCEPT
- [ ] EXCEPT ALL
**Describe the solution you'd like**
A clear and concise description of what you want to happen.
**Describe alternatives you've considered**
A clear and concise description of any alternative solutions or features you've considered.
**Additional context**
Add any other context or screenshots about the feature request here.
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 commented on issue #1082: Implement the rest of Set Operators: INTERSECT, EXCEPT, etc
Posted by GitBox <gi...@apache.org>.
xudong963 commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-961937443
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 commented on issue #1082: Implement the rest of Set Operators
Posted by GitBox <gi...@apache.org>.
xudong963 commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-941940893
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 commented on issue #1082: Implement the rest of Set Operators: INTERSECT, EXCEPT, etc
Posted by GitBox <gi...@apache.org>.
xudong963 commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-962469260
All related PRs have finished, after merging, the issue can be closed. Thanks for your help! @alamb @Dandandan @houqp
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 commented on issue #1082: Implement the rest of Set Operators: INTERSECT, EXCEPT, etc
Posted by GitBox <gi...@apache.org>.
xudong963 commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-961937443
After https://github.com/apache/arrow-datafusion/pull/1135 merged, I believe the issue will be closed quickly, because on the basis of https://github.com/apache/arrow-datafusion/pull/1135, `EXCEPT` can be implemented by `ANTI JOIN` with `null_equal_null`.
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Dandandan commented on issue #1082: Implement the rest of Set Operators
Posted by GitBox <gi...@apache.org>.
Dandandan commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-942674160
> psql doesn't seem to have direct `SEMI JOIN`. It uses `IN` to do the same thing as `SEMI JOIN`. So how to use `is distinct from` in `select distinct a from foo where a in (select a from bar);`?
We also don't have the semi / anti join SQL syntax, but we support semi and anti join in a `LogicalPlan` and support it in the hash join nodes / implementation. We don't need it at the syntax level to support operators like `INTERSECT`, as long as we can map the input query to a logical plan / physical plan.
I didn't have a look at using select distinct + semi join for `INTERSECT` but if it's equivalent that totally makes sense to me.
Complication with using things like `is distinct from` will not supported by a equi-join implementation (which only supports `A=B` type of expressions, so I'm not sure if this is an efficient / easy way to implement it.
I think supporting the `is distinct from` operator is useful in itself.
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Dandandan commented on issue #1082: Implement the rest of Set Operators
Posted by GitBox <gi...@apache.org>.
Dandandan commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-942696160
It looks like PostgreSQL has some explicit operator (`HashSetOp`) for these kind of things:
```sql
> explain select * from demo intersect select * from demo;
HashSetOp Intersect (cost=0.00..30.40 rows=160 width=458)
-> Append (cost=0.00..28.00 rows=320 width=458)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..13.20 rows=160 width=458)
-> Seq Scan on demo (cost=0.00..11.60 rows=160 width=454)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..13.20 rows=160 width=458)
-> Seq Scan on demo demo_1 (cost=0.00..11.60 rows=160 width=454)
```
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 edited a comment on issue #1082: Implement the rest of Set Operators
Posted by GitBox <gi...@apache.org>.
xudong963 edited a comment on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-942442780
> perhaps since `NULL = NULL` is not true, then semi join with equality isn't correct. 🤔
Yes.
```sql
postgres=# select * from bar;
a | b
---+---
1 | 2
| 3
3 | 4
(3 rows)
postgres=# select * from foo;
a | b
---+---
1 | 2
| 3
(2 rows)
-- intersect
select a from foo intersect select a from bar;
/**
a
---
1
(2 rows)
**/
-- equivalent transformation ? (x) since NULL = NULL is not true
select distinct a from foo where a in (select a from bar);
/**
a
---
1
(1 row)
**/
```
psql doesn't seem to have direct `SEMI JOIN`. It uses `IN` to do the same thing as `SEMI JOIN`. So how to use `is distinct from` in `select distinct a from foo where a in (select a from bar);`?
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 commented on issue #1082: Implement the rest of Set Operators
Posted by GitBox <gi...@apache.org>.
xudong963 commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-942536894
I'm still surveying set operators in cockroachdb.
I won't rush to write code until I figure out the best and most detailed solution.
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #1082: Implement the rest of Set Operators
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-942288321
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 commented on issue #1082: Implement the rest Set Operators: INTERSECT & MINUS
Posted by GitBox <gi...@apache.org>.
xudong963 commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-938268140
Please assign me, thanks! @houqp
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 commented on issue #1082: Implement the rest of Set Operators
Posted by GitBox <gi...@apache.org>.
xudong963 commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-941940893
First of all, I'll implement `INTERSECT`
My direct idea is to rewrite it to `left-semi Join`
such as
```sql
SELECT a1 FROM t1 INTERSECT SELECT a2 FROM t2;
to
SELECT DISTINCT a1 FROM t1 LEFT SEMI JOIN t2 ON a1 is distinct from a2
```
How do you think about it? @houqp @alamb @Dandandan
If it's ok, I think I should implement `is distinct from` firstly. https://www.postgresql.org/docs/14/functions-comparison.html
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb closed issue #1082: Implement the rest of Set Operators: INTERSECT, EXCEPT, etc
Posted by GitBox <gi...@apache.org>.
alamb closed issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 edited a comment on issue #1082: Implement the rest of Set Operators
Posted by GitBox <gi...@apache.org>.
xudong963 edited a comment on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-941940893
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #1082: Implement the rest of Set Operators: INTERSECT, EXCEPT, etc
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-962444079
Reopening as github API got a little too excited
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Dandandan edited a comment on issue #1082: Implement the rest of Set Operators
Posted by GitBox <gi...@apache.org>.
Dandandan edited a comment on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-942674160
> psql doesn't seem to have direct `SEMI JOIN`. It uses `IN` to do the same thing as `SEMI JOIN`. So how to use `is distinct from` in `select distinct a from foo where a in (select a from bar);`?
We also don't have the semi / anti join SQL syntax, but we support semi and anti join in a `LogicalPlan` and support it in the hash join nodes / implementation. We don't need it at the syntax level to support operators like `INTERSECT`, as long as we can map the input query to a logical plan / physical plan.
I didn't have a look at using select distinct + semi join for `INTERSECT` but if it's equivalent that totally makes sense to me.
Complication with using things like `is distinct from` will not supported by a equi-join implementation (which only supports `A=B` type of expressions), so I'm not sure if this is an efficient / easy way to implement it.
I think supporting the `is distinct from` operator is useful in itself.
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 edited a comment on issue #1082: Implement the rest of Set Operators
Posted by GitBox <gi...@apache.org>.
xudong963 edited a comment on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-942442780
> perhaps since `NULL = NULL` is not true, then semi join with equality isn't correct. 🤔
Yes.
```sql
postgres=# select * from bar;
a | b
---+---
1 | 2
| 3
3 | 4
(3 rows)
postgres=# select * from foo;
a | b
---+---
1 | 2
| 3
(2 rows)
-- intersect
select a from foo intersect select a from bar;
/**
a
---
1
(2 rows)
**/
-- equivalent transformation ? (x) since NULL = NULL is not true
select distinct a from foo where a in (select a from bar);
/**
a
---
1
(1 row)
**/
```
psql doesn't seem to have direct `SEMI JOIN`. It uses `IN` to do the same thing as `SEMI JOIN`. So how to use `is distinct from` in `select distinct a from foo where a in (select a from bar);`?
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 edited a comment on issue #1082: Implement the rest of Set Operators: INTERSECT, EXCEPT, etc
Posted by GitBox <gi...@apache.org>.
xudong963 edited a comment on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-962469260
All related PRs have finished, after merging, the issue can be closed. Thanks again for your help! @alamb @Dandandan @houqp
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 commented on issue #1082: Implement the rest of Set Operators: INTERSECT, EXCEPT, etc
Posted by GitBox <gi...@apache.org>.
xudong963 commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-962438419
The issue needs to reopen, there are other things to do, such as `Expect` support. @alamb
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #1082: Implement the rest of Set Operators
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-942500766
Creating / using `is distinct from` makes sense to me 👍 ). Good thinking @xudong963
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 commented on issue #1082: Implement the rest of Set Operators
Posted by GitBox <gi...@apache.org>.
xudong963 commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-943386326
> It looks like PostgreSQL has some explicit operator (`HashSetOp`) for these kind of things:
Yes, I have seen the pg code which uses the `HashSetOp` for `INTERSECT` and `EXCEPT`
```
* In SETOP_HASHED mode, the input is delivered in no particular order,
* except that we know all the tuples from one input relation will come before
* all the tuples of the other. The planner guarantees that the first input
* relation is the left-hand one for EXCEPT, and tries to make the smaller
* input relation come first for INTERSECT. We build a hash table in memory
* with one entry for each group of identical tuples, and count the number of
* tuples in the group from each relation. After seeing all the input, we
* scan the hashtable and generate the correct output using those counts.
* We can avoid making hashtable entries for any tuples appearing only in the
* second input relation, since they cannot result in any output.
*
* This node type is not used for UNION or UNION ALL, since those can be
* implemented more cheaply (there's no need for the junk attribute to
* identify the source relation).
```
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 edited a comment on issue #1082: Implement the rest Set Operators: INTERSECT & MINUS
Posted by GitBox <gi...@apache.org>.
xudong963 edited a comment on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-938268140
Please assign it to me, thanks! @houqp
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb closed issue #1082: Implement the rest of Set Operators: INTERSECT, EXCEPT, etc
Posted by GitBox <gi...@apache.org>.
alamb closed issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #1082: Implement the rest of Set Operators
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-942686726
I remember other systems that basically had special equality checking in semi joins to handle the null stuff -- in fact when using semi join for `x IN (select ..)` type queries, the `is distinct from` operator might required to get correct results in the presence of nulls, but I can't remember
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] xudong963 commented on issue #1082: Implement the rest of Set Operators
Posted by GitBox <gi...@apache.org>.
xudong963 commented on issue #1082:
URL: https://github.com/apache/arrow-datafusion/issues/1082#issuecomment-942442780
> perhaps since `NULL = NULL` is not true, then semi join with equality isn't correct. 🤔
Yes.
```sql
postgres=# select * from bar;
a | b
---+---
1 | 2
| 3
3 | 4
(3 rows)
postgres=# select * from foo;
a | b
---+---
1 | 2
| 3
(2 rows)
-- intersect
select a from foo intersect select a from bar;
/**
a
---
1
(2 rows)
**/
-- equivalent transformation ? (x) since NULL = NULL is not true
select distinct a from foo where a in (select a from bar);
/**
a
---
1
(1 row)
**/
```
psql doesn't seem to have direct `SEMI JOIN`. It uses `IN` to do the same thing as `SEMI JOIN`. So how to use `is distinct from` in `select distinct a from foo where a in (select a from bar);`
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org