Computed columns with implicit/explicit datatypes are not distinguishable [CORE5168]

[firebird-automations]

Submitted by: Arioch (arioch)

Jira_subtask_inward CORE1407

As of today when inspecting the database (extracting metadata) tools can not tell inspecting COMPUTED-BY columns whether it was generated with specific user-mandate type or with default type.

create table xxx( ID SMALLINT NOT NULL,

g_Default COMPUTED BY ( -ID )
g_Explicit SMALLINT COMPUTED BY (-ID)

g_Extended INTEGER COMPUTED BY (-ID) );

At best one can use heuristics to for a guesswork whether the column LOOKS like being created with explicit/default datatype.

http://www.sql.ru/forum/1205742/skript-tablic-s-polem-vychislimogo-polya-s-yavnym-tipom

There seems to be no way to inspect the table and reconstruct/distinguish the declarations of both g_Default and g_Explicit

[firebird-automations]

Commented by: Arioch (arioch)

Interesting "shooting in the leg" scenario

ODS 11.1 or prior

CREATE TABLE DATA ( VAL1 INTEGER NOT NULL, VAL2 INTEGER NOT NULL, RATIO DOUBLE PRECISION COMPUTED BY ( VAL1 / VAL 2 ) );

CREATE PROCEDURE CALCULATE_AVERAGE_RATIO RETURNS ( RESULT DOUBLE PRECISION ) AS /* making loop through all the rows in DATA and fetching RATIO column from it */

CREATE PROCEDURE CALCULATE_REVERSE_RATIO RETURNS ( RESULT DOUBLE PRECISION ) AS /* calculate and return 1.0 / CALCULATE_AVERAGE_RATIO() */

----

The DATA table then gets populated and contains rows like (1,2, 0.5) and (4, -10, -0.4);

As ODS 11.1 lacks int64 datatype the explicit datatype of the RATIO computed column matches implicit default datatype of its expression.

Initially created with FB 2.1 the database survives software upgrade to FB 2.5.

One day the database gets corrupt, corrupt enough that it gets decided to recreate the database by script. So some tool like IBExpert is used to extract metadata and data into text file.

As there is no way to tell the explicit user-defined datatype of the computed column the script extracted looks like

CREATE TABLE DATA ( VAL1 INTEGER NOT NULL, VAL2 INTEGER NOT NULL, RATIO COMPUTED BY ( VAL1 / VAL 2 ) );

However when recreating the database using FB 2.5 we are starting with ODS 11.2 with its BIGINT datatype.

Now we have

CREATE TABLE DATA ( VAL1 INTEGER NOT NULL, VAL2 INTEGER NOT NULL, RATIO /* implicit default BIGINT */ COMPUTED BY ( VAL1 / VAL 2 ) );

--------

When the same data being - from the script - entered into the DATA table, the aforementioned rows now would look like (1,2, 0) and (4, -10, 0) instead of expected prior (1,2, 0.5) and (4, -10, -0.4). However there is no error in the recreation script running.

When days later any calculation calls CALCULATE_REVERSE_RATIO - users get a sudden Divizion By Zero error where everything worked smoothly before.

Worse than that, if DATA initially contained mix of records with abs(RATIO) both above and below 1.0 - then there would be no explicit DivByZero in such a scenario - but covert computational errors instead

[firebird-automations]

Modified by: Arioch (arioch)

description: As of know when inspecting the database \(extracting metadata\) tools can not tell inspecting COMPUTED\-BY columns whether it was generated with specific user\-mandate type or with default type\. create table xxx\( ID SMALLINT NOT NULL, g\_Default COMPUTED BY \( \-ID \) g\_Explicit SMALLINT COMPUTED BY \(\-ID\) g\_Extended INTEGER COMPUTED BY \(\-ID\) \); At best one can use heuristics to for a guesswork whether the column LOOKS like being created with explicit/default datatype\. There seems to be no way to inspect the table and reconstruct/distinguish the declarations of both g\_Default and g\_Explicit =\> As of today when inspecting the database \(extracting metadata\) tools can not tell inspecting COMPUTED\-BY columns whether it was generated with specific user\-mandate type or with default type\. create table xxx\( ID SMALLINT NOT NULL, g\_Default COMPUTED BY \( \-ID \) g\_Explicit SMALLINT COMPUTED BY \(\-ID\) g\_Extended INTEGER COMPUTED BY \(\-ID\) \); At best one can use heuristics to for a guesswork whether the column LOOKS like being created with explicit/default datatype\. There seems to be no way to inspect the table and reconstruct/distinguish the declarations of both g\_Default and g\_Explicit