Issue Details (XML | Word | Printable)

Key: CORE-6116
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Adriano dos Santos Fernandes
Reporter: Luciano Mendes
Votes: 0
Watchers: 2
Operations

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

The Metadata script extracted using ISQL of a database restored from a Firebird 2.5.9 Backup is invalid/incorrect when table has COMPUTED BY field

Created: 06/Aug/19 12:44 AM   Updated: 21/Apr/20 02:03 PM
Component/s: ISQL
Affects Version/s: 3.0.5
Fix Version/s: 4.0 Beta 2, 3.0.6

File Attachments: None
Image Attachments:

1. ISSUE.PNG
(34 kB)
Environment:
Windows 10 x64
Firebird 3.0.5.33161 (x64)
Issue Links:
Relate
 

QA Status: Done successfully


 Description  « Hide
ACTUAL RESULT
- The Metadata script extracted using ISQL of a database restored from a Firebird 2.5.9 Backup is invalid/incorrect when table has COMPUTED BY field
- The COMPUTED BY field is generated as VARCHAR(0) instead of the Max size of the COMPUTED BY field
- A Metadata script with COMPUTED BY with VARCHAR(0) size always generate an exception when it is tried to recreate a database from it

EXPECTED RESULT
- The Metadata script extracted using ISQL of a database restored from a Firebird 2.5.9 Backup should be valid/correct when table has COMPUTED BY field
- The COMPUTED BY field should be generated with the size of the collumns combinations (e.g.: VARCHAR(100)) instead of size 0: VARCHAR(0);


STEPS TO REPRODUCE THE ISSUE

1- Install the Firebird 2.5.9 and setup it

2- Create a database and than running the following DDL:
CREATE TABLE USERS (ID BIGINT NOT NULL,
        USER_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
        FIRST_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
        LAST_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
        FULL_NAME COMPUTED BY ((FIRST_NAME || ' ' || LAST_NAME || ' (' || USER_NAME || ')') COLLATE WIN_PTBR),
CONSTRAINT PK_USERS_1 PRIMARY KEY (ID),
CONSTRAINT UK_USERS_1 UNIQUE (USER_NAME));

3- Backup the database using the gbak.exe;

4- Uninstall the Firebird 2.5.9, install the Firebird 3.0.4 and setup it;

5- Replace the Firebird 3.0.4 isql.exe by the Firebird 3.0.5.33161 (Snapshot) isql.exe version to avoid the issue CORE-6040;

6- Restore the Database backup in the step 3 using the the Firebird 3.0.4 gbak.exe;

7- Extract the Database DDL using the Firebird 3.0.4 + Firebird 3.0.5.33161 (Snapshot) isql.exe:

/* Table: USERS, Owner: SYSDBA */
CREATE TABLE USERS (ID BIGINT NOT NULL,
        USER_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
        FIRST_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
        LAST_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
        FULL_NAME VARCHAR(0) CHARACTER SET WIN1252 COMPUTED BY (NULL) COLLATE WIN_PTBR,
CONSTRAINT PK_USERS_1 PRIMARY KEY (ID),
CONSTRAINT UK_USERS_1 UNIQUE (USER_NAME));
/* Computed fields */
ALTER TABLE USERS
        ALTER FULL_NAME TYPE VARCHAR(0) CHARACTER SET WIN1252 COMPUTED BY ((FIRST_NAME || ' ' || LAST_NAME || ' (' || USER_NAME || ')') COLLATE WIN_PTBR);

WORKAROUNG FOR FIX THIS ISSUE
- After restore a Firebird 2.5.9 backup into Firebird 3.0.5 (Snapshotp) the user have to run the following command for fix the Firebird 3.0.5 DDL:
ALTER TABLE USERS ALTER FULL_NAME TYPE VARCHAR(100) CHARACTER SET WIN1252 COMPUTED BY (FIRST_NAME || ' ' || LAST_NAME || ' (' || UID || ')');

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Luciano Mendes added a comment - 06/Aug/19 11:59 AM
From ISSUE.png:

- Red Line: Metadata script extracted from a database generated on Firebird 3.0;
- Green Line: Metadata script extracted from a Firebird 2.5.9 database restored on Firebird 3.0;

Luciano Mendes added a comment - 20/Jan/20 11:40 AM
Retest result on Firebird 3.0.5.33220 (Official Firebird 3.0.5): - FAILED (#motorolablocker)

This issue will be tested in Firebird 3.0.6 (Snapshot) soon.

Luciano Mendes added a comment - 21/Apr/20 02:03 PM
Retest result using the isql.exe from Firebird 3.0.6.33288 (snapshot): PASSED!

Thank you very much for the fix Adriano!

Best Regards,
Luciano