Hey Ian, It sounds like that in the single join example the CorrelateProjectExtractor [1] is kicking in and simplifies the decorrelation while in the case of two joins the extractor does not kick in so we are passing from the generic decorrelation logic.
BTW, it would be helpful if in the future you send sample queries that are human-friendly and the plans closer to the default Calcite representation. It takes a bit more effort to prepare the message but it is much easier for people contributing to the project to go over them (potentially run them) and provide feedback. In fact, you could even go one step further and build a test case in Calcite (e.g., in RelDecorrelatorTest [2]) that could potentially give you the answer or prepare the road for a solid contribution to the project. Best, Stamatis [1] https://github.com/apache/calcite/blob/e5682547cb07320634f4b1f335b50abb13f30cac/core/src/main/java/org/apache/calcite/sql2rel/CorrelateProjectExtractor.java [2] https://github.com/apache/calcite/blob/e5682547cb07320634f4b1f335b50abb13f30cac/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java On Wed, Dec 10, 2025 at 10:53 PM Ian Bertolacci via dev <[email protected]> wrote: > > > using some constant reduction rules prior to decorrelation you can optimize > > the constant addition away > > The constant addition is just a simple demonstration of the issue. > We’ve also seen this with other expressions which are not constant-foldable. > -Ian >
