Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
duey
Starting Member
1 Post |
 Posted - 2004-05-11 : 13:56:14
|
| I have encountered a very annoying issue probably concerning type conversion.Scenario:MS SQL Server Standard Edition (v8.00.760 (SP3))OS: MS Windows NT 5.0 (2195)Here is a simplified version of the original problem with constant expressions.The only difference between the first two queries below is the number of `*1´s in the third line of each query.The only difference between the second and the third queries is that one of the `*1´s is moved to the end of the whole expression in the third query..Evidently, the three queries should return the same result: 0.010000.However, they return 0.010000, 0.000000 and 0.010000 respectively. (The second is apparently incorrect.)select cast(1 as numeric(7,6)) *1 *1 *1 *1 *1 *1 *1 *1 *1 *1 ----- 10 TIMES `*1´ * cast(0.000001 as numeric(7,7)) * cast(0.000001 as numeric(7,7)) * 100000*100000/* Result: 0.010000 */select cast(1 as numeric(7,6)) *1 *1 *1 *1 *1 *1 *1 *1 *1 *1 *1 ----- 11 TIMES `*1´ * cast(0.000001 as numeric(7,7)) * cast(0.000001 as numeric(7,7)) * 100000*100000/* Result: 0.000000 */select cast(1 as numeric(7,6)) *1 *1 *1 *1 *1 *1 *1 *1 *1 *1 --<---- MOVED FROM HERE * cast(0.000001 as numeric(7,7)) * cast(0.000001 as numeric(7,7)) * 100000*100000 *1 --<-----------------------MOVED HERE/* Result: 0.010000 */The same applies if I change the 1's to 1.0000000000's and the 100000's to 100000.000000000's.Any suggestions please why the second query returns the wrong result and how to circumvent the problem?Thanks in advance. |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-05-11 : 15:27:29
|
| Really, Really ODD!I wrote these 2 queries to follow the results step by step (bar a few of the 1's in the middle) and you can see where it starts going wrong.This would indicate that SQL Server is using a predefined amount of space to perform the calculations and because there are decimals involved - Those 1's are treated as decimals too (Thereby using up valueable decimal place holders)Study the results of these 2 queries - to see what I mean.The first is from your query number 2and the second is from your query number 3--**************************************************************************select cast(1 as numeric(7,6)), cast(1 as numeric(7,6)) *1 , cast(1 as numeric(7,6)) *1 * 1,cast(1 as numeric(7,6)) *1 * 1 * 1, cast(1 as numeric(7,6)) *1 * 1 * 1 * 1,cast(1 as numeric(7,6)) *1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1,cast(1 as numeric(7,6)) *1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1,cast(1 as numeric(7,6)) *1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * cast(0.000001 as numeric(7,7)),cast(1 as numeric(7,6)) *1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * cast(0.000001 as numeric(7,7)) * cast(0.000001 as numeric(7,7)),cast(1 as numeric(7,6)) *1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * cast(0.000001 as numeric(7,7)) * cast(0.000001 as numeric(7,7)) * 100000,cast(1 as numeric(7,6)) *1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * cast(0.000001 as numeric(7,7)) * cast(0.000001 as numeric(7,7)) * 100000 * 100000select cast(1 as numeric(7,6)), cast(1 as numeric(7,6)) *1 , cast(1 as numeric(7,6)) *1 * 1,cast(1 as numeric(7,6)) *1 * 1 * 1, cast(1 as numeric(7,6)) *1 * 1 * 1 * 1,cast(1 as numeric(7,6)) *1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1,cast(1 as numeric(7,6)) *1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * cast(0.000001 as numeric(7,7)),cast(1 as numeric(7,6)) *1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * cast(0.000001 as numeric(7,7)) * cast(0.000001 as numeric(7,7)),cast(1 as numeric(7,6)) *1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * cast(0.000001 as numeric(7,7)) * cast(0.000001 as numeric(7,7)) * 100000,cast(1 as numeric(7,6)) *1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * cast(0.000001 as numeric(7,7)) * cast(0.000001 as numeric(7,7)) * 100000 * 100000,cast(1 as numeric(7,6)) *1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * 1 * cast(0.000001 as numeric(7,7)) * cast(0.000001 as numeric(7,7)) * 100000 * 100000 * 1--**********************************************Duane. |
 |
|
|
|
|
|
|
|
numeric type conversion issue