Bug when aggregate in select?

Question

Bug when aggregate in select?

michael.rosquist

Ten Centuries

Points: 1227
More actions
January 18, 2013 at 2:23 am

#268826

Try:
select case when 1=1 then 8 else sum(-10+1) end
from Master.dbo.Items
where 1=0
select case when 1=1 then 8 else abs(-10+1) end
from Master.dbo.Items
where 1=0
Isn't it a scary experience?
Is dangerous behavoiour I think, what do you think?
/m

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

Its possibly more dangerous to have a user defined objects in your Master database.

However, I dont think its an issue when you consider it logically the sum indacates that you are looking at returning a value, and if there is no record set that value would be NULL, however you over write that with the number 8.

However ABS works on the number but doesnt create a value, this is due to the way aggregates work.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

michael.rosquist Ten Centuries Points: 1227 More actions · Answer 2

I don't think it overwrites.

select case when 1=0 then 8 else sum(-10+1) end

from Master.dbo.spt_values

where 1=0

select case when 1=0 then 8 else abs(-10+1) end

from Master.dbo.spt_values

where 1=0

When aggregate it does not look at the where-clause.

When no aggregate it does look at the where-clause.

demonfox SSCertifiable Points: 6289 More actions · Answer 3

Jason-299789 (1/18/2013)
Its possibly more dangerous to have a user defined objects in your Master database.

Good one Jason !!! 😀 :w00t:

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

Michael,

Sorry I meant the way you wrote the query, will over write the NULL value, if you do

Select Sum(1)

From Master.sys.objects

where 1=0

You will get a NULL cell returned, however you force the overwrite of the cell value with the CASE WHERE 1=1 THEN 8.

So you will always get a number in place of the NULL.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

demonfox SSCertifiable Points: 6289 More actions · Answer 5

Jason-299789 (1/18/2013)
Its possibly more dangerous to have a user defined objects in your Master database.
However, I dont think its an issue when you consider it logically the sum indacates that you are looking at returning a value, and if there is no record set that value would be NULL, however you over write that with the number 8.
However ABS works on the number but doesnt create a value, this is due to the way aggregates work.

I don't understand , what are you looking for ?

but, I agree with Jason ;

try the result set :

select case when 1=0 then 8 else sum(-10+1) end

from Master.dbo.spt_values

--where 1=0

select case when 1=0 then 8 else abs(-10+1) end

from Master.dbo.spt_values

--where 1=0

You will see the difference.

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

With such findings, it is best to always mention the actual engine version on which you performed the test.

Select Serverproperty('ProductVersion') as ProductVersion

, Serverproperty('ProductLevel') as ProductLevel

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

davidandrews13 SSCertifiable Points: 6809 More actions · Answer 7

could someone explain what the original post is returning, and what is believed *should* be returned, as i dont understand.

when i run the code, it returns '8', which is what i would expect.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 8

IMO it should return no row at all because of the where clause condition 1 = 0

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Strip out the noise, and introduce another little statement.

SELECT [An Aggregate] = SUM(1)

FROM (SELECT n = 1) d

WHERE 1=0

SELECT [An Aggregate] = COUNT(*)

FROM (SELECT n = 1) d

WHERE 1=0

SELECT [Something] = 'anything'

FROM (SELECT n = 1) d

WHERE 1=0

Got it now?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden