apache-calcite

RelNode of a query in which FROM clause itself has a query

I want to achieve result from a table where I ORDER BY column id and I don't want id to be present in the result. I can achieve this using the following query.

SELECT COALESCE (col1, '**')
FROM (select col1, id FROM myDataSet.myTable WHERE col4 = 'some filter' ORDER BY id);

Now, I want to create a RelNode of the above query. As far as I know, in calcite, to perform table scan, there are only two methods scan(String tableName) and scan(Iterable<String> tableNames). Is there a way to scan(RelNode ) ? How to do this ?

Solution

  • Just simply create a RelNode of inner subquery and create another projection on top of it. Like so.

    builder.scan('myTable')
 .filter(builder.call(SqlStdOperator.EQUALS, builder.field(col4), builder.literal('some filter') )))
  .project(builder.field('col1'), builder.field('id'))
  .sort(builder.field('id'))
  .project(builder.call(SqlStdOperator.COALESCE(builder.field('col1'), builder.literal('**'))))
  .build()