At 02:04 22/08/2008, Michael L. Horne wrote:

>Hello,
>
>I created a TIB_Query with the following SQL:
>
>select * from PURCHASE_AM_Order
>
>CREATE TABLE PURCHASE_AM_ORDER (
> PAMO_ID integer not NULL,
> PAMO_PART varchar(16),
> PAMO_QTY integer,
> PAMO_COST Numeric(9,2));
>
>In the Ordering tab I clicked on the "Create" button
>when I checked the Part, Qty, Cost, and clicked "OK",
>said yes to "Assign ordering items?" Where it created
>the following:
>
>PAMO_PART=PAMO_PART;PAMO_PART DESC
>PAMO_QTY=PAMO_QTY;PAMO_QTY DESC
>PAMO_COST=PAMO_COST;PAMO_COST DESC
>
>PAMO_PART=ITEM=1;POS=0
>PAMO_QTY=ITEM=2;POS=0
>PAMO_COST=ITEM=3;POS=0
>
>If I go to data tab and click on the column header
>to sort the "PAMO_COST" column I get an
>"Integer overflow" error.
>
>The way to solve this problem is to remove
>the ";POS=0" from the line "PAMO_COST=ITEM=3;POS=0".
>
>This seems to be a possible bug to me.

...or not. IBO doesn't create the ascending and descending indexes to support these sorts: it's assumed that if you want to sort in these ways you will have created the indexes ahead.

When you request the reordering, IBO sends the appropriate ORDER BY clause without knowledge of whether those supporting indexes exist. Without a suitable index, the Firebird engine has to walk the data and create a temporary bitmap for the sort. The more obvious cause of your exception is that the engine found a record containing data in this field that exceeds the precision specified for it - numeric(9,2) is a 32-bit integer with a scale of 2, so any number greater than 21,474,836.47 is going to throw an overflow error as soon as it is touched.

To test this angle, I suggest your next move would be to attempt to create the pairs of indexes that support the OrderingItems you defined, using bare ISQL rather than any GUI tool. If you have oversized data there (or other data misfits) then the CREATE INDEX request should throw it up.

Helen