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

Question

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 ?

Answer 1

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

Answer 2

The query

select col1, col2, col2 FROM myDataSet.myTable WHERE col4 = 'some filter' ORDER BY id

should also give you the desired result.

If you want to represent the query you have written more directly, you would start by constructing a RelNode for the query in the from clause, starting with a scan of myDataSet.myTable, adding the filter, and the order. Then you can project the specific set of columns you want.