Re: Select fails with "Arithmetic overflow" after indexing a decim

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 11/12/04 

Date: Fri, 12 Nov 2004 08:54:53 -0800

Uri,

The problem is after creating the index.

AMB

"Uri Dimant" wrote:

> Hi Steve
> I have just tested it on my workstation and it works fine
>
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
>
> CREATE TABLE [dec_test] (
> [prop_value] [decimal](2, 2)not NULL primary key
> ) ON [PRIMARY]
> GO
> insert into dec_test values(0.16)
> go
> select * from dec_test where prop_value < 1
>
> -----
> ..16
>
> |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1003], [Expr1004],
> [Expr1005]))
> |--Compute Scalar(DEFINE:([Expr1003]=NULL,
> [Expr1004]=Convert(Convert([@1]))+1.00, [Expr1005]=If
> (Convert(Convert([@1]))+1.00=NULL) then 0 else 10))
> | |--Constant Scan
> |--Index Seek(OBJECT:([Northwind].[dbo].[dec_test].[idx_prv]),
> SEEK:([dec_test].[prop_value] > [Expr1003] AND [dec_test].[prop_value] <
> [Expr1004]), WHERE:(Convert([dec_test].[prop_value])<Convert([@1])) ORDERED
> FORWARD)
>
>
>
>
> "Steve Kass" <skass@drew.edu> wrote in message
> news:OXFTzpAyEHA.2040@tk2msftngp13.phx.gbl...
> > Adrian,
> >
> > I think the overflow occurs when the number 1 (in the where clause) is
> > implicitly converted to DECIMAL(2,2). Note that this error occurs even
> > if the table is empty!
> >
> > There are over 700 distinct decimal types in SQL Server, and there are
> > no firm rules to rely on concerning implicit conversions between
> > different types. Apparently, when there is no index the literal value 1
> > in (prop_value = 1) is converted for comparison to a type that can hold
> > the value 1, but when the index is in place, a different conversion rule
> > is applied, and an attempt is made to implicitly calculate cast(1 as
> > decimal(2,2)).
> >
> > There's probably no real need to compare a decimal(2,2) with the number
> > 1, since all decimal(2,2) values are less than 1, and you should be able
> > to avoid this error by avoiding any comparison of the form
> > (decimalColumn < literalValue) when the literal exceeds the bounds of
> > the decimal type of the column. You can also change the type of your
> > column so that it not only holds the values you store, but could hold
> > values you compare against as well.
> >
> > I should add that I don't think the way things work here is very good,
> > and a lot of things I tried as workarounds don't solve the problem.
> > I'll report this to Microsoft as a potential bug, but for now, I hope
> > you can find a way around this, especially one that doesn't require a
> > conversion of the column that would make the index unusable.
> >
> > Steve Kass
> > Drew University
> >
> > Adrian Cucu wrote:
> >
> > >[Select fails with "Arithmetic overflow", after indexing a decimal
> column]
> > >
> > >Does anybody know why a select could fail, after indexing a decimal
> column?
> > >Please find below a test case.
> > >
> > >OS: W2K SP4
> > >MSSQL @@VERSION:
> > >Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> > >Dec 17 2002 14:22:05
> > >
> > >Does anybody know why a select could fail after indexing a decimal
> column
> > >
> > >[How to reproduce]
> > >1. create the dec_test table using the following DDL:
> > >
> > >CREATE TABLE [dec_test] (
> > > [prop_value] [decimal](2, 2) NULL
> > >) ON [PRIMARY]
> > >GO
> > >
> > >2. insert a value into dec_test:
> > >
> > >insert into dec_test values(0.16)
> > >
> > >3. display all dec_test entries having prop_value < 1:
> > >
> > >select * from dec_test where prop_value < 1
> > >
> > >4. create an index on prop_value using the following DDL:
> > >
> > >CREATE INDEX [idx_prv] ON [dbo].[dec_test]([prop_value]) ON [PRIMARY]
> > >GO
> > >
> > >5. run the previous SELECT:
> > >
> > >"Arithmetic overflow ..."
> > >
> > >
>
>
>