SQLite Forum

Bad query results involving random()
> That should obviously only output '1's or nothing...

It should obviously not.

When "SELECT random() & 3" gets evaluated for the WHERE clause it may well have the value 1, but when the same value moments later get evaluated again for the output queue, it can very much result in a different value this time. Random is NOT a deterministic function (luckily) and two consecutive calls to Random() should not and would not give the same answer (unless by random luck).

Do not confuse "Fields" in a RDBMS with "Variables" in software. Even though they share some properties, they are not the same thing. A field based on a calculation may (or ma not, depending on engine efficiency decisions) be evaluated multiple times, even during the same step in the same query, and may or may not result in different values every time if the field's calculation is non-deterministic.

"Random()" is precisely such a calculation.

PS: There are tricks for making the random data into a materialized set (which won't change) if you are interested, but that is not really the question here.