Julian, Lana
Thanks Lana.
I tried with the FILTER_SUB_QUERY_TO_CORRELATE to pull the IN clause
subquery up and it worked... so thanks
eg
Explanation
LogicalProject(t1=[$0])
LogicalProject(t1=[$0], i1=[$1], rowid=[$2])
LogicalFilter(condition=[=($0, $3)])
LogicalJoin(condition=[true], joinType=[left])
LogicalFilter(condition=[OR(=($0, 'a'), =($0, 'b'), =($0, 'c'))])
LogicalTableScan(table=[[db, t6]])
LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
LogicalProject(t1=[$0])
LogicalFilter(condition=[OR(=($0, 'a'), =($0, 'b'), =($0,
'c'))])
LogicalTableScan(table=[[db, t4]])
*BUT...* I would prefer not to have to deal with a correlated query.
Is there some way I can just get rule to occur in the Filter? or maybe
decorrelate the query after the expansion?
thanks
Michael
On Sat, Feb 13, 2021 at 12:10 PM Julian Hyde <[email protected]> wrote:
> Lana, that makes perfect sense. The rule engine does not descent into
> RexSubQuery. (We can debate whether it should.)
>
> Julian
>
> > On Feb 12, 2021, at 23:00, Lana Ramjit <[email protected]> wrote:
> >
> > I am not sure if this helps and one of the dev team will probably know
> > better than me, but I think I ran into a similar issue.
> > The withOperandSupplier matches LogicalTableScan nodes, but the subquery
> is
> > a RexSubQuery contained in a LogicalFilter; so it skips the LogicalFilter
> > and does not descend into the expression tree to start firing rules.
> >
> > I believe that it works in the full planner because the full planner
> uses a
> > SubqueryRemoveRule that lifts the subquery out of an expression in the
> > LogicalFilter and into a set of first-class logical operators. So I did
> > manage to fix a similar issue by making sure that SubqueryRemoveRule is
> > fired first.
> >
> > Hope this helps; I am interested to know if my fix is right since I ran
> > into a similar issue!
> > -Lana
> >
> >> On Fri, Feb 12, 2021 at 10:33 PM Michael Thomson <
> [email protected]>
> >> wrote:
> >>
> >> Julian
> >>
> >> Thanks for your comment, I am using Calcite 1.25 currently by the way.
> >> Here is a simplified version of the code that runs the rule
> >>
> >>
> >> @Override
> >> public RelRoot rel(SqlNode sql) {
> >> RelRoot root = super.rel(sql);
> >> root = applyInjectFilterRule(root, restriction);
> >> return root;
> >> }
> >>
> >> private RelRoot applyInjectFilterRule(RelRoot root, Restriction
> >> restriction) {
> >> final InjectFilterRule injectFilterRule =
> >> InjectFilterRule.Config.DEFAULT.toRule(restriction);
> >>
> >> final HepProgram program =
> >>
> HepProgram.builder().addRuleInstance(injectFilterRule).build();
> >> HepPlanner prePlanner = new HepPlanner(program);
> >> prePlanner.setRoot(root.rel);
> >> final RelNode rootRelNode = prePlanner.findBestExp();
> >> return root.withRel(rootRelNode);
> >> }
> >>
> >> Does this look reasonable?
> >>
> >> I can produce a complete test program to try to reproduce in isolation
> if
> >> nothing in this code jumps out.
> >>
> >> thanks
> >> Michael.
> >>
> >> On Fri, Feb 12, 2021 at 3:43 PM Julian Hyde <[email protected]>
> >> wrote:
> >>
> >>> It looks like you’ve defined it right. It should fire once for each
> >>> LogicalTableScan in the plan.
> >>>
> >>> Make sure that you have registered a rule instance in the planner.
> >>>
> >>>> On Feb 11, 2021, at 11:19 PM, Michael Thomson <
> [email protected]
> >>>
> >>> wrote:
> >>>>
> >>>> Hi,
> >>>>
> >>>> I suspect I am doing something wrong but it's not obvious to me and
> >>> hoping
> >>>> someone can spot what my issue is.
> >>>>
> >>>> I am trying to add a simple filter after detection of
> LogicalTableScans
> >>> on
> >>>> particular tables
> >>>>
> >>>> I have a rule that looks like this
> >>>>
> >>>> public interface Config extends RelRule.Config {
> >>>> Config DEFAULT =
> >>>> EMPTY.withOperandSupplier(b0 ->
> >>>> b0.operand(LogicalTableScan.class).anyInputs())
> >>>> .as(Config.class);
> >>>>
> >>>> @Override
> >>>> default InjectFilterRule toRule() {
> >>>> return new InjectFilterRule(this, null);
> >>>> }
> >>>> }
> >>>>
> >>>> Everything works as expected with most sql
> >>>>
> >>>> but when using a subqueries like this
> >>>>
> >>>> select * from t6 where t6.t1 in (select t1 from t6);
> >>>>
> >>>> The result I see is
> >>>>
> >>>> LogicalProject(t1=[$0], i1=[$1])
> >>>> LogicalFilter(condition=[IN($0, {
> >>>> LogicalProject(t1=[$0])
> >>>> LogicalTableScan(table=[[database, t6]])
> >>>> })])
> >>>> LogicalFilter(condition=[OR(=($0, 'a'), =($0, 'b'), =($0, 'c'))])
> >>>> LogicalTableScan(table=[[database, t6]])
> >>>>
> >>>> You will note the bogus FILTER added to the first LogicalTableScan
> >>>>
> >>>> LogicalFilter(condition=[OR(=($0, 'a'), =($0, 'b'), =($0, 'c'))])
> >>>>
> >>>> but nothing was added to the one in the IN subquery
> >>>>
> >>>> When I look in the debug it seems the onMatch code never receives an
> >>>> invocation for the subquery Scan.
> >>>>
> >>>> The rules are run as part of the planner.rel() call.
> >>>>
> >>>> Any ideas of what I am overlooking or misunderstanding would be much
> >>>> appreciated
> >>>>
> >>>> thanks
> >>>> Michael.
> >>>
> >>>
> >>
>
