On Sun, 13 Sep 2015 10:46:21 +0200 Clemens Ladisch <clemens at ladisch.de> wrote:
> James K. Lowden wrote: > > select a, r from ( > > SELECT a, random() as r FROM a > > ) as R > > WHERE r <> r; > > > > will yield zero rows, every time. > > $ sqlite3 > SQLite version 3.8.12 2015-09-12 19:50:58 > ... > sqlite> create table a(a); > sqlite> insert into a values (1),(2),(3); > sqlite> select a, r from (SELECT a, random() as r FROM a) as R > sqlite> WHERE r <> r; > 1|-909199267849538533 > 2|8366540922678358399 > 3|-6124149463908475628 Oh, Jimminy Crickets. That's Just Wrong. Thank you for the correction viz SQLite. I hope we can look forward to seeing it fixed. R.r doesn't refer, semantically, to the RANDOM function. It refers to the product of RANDOM. Apparently, deep in the bowels of SQLite, the query evaluation logic doesn't recognize -- and deal correctly with -- nondeterministic functions. Afaik there are only 2: DATE(now) and RANDOM. And they've both caused problems. Deterministic functions are idempotent. No matter how often you call, say, ABS(x), you get the same result for the same x, every time The execution evaluation can invoke ABS as often as it likes; the worst that will happen is a little inefficiency. When it comes to nondeterministic functions, though, it matters a great deal how often they're called, else you get nonsensical answers like R.r not being equal to itself. --jkl
