Nothing to do with CTEs. The queries were badly written to begin with and should have used LATERAL.

Yes, I forgot the actual issue was not CTEs directly.

I agree the query could have written better (I am still getting my head around how to use LATERAL), but it worked fine in 9.6 and stopped working in 10. From a backward compatibility viewpoint, code working in one version should still work in the next (even if it isn't the best code.) Or at least, start issuing deprecation warnings one version before making it not work.

Anyway, posting this to HN has triggered someone to go rewrite my code for me (thanks Ants Aasma, whoever you are), so now my Postgres 10 upgrade blocker is solved :)

You're welcome. Wanted to see how hard it is to port a query over.

Postgres generally tries its best to not break users code. However sometimes it is necessary for making forward progress. In this case the undocumented behavior of set returning functions within select list had some pretty funky, mostly accidental, semantics that were getting in the way of executor improvements. For example try to figure out how to explain the output of these two queries on 9.6:

    select generate_series(1,2), generate_series(1,4);
    select generate_series(1,3), generate_series(1,4);

That is one example of a silent behavior change between versions that was justified that applications that are seeing that behavior are probably broken anyway. Set returning functions within case expressions had more reasonable behavior so to avoid silent breakage they were made to result in an error.

Deprecation warnings are nice in theory, but in practice they would require an unreasonable amount of effort to properly implement, not seeing any warnings still wouldn't be a guarantee that your application works on new version. And it seems most users ignore deprecation warnings anyway. Besides, it's not like you can avoid making the changes, you just have slightly less schedule flexibility on when to implement them.