At 21:11 27/08/2015, you wrote: > > 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. > >Agreed, though I'm pretty sure I've read messages in this thread at >advocate the same function should return the same value when called >multiple times in a single select statement.
While this does make sense for datetime functions, it doesn't really prove helpful with especially random(), but it turns to nonsense for generic user-defined function as long as SQL doesn't have the faintest clue whet it does. That means external function, not TSQL or friends. >Using sqlfiddle.com I'm testing the following: > >create table blah(a int); >insert into blah values(1),(2),(3); >select rand(), rand() as b from blah order by b; > >where rand() is whatever random number function the platform in question >provides. Various engines may find it clever to treat the engine's rand() in a particular way. But the real question is: how do they process user-defined external function fct()? >SQL Server 2008: same row three times; rand() is computed at the beginning >of the query and cached. Which makes for decidedly non-random numbers and >doesn't seem very useful IMO. Don't trick me into commenting microsoftisms ;-) >MySQL 5.6 returns three different rows, and it seems to be sorted on >b. Six >invocations of the query always resulted in the set being sorted on b, so >I'm going to assume it worked as expected without generating a third >random >number. >PostgreSQL 9.3: same result as MySQL 5.6. > >Oracle 11g R2: same result as MySQL 5.6. Sounds logical. >I think it still comes back to my earlier comment: Would changing it to >behave more like the most common / expected outcome above be a breaking >change? Looks like "How many zillion devices are going to misbehave if this is fixed?" vs. "Now developpers will be certain that such constructs don't produce non-sensical results any more and will stop using application-level kludges instead". I would give the second possibility a chance because the issue seem to have previously surfaced only about datetime functions and indeed the construct is not the most common. The mere fact that PG, Oracle and MySQL converge on this point makes that the most sensible thing to do. But who am I?
