SQLite Forum

Bad query results involving random()
Login
Note also that this is not merely SQLite being a bit quirky, this behaviour of "remembering output from functions" is specifically unwanted and eschewed in SQL for non-deterministic functions in most cases.

[See this link on determinism in SQLite functions.](https://sqlite.org/deterministic.html)

Imagine for a moment that you were interested in the real time during any part of a statement, you would be really dismayed if simply every row/inserted value has exactly the same time. The same goes for Random values for most use-cases.

That said there are cases in which the same time is very much required, at least throughout a transaction (say to ensure several related transactions all carry the same Posting time) as much you needed the same Random() value. To achieve that SQLite allows adding user-defined functions marked as "Deterministic" or non-deterministic (as described in the link above). In other engines there are sometimes different time functions for deterministic times and non-deterministic times. I've not seen a deterministic Random() function yet, but I haven't seen everything. :)

Lastly, if you can force the ephemeral table (composed by the CTE) to be materialized, then your query output is remembered (obviously) and every consecutive reference will have the same value for the called function.

I am never doing it that way, because of preference only, but I have seen Keith mention that if you add an "ORDER BY" to the CTE it forces the materialization. Not sure if this is always or under special circumstances only - hopefully someone else can confirm/elaborate or correct me.

Hope that provides some clarity!