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 with COLLATION [CORE6133] #6382
Comments
Modified by: @luronumenAttachment: DDL.SQL [ 13376 ] |
Commented by: @luronumen Retest result on Firebird 3.0.5.33220 (Official Firebird 3.0.5): - FAILED (#motorolablocker) |
Modified by: @luronumenAttachment: Issue.png [ 13416 ] |
Commented by: @asfernandes When you created the database the database character set is not used for 'A'. What was used that made it to work was that you were using WIN1252 as the connection charset. When you extract script this is a lost information. You must extract and recreate the script using WIN1252 as connection charset. This is a bad thing but I don't see a way to fix as each metadata command may have been created with a different connection charset. |
Modified by: @asfernandespriority: Critical [ 2 ] => Minor [ 4 ] |
Commented by: @luronumen Thank you very much for your explanations Adriano! Could you take a look at this other bug related to metadata script? This is other issue that are blocking us for migrating to Firebird 3.0. Thanks in advance, |
Commented by: @luronumen Hi Adriano, I just confirmed that this issue is NOT reproducible in Firebird 2.5.9. This means that a regression occurred in Firebird 3.0 and the fix to it may be in the Firebird 2.5.9 branch. Best Regards, |
Commented by: @luronumen Workaround to avoid this issue: Use: ALTER TABLE ACCESSORIES ALTER AID TYPE VARCHAR(21) CHARACTER SET WIN1252 COMPUTED BY (CAST(('A' || ID) AS VARCHAR(21) CHARACTER SET WIN1252) COLLATE WIN_PTBR); Instead of: ALTER TABLE ACCESSORIES ALTER AID TYPE VARCHAR(21) CHARACTER SET WIN1252 COMPUTED BY (('A' || ID) COLLATE WIN_PTBR); The most recent firebird 3.0 isql.exe already extract the database metadata using the workaround solution. So, this issue has become minor and no longer blocks our migration to Firebird 3.0. Now we just have the CORE6147 blocking us for the Firebird 3.0 migration. Best Regards, |
Commented by: @asfernandes I disagree it's fixed in v3. As I said, it works if the connection charset is also WIN1252. On v2.5 there is the same problem, but it uses the default character set. It's a problem too because the default character set is also changeable after part's of metadata is created. |
Submitted by: @luronumen
Attachments:
DDL.SQL
Issue.png
ACTUAL RESULT:
- Metadata script extracted using ISQL is invalid/incorrect when table has COMPUTED BY field with COLLATION
- Unable to recreate the database structure from an extracted database structure file (DDL.SQL) when there is any table with COMPUTED BY field with COLLATION:
Statement failed, SQLSTATE = 22021
unsuccessful metadata update
-ALTER TABLE ACCESSORIES failed
-Dynamic SQL Error
-SQL error code = -204
-COLLATION WIN_PTBR for CHARACTER SET NONE is not defined
After line 7 in file DDL.SQL
- The line is generating the error:
ALTER TABLE ACCESSORIES
ALTER AID TYPE VARCHAR(21) CHARACTER SET WIN1252 COMPUTED BY (('A' || ID) COLLATE WIN_PTBR);
EXPECTED RESULT:
- The user should be able to recreated the database structure using the DDL.SQL file generated by the Firebird 3.0.5.33161 (Snapshot) isql.exe
- The isql.exe should create the DDL.SQL ALTER TABLE like this
ALTER TABLE ACCESSORIES
ALTER AID TYPE VARCHAR(21) CHARACTER SET WIN1252 COMPUTED BY (CAST(('A' || ID) AS VARCHAR(21) CHARACTER SET WIN1252) COLLATE WIN_PTBR);
instead of
ALTER TABLE ACCESSORIES
ALTER AID TYPE VARCHAR(21) CHARACTER SET WIN1252 COMPUTED BY (('A' || ID) COLLATE WIN_PTBR);
STEPS TO REPRODUCE THE ISSUE
1- Install the Firebird 3.0.4 and setup it;
2- Replace the Firebird 3.0.4 isql.exe by the Firebird 3.0.5.33161 (Snapshot) isql.exe version to avoid the issue CORE6040;
3- Create a Firebird 3.0.4 using WIN1252 default character set
4- Run the following commant to recreate the database structure:
"%ProgramFiles%\Firebird\Firebird_3_0\isql.exe" -user SYSDBA -password SYSDBAPWD -quiet -input "DDL.SQL" "LOCALHOST:IRIS_CIN"
==========DDL.SQL==========
SET SQL DIALECT 3;
/* CREATE DATABASE 'IRIS_CIN' PAGE_SIZE 4096 DEFAULT CHARACTER SET WIN1252; */
COMMIT WORK;
/* Table: ACCESSORIES, Owner: SYSDBA */
CREATE TABLE ACCESSORIES (ID BIGINT NOT NULL,
AID VARCHAR(21) CHARACTER SET WIN1252 COMPUTED BY (CAST(NULL AS VARCHAR(1) CHARACTER SET WIN1252) COLLATE WIN_PTBR),
CONSTRAINT PK_ACCESSORIES_1 PRIMARY KEY (ID));
/* Computed fields */
ALTER TABLE ACCESSORIES
ALTER AID TYPE VARCHAR(21) CHARACTER SET WIN1252 COMPUTED BY (('A' || ID) COLLATE WIN_PTBR);
The text was updated successfully, but these errors were encountered: