I think the changelog is a bit confusing on that point. I interpret it as meaning that CTE is a default optimization:

"The default behavior was formerly NOT MATERIALIZED, but is now changed to MATERIALIZED for CTEs that are used more than once"

The default was NOT MATERIALIZED only because previous versions of SQLite didn't have this feature at all.

"now changed to MATERIALIZED for CTEs that are used more than once" means that the query planner DOES use this optimization by default if you don't do anything - the optimization only makes sense for CTEs that are used more than once in the same query.

> The default was NOT MATERIALIZED only because previous versions of SQLite didn't have this feature at all.

The default was NOT MATERIALIZED because it's what the sqlite devs had selected whe they implemented CTEs.

Pg did the exact opposite before the feature was added (and exposed to end-users): CTEs would always be MATERIALIZED. And similarly, with the introduction of [NOT] MATERIALIZED they changed the default in some cases: for pg if the CTE is non-recursive, has no side-effects, and is only referenced once, then it defaults to NOT MATERIALIZED.

I think the change introduced this syntax

with history as materialized { .. }

so you have to declare the CTE as materialized to get this optimization

> so you have to declare the CTE as materialized to get this optimization

Not necessarily. You can get a materialized CTE if you request it regardless of its usage pattern, but you will also get one if the CTE is used more than once unless you suppress it.

That's not how I interpret the release notes, but I can see why they are being misunderstood like that (assuming I'm right in my interpretation).