3 ms·
Well, since random() doesn’t depend on anything, it could also just be evaluated once for the whole query and be treated like a constant. But really, this is si
by layer8 3y ago
Well, since random() doesn’t depend on anything, it could also just be evaluated once for the whole query and be treated like a constant. But really, this is simply a case of the language semantics being underspecified.
- tantalor 3y agohttps://xkcd.com/221/ https://xkcd.com/221/
- codeulike 3y agoin practice SQL engines will know whether functions are determinsitic or non-deterministic. So with random() it will know it needs to re-evauluate it all the time because its non-deterministic. with left(somecolumn,2) it knows its deterministic so will only re-evaluate it when somecolumn changes
- dspillett 3y ago> So with random() it will know it needs to re-evauluate it all the time because its non-deterministic. This is often not true. SQL Server for one, and I know it isn't the only common DB that behaves this way: SELECT RAND() AS NotSoRandom , RAND() AS NotSoRandomAgain FROM SomeTable results in: NotSoRandom NotSoRandomAgain 0.417713566427078 0.417713566427078 0.417713566427078 0.417713566427078 0.417713566427078 0.417713566427078 0.417713566427078 0.417713566427078 ... This often confuses people trying to select random rows (or all results in a random order) with ORDER BY RAND().
- codeulike 3y agoActually yes now you remind me, SQL Server doesn't recognise Rand() as non-determinstic but it does recognise some functions as being non-deterministic, e.g. if you use newid() you get a new value for every row
- somat 3y agoI don't think they should optimize based solely on table access. What the function does matters as well. Now the optimizer either does not know what the function does(it could be modifying rows, who knows?) or, in the specific case of random() it is intentionally different every single call. The only sane thing to do in this case is avoid moving it around trying to optimize it. Now if you could mark a function as "pure", that is, it's outputs are strictly dependent on it's inputs, the optimizer would be free to, well, optimize it's location. I think this is what postgres is trying to do with it's function volatility syntax(volatile, stable, immutable) one of the examples in the article, cockroachdb, has inherited this syntax. but they either drew a different conclusion as to what it means, or they ignore it.