Besides what Stamatis and Mihai mentioned, I'd love to share my current insight for your case.
In this example, you have 6 tables joining together, the problem seems related with the join reordering optimizations. The default join reorder rules are JOIN_COMMUTE[1] and JOIN_ASSOCIATE[2]. There are other heuristic join reorder rules such as MULTI_JOIN_OPTIMIZE_BUSHY[3], MULTI_JOIN_OPTIMIZE[4], which are more efficient in complex join scenarios. If you could managed to custom your optimizing rules, you can try these rules. There is a utility method to enable this[5] which is only used in tests, it will be helpful if we also have a configuration for this behavior. [1] https://github.com/apache/calcite/blob/b64cb1325cfe1a5143ea3ca534f991b6f881c3c5/core/src/main/java/org/apache/calcite/rel/rules/JoinCommuteRule.java#L57 [2] https://github.com/apache/calcite/blob/b64cb1325cfe1a5143ea3ca534f991b6f881c3c5/core/src/main/java/org/apache/calcite/rel/rules/JoinAssociateRule.java#L51 [3] https://github.com/apache/calcite/blob/b64cb1325cfe1a5143ea3ca534f991b6f881c3c5/core/src/main/java/org/apache/calcite/rel/rules/MultiJoinOptimizeBushyRule.java#L78 [4] https://github.com/apache/calcite/blob/b64cb1325cfe1a5143ea3ca534f991b6f881c3c5/core/src/main/java/org/apache/calcite/rel/rules/LoptOptimizeJoinRule.java#L77 [5] https://github.com/apache/calcite/blob/b64cb1325cfe1a5143ea3ca534f991b6f881c3c5/core/src/main/java/org/apache/calcite/tools/Programs.java#L182-L189 Mihai Budiu <[email protected]> 于2023年2月9日周四 06:06写道： > You haven't described your setup precisely enough for me to understand > what is going on. > But in general with Calcite you can control very precisely which plan > rewrite rules are being applied and when. > > Here is what we do. > In the SqlToRelConverter we use a HepPlanner with an empty set of rules. > After we get a RelNode from the converter we run a series of HepPlanners, > each with a list of hand-picked rules. So we control exactly what code > transformations are applied. > In fact, we can even look at the query code and choose the list > transformations depending on that; for example, the BushyJoin optimization > rules does not work for outer joins, so if the plan contains outer joins, > we avoid applying this rule. > Here is the source code for our current workflow: > https://github.com/vmware/sql-to-dbsp-compiler/blob/main/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/sqlparser/CalciteCompiler.java > > I am relatively new to Calcite, so I may not be using it in the best way, > but I think it gives you complete control. > For infinite loops, perhaps you could setup a timer and abort executing a > optimization step when it expires. > Mihai > > > > -----Original Message----- > > From: Stamatis Zampetakis <[email protected]> > > Sent: Wednesday, February 8, 2023 1:52 PM > > To: [email protected] > > Subject: Re: Volcano Planner Issue > > > > !! External Email > > > > You cannot execute a query without planning it. I don't have enough > context > > to answer your question. Sorry! > > > > Best, > > Stamatis > > > > On Wed, Feb 8, 2023, 7:22 PM Suchit Kumar <[email protected]> > > wrote: > > > > > Hi Stamatis, > > > > > > Hope you are doing well. > > > I will upload this to git meanwhile can you suggest if is there any > > > way we can bypass the planner and directly execute the query? > > > > > > Thanks, > > > Suchit Kumar > > > > > > On Wed, Feb 8, 2023 at 3:57 PM Stamatis Zampetakis > > <[email protected]> > > > wrote: > > > > > > > Hey Suchit, > > > > > > > > It is usually better to share links to GiHub (or other CVS > > > > platforms) > > > with > > > > projects showing the problem instead of attaching zip files. > > > > Many security conscious people will never download a zip file from > > > someone > > > > they don't know. > > > > Moreover, with GitHub it is easier for people to give a quick glance > > > > in case they can spot something quickly. > > > > > > > > If you need help with Calcite, this list is the best place to get > help. > > > > Take into account that we are all volunteers with limited time so > > > > there > > > is > > > > no guarantee that someone will pick this up. > > > > > > > > Best, > > > > Stamatis > > > > > > > > On Wed, Feb 8, 2023 at 3:31 AM Suchit Kumar <[email protected]> > > > wrote: > > > > > > > > > Hi, > > > > > > > > > > > > > > > Thanks for your reply could you guide me if there is any way I can > > > > execute > > > > > my query without an optimizer? > > > > > I am not sure whom I can reach can you tell me any channel where I > > > > > can > > > > post > > > > > this issue? > > > > > > > > > > Thanks, > > > > > Suchit Kumar > > > > > > > > > > > > > > > On Wed, Feb 8, 2023 at 7:57 AM Benchao Li <[email protected]> > > > wrote: > > > > > > > > > > > Hi Suchit, > > > > > > > > > > > > FYI, I've reproduced the problem with your example, however, I > > > > > > cannot > > > > > tell > > > > > > the reason for now, it may take a while. > > > > > > > > > > > > I would be great if someone else who's interested with the > > > > > > problem > > > > could > > > > > > also take a look at it. > > > > > > > > > > > > Suchit Kumar <[email protected]> 于2023年2月7日周二 11:17 > > 写道： > > > > > > > > > > > > > Hi, > > > > > > > > > > > > > > I have attached 4 files you can run ApacheClient file to check > > > > > > > the example. Please let me know if you face any issues running > > > > > > > my > > > > example. > > > > > > > > > > > > > > Thanks, > > > > > > > Suchit Kumar > > > > > > > > > > > > > > On Tue, Feb 7, 2023 at 7:31 AM Benchao Li > > > > > > > <[email protected]> > > > > > wrote: > > > > > > > > > > > > > >> Hi Suchit, > > > > > > >> > > > > > > >> Sorry that I couldn't find your example. Am I missing > something? > > > > > > >> > > > > > > >> Suchit Kumar <[email protected]> 于2023年2月7日周二 > > 00:01写道： > > > > > > >> > > > > > > >> > Hi, > > > > > > >> > > > > > > > >> > Thank you for your reply, This is the example that I am > > > > > > >> > creating > > > > and > > > > > > >> > inside iterativeRuleQueue it keeps running. > > > > > > >> > In this example, I need to query on top of the java hashmap. > > > > > > >> > Please let me know what I am missing and if there is > > > > > > >> > anyway. So > > > > > that I > > > > > > >> can > > > > > > >> > bypass the optimiser and run the query directly. > > > > > > >> > > > > > > > >> > > > > > > > >> > > > > > > > >> > On Mon, Feb 6, 2023 at 4:39 PM Benchao Li > > > > > > >> > <[email protected] > > > > > > > > > > wrote: > > > > > > >> > > > > > > > >> >> Hi Suchit, > > > > > > >> >> > > > > > > >> >> It would be helpful if you could provide more information > > > > > > >> >> such > > > > as: > > > > > > >> >> - What the version are you using > > > > > > >> >> - How you used Calcite, e.g., what's the rules you are > > > > > > >> >> using, > > > is > > > > > > there > > > > > > >> any > > > > > > >> >> custom rule? > > > > > > >> >> - If there is a pure Calcite reproducible test case, it > > > > > > >> >> would > > > be > > > > > > easier > > > > > > >> >> for > > > > > > >> >> others to understand the problem and propose a solution > > > > > > >> >> > > > > > > >> >> Come back to your question, I'm guessing that is there a > > > > > > >> >> custom > > > > > rule > > > > > > >> which > > > > > > >> >> is not implemented well, and it always matches and > > > > > > >> >> produces > > > > another > > > > > > >> result > > > > > > >> >> which could match the rule again, which will go into an > > > infinite > > > > > > loop? > > > > > > >> >> > > > > > > >> >> Suchit Kumar <[email protected]> 于2023年2月6日周一 > > 13:13写道： > > > > > > >> >> > > > > > > >> >> > Hi Team, > > > > > > >> >> > > > > > > > >> >> > I am using apache calcite for one of our use cases but > > > > > > >> >> > in 1 > > > > > query I > > > > > > >> am > > > > > > >> >> > getting an issue where the volcano planner is not > > > > > > >> >> > returning > > > > > > anything > > > > > > >> >> and it > > > > > > >> >> > keeps running I have checked online as well but I > > > > > > >> >> > haven't > > > found > > > > > any > > > > > > >> >> > solution. Could you please let me know if there is any > > > > > > >> >> > way to > > > > > > disable > > > > > > >> >> the > > > > > > >> >> > planner optimizer? > > > > > > >> >> > > > > > > > >> >> > Thanks, > > > > > > >> >> > Suchit Kumar > > > > > > >> >> > > > > > > > >> >> > > > > > > >> >> > > > > > > >> >> -- > > > > > > >> >> > > > > > > >> >> Best, > > > > > > >> >> Benchao Li > > > > > > >> >> > > > > > > >> > > > > > > > >> > > > > > > >> -- > > > > > > >> > > > > > > >> Best, > > > > > > >> Benchao Li > > > > > > >> > > > > > > > > > > > > > > > > > > > -- > > > > > > > > > > > > Best, > > > > > > Benchao Li > > > > > > > > > > > > > > > > > > > > > > !! External Email: This email originated from outside of the > organization. Do > > not click links or open attachments unless you recognize the sender. > -- Best, Benchao Li
