Metadata script extracted using ISQL is invalid/incorrect when table has
COMPUTED BY field with COLLATION
---------------------------------------------------------------------------------------------------------
Key: CORE-6133
URL: http://tracker.firebirdsql.org/browse/CORE-6133
Project: Firebird Core
Issue Type: Bug
Affects Versions: 3.0.4, 3.0.5
Environment: Windows 10 x64
Firebird 3.0.5.33161 (x64)
Reporter: Luciano Mendes
Priority: Critical
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);
===========================
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
