← 输入框|github.com|刷新
Skip to content

[SQL] Replace IN over a constant UNNEST with an IN list - #7936

Open
mihaibudiu wants to merge 1 commit into
mainfrom
unnest-in-list
Open

mihaibudiu wants to merge 1 commit into
mainfrom
unnest-in-list

Conversation

@mihaibudiu

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.

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(),

Copy link
Copy Markdown
Contributor

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.

@mythical-fred-oss

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 ✅.

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

Signed-off-by: Mihai Budiu <mbudiu@feldera.com>

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

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant