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

Metadata script extracted using ISQL is invalid/incorrect when table has COMPUTED BY field [CORE6040] #6290

Closed
firebird-automations opened this issue Apr 1, 2019 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @luronumen

Relate to CORE6116

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

Commits: cb69325 f00d04c

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Component: ISQL [ 10003 ]

summary: Unable to recreate the database structure from the extracted database structure file when there is any table with COMPUTED BY field => Metadata script extracted using ISQL is invalid/incorrect when table has COMPUTED BY field

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Version: 4.0 Beta 1 [ 10750 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0.5 [ 10885 ]

Fix Version: 4.0 Beta 2 [ 10888 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Thanks!

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

Hi Adriano,

I have opened the issue CORE6116 for a residual problem in the Firebird 3.0.5 (Snapshot).
Could you please take a look on this?

Thanks in advance,
Luciano

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE6116 [ CORE6116 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

Retest result on Firebird 3.0.5.33220 (Official Firebird 3.0.5): - PASSED (#⁠nonmotorolablocker)

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