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-issue-importer opened this issue Apr 1, 2019 · 11 comments

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Apr 1, 2019

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 1, 2019

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 2, 2019

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 12, 2019

Modified by: @asfernandes

Version: 4.0 Beta 1 [ 10750 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 12, 2019

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 16, 2019

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 16, 2019

Modified by: @pavel-zotov

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 5, 2019

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-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 6, 2019

Commented by: @asfernandes

Thanks!

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 6, 2019

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-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 24, 2019

Modified by: @dyemanov

Link: This issue relate to CORE6116 [ CORE6116 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jan 20, 2020

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