Incorrect Query? - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Incorrect Query?

Author

Topic

puow
Starting Member

2 Posts

Posted - 2011-06-01 : 07:00:29

Hi,

I'm using SQLServer 2008 and I want to get somebody else's opinion on this issues.

I have the following SQL query:

   
         select T1.value1
                ,
                T1.value2
           from T1
          where not exists (select 1 from T2 where T2.id = T1.id and T2.year = T1.year)
            and T1.batch = 163
       order by T1.value2 asc

This returns the following results:


313.000000000000	486.628500000000
380.000000000000	590.795000000000
701.900000000000	1091.260600000000
...

However if I modify the query thus:


         select T1.value1
                /
                T1.value2

I get:


Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

Now if I remove the not exists from the original query the zero in the value2 column shows up. What appears to be happening is the divide is being applied before the filtering of the where clause.

Is this the correct behaviour? I've not stumbled upon this issue before or is there no guarantee in the order that these things happen and this is a side effect of an optimisation?

Is this possible in DB2/Oracle and MySQL or a SQLServer specific issue?

Thoughts opinions gratefully welcomed.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-01 : 07:19:03

Yes, that is a behaviour that I know.
Workaround:
select T1.value1 / nullif(T1.value2,0.0)

No, you're never too old to Yak'n'Roll if you're too young to die.

lappin
Posting Yak Master

182 Posts

Posted - 2011-06-01 : 07:21:40

You could add a case around the division:
select Case when t1.value2 <> '0' then (T1.value1/T1.value2) else null end as Result

sorry too late with my reply.

puow
Starting Member

2 Posts

Posted - 2011-06-01 : 07:24:02

adding:


and T1.value2 > 0

fixes the problem, I'm more curious about what is going on.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-01 : 07:30:13

This should not be happening, because logically, SELECT comes after the WHERE clause is applied. So it has to be something else. My test case here:


CREATE TABLE #tmp1(id1 INT, id2 INT);
CREATE TABLE #tmp2(id1 INT, id2 INT);

INSERT INTO #tmp1 VALUES (1,0);
INSERT INTO #tmp1 VALUES (4,2);
INSERT INTO #tmp2 VALUES (1,0);

SELECT
   a.id1/a.id2
FROM
   #tmp1 a
WHERE
   NOT EXISTS (SELECT * FROM #tmp2 b WHERE b.id1 = a.id1 AND b.id2 = a.id2);
   
DROP TABLE #tmp1;
DROP TABLE #tmp2;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-01 : 07:31:06

Ok Fred, now we are even, but I would have won if I was not distracted by real work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources