Repository navigation
[SQL] Replace IN over a constant UNNEST with an IN list - #7936
mihaibudiu wants to merge 1 commit into
Conversation
| 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(), |
There was a problem hiding this comment.
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 areCHAR(1), so the join stays. This happens even when every element has the same length.x BIGINT IN (... ARRAY[1, 4]): the literals areINTEGER, 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.
|
What I ran:
No correctness bugs found. The three-valued logic for
|
x IN (SELECT * FROM UNNEST(ARRAY[c1, ..., cn])) is replaced with x IN (c1, ..., cn). Signed-off-by: Mihai Budiu <mbudiu@feldera.com>
c19a23f to
1ff5a6c
Compare
x IN (SELECT * FROM UNNEST(ARRAY[c1, ..., cn]))is replaced withx IN (c1, ..., cn).This pattern was seen in a customer program. This saves an extra join.