Re: scope of rex expressions

[Guillaume Masse]

I found a weak solution to this problem, I'm wondering if there is a better
way:

Grab the LogicalProject for the original query,
use getRowType to produce a Map from String to RexInputRef
call convertExpression
<https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql2rel/SqlToRelConverter.html#convertExpression(org.apache.calcite.sql.SqlNode,java.util.Map)>


In Scala it looks like this:

val hack: JMap[String, RexNode] =
  rel match {
    case p: LogicalProject =>
      p.getRowType() match {
        case record: RelRecordType =>
          record.getFieldList().asScala.toList.map{field =>
            (field.getName(), new RexInputRef(field.getIndex(),
field.getType()): RexNode)
          }.toMap.asJava
      }
  }

sqlToRelConverter.convertExpression(expressionNode, hack)

This sounds a bit fragile since the identifiers are not fully qualified.

On Tue, Feb 21, 2023 at 6:12 PM Guillaume Masse <
masse.guilla...@narrative.io> wrote:

> Hi All,
>
> I have a question regarding how to get a rex expression scoped to a select
> list:
>
> Let's say I have a table t:
>
> CREATE TABLE t (
>     foo int,
>     bar int
> );
>
> And a simple query:
>
> SELECT foo FROM t
>
> And some standalone expressions related to that query/table stored
> elsewhere that I want to inject:
>
> bar + 123
>
> Where the final desired query is:
>
> SELECT foo, bar + 123 FROM t
>
> How can I produce a RexNode for the expression `bar + 1` (eg `$1 + 123`)?
>
> My intuition was to validate the simple query and get the scope at the
> select list:
>
> SqlValidator validator
> SqlToRelConverter converter
>
>
> SqlSelect select = (SqlSelect) parse("SELECT foo FROM t")
> validator.validate(select)
>
> SqlNode node = parse("bar + 123")
> val scope = validator.getSelectScope(select)
> node.validateExpr(validator, scope)
>
> RexNode rex = converter.convertExpression(node)
>
> however, I can't get the identifier resolved.
> [info]   Cause: java.lang.AssertionError: no unique expression found for
> {id: bar, prefix: 1}; count is 0
> [info]   at
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.lookupExp(SqlToRelConverter.java:5009)
> [info]   at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:4299)
> [info]   at
> org.apache.calcite.sql2rel.SqlToRelConverter.access$2600(SqlToRelConverter.java:229)
> [info]   at
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5532)
> [info]   at
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4702)
> [info]   at
> org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:324)
> [info]   at
> org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5351)
> [info]   at
> org.apache.calcite.sql2rel.StandardConvertletTable.convertOperands(StandardConvertletTable.java:971)
> [info]   at
> org.apache.calcite.sql2rel.StandardConvertletTable.convertOperands(StandardConvertletTable.java:963)
> [info]   at
> org.apache.calcite.sql2rel.StandardConvertletTable.convertCall(StandardConvertletTable.java:916)
>
>
> For the rest of the process (rebuilding the select with the new select
> list), I have no problem.
>
> I'm really stuck with resolving identifiers given the scope of the select
> list.
>
> Any help would be greatly appreciated.
>
> Guillaume Massé
> 马赛卫
>