SQL Server 7.0 won't subtract properly??!!

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Server 7.0 won't subtract properly??!!

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-12-13 : 17:19:24

I've just discovered that one of my ALTER TABLE calculated fields is delivering innacurate data due to what I thought was the proper sequence for addition/subtraction. Basically, I'm using this syntax

(5 + 1) - (5 + 1)

I expected that the above would return zero, but it is instead returning 2, suggesting it is marching left to right and ignoring the nested priority items. Multiplication and division work fine in this scenario, but subtraction does not. Ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-12-13 : 17:29:12

Maybe you should do something like this:

declare @a int
declare @b int

set @a=5+1
set @b=5+1

select @a-@b

Edited by - tduggan on 12/13/2002 17:30:15

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-13 : 19:33:40

Try
(5 + 1 - (5 + 1))

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-13 : 22:41:58

Can you post the exact context that the math came out wrong? I'd really like to see it. I find this very disturbing!

- Jeff

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-12-14 : 09:53:02

Valter,

Tried that. Didn't work, same result. SQL Server just goes left to right no matter how I nest the parens. The main routine is an ALTER TABLE routine that creates a computed field by adding a bunch of fields, then subtracting the result from another bunch of added fields, like so:

ALTER TABLE test_table

ADD (field1 + field2 + field3) - (field4 + field5 + field6) AS SubtractResult

The way I've written the statement above is the original syntax I was using when I discovered that SQL Server is evaluating the expression as follows:

field1 + field2 + field3 - field4 + field5 + field6

I tried the following nested parens but got same results:

ADD (field1 + field2 + field3 - (field4 + field5 + field6)) AS SubtractResult

ADD ((field1 + field2 + field3) - (field4 + field5 + field6)) AS SubtractResult

ADD ((field1 + field2 + field3) - field4 + field5 + field6) AS SubtractResult

...etc...nothing works. I'm bashing my head against the wall on this one. Again, multiplication and division work as expected. Data type for all fields in question is SMALLINT.

Also, I'm running this ALTER TABLE script from Query Analyzer on SQL Server 7.0.

thx

Edited by - steelkilt on 12/14/2002 10:04:28

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-14 : 10:13:22

Algebraically speaking:

(field1 + field2 + field3) - (field4 + field5 + field6)

is equivalent to:

field1 + field2 + field3 - field4 - field5 - field6

Why not just use that expression for your formula instead?

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-12-14 : 11:58:37

Rob!

Here's the rig I came up with and was preparing to post, when I saw your message:

(field1 + field2 + field3) + (-field4 + -field5 + -field6)

My mathematical reasoning is a bit muddled, but the above achieves the same result. However, I will use your solution since it's cleaner. Thx.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-14 : 12:37:49

I think it's fairly easy to come up alternate ways of calculating the expression until one works, but I think the fact that this is occurring at all is very disturbing.

You shouldn't have to use a "work-around" to calculate a valid algebraic expression for a product like SQL Server! Or worse yet, verify results of (A-B)+(C-D) by hand to make sure the software is adding up fields correctly! Yikes!

I'm just surprised I'm the only one who seems concerned about this...

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-12-14 : 14:04:17

Works fine when I try it (SQL Server 7 sp2 on NT 4.0)


CREATE TABLE CalcTest (
  field1 smallint,
  field2 smallint,
  field3 smallint,
  field4 smallint,
  field5 smallint,
  field6 smallint
)

INSERT INTO CalcTest VALUES (1,2,3,1,2,3)

ALTER TABLE CalcTest
ADD SubtractResult AS (field1 + field2 + field3) - (field4 + field5 + field6)

SELECT * FROM CalcTest

Returns


field1 field2 field3 field4 field5 field6 SubtractResult 
------ ------ ------ ------ ------ ------ -------------- 
1      2      3      1      2      3      0

as expected. Or am I misunderstanding?

Edited by - Arnold Fribble on 12/14/2002 14:04:47

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-14 : 14:07:07

CREATE TABLE #Test(
field1 int,
field2 int,
field3 int,
field4 int,
field5 int,
field6 int
)

INSERT INTO #Test(field1,field2,field3,field4,field5,field6)
VALUES (1,2,3,4,5,6)

INSERT INTO #Test(field1,field2,field3,field4,field5,field6)
VALUES (2,3,4,5,6,7)

ALTER TABLE #Test
ADD SubtractResult AS (field1 + field2 + field3) - (field4 + field5 + field6)

SELECT * FROM #Test

DROP TABLE #Test

Based on Arnolds test on sql7 and mine on sql2k I think everyone can be at rest it was just a syntax error.

Edited by - ValterBorges on 12/14/2002 14:20:48

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-12-16 : 09:10:09

Yikes!

I ran Valter's script on my machine and here are the results:

field1 field2 field3 field4 field5 field6 SubtractResult
------ ------ ------ ------ ------ ------ --------------
1 2 3 1 2 3 10

Here are the stats on my server:

Compaq Proliant ML370
MS Windows 2000 Server
IIS 5.0
SQL Server 7.0 SP1 (build 7.00.694)

Anyone know if this is a documented bug and if applying SP2 might fix it?

thx

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-16 : 09:18:19

I would say there is definitely something wrong.

Using the following values here are another
INSERT INTO #Test(field1,field2,field3,field4,field5,field6)
VALUES (1,2,3,4,5,6)

INSERT INTO #Test(field1,field2,field3,field4,field5,field6)
VALUES (2,3,4,5,6,7)

SQL 2000 SP2 WIN2k SP3

field1 field2 field3 field4 field5 field6 SubtractResult
1 2 3 4 5 6 -9
2 3 4 5 6 7 -9

SQL 2000 SP2 NT4.0 SP6

field1 field2 field3 field4 field5 field6 SubtractResult
1 2 3 4 5 6 -9
2 3 4 5 6 7 -9

Found a 7.0 machine
Here's what I got
field1 field2 field3 field4 field5 field6 SubtractResult
1 2 3 4 5 6 13
2 3 4 5 6 7 17

Uhhhmmmm...

Edited by - ValterBorges on 12/16/2002 09:22:05

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-16 : 09:21:35

I couldn't find anything in the fix lists for SQL 7.0 service packs that specifically addressed computed columns, but it might be hidden in there somewhere. If you are going to apply a service pack, go with SP4. You'll get all of the fixes then, including everything in SP2 and SP3.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-16 : 09:26:32

Tested robs idea of using the distributive property and that seems to work.

ALTER TABLE #Test
ADD SubtractResult AS field1 + field2 + field3 - field4 - field5 - field6

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-12-16 : 09:40:00

Yes, but why is SQL 7.0 sp1 machine taking this:

(1 + 2 + 3) - (4 + 5 + 6)

and treating it like this:

1 + 2 + 3 - 4 + 5 + 6

I'm preparing to install SP4. I'll let you know if this changes anything.

thx

Edited by - steelkilt on 12/16/2002 09:56:06

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-16 : 10:29:45

v7 sp1 doesn't really work as a multi-user relational database - gives wrong results - doesn't update reliably.
sp2 slows things down a lot but at least it does work (in most cases).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-12-16 : 13:02:27

nr,

that's a pretty stinging indictment of v7 sp1.

anyway, I'm going to sp4. hope it fixes this.

Edited by - steelkilt on 12/17/2002 11:11:17

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-12-16 : 15:35:07

Installation of SP4 fixed the problem.

thx.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-17 : 11:40:11

quote:

nr,

that's a pretty stinging indictment of v7 sp1.

Yep but unfortunately true.
Just like v6 sp2.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lee-Z
Starting Member

7 Posts

Posted - 2002-12-20 : 10:25:55

Fun thing is:

it works correctly in a clean SQL7 (no servicepacks)

(1+2+3)-(1+2+3)
in query analyzer returns 0

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-20 : 10:27:39

I don't think it's a query problem, but only specific to a calculated column in an ALTER TABLE statement.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources