Hi, I just became aware of this requirement in Phoenix using Calcite last week that for some Phoenix tables, we'd like to have two slightly different table scan strategies, "ordered" vs. "unordered", the latter of which does not guarantee primary key of the returned rows but can be significantly faster.
At first I thought this would be easy, just by having two physical TableScan operators and some rules to replace the default one with the alternative one. But this didn't work, regardless which is which, basically because the parent rel would not be aware of the existence of a new subset of its child rel, which means we couldn't create a chain of new rels comprising the new subsets from bottom-up just like the way we'd create a chain of new rels with new subsets top-down using ConvertRules. To further justify that RelOptRules couldn't work, a rule that matches Sort over TableScan wouldn't be good for all cases, for we have operators like PhoenixServerJoin that does not require a specific collation for its children but instead surfaces the collation from one of them as its own collation trait. However, I found SubstitutionVisitor exactly worked for this case. I ended up modeling the alternative table scan as a materialization, with a virtual table name and as an identity projection of the original table, e.g. table A has an unordered version A' (which physically does not exist) and A' is registered as a materialization of A. My conclusion was: 1. When using RelOptRules, you shouldn't expect the transformed new rel to be in a different subset from the original rel. It can have its own subset but this subset should be the subset of the original subset, otherwise its parent wouldn't be aware of its existence. And even if it is a sub-subset, you can't expect it to affect the planning on higher levels of the tree. 2. The SubstitutionVisitor (or materialization substitution) can be useful in such cases where you want to change the planning of the tree from bottom-up, while volcano rules can't. This coincided with a conversation Julian, James and I had last week about the SubstitutionVisitor and thought this might be helpful in further discussions. Please let me know whether my solution sounds reasonable and whether the statement above is correct. Thanks, Maryann
