apache-calcite

How to use SqlStdOperatorTable.IN with RelBuilder?


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

Solution

  • 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