Unstable query in SQL Server

Question

I am using SQL Server 2008-R2 Express Edition.

I wrote the query shown below to generate number sequences from 1 to @n. In testing it (purely in a query window), I found that if I significantly change the value of @n, I get incorrect results. Re-execution yields the same errors. However, if I open a new query window, the results are perfect.

Looking at the algorithm, it makes no sense to me as to why I should be getting unstable results (or even that a query can produce varying results against a fixed input).

DECLARE @n INT;

SET @n = 65536;

DECLARE @t TABLE (n INT NOT NULL PRIMARY KEY);

IF @n > 0 BEGIN
    DECLARE @r INT, @i INT, @l INT;

    SET @r = FLOOR(1.442695040888964 * LOG(@n));
    SET @i = 1;
    SET @l = 0;

    INSERT INTO @t (n) VALUES (1);

    WHILE @l < @r BEGIN
        INSERT INTO @t (n) SELECT n + @i FROM @t;

        SET @i = @i * 2;
        SET @l = @l + 1;
    END;

    INSERT INTO @t (n) SELECT TOP (@n - @i) n + @i FROM @t;
END;

--SELECT COUNT(1) FROM @t;

select * from @t

EDIT

Change the 65536 to 5000, execute, change back to 65536, and scroll down to say row 169,770. I get row 169770 = 40000. In a new window, it runs correctly.

EDIT2

Besides getting randomly correct/incorrect results, it appears something else is wrong. I now am getting consistently incorrect results for some numbers such as 655360.

What are you trying to actually do? There are many Number tables solutions out there? What values does it fail for? — gbn, Mar 06 '11 at 08:48
@gbn this started as a code golf exercise to create a number sequence. It works perfectly, unless I change @n dramatically. But, like I said, I can copy & paste the query to a new window, and I'll get 100% correct results. So it seems to me there is a bug that is not in my query. — IamIC, Mar 06 '11 at 08:51
It works perfectly for me because you haven't given any example numbers that fail. What I don't get is the use of LOG and FLOOR for a Tally table — gbn, Mar 06 '11 at 08:53
@gbn change the 65536 to 655360 and scroll down to say row 160,000. I get row 169770 = 40000. In a new window, it runs correctly. — IamIC, Mar 06 '11 at 08:56
@gbn the LOG & FLOOR is simply the formula for @R = TRUNC(LOG_2(@N)) — IamIC, Mar 06 '11 at 08:57
I use the log to work out the maximum number of times I can run the table doubling add. The balance is performed by the final insert. — IamIC, Mar 06 '11 at 08:58

score 2 · Accepted Answer · edited May 23 '17 at 12:19

2

Add an ORDER BY to the last statement.

There is no natural or default order in any table

Edit:

I attribute it the use of floating point numbers

I posted how to do a tally table an hour ago: Maximum recursion has been exhausted

edited May 23 '17 at 12:19

Community

1
1

answered Mar 06 '11 at 09:10

gbn

422,506
82
585
676

I tried putting an ORDER BY to the first select and temp. remarked out the last one. It didn't help. For @n = 655360, I get the following: row 4354 => n = 4354; row 4355 => n = 133731. Makes no sense. – IamIC Mar 06 '11 at 09:12
I even tried this, to no avail: INSERT INTO @t (n) SELECT t.n + @i FROM ( SELECT TOP (100) PERCENT n FROM @t ORDER BY n ) AS t; – IamIC Mar 06 '11 at 09:16
@gbn oh, you mean to the final select. Got you. – IamIC Mar 06 '11 at 09:24
@gbn I got it working 100%, thanks. It's several times faster than using recursion, too. Without digging more, I'm not clear on how to use your tally code to simply generate an ordered sequence. – IamIC Mar 06 '11 at 16:23
@gbn ok I figured it out. Yours is faster too ;) How the heck did you figure that code out? – IamIC Mar 06 '11 at 16:29
@gbn I get how your tally code works. Smart. I wanted to go that route but didn't know how. What I don't understand is your format, specifically your use of WITH. There is no CTE name or AS. The final query part just flows on, as if a CTE doesn't exist. What use of WITH is this? – IamIC Mar 06 '11 at 19:38
There are 7 CTEs (Pass0 to Tally) and the main query starts "SELECT TOP 1" – gbn Mar 06 '11 at 19:47

Unstable query in SQL Server

1 Answers1