Joe Wilson wrote: > > What do other databases return for the types of SQL queries below? > > SELECT random(1) AS func FROM test ORDER BY func; > SELECT random() AS func FROM test WHERE func > 10; > > MS Access appears to assume all functions called with the same > arguments are constant and returns the same result for every row: > > select rnd() from test; > > 0.5795186162 > 0.5795186162 > 0.5795186162 > 0.5795186162 > 0.5795186162 > > SQLite is apparently the other extreme - it assumes that each call a > function can potentially yield a different result each time. >
If I may be so blunt and refer to Fortran and C for a comparison: - the random function in C has no arguments, it will produce a different result on every call (within the limits of the random number generator that is used). Of course from a mathematical point of view this is a monstrosity ;). Functions should return the same values given the same arguments. - functions without arguments in Fortran are presumed to return the same value. The compiler may decide to cache that value and _always_ return the same value. This is the reason in Fortran (90, ...) random numbers are generated via a subroutine, instead of a function. Apart from this distinction, you can also encounter the situation where one platform (compiler, OS, hardware) always initialises the random number generator with the same seed and another will use a seed derived from, say, the system clock. I know too little of SQL to say that the standard defines the random function in this way or that, but these are the variations I have seen and my guess is the standard does not prescribe anything about it. Regards, Arjen
