@ypercubeᵀᴹ Huh. I learned something.

And again:

@PaulWhite @ypercubeᵀᴹ But if that was the case, the plan wouldn't be reflecting what was happening in the derived table, would it?

@AndriyM Not sure what you mean?

@PaulWhite I understood @ypercubeᵀᴹ that if the derived table is materialised even when the statement including it is not actually executed but is the argument of EXPLAIN, then EXPLAIN would only see it as a materialised table and be unable to show the derived table's plan.

Oh I see.

@AndriyM Not sure I understand you.

I meant that derived tables were (almost always, not sure if there were any exceptions) always materialized, even during execution plans (which they shouldn't)

Like Postgres CTEs, they were optimization fences

So they could be used for good, if for example the derived table used indexes and yielded a small result set), guiding the optimizer to follow that path, or for bad, if the resut set was from some enormous join, even if the surrounding conditions in the external query were restricting the result, the huge intermediate result set would be materialized.

MariaDB made several improvements in their optimizer, in (their) versions 5.2, 5.3 and 5.5

MySQL did something similar in 5.6 and 5.7

@AndriyM The explain would show the plan for the derived table as well. In the process of doing so, it would materialize it.

@ypercubeᵀᴹ I understand now

They are progressing a bit quicklier now, that Oracle has bought it.

They are developing CTEs and window functions

Both MySQL and MariaDB.

I giuess competition does work.

Downloading SQL Server 2016 ;)