Bizarre 'divide by zero' - no division happening!

  • I have a query that's run as part of a data import process that has suddenly broken on one server while working perfectly on another against the exact same import data. Both are running SQLServer 2008 R2.

    The query starts something like this:

    select

    i.ImportID,

    i.some_ID,

    @ReportDate,

    b.someotherID,

    b.yetanotherID,

    b.yep_an_id,

    b.lastID_i_promise,

    i.othervalue,

    b.I_Lied_ID,

    Asset,

    'some static value',

    i.Currency,

    Total * fx.rate,

    [01D] * ISNULL(fx.rate,0),

    [01W] * ISNULL(fx.rate,0),

    [02W] * ISNULL(fx.rate,0),

    [01M] * ISNULL(fx.rate,0),

    [02M] * ISNULL(fx.rate,0),

    [03M] * ISNULL(fx.rate,0),

    [04M] * ISNULL(fx.rate,0),

    [05M] * ISNULL(fx.rate,0),

    [06M] * ISNULL(fx.rate,0),

    [09M] * ISNULL(fx.rate,0),

    [01Y] * ISNULL(fx.rate,0),

    [18M] * ISNULL(fx.rate,0),

    [02Y] * ISNULL(fx.rate,0),

    [03Y] * ISNULL(fx.rate,0),

    [04Y] * ISNULL(fx.rate,0),

    [05Y] * ISNULL(fx.rate,0),

    [06Y] * ISNULL(fx.rate,0),

    [07Y] * ISNULL(fx.rate,0),

    [08Y] * ISNULL(fx.rate,0),

    [09Y] * ISNULL(fx.rate,0),

    [10Y] * ISNULL(fx.rate,0),

    [11Y] * ISNULL(fx.rate,0),

    [12Y] * ISNULL(fx.rate,0),

    [15Y] * ISNULL(fx.rate,0),

    [20Y] * ISNULL(fx.rate,0),

    [25Y] * ISNULL(fx.rate,0),

    [30Y] * ISNULL(fx.rate,0),

    [40Y] * ISNULL(fx.rate,0),

    [50Y] * ISNULL(fx.rate,0)

    from

    ... followed by a series of somewhat complex sub-queries which a) all work on one of the servers and b) all work by themselves on the "bad" server. The basic idea is to apply some criteria to move some values into time "buckets" and multiply by an exchange rate.

    Starting today, running this has been producing a divide by zero error. I thought it odd enough to get divide by 0 on a query that doesn't divide anything, but that's just the beginning. After much tinkering to try to find if one specific calculation was causing the problem, I discovered the following:

    Of the 42 columns returned by the query, commenting out any arbitrary combination of 14 of them will make the query work. Doesn't matter which ones I take out, as long as there are a maximum of 28 columns being returned. When I un-comment that 29th column, no matter which one - DIVIDE BY ZERO.

    Anyone have any clue as to what might actually be happening here?

  • If the query hasn't changed and has suddenly started to throw errors, then something else must have changed. What else changed on the server that isn't working that hasn't changed on the server that is working? Any tables get altered lately?

    If nothing has changed, please post DDL, sample data and the whole query so we have something to look at. At least then we'll have a chance of coming up with something.

  • If it's an import process, are you inserting rows on a table with a trigger that divides something?

  • In terms of structure, stored procedures, etc, the two databases are identical. I'm going to try copying the "good" database to the "bad" server & see if the problem still happens.

    I'd post more of the query, but the way things are run around here I'd probably have to clear it with the legal department before I posted more. :ermm:

  • laurie-789651 (8/5/2013)


    If it's an import process, are you inserting rows on a table with a trigger that divides something?

    The actual stored proc does do a table insert, but there are no triggers on the destination and the error can be reproduced without the insert code in place.

  • drlloyd2 (8/5/2013)


    Of the 42 columns returned by the query, commenting out any arbitrary combination of 14 of them will make the query work. Doesn't matter which ones I take out, as long as there are a maximum of 28 columns being returned. When I un-comment that 29th column, no matter which one - DIVIDE BY ZERO.

    This I can explain: when you change the number of columns in the result set, the execution plan changes, so that the intermediate result which causes the error is never the produced.

    As for why you get the error, I cannot of course say much as I have not see the full query.

    Any calls to user-defined functions in the query?

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland Sommarskog (8/5/2013)


    drlloyd2 (8/5/2013)


    This I can explain: when you change the number of columns in the result set, the execution plan changes, so that the intermediate result which causes the error is never the produced.

    As for why you get the error, I cannot of course say much as I have not see the full query.

    Any calls to user-defined functions in the query?

    I was thinking along the same lines as user-defined functions or division in a subquery or view. I'd not thought of the execution plan, though. I'm going to have to think that one through. Would a WITH RECOMPILE fix it if that's the problem?

    drlloyd2 - Can you post the full query or is that going to cause trouble with your employer? We'd like to help (it's a neat problem), but certainly don't want to cause problems for anyone at work.

Viewing 7 posts - 1 through 6 (of 6 total)

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