Domain problem after dialect and ODS change

[Gabor Boros]

Hi All,

Create a dialect 1 database with 2.5.9, then create a domain and a table
in it:

CREATE DOMAIN N_15_4 AS NUMERIC(15,4);
CREATE TABLE TBL1 (FLD1 N_15_4);

Set the dialect of the database to 3 with gfix.
Backup with gbak(2.5.9) and restore with 3.0.7.

SQL> CREATE DOMAIN N_15_4_N AS NUMERIC(15,4) NOT NULL;
SQL> COMMIT;
SQL> ALTER TABLE TBL1 alter FLD1 type N_15_4_N;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-ALTER TABLE TBL1 failed
-Cannot change datatype for FLD1. Conversion from base type DOUBLE
PRECISION to BIGINT is not supported.

SQL> SELECT rdb$field_name,
CON> rdb$field_length, rdb$field_scale, rdb$field_type,
rdb$field_sub_type,
CON> rdb$null_flag, rdb$field_precision
CON> FROM rdb$fields
CON> WHERE (rdb$field_name starting with 'N_15');

RDB$FIELD_NAME N_15_4
RDB$FIELD_LENGTH 8
RDB$FIELD_SCALE -4
RDB$FIELD_TYPE 27
RDB$FIELD_SUB_TYPE 0
RDB$NULL_FLAG <null>
RDB$FIELD_PRECISION <null>

RDB$FIELD_NAME N_15_4_N
RDB$FIELD_LENGTH 8
RDB$FIELD_SCALE -4
RDB$FIELD_TYPE 16
RDB$FIELD_SUB_TYPE 1
RDB$NULL_FLAG 1
RDB$FIELD_PRECISION 15


Is it by design or bug?

Gabor

[Dmitry Yemanov]

21.07.2021 12:48, Gabor Boros wrote:
>
> Create a dialect 1 database with 2.5.9, then create a domain and a table
> in it:
>
> CREATE DOMAIN N_15_4 AS NUMERIC(15,4);
> CREATE TABLE TBL1 (FLD1 N_15_4);
>
> Set the dialect of the database to 3 with gfix.

GFIX changes the default database dialect (implied when new metadata is
created), but it does not affect the existing metadata.

> Backup with gbak(2.5.9) and restore with 3.0.7.
>
> SQL> CREATE DOMAIN N_15_4_N AS NUMERIC(15,4) NOT NULL;
> SQL> COMMIT;
> SQL> ALTER TABLE TBL1 alter FLD1 type N_15_4_N;
> Statement failed, SQLSTATE = 42000
> unsuccessful metadata update
> -ALTER TABLE TBL1 failed
> -Cannot change datatype for FLD1.  Conversion from base type DOUBLE
> PRECISION to BIGINT is not supported.

Interesting case. The field internally remains being backed by DOUBLE
PRECISION, so conversion into BIGINT is prohibited. From another side,
its public declaration remains the same -- NUMERIC(15, 4) -- thus the
allowed set of values is theoretically the same and conversion should be
allowed. But practice is somewhat different, as Firebird never validates
the actual precision and some stored DOUBLEs may be outside the BIGINT
range. So this restriction could make sense.

Moreover, I doubt ALTER TYPE should be generally recommended for
migration from dialect 1 to dialect 3, AFAIU its design is not prepared
to handle dialect differences. And all stored PSQL objects will still
work using the dialect 1 logic, because BLR generated for dialect 1 is
different. They must be recompiled in dialect 3.

IMHO, database should rather be migrated via a script + data pump.


Dmitry