On 2015-08-27 03:03 AM, James K. Lowden wrote: > On Wed, 26 Aug 2015 13:39:09 +0100 > Simon Slavin <slavins at bigfraud.org> wrote: > >> On 26 Aug 2015, at 11:27am, tonyp at acm.org wrote: >> >>> Plus, it apparently recognizes if the random() expression in the >>> ORDER BY is the same as the SELECT one and again sort correctly >>> (without re-evaluating) and without needing an alias. >> Ah, but I would call /that/ a bug ! > I think I can convince you Postgres is right and SQLite is not. :-) > I'll give you 4 reasons: > > 1. Theory > 2. Authority > 3. Atomicity > 4. Consistency
Thanks for the informative post, and I do agree with it except for specifically the Random() function. Random() is not a function you use when you expect reproducible results. The very nature of the word suggests the opposite. Further to this, among other posts there seem to be some confusion about the purpose of aliasing - especially since people think in programming terms. An alias isn't a variable or an array, it doesn't magically hold a state - it simply is a nick-name for a function. This statement: SELECT Random() AS rr ORDER BY rr cannot ever be functionally different to: SELECT Random() ORDER BY Random() To use programming terms: rr is not a variable - it does not "hold" a value for the Random() function, it simply is a alphanumeric "pointer" to the the Random() function (in this case). I wouldn't go so far as to call the behavior in Postgres a "bug" but just an implementation detail - I mean the values do end up being random, do they not? They just chose to make the sorting possible by keeping the state information - a good thing since it produces what most people (especially programmers) seem to expect, but in no way (I think) an absolute adherence to a standard. By James' standard, the Query: SELECT Random() FROM t; -- imagine table t hold 10 tuples Should provide: Random() -------------- 76398763987097830 76398763987097830 76398763987097830 76398763987097830 76398763987097830 76398763987097830 76398763987097830 76398763987097830 76398763987097830 76398763987097830 And I happen to agree if we do apply strict relational theory. The thing is though, when people invoke the Random() function, they really do not wish to see the above (as opposed to when the reference the Now() or similar functions, the above would be exactly what they wish to see). So SQLite, like PostGres, offer an alternate implementation detail in this regard. The debate on whether either is absolutely "right" or "wrong" is just a silly one. Postgres has the luxury of not being "Lite" nor needing to run on a phone, so the added code and memory to deal with saving a state of randomness for an entire result-set is not really problematic in Postgres, and not really in the design goals of SQLite. FWIW, Ryan
