Like Julian said, you could try to change the way rules fire to descend into the subquery. I think you would need to modify the code here ( https://github.com/apache/calcite/blob/fdcb195b829fdc4f52d777ae09630ee65eb0a977/core/src/main/java/org/apache/calcite/plan/hep/HepPlanner.java#L605) to check whether the node that just failed a match contains a RexSubQuery in an expression tree and then call the matchOperands() function on the subquery root (not sure about this though).
In theory, you could treat it like you're rewriting the filter expression tree and add another rule that specifically matches a LogicalFilter with a predicate constraint that it has a subquery, something like this rule that rewrites the expression tree of a filter. <https://github.com/apache/calcite/blob/fdcb195b829fdc4f52d777ae09630ee65eb0a977/core/src/main/java/org/apache/calcite/rel/rules/FilterRemoveIsNotDistinctFromRule.java> I think that'd be pretty ugly though because you'd essentially have to reimplement the search/replace functions inside onMatch() itself. On Sat, Feb 13, 2021 at 1:40 PM Michael Thomson <[email protected]> wrote: > 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. > > >>> > > >>> > > >> > > >
