Apache Calcite

// calcite-core 1.42.0 on JDK 21
import org.apache.calcite.adapter.java.ReflectiveSchema;
import org.apache.calcite.config.Lex;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.plan.hep.*;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.rel2sql.RelToSqlConverter;
import org.apache.calcite.rel.rules.CoreRules;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.sql.dialect.PostgresqlSqlDialect;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.tools.*;

public class Demo {
  public static class Emp {  // public fields become columns
    public final int deptno; public final String name; public final int salary;
    Emp(int deptno, String name, int salary) { this.deptno = deptno; this.name = name; this.salary = salary; }
  }
  public static class Dept {
    public final int deptno; public final String name;
    Dept(int deptno, String name) { this.deptno = deptno; this.name = name; }
  }
  public static class Hr {
    public final Emp[] emps = { new Emp(10, "Bill", 10000), new Emp(10, "Theodore", 11500) };
    public final Dept[] depts = { new Dept(10, "Sales") };
  }

  public static void main(String[] args) throws Exception {
    SchemaPlus root = Frameworks.createRootSchema(true);
    root.add("hr", new ReflectiveSchema(new Hr()));  // any Java object can be a schema
    Planner planner = Frameworks.getPlanner(Frameworks.newConfigBuilder()
        .defaultSchema(root).parserConfig(SqlParser.config().withLex(Lex.JAVA)).build());

    // 1. Parse, validate against the schema, convert to relational algebra
    RelNode plan = planner.rel(planner.validate(planner.parse(
        "SELECT d.name, COUNT(*) AS n FROM hr.emps AS e JOIN hr.depts AS d "
      + "ON e.deptno = d.deptno WHERE e.salary > 5000 GROUP BY d.name"))).project();

    // 2. Rewrite it with a rule: push the filter below the join
    HepPlanner hep = new HepPlanner(HepProgram.builder()
        .addRuleInstance(CoreRules.FILTER_INTO_JOIN).build());
    hep.setRoot(plan);
    RelNode better = hep.findBestExp();
    System.out.println(RelOptUtil.toString(better));
    // LogicalAggregate(group=[{0}], n=[COUNT()])
    //   LogicalProject(name=[$4])
    //     LogicalJoin(condition=[=($0, $3)], joinType=[inner])
    //       LogicalFilter(condition=[>(CAST($2):INTEGER NOT NULL, 5000)])
    //         LogicalTableScan(table=[[hr, emps]])
    //       LogicalTableScan(table=[[hr, depts]])

    // 3. Turn the plan back into SQL for another engine
    var pg = PostgresqlSqlDialect.DEFAULT;
    System.out.println(new RelToSqlConverter(pg).visitRoot(better).asStatement().toSqlString(pg));
    // SELECT "depts"."name", COUNT(*) AS "n"
    // FROM (SELECT *
    // FROM "hr"."emps"
    // WHERE CAST("salary" AS INTEGER) > 5000) AS "t"
    // INNER JOIN "hr"."depts" ON "t"."deptno" = "depts"."deptno"
    // GROUP BY "depts"."name"
  }
}