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

opt: extra filters add to lookup join ON condition when derive FK equalities #101844

Closed
mgartner opened this issue Apr 19, 2023 · 0 comments · Fixed by #101873
Closed

opt: extra filters add to lookup join ON condition when derive FK equalities #101844

mgartner opened this issue Apr 19, 2023 · 0 comments · Fixed by #101873
Assignees
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-queries SQL Queries Team

Comments

@mgartner
Copy link
Collaborator

mgartner commented Apr 19, 2023

In #90599 and 0cd8b49, we added equality conditions derived from FK constraints and uniqueness constraints. These equality conditions are unnecessarily added to the lookup join ON conditions in some cases. For example:

exec-ddl
CREATE TABLE p (
  r INT,
  id INT,
  PRIMARY KEY (r, id),
  UNIQUE WITHOUT INDEX (id)
)
----

exec-ddl
CREATE TABLE c (
  r INT,
  p_id INT,
  id INT,
  PRIMARY KEY (r, p_id, id),
  UNIQUE INDEX c_p_id_id_key (p_id, id),
  FOREIGN KEY (r, p_id) REFERENCES p (r, id)
)
----

opt
SELECT *
FROM p LEFT LOOKUP JOIN c@c_p_id_id_key
ON c.p_id = p.id AND c.id = 1234
----
left-join (lookup c@c_p_id_id_key)
 ├── columns: r:1(int!null) id:2(int!null) r:5(int) p_id:6(int) id:7(int)
 ├── flags: force lookup join (into right side)
 ├── key columns: [2 10] = [6 7]
 ├── lookup columns are key
 ├── stats: [rows=1000, distinct(6)=9.56179, null(6)=990]
 ├── key: (2)
 ├── fd: (2)-->(1,5-7), (6)-->(5)
 ├── project
 │    ├── columns: "lookup_join_const_col_@7":10(int!null) p.r:1(int!null) p.id:2(int!null)
 │    ├── stats: [rows=1000, distinct(1)=100, null(1)=0, distinct(2)=1000, null(2)=0, distinct(10)=1, null(10)=0]
 │    ├── key: (2)
 │    ├── fd: ()-->(10), (2)-->(1)
 │    ├── scan p
 │    │    ├── columns: p.r:1(int!null) p.id:2(int!null)
 │    │    ├── stats: [rows=1000, distinct(1)=100, null(1)=0, distinct(2)=1000, null(2)=0]
 │    │    ├── key: (2)
 │    │    └── fd: (2)-->(1)
 │    └── projections
 │         └── 1234 [as="lookup_join_const_col_@7":10, type=int]
 └── filters
      └── c.r:5 = p.r:1 [type=bool, outer=(1,5), constraints=(/1: (/NULL - ]; /5: (/NULL - ]), fd=(1)==(5), (5)==(1)]

Notice the synthesized filters at the bottom, c.r:5 = p.r:1, which are unnecessary.

Jira issue: CRDB-27156

@mgartner mgartner added the C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. label Apr 19, 2023
@mgartner mgartner self-assigned this Apr 19, 2023
@blathers-crl blathers-crl bot added the T-sql-queries SQL Queries Team label Apr 19, 2023
craig bot pushed a commit that referenced this issue Apr 20, 2023
101873: opt: do not add derived FK equalities to lookup join ON filters r=mgartner a=mgartner

This commit fixes a minor bug introduced in #90599 which can
unnecessarily add derived FK equality filters to a lookup join's ON
condition. The bug is minor because it does not cause incorrect results.
It only adds a bit of extra work to evaluate the equality. This commit
ensures that the derived FK filters are only used as equality columns in
the lookup join.

Fixes #101844

Release note: None


101908: util/intsets: fix test-only Fast r=mgartner a=mgartner

In #90009, `x/tools/Sparse` was replaced by a new `Sparse` integer set
library. In that commit we forgot to update the test-only implementation
of `Fast` to use the new library correctly. This was not caught earlier
because CI and roachtests are not run with the `fast_int_set_small` and
`fast_int_set_large` build tags.

Epic: None

Release note: None


Co-authored-by: Marcus Gartner <[email protected]>
@craig craig bot closed this as completed in f4ed83e Apr 20, 2023
blathers-crl bot pushed a commit that referenced this issue Apr 20, 2023
This commit fixes a minor bug introduced in #90599 which can
unnecessarily add derived FK equality filters to a lookup join's ON
condition. The bug is minor because it does not cause incorrect results.
It only adds a bit of extra work to evaluate the equality. This commit
ensures that the derived FK filters are only used as equality columns in
the lookup join.

Fixes #101844

Release note: None
@mgartner mgartner moved this to Done in SQL Queries Jul 24, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-queries SQL Queries Team
Projects
Archived in project
Development

Successfully merging a pull request may close this issue.

1 participant