cte using POWER and Numbers table causes arithmetic overflow

Question

Post reply

cte using POWER and Numbers table causes arithmetic overflow

jcdenmark

Valued Member

Points: 65
More actions
March 13, 2025 at 4:20 pm

#4558850
could someone explain why my cte using a numbers table causes an overflow ?
my "numbers" table is exactly that - only a single column of numbers, but there are 4096 rows, 4096 int numbers, and I can
only think using a CTE like i have here causes all the rows to be retrieved...
```
declare @status as int=83457678; 
;with nums(num, 2power_Num, bitcheck)
as
(
SELECT Num, POWER(2, Num), (@status & POWER(2,Num))
FROM dbo.Numbers WHERE Num<30
)
SELECT MAX(Bitcheck) FROM nums
***INCLUDING THE FOLLOWING WHERE CLAUSE CAUSES THE OVERFLOW ERROR
WHERE bitcheck>0
```
this is really only for my understanding as to why I can't use a CTE for something like this...thx..

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

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

Phil Parkin SSC Guru Points: 247094 More actions · Answer 1

Phil Parkin

SSC Guru

Points: 247094

March 13, 2025 at 4:21 pm

#4558851

Can you post your code?

jcdenmark Valued Member Points: 65 More actions · Answer 2

I thought I did....i can see it anyway

declare @status as int=83457678;

;with nums(num, 2power_Num, bitcheck)

as

(

SELECT Num, POWER(2, Num), (@status & POWER(2,Num))

FROM dbo.Numbers WHERE Num<30

---get only numbers 0-29

)

SELECT MAX(Bitcheck) FROM nums

***INCLUDING THE FOLLOWING WHERE CLAUSE CAUSES THE OVERFLOW ERROR

WHERE bitcheck>0

Jonathan AC Roberts SSCoach Points: 18396 More actions · Answer 3

I just tried this and it works

DROP TABLE IF EXISTS #Numbers
-- Create a temporary table
CREATE TABLE #Numbers (
    Num INT 
);

-- Populate the temporary table with numbers from 0 to 30
INSERT INTO #Numbers (Num)
SELECT TOP (4000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM master.dbo.spt_values a, master.dbo.spt_values b;

-- Declare the status variable
DECLARE @status AS INT = 83457678; 

-- Use a Common Table Expression (CTE) to perform the bitwise operation
;WITH nums AS
(
    SELECT Num, 
           POWER(2, Num) AS TwoPower_Num, 
           (@status & POWER(2, Num)) AS Bitcheck
    FROM #Numbers 
    WHERE Num < 30
)
SELECT MAX(Bitcheck) 
FROM nums
WHERE bitcheck>0;

jcdenmark Valued Member Points: 65 More actions · Answer 4

Sorry, I should have been more specific; the reason I don’t use a temp table is the CTE is in an inline TVF which takes @status as a parameter; I’ve done what you suggested in a multi statement TVF…I just wondered why I get the overflow in the inline TVF when I use a physical numbers table. Thanks though for your input

ScottPletcher SSC Guru Points: 101170 More actions · Answer 5

Exact code works for me in SQL 2022.

Maybe use bigint rather than int!?:

declare @status as bigint=83457678; 
;with nums(num, [2power_Num], bitcheck)
as
(
SELECT Num, POWER(2, CAST(Num AS bigint)), (@status & POWER(2,CAST(Num AS bigint)))
FROM dbo.Numbers WHERE Num<30
)
SELECT MAX(Bitcheck) FROM nums
/*INCLUDING THE FOLLOWING WHERE CLAUSE CAUSES THE OVERFLOW ERROR*/
WHERE bitcheck>0

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18396 More actions · Answer 6

jcdenmark wrote:

Sorry, I should have been more specific; the reason I don’t use a temp table is the CTE is in an inline TVF which takes @status as a parameter; I’ve done what you suggested in a multi statement TVF…I just wondered why I get the overflow in the inline TVF when I use a physical numbers table. Thanks though for your input

Have you got Num < 30 in the TVF?

Can you paste in the TVF?

It is probably because the TVF is evaluating numbers greater than the max return of the POWER function when you pass in an int.

You could use POWER(CAST(2 as bigint), Num) then it should evaluate up to 2^62 instead of 2^30

POWER (Transact-SQL)

Screenshot 2025-03-13 191744

jcdenmark Valued Member Points: 65 More actions · Answer 7

tvf pasted below;

the reason is exactly as you suggest...the CTE is evaluating POWER(2, Number) for all numbers

from the numbers table, i.e. up to 4096, hence the overflow, even though only numbers 0-29 are requested;

no data type can handle POWER(2, 4096)...it's well over 10E400

I think it might help my understanding to know why a CTE would function like this;

from other posts, it seems it functions as it should in sql2022; I'm on 2017/14.0.3456.2

CREATE FUNCTION dbo.ufn_GetStatusMSB_NF 
(
-- Add the parameters for the function here
@status as int
)
RETURNS TABLE 
AS
RETURN 
(
with nums(num, power_Num, bitcheck)
as
(
SELECT Num, POWER(2, Num), (@status & POWER(2,Num))
FROM dbo.Numbers WHERE Num<30
)
SELECT MAX(Bitcheck) as BitCheck FROM nums
--INCLUDING THE FOLLOWING WHERE CLAUSE CAUSES THE OVERFLOW ERROR
WHERE bitcheck>0
)
GO

Ed B Hall of Fame Points: 3681 More actions · Answer 8

It works fine as written on our SQL 2017 14.0.3238.1. database. If I remove the <30 filter I get the arithmetic error so it's clearly applying the filter first. Is there anything unusual about your numbers table? Mine goes to 10 million with a clustered primary key on the number column. I rewrote it using a derived table and not a CTE and the result was the same. I tried adding a random varchar column and changing the clustered index to that column to see if that would trick it into reading values >= 30, but it will worked fine.

CREATE OR ALTER FUNCTION dbo.ufn_GetStatusMSB_NF 
(
-- Add the parameters for the function here
@status as int
)
RETURNS TABLE 
AS
RETURN 
(
with nums(num, power_Num, bitcheck)
as
(
SELECT num, POWER(2, num), (@status & POWER(2,num))
FROM dbo.Numbers WHERE number<30
)
SELECT MAX(Bitcheck) as BitCheck FROM nums
--INCLUDING THE FOLLOWING WHERE CLAUSE CAUSES THE OVERFLOW ERROR
WHERE bitcheck>0
)
GO

SELECT *
FROM dbo.ufn_GetStatusMSB_NF (83457678)