v.21.2Improvements
Allow Aliasing of CTEs and Improve CSE Propagation in ClickHouse
Allow CTE (Common Table Expressions) to be further aliased. Propagate CSE (Common Subexpressions Elimination) to subqueries in the same level when enable_global_with_statement = 1. This fixes #17378 . This fixes https://github.com/ClickHouse/ClickHouse/pull/16575#issuecomment-753416235 . #18684 (Amos Bird).Why it matters
This feature addresses limitations in query optimization involving Common Table Expressions (CTEs) by allowing additional aliasing and enhancing the propagation of common subexpression elimination (CSE) to subqueries at the same level. This improves query performance and correctness and resolves issues related to CTE evaluation and optimization, particularly fixing issue #17378.How to use it
To utilize this feature, set the settingenable_global_with_statement to 1, which enables global WITH statement optimizations. This allows further aliasing of CTEs and the propagation of CSE optimizations to subqueries on the same hierarchical query level, enhancing query execution plans.