SQLite Forum

Bad query results involving random()

Observe the following query:

WITH RECURSIVE cnt(x) AS(values(1) UNION ALL SELECT x+1 FROM cnt WHERE x<100),
               ran(x) AS(SELECT random() & 3 FROM cnt)
SELECT x FROM ran WHERE x = 1;

That should obviously only output '1's or nothing, according to the WHERE clause. But I'm seeing it return a list of random numbers between 0 and 3.

I'm guessing that the `random() & 3` subexpression is being inlined in the query plan, hence using different values for comparison and returning.

OS: Gentoo amd64.

$ sqlite3 --version
3.33.0 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0alt1