GV

GeezerVison

The World Wide Web, restored to 800 pixels of glory.

Home|About|Gaming cabinet|Archives|How it works|Starter sites

On this site

  • Sign in
  • ActionsCI/CD and automation
  • CodespacesCloud development environments
  • All Features
  • Documentation
  • Training
  • Certifications
  • Build with Copilot
  • Customize Copilot
  • Integrations
  • Trending repos
  • GitHub Sponsors
  • Developer articles
  • Enterprises

This view

  • Original page
  • New address

GeezerVison is showing github.com — [SQL] Replace IN over a constant UNNEST with an IN list by mihaibudiu · Pull Request #7936 · feldera/feldera

Conversation

@mihaibudiu

@mihaibudiu mihaibudiu commented Nov 11, 2026

Copy link
Copy Markdown
Contributor

x IN (SELECT * FROM UNNEST(ARRAY[c1, ..., cn])) is replaced with x IN (c1, ..., cn).

  • Unit tests added/updated

This pattern was seen in a customer program. This saves an extra join.

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.
All reactions
mythical-fred-oss[bot]
mythical-fred-oss Bot reviewed Nov 11, 2026
View reviewed changes
Comment thread ...a/org/dbsp/sqlCompiler/compiler/frontend/calciteCompiler/optimizer/InUnnestToInListRule.java Outdated
RexNode operand = subQuery.getOperands().get(0).accept(this);
// The IN list compares without casts, so the types must agree
for (RexLiteral literal : literals) {
if (!SqlTypeUtil.equalSansNullability(this.builder.getTypeFactory(),

@mythical-fred-oss mythical-fred-oss Bot Nov 11, 2026

Copy link
Copy Markdown
Contributor

There was a problem hiding this comment.

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

pitfall (medium): requiring exact type equality means the rule does not fire for some very common shapes. I checked by counting joins:

  • x VARCHAR IN (SELECT * FROM UNNEST(ARRAY['a','b'])): the literals are CHAR(1), so the join stays. This happens even when every element has the same length.
  • x BIGINT IN (... ARRAY[1, 4]): the literals are INTEGER, so the join stays.

String ID/status lists are probably the most common form of this pattern. When the conversion is lossless (CHAR→VARCHAR, integer widening), could we coerce the literals to operand.getType() (e.g. builder.makeLiteral(literal.getValue(), operand.getType(), false), or let makeIn wrap a cast)? Please also add a VARCHAR test, so a test catches it if the rule silently stops firing.

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.
All reactions
@mythical-fred-oss

mythical-fred-oss Bot commented Nov 11, 2026

Copy link
Copy Markdown
Contributor

What I ran: ./build.sh, then mvn test -pl SQL-compiler -Dtest='InUnnest*'. The PR's 3 tests pass. I also wrote 9 temporary QA tests in a subclass that counts joins and checks results (not pushed):

Case Rewritten (no join)? Result
Single element ARRAY[4] (becomes =) yes correct
NOT IN in WHERE, duplicate elements [1,1,2] yes correct, NULL row dropped
DOUBLE literals yes correct
Aliased SELECT a FROM UNNEST(..) AS u(a) yes —
IN inside a JOIN ... ON condition yes (only the real join is left) correct
BIGINT column vs INT literals no correct
VARCHAR column vs string literals no correct

No correctness bugs found. The three-valued logic for NOT IN matches, and NULL elements and non-literal elements are rejected.

  • missing (medium): the tests do not cover the single-element = path, NOT IN in a WHERE filter, IN in a join condition, or a type the rule skips (VARCHAR/BIGINT). Please add at least the single-element case and a string case (see inline).
  • Gates: tests ✅, no flakiness, no docs needed (internal optimization), commit message ✅.
All reactions

Sorry, something went wrong.

Uh oh!

There was an error while loading. Please reload this page.
@mihaibudiu
[SQL] Replace IN over a constant UNNEST with an IN list
1ff5a6c 
x IN (SELECT * FROM UNNEST(ARRAY[c1, ..., cn])) is replaced
with x IN (c1, ..., cn).

Signed-off-by: Mihai Budiu <[email protected]>
@mihaibudiu
mihaibudiu force-pushed the unnest-in-list branch from c19a23f to 1ff5a6c Compare November 11, 2026 08:21

This branch has not been deployed

No deployments
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@mythical-fred-oss mythical-fred-oss[bot] mythical-fred-oss[bot] left review comments

At least 1 approving review is required to merge this pull request.

Uh oh!

There was an error while loading. Please reload this page.

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

Uh oh!

There was an error while loading. Please reload this page.

1 participant

@mihaibudiu
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
You are visitor 01402503 — GeezerVison does not store the pages you look up.
Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.