firebird - 插入查询中的数字溢出

Question

我收到以下错误:

Arithmetic overflow or division by zero has occurred. arithmetic exception, numeric overflow, or string truncation. numeric value is out of range.

这可以复制:

create table testing (avalue numeric(3,2));

和以下插入:

insert into testing values (328);

但是，使用以下方法可以正常工作:

insert into testing values (327);

328 似乎是错误发生的神奇数字。对我来说，numeric(3,2) 声明应该允许我使用 000-999 和 2 位小数，但基于上述情况是错误的。

谁能解释这是为什么以及我应该如何声明我的域，就好像我想允许 0-999 和小数点后两位一样。

谢谢

Answer 1

328 不是“魔法”数字 :)魔数(Magic Number)是 32767 (0x7FFF)。这是 SMALLINT 类型限制。

注意:Firebird 不支持无符号整数类型。

NUMERIC 类型的限制因存储类型和规模而异。内部存储类型按精度分为SMALLINT、INTEGER和BIGINT:

精密型

1..4 - SMALLINT

5..9 - 整数

10..18 - 双整数

所以

NUMERIC(3,2) 是 SMALLINT 内部类型最大 32767/100 = 327.67。

更新

Firebird 2.5 Language Reference经过保罗·文克努格，德米特里·叶马诺夫和托马斯·温克

包含比其他官方 Firebird 文档更全面的 NUMERIC 类型描述。

NUMERIC (precision, scale) is the exact number with the decimal precision and scale specified by the and .

Syntax: NUMERIC [precision [, scale]]

The scale of NUMERIC is the count of decimal digits in the fractional part, to the right of the decimal point. The precision of NUMERIC is the total count of decimal digits in the number.

The precision must be positive, the maximum supported value is 18. The scale must be zero or positive, up to the specified precision.

If the scale is omitted, then zero value is implied, thus meaning an integer value of the specified precision, i.e. NUMERIC (P) is equivalent to NUMERIC (P, 0). If both the precision and the scale are omitted, then precision of 9 and zero scale are implied, i.e. NUMERIC is equivalent to NUMERIC (9, 0).

The internal representation of the NUMERIC data type may vary. Numerics with the precision up to (and including) 4 are always stored as scaled short integers (SMALLINT). Numerics with the precision up to (and including) 9 are always stored as scaled regular integers (INTEGER). Storage of higher precision numerics depends on the SQL dialect. In Dialect 3, they are stored as scaled large integers (BIGINT). In Dialect 1, however, large integers are not available, therefore they are stored as double precision floating-point values (DOUBLE PRECISION).

The effective precision limit for the given value depends on the corresponding storage. For example, NUMERIC (5) will be stored as INTEGER, thus allowing values in the precision range up to (and including) NUMERIC (9). So beware that the declared precision is not strictly enforced.

Values outside the range limited by the effective precision are not allowed. Values with the scale larger than the declared one will be rounded to the declared scale while performing an assignment.