Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support Null-aware Anti Join #37525

Closed
fixdb opened this issue Aug 31, 2022 · 0 comments · Fixed by #37512
Closed

Support Null-aware Anti Join #37525

fixdb opened this issue Aug 31, 2022 · 0 comments · Fixed by #37512
Labels
type/feature-request Categorizes issue or PR as related to a new feature.

Comments

@fixdb
Copy link
Contributor

fixdb commented Aug 31, 2022

Feature Request

create table foo(a int, b int, c int);
create table bar (a int not null, b int, c int);

mysql> explain select * from foo where a not in (select b from bar);
+-----------------------------+----------+-----------+---------------+-----------------------------------------------------------------+
| id                          | estRows  | task      | access object | operator info                                                   |
+-----------------------------+----------+-----------+---------------+-----------------------------------------------------------------+
| HashJoin_8                  | 8000.00  | root      |               | CARTESIAN anti semi join, other cond:eq(test.foo.a, test.bar.b) |
| ├─TableReader_12(Build)     | 10000.00 | root      |               | data:TableFullScan_11                                           |
| │ └─TableFullScan_11        | 10000.00 | cop[tikv] | table:bar     | keep order:false, stats:pseudo                                  |
| └─TableReader_10(Probe)     | 10000.00 | root      |               | data:TableFullScan_9                                            |
|   └─TableFullScan_9         | 10000.00 | cop[tikv] | table:foo     | keep order:false, stats:pseudo                                  |
+-----------------------------+----------+-----------+---------------+-----------------------------------------------------------------+
5 rows in set (0.00 sec)

Currently planner always generate CARTESIAN anti semi join, which has bad performance, because we don't support NAAJ (Null-aware Anti Join) yet.
We should support NAAJ to gain better performance.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
type/feature-request Categorizes issue or PR as related to a new feature.
Projects
None yet
1 participant