Issue Details (XML | Word | Printable)

Key: CORE-6133
Type: Bug Bug
Status: Open Open
Priority: Minor Minor
Assignee: Unassigned
Reporter: Luciano Mendes
Votes: 0
Watchers: 4
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 with COLLATION

Created: 02/Sep/19 03:54 PM   Updated: 22/Apr/20 12:50 AM
Component/s: None
Affects Version/s: 3.0.4, 3.0.5
Fix Version/s: None

File Attachments: 1. File DDL.SQL (0.5 kB)

Image Attachments:

1. Issue.png
(10 kB)
Environment:
Windows 10 x64
Firebird 3.0.5.33161 (x64)

QA Status: No test


 Description  « Hide
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 CORE-6040;

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);
===========================

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Luciano Mendes added a comment - 20/Jan/20 12:11 PM
Retest result on Firebird 3.0.5.33220 (Official Firebird 3.0.5): - FAILED (#motorolablocker)

Adriano dos Santos Fernandes added a comment - 11/Mar/20 02:08 PM
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.

Luciano Mendes added a comment - 11/Mar/20 04:12 PM
Thank you very much for your explanations Adriano!
We will try an alternative path when we migrate our databases from FB 2.5 to 3.0.

Could you take a look at this other bug related to metadata script?
http://tracker.firebirdsql.org/browse/CORE-6147

This is other issue that are blocking us for migrating to Firebird 3.0.

Thanks in advance,
Luciano

Luciano Mendes added a comment - 21/Apr/20 01:28 PM
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,
Luciano

Luciano Mendes added a comment - 21/Apr/20 10:44 PM
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 http://tracker.firebirdsql.org/browse/CORE-6147 blocking us for the Firebird 3.0 migration.

Best Regards,
Luciano

Adriano dos Santos Fernandes added a comment - 22/Apr/20 12:50 AM
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.