Hi Ian, regarding commutativity/associativity I think this ML discussion <https://lists.apache.org/thread.html/r68b60538222d01a3cf065e44f8dbad7c23d042350d9cd6b7b52ee811%40%3Cdev.calcite.apache.org%3E> could be relevant and it has some pointers.
For what concerns decorrelation, I think that most of Calcite's code relies on subqueries being decorrelated, like in the example you have cited. I recall some ML discussions where this topic has been elaborated but I can't find any right now. Best regards, Alessandro
