How to use SqlStdOperatorTable.IN with RelBuilder?

Question

I'm trying to build a rexNode similar to <SELECT * FROM foo WHERE x IN ("bar1", "bar2")> using RelBuilder. But I can't really find any example or any test using SqlStdOperatorTable.IN. Could please somebody share any? Or explain how to use SqlStdOperatorTable.IN.

Saw something here, but it didn't help much and didn't work for me(

    RelBuilder builder = RelBuilder.create(calciteConfig);
    RelRunner relRunner = calciteConnection.unwrap(RelRunner.class);

    builder.scan("dremio", "test1");
    RexBuilder rexBuilder = new RexBuilder(new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT));
    RexNode rexNode = rexBuilder.makeCall(
            SqlStdOperatorTable.IN,
            builder.field("device_type"),
            rexBuilder.makeLiteral("TABLET"),
            rexBuilder.makeLiteral("MOBILE"));
    RelNode test1 = builder
            .filter(rexNode)
            .limit(10, 100)
            .build();

score 1 · Accepted Answer · answered Jan 28 '22 at 13:17

You have to use RelBuilder#in or RexBuilder#makeIn APIs. Here is the test case:

  @Test void testIn() {
    final RelBuilder builder = RelBuilder.create(config().build());
    builder.scan("EMP");

    final RexNode literal1 = builder.literal("foo");
    final RexNode literal2 = builder.literal("bar");

    RexNode inRex = builder.in(builder.field("ENAME"), ImmutableList.of(literal1, literal2));
    RelNode root = builder.filter(inRex)
            .build();

    final String expected = "" +
        "LogicalFilter(condition=[SEARCH($1, Sarg['bar', 'foo']:CHAR(3))])\n" +
        "  LogicalTableScan(table=[[scott, EMP]])\n";
  }

If you want to get more context why a check is present in the constructor of RexCall() which is assert operator.kind != SqlKind.IN please refer to this CALCITE-4173

Cool, missed this API... Thank you a lot! Now it works fine for me. — kalmar, Jan 28 '22 at 17:46
This answer is correct, but I will note that if you want to write `IN (SELECT ... )`, you need to use the other `RelBuilder.in` method that was added in https://issues.apache.org/jira/browse/CALCITE-3524. See the [test case](https://github.com/apache/calcite/blob/bf56743554ea27d250d41db2eb83806f9e626b55/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java#L2442). — Julian Hyde, Feb 10 '22 at 20:41

How to use SqlStdOperatorTable.IN with RelBuilder?

1 Answers1