Incorrect result type of COALESCE and pair (INTEGER, NUMERIC(6,1))

[dmitry-lipetsk]

ISQL Version: WI-V3.0.8.33506 Firebird 3.0

SQL> create table TTT4(COL_INTEGER INTEGER, COL_NUM_6_1 NUMERIC(6,1)); SQL> insert into TTT4 (COL_INTEGER, COL_NUM_6_1) values (2147483647,12345.6); SQL> select COALESCE(COL_INTEGER,COL_NUM_6_1) from TTT4;

Result

COALESCE

============ Statement failed, SQLSTATE = 22003 arithmetic exception, numeric overflow, or string truncation -numeric value is out of range

[dyemanov]

From the SQL spec:

If all of the data types in DTS are exact numeric, then the result data type is exact numeric with implementation-defined precision and with scale equal to the maximum of the scales of the data types in DTS.

It seems that we derive the precision from the arguments via MAX(10, 6) in this case, with result becoming NUMERIC(10, 1) and 2147483647 obviously does not fit. To preserve the integral part the result should be NUMERIC(11, 1) instead, and it would be backed by BIGINT rather than INT. This change may affect existing applications.

[dmitry-lipetsk]

Firebird uses BIGINT-storage for NUMERIC(10,1).

If I understood correctly, the problem is in the wrong logic of the following code [FB3 src]:

https://github.com/FirebirdSQL/firebird/blob/6c303faa63545b9e9e887a02d7d26829dd22a449/src/jrd/DataTypeUtil.cpp#L156-L159

[dyemanov]

Yes, but as I said returning SQL_INT64 instead of SQL_LONG may upset some users (those using static type binding in their applications). We already had the same compatibility issue after starting to return BIGINT for COUNT. So the question is whether we can afford fixing it in point releases or in v5 only.

[dmitry-lipetsk]

I not checked exactly, but seems mistake of DataTypeUtilBase::makeFromList affects not only to COALESCE.

For example, following SQL also returns wrong type of result column:

select COL_INTEGER FROM TTT4
union
select COL_NUM_6_1 FROM TTT4

arithmetic exception, numeric overflow, or string truncation numeric value is out of range

---

Workaround - explicit cast of COL_INTEGER to NUMERIC(10,0)

So the question is whether we can afford fixing it in point releases or in v5 only.

Don't know.

Personally, in current time I write a code for FB3. FB4 does not tested.

[aafemt]

those using static type binding in their applications

These will get values coerced to requested static types. The problem is Delphi that compare the types instead or requesting them.

So the question is whether we can afford fixing it in point releases or in v5 only.

V5 only should be fine.