Seeding SQLite RANDOM()

Question

Does SQLite support seeding the RANDOM() function the same way MySQL does with RAND()?

$query = "SELECT * FROM table ORDER BY RAND(" . date('Ymd') . ") LIMIT 1;";

From the MySQL Manual about RAND(N):

If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values. In the following example, note that the sequences of values produced by RAND(3) is the same both places where it occurs.

If not, is there any way to archive the same effect using only one query?

Lifework

Answer 1

If you need a pseudo-random order, you can do something like this (PHP):

$seed = md5(mt_rand());
$prng = ('0.' . str_replace(['0', 'a', 'b', 'c', 'd', 'e', 'f'], ['7', '3', '1', '5', '9', '8', '4'], $seed )) * 1;
$query = 'SELECT id, name FROM table ORDER BY (substr(id * ' . $prng . ', length(id) + 2))';

Plus, you can set $seed to the predefined value and always get same results.

I've learned this trick from my colleague http://steamcooker.blogspot.com/

Koralie

Can you please explain this solution? – Interferometer

Unfortunately this doesn't scale very well for large tables. – Dichotomous

Luda, sorry for not answering in time. https://mcmap.net/q/49600/-order-by-random-with-seed-in-sqlite is a good explanation of what's going on there. – Koralie

Is this missing a trailing bracket? I've got ORDER BY (substr(....) – Thither

@Thither Indeed, thanks! – Koralie

Answer 2

Have a look at the sqlite3_randomness() function:

SQLite contains a high-quality pseudo-random number generator (PRNG) used to select random ROWIDs when inserting new records into a table that already uses the largest possible ROWID. The PRNG is also used for the build-in random() and randomblob() SQL functions.

...

The first time this routine is invoked (either internally or by the application) the PRNG is seeded using randomness obtained from the xRandomness method of the default sqlite3_vfs object. On all subsequent invocations, the pseudo-randomness is generated internally and without recourse to the sqlite3_vfs xRandomness method.

Looking at the source of this xRandomness method, you can see that it reads from /dev/urandom on Unix. On Windows, it just returns the return values of some time functions. So it seems that your only option is to start hacking on the SQLite source code.

Sisterly

Answer 3

Based on the answer of @jankkhvej I will do a modification to the code to control generating PRNG using a predefined seed (integer-32 bit number) and document the code.

Actually the seed in PHP is defined using the function mt_srand($seed)

//define seed (modify it as needed), so you can get the same series every time you run the code.
$seed=123456789;
//mt_srand — Seeds the Mersenne Twister Random Number Generator
mt_srand($seed);

//compute hash md5 for mt_rand()
$md5 = md5(mt_rand());
//use md5 to get a decimal number by replacing the hex-decimal chars abcef by numeric values, then prefix the result with "0."
$prng = ('0.' . str_replace(['0', 'a', 'b', 'c', 'd', 'e', 'f'], ['7', '3', '1', '5', '9', '8', '4'], $md5 )) * 1;

//sqlite is using rowid, a unique number for row. If you defined a primary key, it can be rowid. 
//multiply rowid by $prng , and extract only the fraction part using substr function
//e.g 1.0519815151568 will be   0519815151568
$query = "SELECT id, name FROM table ORDER BY (substr(rowid * $prng , length(rowid) + 2))";

Try onlin demo

output

seed= 1234567890
mt_rand= 1328851649
MD5 hash= 1abfd768d39fc907c278ec2cb8ae809b
prng= 0.1314976893946
SQLite version 3.7.17
Selecting 3 random rows of 10, with PRNG: 0.1314976893946
Id  name    rowid*prng         random
8   p_8     1.0519815151568    0519815151568
1   p_1     0.1314976893946    1314976893946
10  p_10    1.314976893946     14976893946

I provided an alternative solution to generate PNRG , you can find here

Ashe