RelNode rewrite

[Christian Beikov <ch...@gmail.com>]

Hello,

I'm trying to generate SQL for a trigger that requires a little rewrite 
of a RelNode structure and I wanted to know if anyone could help me 
figure out how to do a few things

  * I need to swap left and right of a LogicalJoin. Doing that messes up
    predicates and projections though. Is there a utility I could use or
    how would I do that?
  * I need to introduce an "alias" like "NEW" into a RelNode for which I
    want to create a RexNode "NEW.key1" for. I want to add a WHERE
    condition based on the NEW alias. Not sure how I would do that
    though. On SqlNode level only maybe?
  * Can I somehow determine what columns of a table are unique or part
    of the primary key? I want to create a predicate like (NEW.key1,
    NEW.key2) = (existing.key1, existing.key2)

Thanks in advance!

Regards,

Christian

Re: RelNode rewrite

[Christian Beikov <ch...@gmail.com>]

Hey Jess!

Am 14.02.2020 um 02:16 schrieb Jess Balint:
> Hey Christian,
>
> On Thu, Feb 13, 2020 at 6:30 PM Christian Beikov <ch...@gmail.com>
> wrote:
>
>> Hello,
>>
>> I'm trying to generate SQL for a trigger that requires a little rewrite
>> of a RelNode structure and I wanted to know if anyone could help me
>> figure out how to do a few things
>>
>>    * I need to swap left and right of a LogicalJoin. Doing that messes up
>>      predicates and projections though. Is there a utility I could use or
>>      how would I do that?
>>
> You can use JoinCommuteRule, or check it's code for your needs.
Thanks for the hint. I just hope the re-projection isn't confusing for 
the DBMS optimizer. Would be cool if a RelNode could be rewritten from 
bottom up to avoid this. Or is there some kind of Rule I can apply to 
optimize the re-projection away?
>>    * I need to introduce an "alias" like "NEW" into a RelNode for which I
>>      want to create a RexNode "NEW.key1" for. I want to add a WHERE
>>      condition based on the NEW alias. Not sure how I would do that
>>      though. On SqlNode level only maybe?
>>
> I don't understand what you're after here. The WHERE condition will be a
> filter node. Can you just add it with attaching something to the rel node?

So in a SQL trigger, there is the alias "NEW" and "OLD" available which 
I want to reference in a correlation predicate. I essentially want to 
correlate the RelNode of the changed table with the "NEW" alias and I 
try to do that through the primary key. Let's consider the example query 
"select * from table1 t". So for a trigger for "table1" I essentially 
want to create the query "select * from table1 t where (t.id) = (NEW.id)".

This is what I have so far, maybe you could point me to the right 
direction? The uniqueKeys are unfortunately null although the node is a 
JdbcTableScan.

private RexNodecreateNewCorrelatedJoinCondition(LogicalJoin join, RelNode node) {
     Set<ImmutableBitSet> uniqueKeys = node.getCluster().getMetadataQuery().getUniqueKeys(node); ImmutableBitSet uniqueFieldBitSet = uniqueKeys.iterator().next(); List<RelDataTypeField> fieldList = node.getRowType().getFieldList(); RelBuilder relBuilder = RelBuilder.create(config); RexNode condition = join.getCondition(); List<RexNode> conjuncts =new ArrayList<>(uniqueFieldBitSet.size() +1); conjuncts.add(condition); for (Integer index : uniqueFieldBitSet) {
         conjuncts.add(
             relBuilder.equals(
                 relBuilder.field("NEW", fieldList.get(index).getName()), relBuilder.field(fieldList.get(index).getIndex())
             )
         ); }
     return RexUtil.composeConjunction(rexBuilder, conjuncts); }

Thanks,

Christian

Re: RelNode rewrite

[Jess Balint <jb...@gmail.com>]

Hey Christian,

On Thu, Feb 13, 2020 at 6:30 PM Christian Beikov <ch...@gmail.com>
wrote:

> Hello,
>
> I'm trying to generate SQL for a trigger that requires a little rewrite
> of a RelNode structure and I wanted to know if anyone could help me
> figure out how to do a few things
>
>   * I need to swap left and right of a LogicalJoin. Doing that messes up
>     predicates and projections though. Is there a utility I could use or
>     how would I do that?
>

You can use JoinCommuteRule, or check it's code for your needs.


>   * I need to introduce an "alias" like "NEW" into a RelNode for which I
>     want to create a RexNode "NEW.key1" for. I want to add a WHERE
>     condition based on the NEW alias. Not sure how I would do that
>     though. On SqlNode level only maybe?
>

I don't understand what you're after here. The WHERE condition will be a
filter node. Can you just add it with attaching something to the rel node?


>   * Can I somehow determine what columns of a table are unique or part
>     of the primary key? I want to create a predicate like (NEW.key1,
>     NEW.key2) = (existing.key1, existing.key2)
>

You can use RelMetadataQuery.

Best,

Jess


>
> Thanks in advance!
>
> Regards,
>
> Christian
>
>