Issue Details (XML | Word | Printable)

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

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

Metadata script extracted using ISQL is invalid/incorrect when table has COMPUTED BY field

Created: 01/Apr/19 06:58 PM   Updated: 06/Aug/19 12:47 AM
Component/s: ISQL
Affects Version/s: 3.0.4, 4.0 Beta 1
Fix Version/s: 3.0.5, 4.0 Beta 2

Environment:
Windows 10 x64
Firebird 3.0.4.33054 (x64)

QA Status: Done successfully


 Description  « Hide
ACTUAL RESULT:
- Unable to recreate the database structure from the extracted database structure file when there is any table with COMPUTED BY field:
Message: isc_dsql_prepare failed

SQL Message : -104
Invalid token

Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 5, column 73
COLLATE


EXPECTED RESULT:
- The user should be able to recreate the database structure from the extracted database structure file when there is any table with COMPUTED BY field without any issue


STEPS TO REPRODUCE THE ISSUE

1- Create a Firebird SQL 3.0.4 database

2- Execute the following SQL Statements to generate the USERS table:
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- Execute the isql.exe to extract the database structure:
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(100) CHARACTER SET WIN1252 COMPUTED BY (NULL) COLLATE WIN_PTBR,
CONSTRAINT PK_USERS_1 PRIMARY KEY (ID),
CONSTRAINT UK_USERS_1 UNIQUE (USER_NAME));
ALTER TABLE USERS ALTER FULL_NAME TYPE VARCHAR(100) CHARACTER SET WIN1252 COMPUTED BY ((FIRST_NAME || ' ' || LAST_NAME || ' (' || USER_NAME || ')') COLLATE WIN_PTBR);

4- Drop the Database and then create a new one

5- Execute the database structure previously extracted to recreated the USERS table

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Luciano Mendes added a comment - 05/Aug/19 06:31 PM - edited
FIX VALIDATION on Firebird 3.0.5.33161 (Snapshot)

1- Extract the DDL using the Firebird 2.5.9: FB25_DDL.SQL;
2- Create a Firebird 3.0.5 database using the FB25_DDL.SQL;
3- Extract the DDL using the Firebird 3.0.5: FB30_DDL.SQL;
4- Create a Firebird 2.5.9 database using the FB30_DDL.SQL;
5- Extract the DDL using the Firebird 2.5.9: Verify that the new FB25_DDL.SQL is the same generated in the first step - PASSED

Best Regards,
Luciano

Adriano dos Santos Fernandes added a comment - 05/Aug/19 11:22 PM
Thanks!

Luciano Mendes added a comment - 06/Aug/19 12:47 AM
Hi Adriano,

I have opened the issue http://tracker.firebirdsql.org/browse/CORE-6116 for a residual problem in the Firebird 3.0.5 (Snapshot).
Could you please take a look on this?

Thanks in advance,
Luciano