Skip to content

Get table bindings for all operators under a logical get if the logical get function is an unnest #19467

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.

Sign up for GitHub

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

Jump to bottom
Merged
hannes merged 1 commit into from
Oct 24, 2025
Merged

Get table bindings for all operators under a logical get if the logical get function is an unnest #19467

hannes merged 1 commit into from
Oct 24, 2025

Conversation

@Tmonster
Copy link
Contributor

@Tmonster Tmonster commented Oct 22, 2025 •
edited
Loading

The issue is caused by a very specific plan where a logical get (that has an unnest function) is the direct child of a logical comparison join. Normally for logical gets, we only add the one table index to the set of table indexes that can be joined on. However, if the logical get has an unnest function and there are children, the table indexes of those children can also be joined on. The Join Order Optimizer did not consider this case, which also turns out to be extremely rare. Here is the plan before the join order optimizer gets to it.

I tried to write another test case but struggled for about 30 min.

┌───────────────────────────┐
│       PROJECTION #23      │
│    ────────────────────   │
│        Expressions:       │
│     #[0.0] (VARCHAR[])    │
│      #[0.1] (VARCHAR)     │
│      #[0.2] (INTEGER)     │
│      #[0.3] (VARCHAR)     │
│      #[0.4] (VARCHAR)     │
│      #[8.0] (VARCHAR)     │
│       #[17.0] (JSON)      │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         DELIM_JOIN        │
│    ────────────────────   │
│      Join Type: INNER     │
│                           ├──────────────┐
│        Conditions:        │              │
│  (#[29.0] IS NOT DISTINCT │              │
│        FROM #[2.1])       │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│         WINDOW #29        ││      COMPARISON_JOIN      │
│    ────────────────────   ││    ────────────────────   │
│        Expressions:       ││      Join Type: INNER     │
│  ROW_NUMBER() OVER (ROWS  ││                           │
│      BETWEEN UNBOUNDED    ││        Conditions:        │
│ PRECEDING AND CURRENT ROW)││  (#[2.1] IS NOT DISTINCT  ├──────────────┐
│                           ││        FROM #[17.1])      │              │
│                           ││  (#[2.2] IS NOT DISTINCT  │              │
│                           ││        FROM #[17.2])      │              │
│                           ││  (#[2.3] IS NOT DISTINCT  │              │
│                           ││        FROM #[17.3])      │              │
└─────────────┬─────────────┘└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│        SEQ_SCAN #0        ││         UNNEST #8         ││       PROJECTION #17      │
│    ────────────────────   ││    ────────────────────   ││    ────────────────────   │
│   Table: xxxxxxxxxxxxxx   ││                           ││        Expressions:       │
│   Type: Sequential Scan   ││                           ││       #[16.1] (JSON)      │
│                           ││                           ││      #[10.1] (BIGINT)     │
│                           ││                           ││     #[10.2] (VARCHAR)     │
│                           ││                           ││    #[10.3] (VARCHAR[])    │
└───────────────────────────┘└─────────────┬─────────────┘└─────────────┬─────────────┘
                             ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
                             │       PROJECTION #2       ││       JSON_EACH #16       │
                             │    ────────────────────   ││    ────────────────────   │
                             │        Expressions:       ││                           │
                             │    #[30.2] (VARCHAR[])    ││                           │
                             │      #[30.0] (BIGINT)     ││                           │
                             │     #[30.1] (VARCHAR)     ││                           │
                             │    #[30.2] (VARCHAR[])    ││                           │
                             └─────────────┬─────────────┘└─────────────┬─────────────┘
                             ┌─────────────┴─────────────┐┌─────────────┴─────────────┐
                             │       DELIM_GET #30       ││       PROJECTION #10      │
                             │    ────────────────────   ││    ────────────────────   │
                             │                           ││        Expressions:       │
                             │                           ││     #[31.1] (VARCHAR)     │
                             │                           ││      #[31.0] (BIGINT)     │
                             │                           ││     #[31.1] (VARCHAR)     │
                             │                           ││    #[31.2] (VARCHAR[])    │
                             └───────────────────────────┘└─────────────┬─────────────┘
                                                          ┌─────────────┴─────────────┐
                                                          │       DELIM_GET #31       │
                                                          │    ────────────────────   │
                                                          └───────────────────────────┘

@Tmonster Tmonster force-pushed the md_405_fix_nullptr_deref branch from 41c28e5 to d430625 Compare October 22, 2025 12:57
@duckdb-draftbot duckdb-draftbot marked this pull request as draft October 22, 2025 12:57
@Tmonster Tmonster marked this pull request as ready for review October 22, 2025 12:58
@hannes hannes merged commit 4248409 into duckdb:v1.4-andium Oct 24, 2025
86 checks passed
@hannes
Copy link
Member

hannes commented Oct 24, 2025

thanks!

github-actions bot pushed a commit to duckdb/duckdb-r that referenced this pull request Oct 24, 2025
@krlmlr
vendor: Update vendored sources to duckdb/duckdb@4248409
834cfda 
Get table bindings for all operators under a logical get if the logical get function is an unnest (duckdb/duckdb#19467)
@github-actions github-actions bot mentioned this pull request Oct 24, 2025
Merged
github-actions bot added a commit to duckdb/duckdb-r that referenced this pull request Oct 24, 2025
@github-actions @krlmlr
vendor: Update vendored sources to duckdb/duckdb@4248409 (#1639)
be55200 
Get table bindings for all operators under a logical get if the logical get function is an unnest (duckdb/duckdb#19467)

Co-authored-by: krlmlr <krlmlr@users.noreply.github.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

2 participants

@Tmonster @hannes