March 12, 2011 at 1:36 am
Years later and the question is still broken... 😉
August 11, 2011 at 1:49 pm
UMG Developer (3/12/2011)
Years later and the question is still broken... 😉
Yes it is. You have to wonder where was Steve? He has stepped in for questions that were better for this.
Like the one BitBucket posted yesterday. Hrmm three years later still has the wrong idea about QOTD.
August 11, 2011 at 3:55 pm
SanDroid (8/11/2011)
UMG Developer (3/12/2011)
Years later and the question is still broken... 😉Yes it is. You have to wonder where was Steve? He has stepped in for questions that were better for this.
Like the one BitBucket posted yesterday. Hrmm three years later still has the wrong idea about QOTD.
Completely broken.
Since the optimiser may choose different evaluation techniques dependent on available resources for a given query, causing addition operations in the calculation of a SUM aggregate, the argument presented about slightly different reusults when float is used applies to two instances of the same query - so by the logic of this QoTD a query is not even the same as itself.
And why bring floats (and small differences) into the argument? Is this an assert the nonsense (to perpetuate the myth if you want to express it that way) that arithmetic using exact numerics is not subject to dependecy on evaluation order while floats are? Lets take int, or numeric(p,s), or money as the datatype and worry only about enormous differences, not small ones - and we'll still find that a query isn't equivalent to itself since the evaluation order can change depending on (environmental) factors other than the data, and the computation of an aggregate may produce a valid result or produce arithmetic overflow for identical data but a different evaluation order. For example is we want the sum of 20000 numbers of which 10000 have the values {power(2,30)-i: i in 1..10000} and the other 10000 have the values {i-power(2,30): i in 1..10000} using the int datatype; the result is very obviously order dependent) - a very small proportion of possible evaluation orders will deliver 0, all other evaluation orders will deliver an error.
Tom
August 12, 2011 at 6:24 am
Tom.Thomson (8/11/2011)
SanDroid (8/11/2011)
UMG Developer (3/12/2011)
Years later and the question is still broken... 😉Yes it is. You have to wonder where was Steve? He has stepped in for questions that were better for this.
Like the one BitBucket posted yesterday. Hrmm three years later still has the wrong idea about QOTD.
Completely broken.
Since the optimiser may choose different evaluation techniques dependent on available resources for a given query, causing addition operations in the calculation of a SUM aggregate, the argument presented about slightly different reusults when float is used applies to two instances of the same query - so by the logic of this QoTD a query is not even the same as itself.
And why bring floats (and small differences) into the argument? Is this an assert the nonsense (to perpetuate the myth if you want to express it that way) that arithmetic using exact numerics is not subject to dependecy on evaluation order while floats are? Lets take int, or numeric(p,s), or money as the datatype and worry only about enormous differences, not small ones - and we'll still find that a query isn't equivalent to itself since the evaluation order can change depending on (environmental) factors other than the data, and the computation of an aggregate may produce a valid result or produce arithmetic overflow for identical data but a different evaluation order. For example is we want the sum of 20000 numbers of which 10000 have the values {power(2,30)-i: i in 1..10000} and the other 10000 have the values {i-power(2,30): i in 1..10000} using the int datatype; the result is very obviously order dependent) - a very small proportion of possible evaluation orders will deliver 0, all other evaluation orders will deliver an error.
Tom, your math is impecable and correct as always. To be exact though, I meant broken in another way. As in unable to admint when you have made a mistake, and take mesures to correct the ones you have made. Have a good Friday and Peace to you. 😎
November 9, 2011 at 12:24 am
L' Eomot Inversé (4/17/2010)
GSquared (9/23/2008)
I know others have already objected on this one, but I decided to post anyway.ditto. I won't post the code I put together after blinking, with boggled mind, at the answer, because apart from making customers ints instead of strings and sales ints instead of money (is it the number of vacuum cleaners sold, or the sales revenue) and using different numbers it's the same as yours.
I have to disagree with the answer given, based on actual tests. I also tested it with the same test, using float instead of money for the sales data type, and still got the same results from both queries.
I think the person who wrote the question mistook the difference between "is the code the same" and "are floating point operations deterministic".
I think you've got that slightly wrong: the FP operations are indeed deterministic on a particular machine, but a string of such operations is not order-independent. Or are T-SQL floating point operations really indeterminate? - that would be quite startling! (Anyway, it can be argued that the result of calculation A is the same as the result of calculation B provided each is within the limt of the other's rounding error, so those apparent differences don't count. )
May be a 3rd answer like floating point operations deterministic would helped. Moreover, the datatype of the sales column or the sample DB used is not specified.
Viewing 5 posts - 46 through 49 (of 49 total)
You must be logged in to reply to this topic. Login to reply