Problem with MAX function on bigint column

Question

Problem with MAX function on bigint column

Bill Mell

SSCommitted

Points: 1621
More actions
September 3, 2008 at 7:51 am

#193113

I have a query that looks for the MAX(bigintcolumn) to find a particular record in the database.
I have recently run into an issue:
If the value of the bigint column is greater than the potential value of an int column, the query fails with a message like this:
The conversion of the nvarchar value "2147486776" overflowed an int column. Maximum integer value exceeded.
I'm trying to select the max bigint column into a bigint variable, so I fail to see what the maximum capacity of an int has to do with it.
Is this a bug in sql2k5 or is there something I am missing?
Thanks,
Bill Mell

Viewing 15 posts - 1 through 15 (of 23 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Bill, can you please post some or all of your query? It's impossible to deduce why an nvarchar value is being converted (to whatever) from what you've posted.

BOL states that MAX is compatible with BIGINT, and the following works fine:

SELECT MAX(BigValue) AS MaxBigValue

FROM (SELECT CAST(21474867706 AS BIGINT) AS BigValue UNION ALL

SELECT 2147486775 UNION ALL

SELECT 2147486774 UNION ALL

SELECT 2147486773 UNION ALL

SELECT 2147486772

) d

Returns

MaxBigValue

---------------------

21474867706

(1 row(s) affected)

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Bill Mell SSCommitted Points: 1621 More actions · Answer 2

declare @test-2 bigint

select @test-2 = MAX(RecID)

FROM DBO.DATAVWOBJACCESSINFO

WHERE DATETIMEX <= '9/2/2008 10:45:03 AM' AND HANDLEID = 2964 AND ID = 567

select @test-2

This fails every time, The RecID column is a bigint, as is the @test-2 variable.

Thanks,

Bill Mell

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

Michael Earl-395764

SSC Guru

Points: 53873

September 3, 2008 at 8:34 am

#866515

What are the data types of HandleID and ID?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Bill Mell (9/3/2008)
declare @test-2 bigint
select @test-2 = MAX(RecID)
FROM DBO.DATAVWOBJACCESSINFO
WHERE DATETIMEX <= '9/2/2008 10:45:03 AM' AND HANDLEID = 2964 AND ID = 567
select @test-2
This fails every time, The RecID column is a bigint, as is the @test-2 variable.
Thanks,
Bill Mell

You must have tried it already, but what's the result of running this:

declare @test-2 bigint

select @test-2 = MAX(CAST(RecID AS BIGINT))

FROM DBO.DATAVWOBJACCESSINFO

WHERE DATETIMEX <= '9/2/2008 10:45:03 AM' AND HANDLEID = 2964 AND ID = 567

select @test-2

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

And RecId is, for sure, a bigint?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bill Mell SSCommitted Points: 1621 More actions · Answer 6

Bill Mell

SSCommitted

Points: 1621

September 3, 2008 at 8:44 am

#866523

They are both int columns

Bill

Bill Mell SSCommitted Points: 1621 More actions · Answer 7

Casting RecID as bigint does not keep it from failing.

and the RecID column is definately a bigint.

Bill

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Have you tried dropping and recreating the view?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

GSquared SSC Guru Points: 260824 More actions · Answer 9

Are you sure there isn't more code before/after that, and that the failure isn't there?

I ask because I just ran this:

create table DBO.DATAVWOBJACCESSINFO (

RecID bigint primary key,

DATETIMEX datetime,

HANDLEID int,

ID int);

go

insert into DBO.DATAVWOBJACCESSINFO (recid, datetimex, handleid, id)

select 2147486776, '9/2/2008 10:45:03 AM', 2964, 567;

go

declare @test-2 bigint;

select @test-2 = MAX(RecID)

FROM DBO.DATAVWOBJACCESSINFO

WHERE DATETIMEX <= '9/2/2008 10:45:03 AM' AND HANDLEID = 2964 AND ID = 567;

select @test-2;

No errors. It worked just fine.

Running on:

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Bill Mell SSCommitted Points: 1621 More actions · Answer 10

Just tried dropping and recreating the view - same issue

Bill

Bill Mell SSCommitted Points: 1621 More actions · Answer 11

Totally sure.

The snippet I put in there, is where I am getting the error.

I realize it makes no sense, but that's where it's failing.

Bill

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Bill Mell (9/3/2008)
Just tried dropping and recreating the view - same issue
Bill

Is it a partitioned view? Or to put this another way, are the values for RecID from one table only?

Is RecID CAST in the view?

Have you checked that RecID is BIGINT in the base table?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 13

Have you tried running the query against the base table?

Something is introducing a string in there.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bill Mell SSCommitted Points: 1621 More actions · Answer 14

It is not a partitioned view

RecID is from a single source table

The source table's column definition is a bigint.

Where else would it get the number from if not from a bigint column.

the number is to large for an int.

I would think the important issue would be whether or not the target is a bigint (which it is)

Bill