When uploading metadata, calculated fields can be higher than one of the composite fields. [CORE6149]

[firebird-automations]

Submitted by: Valchenko Igor (gallemar)

When unloading metadata isql -x , calculated fields must be moved to the end of the list. Example:
Wrong:
CREATE TABLE OPERATIONS (
ID_OPERATION INTEGER NOT NULL ,
NAME_OPERATION VARCHAR(40) NOT NULL ,
DOCTYPE INTEGER,
DOCKIND COMPUTED BY (decode(doctype, -1, 1 - opertype, doctype)),
OPERTYPE INTEGER NOT NULL ,
OPERKIND INTEGER NOT NULL
);

After execution:

Dynamic SQL Error.
SQL error code = -206.
Column unknown.
OPERTYPE.
At line 5, column 61.

correctly:
CREATE TABLE OPERATIONS (
ID_OPERATION INTEGER NOT NULL ,
NAME_OPERATION VARCHAR(40) NOT NULL ,
DOCTYPE INTEGER,
OPERTYPE INTEGER NOT NULL ,
OPERKIND INTEGER NOT NULL ,
DOCKIND COMPUTED BY (decode(doctype, -1, 1 - opertype, doctype))
);

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

I think it is only logical for the fields which a COMPUTED column should be defined before they can be referenced. As such, IMO this is not a bug (certainly not a "Major" one)

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

priority: Major [ 3 ] => Trivial [ 5 ]

[firebird-automations]

Commented by: @reevespaul

If I understand the bug report correctly isql has ouput a computed column before the field definitions it depends upon.

If this is reproduceable it is certainly a bug.

Indeed it is the sort of basic problem that would have been solved Nty years ago so it would also be a regression.

As for defining its priority - I'd say it is major, if it can be reproduced.

Metadata output from isql has to be 100% reliable.

[firebird-automations]

Commented by: @asfernandes

Maybe the field position has changed after its creation?

[firebird-automations]

Commented by: @pavel-zotov

This can be reproduced only in 2.5; in 3.0 it was *for sure* fixed.
Script:

shell del c:\\temp\\c6149\.fdb 2\>nul;
create database 'localhost:c:\\temp\\c6149\.fdb' user 'SYSDBA' password 'masterkey';

set echo on;
set bail on;

create table test\(
    id\_operation integer not null , 
    name\_operation varchar\(40\) not null , 
    doctype integer, 
    opertype integer not null , 
    operkind integer not null , 
    dockind computed by \(decode\(doctype, \-1, 1 \- opertype, doctype\)\) 
\);
commit;

alter table test
alter column DOCKIND position 4
;
commit;

show table test;

=======

If we run there %FB25_HOME%\bin\isql -x <this_database>, then it will generate invalid medatata (checked on WI-V2.5.9.27144).
But this is not about 3.x which produces correct script:

SET SQL DIALECT 3;
. . .
CREATE TABLE TEST (ID_OPERATION INTEGER NOT NULL,
NAME_OPERATION VARCHAR(40) NOT NULL,
DOCTYPE INTEGER,
DOCKIND BIGINT COMPUTED BY (NULL),
OPERTYPE INTEGER NOT NULL,
OPERKIND INTEGER NOT NULL);

/* Computed fields */
ALTER TABLE TEST
ALTER DOCKIND TYPE BIGINT COMPUTED BY (decode(doctype, -1, 1 - opertype, doctype));

So, the only issue than can be seen here: 3.0 does not change computed field position to original (4).