An interesting query has been twitted by Will Leinweber from Postgres Open:
-- this returns a different result each time it is ran
with recursive s as (
select random() from s
) select count(*) from s;
I like this example: a surprising result, which can be explained by (and indeed helps to explain) CTE behaviour.