Simon Slavin <slavins at bigfraud.org> wrote: > On 18 Aug 2015, at 1:32am, Simon Davies <simon.james.davies at gmail.com> > wrote: > >> sqlite> SELECT r FROM (SELECT random() AS r FROM myTable) ORDER BY r DESC; >> -6629212185178073901 >> -5293473521544706766 >> 2649466971390864878 >> -6185422953036640443 >> 1855956853707028764 > > Eek. Sorry, I should have tried it before posting. That's what this > whole thread is about, isn't it. And I suppose it's another sign that > I should be using WITH instead. > > Thanks for the heads-up, Simon.
... and then, at some wonderful moment, sqlite devs will implement query flattening for CTE (like they did for subquery above), and you'll be in square one. (Or, maybe, they will finally implement "common subexpression elimination", and original query will suddenly work again). It is not good idea to depend on undocumented behavior.
