Logical trees: difference between Before Project Normalization and Join Collapsed

The Before Project Normalization tree follows the Join Collapsed tree.

Using a sample set of ~250 queries - and Traceflags 8605, 8606, 8607, 3604 - I have not detected any difference between these two trees, in any of the sample queries.

Is it possible to construct a query where there is a difference between those two trees?

(This is research for a blog post - I will credit anyone who can help.)

Is it possible to construct a query where there is a difference between those two trees?

No.

The sequence of events is:

• The Join-collapsed Tree is printed immediately after Heuristic Join Reordering.

• Memory usage before project normalization is printed (if enabled).

• Normalize Projects is called.

• If project normalization has been disabled, it exits.

• Otherwise, a call to Derive Group Properties is made.

• Nothing has been added or changed, so properties are not rederived.

• Normalize Projects prints the Tree Before Project Normalization.

The extra call to Derive Group Properties has almost zero cost if no work needs to be done. One can only speculate why the tree is debug printed on entry to Normalize Projects.

StackExchange Database Administrators Q#273342, answer score: 7