MyTableScanRule3.java

public class MyTableScanRule3 extends RelOptRule {

	public MyTableScanRule3() {
		super(operand(JdbcTableScan.class, any()));
	}

	@Override
	public boolean matches(RelOptRuleCall call) {
		JdbcTableScan jdbcTableScan = call.rel(0);
		return super.matches(call);
	}

	@Override
	public void onMatch(RelOptRuleCall call) {
		JdbcTableScan jdbcTableScan = call.rel(0);

		RelBuilder relBuilder = call.builder();

		// 1. Scan
		relBuilder.push(jdbcTableScan);

		ImmutableList<RexNode> fields = relBuilder.fields();
		
		// 2. Project with MAX OVER WINDOW:  ( last_version_number=[MAX(exp_date) OVER (PARTITION BY account_id)]
		RelDataType type =  SqlStdOperatorTable.MAX.inferReturnType(
				relBuilder.getTypeFactory(), //new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT),
				ImmutableList.of(relBuilder.field("exp_date").getType()));
		SqlAggFunction operator = SqlStdOperatorTable.MAX;
		List<RexNode> exprs = ImmutableList.of((RexNode)relBuilder.field("exp_date"));
		List<RexNode> partitionKeys = ImmutableList.of((RexNode)relBuilder.field("account_id"));
		ImmutableList<RexFieldCollation> orderKeys = ImmutableList.of();

		RexWindowBound lowerBound = RexWindowBound.create(SqlWindow.createUnboundedPreceding(SqlParserPos.ZERO), null);
		RexWindowBound upperBound = RexWindowBound.create(SqlWindow.createUnboundedFollowing(SqlParserPos.ZERO), null);
		boolean physical = true;
		boolean allowPartial = true;
		boolean nullWhenCountZero = false;

		RexNode maxOverRexNode = jdbcTableScan.getCluster().getRexBuilder().makeOver(
				type, operator, exprs, partitionKeys, orderKeys, lowerBound, upperBound, physical, allowPartial, nullWhenCountZero);

		ImmutableList<RexNode> newProjections = ImmutableList.<RexNode>builder().addAll(fields).add(maxOverRexNode).build();
		RelDataType newProjectDataType = relBuilder.getTypeFactory().builder()
				.addAll(jdbcTableScan.getRowType().getFieldList())
				.add("last_version_number", maxOverRexNode.getType()).build();

		JdbcProject newJdbcProject = new JdbcProject(
				jdbcTableScan.getCluster(),
				jdbcTableScan.getTraitSet(),
				jdbcTableScan, newProjections,
				newProjectDataType);

		relBuilder.push(newJdbcProject);

		// 3. FILTER (exp_date = last_version_number)
		RexNode condition = relBuilder.call(SqlStdOperatorTable.EQUALS,
				relBuilder.field("exp_date"),
				relBuilder.field("last_version_number"));

		JdbcFilter jdbcFilter = new JdbcFilter(jdbcTableScan.getCluster(), jdbcTableScan.getTraitSet(), newJdbcProject, condition);

		relBuilder.push(jdbcFilter);

		// 4. TOP PROJECT (same as the Scan Row Type - filters out the last_version_number column)
		JdbcProject topJdbcProject = new JdbcProject(
				jdbcTableScan.getCluster(),
				jdbcTableScan.getTraitSet(),
				jdbcFilter,
				fields,
				jdbcTableScan.getRowType());

		RelNode relNode = relBuilder
				.push(topJdbcProject)
				.build();

		System.out.println("Expanded RelNode: \n" + RelOptUtil.toString(relNode));

		call.transformTo(relNode);
	}
}