Nondeterministic Functions in MySQL (i.e. Rand) Can Surprise You

I prefer to prepare the datasets first, and not depend on the order of evaluation (which you may fail to predict correctly):<p><pre><code> select * from some_table join (select rand() as random_id) on some_table.the_id = random_id </code></pre> This way it is obvious that rand() will be evaluated only once.

Why do people use &quot;i.e.&quot; instead of &quot;e.g.&quot;?<p>EDIT:&quot; For people who think I don&#x27;t know what they mean, I do. I meant that the author of TFA obviously meant to write &quot;Nondeterministic Functions in MySQL (e.g. Rand)&quot;, because the entire article is about all non-deterministic functions and just uses rand as an example.

As to SQLite3, the author should have indicated what version they tested, and they should have tested more versions. SQLite3 3.8.3 introduced a flag for marking functions as deterministic (vs. not), and in the version I just tried (3.11.0) it works as expected.<p>The rule I expect for where clause expression evaluation is: standard short-circuit semantics. That calls to deterministic functions get memoized, or that constant sub-expressions get hoisted out, is a plus -- I do expect GCSE and optimization of deterministic functions from modern RDBMSes. I also expect that functions meant to be used in queries have no side effects -- that they insert&#x2F;update&#x2F;delete no rows on any tables.

For SQL Server it looks like an index seek on the following so it is evaluated once.<p>SELECT * FROM SomeTable WHERE ID = CAST(Rand() * 1000 as int)<p>In addition this query: SELECT TOP(100) rand() AS RandData from SomeTable<p>Returns: 0.940284064056996 0.940284064056996 0.940284064056996 . . . .<p>A old trick to actually get random data on SQL Server that I think has been replaced with newer functionality is:<p>SELECT ABS(CHECKSUM(NEWID())) FROM SomeTable<p>However if we move the NEWID.... stuff into the WHERE clause we end up switching to an index seek so back to being evaluated once.

My name is Alex, I&#x27;m the author of the original article on Percona Blog. I wanted to thank everyone for the great comments and discussion! Additional SQL examples are great as well.

FWIW, the proper what to get a random row is actually:<p><pre><code> SELECT column FROM table ORDER BY RANDOM() LIMIT 1</code></pre>