>
>First of all this type of query falls into my category 2 where you use a subquery multiple times in which I outlined a CTE has the advantage you can use the definition multiple times in the same query, thus cutting code and points of failure.
>
>That being said, SQL server does not materialize the subquery and therefore writing it as a CTE does not avoid the very_expensive_function to be called for every instance you use it. So substituting W1 and W2 with the CTE most likely will produce the very same execution plan and thus the same performance.
>
>

>SELECT * 
>    FROM (SELECT key, very_expensive_function(val) as f FROM some_table) as W1
>         INNER JOIN  (SELECT key, very_expensive_function(val) as f FROM some_table) as W2 ON W1.f = W2.f
>

>Which is perectly readable to me. For me its better than the CTE version.
>
>I also said that if the CTE is a universally known definition of something it likely is better to define that into a view or inline function. At least then you can re-use it elsewhere and fix problems or extend at one place
>

Hmm, once again I can see that you are thinking CTEs are specific to MS SQL Server. It is not the case, it is part of ANSI SQL. MS SQL server's implementation today may or may not be materializing them (I don't know the details). That doesn't mean they would be done in the same way in the next version. I don't expect the issue to be changed for subqueries.

For the backends that support both materialized and not materialized versions of CTE in a controllable way, that query executes the expensive function only once per row. IOW it looks like:

SELECT key, very_expensive_function(val) as f 

into ImaginaryCTETable

FROM some_table ;

Select * from ImaginaryCTETable w1
     INNER JOIN  ImaginaryCTETable w2 ON W1.f = W2.f
>

In this case, probably you could at least admire about its readability vs 2 identical subqueries even if you might ignore performance aspect based on you want to constraint it to how MS SQL Server is doing today.

>Postgress, pre version 12, have the exact opposite problem where it does materialize but then has the problem that the optimizer cannot look into its definition, leading to poor performance in some case.
>
>In SQL Server, at the least you can choose to execute the very_expensive quering once and load it into a temp table or table variable and do the join afterwards.
>
>Walter,

OK at least you are aware it is different in postgreSQL 12 (releasing at October 2019), that surely would mean it could be the same for MS SQL Server in the future.

You can of course do the loading into a temp table in other backends other than MS SQL Server too. For a query at hand, that might be the best option, or there might be other better options rather than using a CTE or a subquery. That certainly is another matter. At least postgreSQL 12's approach is not just constrained to this sample and shows how a CTE might be more beneficial (both in readability and performance).

IMHO, the easiest solution to this discussion is to agree to disagree.