>I can see both sides of this debate, whether or not random() should be >evaluated twice in this context: > >select random() from blah order by random()
There are 2 distinct and volontary function invokations, so I don't see how SQL<whatever> engine would decide not to perform the second call. >So let me pose a question. What should the following query generate? > >select random(), random() from blah order by random() Ditto, but with now 3 function direct invokations, hence in the case of random(), 3 probably distinct values. That a more recent post shows that SQLite issues an error about ambiguousness of random() is even more worrisome! This is completely different from refering to an *-already computed value-* like in select random() rr where rr < 0 or rr >= 0 group by rr order by rr Here: only 1 function invokation and multiple references to an already computed column. What must occur here is (paraphrased): create temp table tmp as select random() rr; select * from tmp where rr < 0 or rr >= 0 group by rr order by rr; -- providing the result set drop table tmp; Now let's broaden the picture: if rr is in fact a large, complex subquery, taking 45 hours to compute, then should "order by rr" rerun the subquery for every row comparison? Sounds ridiculous to me. I don't know SQLite code enough to start digging but I doubt that having, where, group by and order clauses to have any right and need to introspection into the column definition and private interpretation of what's there, lest re-running what produced them. This is none of their business.
