Collation of fields are not extracted properly

[livius2]

Create database with WIN1250 charset;

then

alter character set win1250  set default collation PXW_PLK;
commit;

CREATE TABLE TEST_CCC
(
A VARCHAR(100) COLLATE PXW_PLK,
B VARCHAR(100) COLLATE WIN1250,
C VARCHAR(100)
);

flamerobin extract it as:

but running below sql show proper collations:

SELECT
    RF.RDB$RELATION_NAME
    , RF.RDB$FIELD_NAME
    , F.RDB$COLLATION_ID
    , C.RDB$COLLATION_NAME
FROM
    RDB$RELATION_FIELDS RF
    INNER JOIN RDB$FIELDS F ON F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE
    INNER JOIN RDB$COLLATIONS C ON F.RDB$COLLATION_ID=C.RDB$COLLATION_ID AND F.RDB$CHARACTER_SET_ID=C.RDB$CHARACTER_SET_ID
WHERE
    RF.RDB$RELATION_NAME IN('TEST_CCC')

[arvanus]

Hi, so I tried with ISQL -x, and I got this output:

SET SQL DIALECT 3;

/* CREATE DATABASE 'localhost:d:\WIN1250.FDB' PAGE_SIZE 16384 DEFAULT CHARACTER SET WIN1250; */

/*  Character sets */
ALTER CHARACTER SET WIN1250 SET DEFAULT COLLATION PXW_PLK;



COMMIT WORK;

/* Table: TEST_CCC, Owner: SYSDBA */
CREATE TABLE TEST_CCC (A VARCHAR(100) CHARACTER SET WIN1250,
        B VARCHAR(100),
        C VARCHAR(100) CHARACTER SET WIN1250);

Obviously differs from Flamerobin, but now I can't say what to expect any more :) Should Fr output the collate as PXW_PLK or none like ISQL? ( obviously as ISQL, but WHY is this it? 😆 )

[livius2]

Hi. Strange about ISQ - it looks also as wrong output. I must check with FB4 or FB5, if the same i will report the issue to FB team.

CREATE TABLE TEST_AAA (
A VARCHAR(100) CHARACTER SET WIN1250,
B VARCHAR(100),
C VARCHAR(100) CHARACTER SET WIN1250
);

will create wrong table, all as PXW_PLK:

But correct output should be:

CREATE TABLE TEST_CCC (
A VARCHAR(100) COLLATE WIN1250,
B VARCHAR(100),
C VARCHAR(100) COLLATE WIN1250
);

As with it, creation of table e.g.

CREATE TABLE TEST_BBB (
A VARCHAR(100) COLLATE WIN1250,
B VARCHAR(100),
C VARCHAR(100) COLLATE WIN1250
);

will report good values:

SELECT
    RF.RDB$RELATION_NAME
    , RF.RDB$FIELD_NAME
    , F.RDB$COLLATION_ID
    , C.RDB$COLLATION_NAME
FROM
    RDB$RELATION_FIELDS RF
    INNER JOIN RDB$FIELDS F ON F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE
    INNER JOIN RDB$COLLATIONS C ON F.RDB$COLLATION_ID=C.RDB$COLLATION_ID AND F.RDB$CHARACTER_SET_ID=C.RDB$CHARACTER_SET_ID
WHERE
    RF.RDB$RELATION_NAME IN('TEST_BBB')

[arvanus]

So, I just tried with Red Expert 2023.10.1, and the output SQL is:

CREATE TABLE TEST_CCC (
    A VARCHAR(100),
    B VARCHAR(100),
    C VARCHAR(100));

[livius2]

Ha ha ha :) Looks like Red Expert ignore it at all.

But ISQL latest snapshot of FB5 allso show it wrongly ;-)

[livius2]

As you can read in https://github.com/FirebirdSQL/firebird/issues/7837 it is fixed in master branch so you can follow