Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: Devide by Zero, whats wrong here ???

    Hello, my SQL Server behaves strangely

    this works:

    Code:
    SELECT count(*)
    FROM dbo.vw_object noexpand
    WHERE 
    SPACE> 0
    AND
    (Y=2007 and Q BETWEEN 1 AND 4)
    AND
    PRICE/SPACE>2
    this doesn't, gives me a "Divide by Zero Error"

    Code:
    SELECT count(*)
    FROM dbo.vw_object noexpand
    WHERE 
    SPACE> 0
    AND
    (Y=2007 AND Q in (1,2,3,4))
    AND
    PRICE/SPACE>2
    Any idea?

  2. #2
    Join Date
    Mar 2007
    Posts
    97
    Nevermind, I just found found out, that PRICE/NULLIF(SPACE, 0) >2 solves the problem.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I rebuilt your scenario on 9.0.3068 and it works with no prob. Of course, I made assumptions that may be incorrect, - the view is a simple select * from the base table, and the data types for fields involved are all int.

    BTW, in your syntax NOEXPAND is not a hint but rather an alias for the view. At a minimum you need to put it in parentheses.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by silas
    Nevermind, I just found found out, that PRICE/NULLIF(SPACE, 0) >2 solves the problem.
    That solves it but I don't see how it explains it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2007
    Posts
    97
    Quote Originally Posted by rdjabarov
    BTW, in your syntax NOEXPAND is not a hint but rather an alias for the view. At a minimum you need to put it in parentheses.
    Thanks for the hint, but "FROM dbo.vw_object (noexpand)" gives me an Error. I think this is because I don't have the "ultimate" Version of SQL Server.

    The Divide by zero only appears, when I use a view. It's a very simple one, but has 3 joins. That must be the reason for the different behaviour.

    Nevertheless, I swear, all I do is change from (Y=2007 and Q BETWEEN 1 AND 4) to (Y=2007 AND Q in (1,2,3,4)) to get that error. Real strange!

    EDIT: Space and PRICE are both float(53)
    Last edited by silas; 07-14-08 at 11:45.

  6. #6
    Join Date
    Sep 2005
    Posts
    161
    Just a hunch, but try it without the unnecessary parenthesis around line 6. I think the optimizer is trying to evaluate PRICE/SPACE before it evaluates SPACE>0.

    Code:
    SELECT count(*)
    FROM dbo.vw_object noexpand
    WHERE 
    SPACE> 0
    AND
    Y=2007 AND Q in (1,2,3,4)
    AND
    PRICE/SPACE>2

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by silas
    EDIT: Space and PRICE are both float(53)
    What data type is Q?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2007
    Posts
    97
    Quote Originally Posted by cascred
    I think the optimizer ...
    No, that doesnt do to trick. The brackets are there, because of my original query ((Y=2007 and Q=1) OR (Y=2007 and Q=2)). For a cleaner look, I used the IN statement, that just behaves the same as the OR -> ERROR

  9. #9
    Join Date
    Mar 2007
    Posts
    97
    Quote Originally Posted by pootle flump
    What data type is Q?
    Y is Smallint, Q is Tinyint

  10. #10
    Join Date
    Sep 2005
    Posts
    161
    Can you post the query plan?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by cascred
    Can you post the query plan?
    Lol - beat me to it. I'm thinking the same as you I think
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Sep 2005
    Posts
    161

    At first I thought. Now I have convinced myself and just need proof. It is not evaluating in the order that the query is written. Normally we don't are what order they are evaluated.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ya - going from BETWEEN to IN is changing the order of evaluation I bets. I wonder if there is a composite index on (Y, Q)....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Mar 2007
    Posts
    97
    Quote Originally Posted by cascred
    Can you post the query plan?
    Yes, I will in about 20 minutes.

  15. #15
    Join Date
    Sep 2005
    Posts
    161
    Assuming we're right about evaluation order, this query will work. This will evaluate to null when SPACE=0, which will not be >2.

    Code:
    SELECT count(*)
    FROM dbo.vw_object noexpand
    WHERE 
    Y=2007 AND Q in (1,2,3,4)
    AND
    PRICE/(CASE WHEN SPACE = 0 THEN NULL ELSE SPACE END)>2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •