Issue Details (XML | Word | Printable)

Key: CORE-6149
Type: Bug Bug
Status: Open Open
Priority: Trivial Trivial
Assignee: Unassigned
Reporter: Valchenko Igor
Votes: 0
Watchers: 5
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 26/Sep/19 06:25 AM   Updated: 27/Sep/19 06:48 AM
Component/s: ISQL
Affects Version/s: 2.5.9
Fix Version/s: None

Environment: Windows Server 2016

QA Status: No test


 Description  « Hide
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))
);





 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 26/Sep/19 02:42 PM
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)

Paul Reeves added a comment - 26/Sep/19 03:26 PM
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.



Adriano dos Santos Fernandes added a comment - 26/Sep/19 03:56 PM
Maybe the field position has changed after its creation?

Pavel Zotov added a comment - 27/Sep/19 06:48 AM
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).