Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
firebird-automations opened this issue Sep 26, 2019 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Commented by: @asfernandes

Maybe the field position has changed after its creation?

@firebird-automations
Copy link
Collaborator Author

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).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant