Bitmap Check Fails? - 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 2000 Forums

SQL Server Development (2000)

Bitmap Check Fails?

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-22 : 08:36:49

SELECT Bit, Value,
CASE WHEN FLOOR(LOG(Value)/LOG(2)) = (LOG(Value)/LOG(2)) THEN 1 ELSE 0 END
FROM (SELECT TOP 100 PERCENT CAST(CAST(digit_01.Digit AS CHAR(1)) + CAST(digit_02.Digit AS CHAR(1)) AS BIGINT) AS [Bit],
POWER(CAST(2 AS BIGINT), CAST(CAST(digit_01.Digit AS CHAR(1)) + CAST(digit_02.Digit AS CHAR(1)) AS BIGINT)) AS [Value]
FROM (SELECT 0 AS Digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS digit_01,
(SELECT 0 AS Digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS digit_02
WHERE CAST(CAST(digit_01.Digit AS CHAR(1)) + CAST(digit_02.Digit AS CHAR(1)) AS BIGINT) < 63
ORDER BY CAST(CAST(digit_01.Digit AS CHAR(1)) + CAST(digit_02.Digit AS CHAR(1)) AS BIGINT)) AS Bitmap

so what it does is generate values for each 2^bit and then checks to make sure that the value it outputs can be used in a bitmap ... but if you run it .. it looks like it is not working for some values? any idea why? shouldn't it? is there a bug in my expression? akc ... madness consumes

-- note
I thought maybe the POWER() function was messed up but the CALC program gives the same EXACT value... but for some reason the expression that checks to see if the value given can be used it flawed somehow? could the LOG function be flawed? check the output of the first value compared to the second and you'll see why the check fails ... there is a value .000000000000004 or .000000000000007 added to each log(Value)/Log(2) output that fails ... hmmm

Edited by - onamuji on 01/22/2003 08:45:21

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-01-22 : 08:51:26

the only thing I can think of is 'precission'

I ran your query with "FLOOR(LOG(Value)/LOG(2)), (LOG(Value)/LOG(2)), LOG(Value), LOG(2)," also included in the main select statement.

and I converted your BIGINT to varchar....(I'm running SQL7...and I think bigint is a feature of SQL2K)

doing so....shows me that "LOG(Value)/LOG(2)" does NOT give a whole number for the alleged flawed values....(ie those with 0 in the CASE statement)

maybe your cross-checking mechanism is at fault....and the results are actually aok???

The log of the value for bit = 39 (ie ln(549755813888)) using the 'win NT v4.0 calculator program' is "27.032740041837867067272052736869"
whereas using SQL QA for SQLv7 I get..."27.032740041837869"

I wonder if the problem is related to this? The differences in the last digit may be significant for this situation. Maybe there is a flaw/difference in the formula used between both implementations? and maybe the difference is the key.

Edited by - AndrewMurphy on 01/22/2003 09:14:58

Edited by - AndrewMurphy on 01/22/2003 09:18:50

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-22 : 09:33:02

Yeah, the loss of precision will contribute to a rounding error that would grow since it's being further multiplied/divided.

BTW, why not use this:

CAST(digit_01.Digit*10+digit_02.Digit AS BIGINT)

instead of:

CAST(CAST(digit_01.Digit AS CHAR(1)) + CAST(digit_02.Digit AS CHAR(1)) AS BIGINT)

And why aren't you using the bitwise operators to test your bitmaps?

SELECT Bit, Value,
Bit | Value AS [OR], Bit ^ Value AS [XOR], Bit & Value AS [AND], ~ Value AS [NOT],
CASE WHEN FLOOR(LOG(Value)/LOG(2)) = (LOG(Value)/LOG(2)) THEN 1 ELSE 0 END
FROM (SELECT TOP 100 PERCENT CAST(digit_01.Digit*10+digit_02.Digit AS BIGINT) AS [Bit],
POWER(CAST(2 AS BIGINT), CAST(digit_01.Digit*10+digit_02.Digit AS BIGINT)) AS [Value]
FROM
(SELECT 0 AS Digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS digit_01,
(SELECT 0 AS Digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS digit_02
WHERE CAST(digit_01.Digit*10+digit_02.Digit AS BIGINT) < 63
ORDER BY CAST(digit_01.Digit*10+digit_02.Digit AS BIGINT)) AS Bitmap

If you wanted to see if the 14th bit is flagged:

SELECT Value & POWER(2, 14)

Or if you just want a 1 or zero:

SELECT SIGN(Value & POWER(2, 14))

That way you don't need to use LOG or FLOOR and you won't have rounding problems.

Edited by - robvolk on 01/22/2003 09:37:15

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-22 : 11:18:57

it was a CHECK statement that I once upon a time used (provided by rrb)

CHECK (Value > 0 AND (FLOOR(LOG(Value)/LOG(2)) = (LOG(Value)/LOG(2))))

I wanted to make sure only that value was a single bit set to on...

Edited by - onamuji on 01/22/2003 11:19:27

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources