> -----Original Message----- > From: Jirí Cincura [mailto:j...@cincura.net] > Sent: Viernes, 11 de Septiembre de 2015 6:10 > > Hi, > > can somebody explain this behavior? Tested on FB 2.5.4 and > 2.5.3 x64 SC. > > CREATE TABLE T_TRANSLATION_META ( > F_TRM_ID BIGINT NOT NULL, > F_TRM_CODE VARCHAR(512) CHARACTER SET UTF8 NOT > NULL COLLATE > UNICODE_CI > );
The collation is stored in rdb$relation_fields not in rdb$fields. SQL> select rdb$collation_id from rdb$relation_fields where rdb$relation_name = 'T_TRANSLATION_META'; RDB$COLLATION_ID ================ <null> 3 <---- > CREATE TABLE T_COUNTRY ( > F_CNR_ID BIGINT NOT NULL, > F_TRM_NAME_ID BIGINT DEFAULT -1 NOT NULL, > CF_CNR_NAME COMPUTED BY (((select > F_TRM_CODE > from T_TRANSLATION_META > where F_TRM_ID = F_TRM_NAME_ID))) > ); > > Now this returns `null` (RDB$5 is CF_CNR_NAME). > select rdb$collation_id from rdb$fields where rdb$field_name = 'RDB$5' SQL> set list; SQL> select rdb$field_name, rdb$collation_id from rdb$relation_fields where rdb$relation_name = 'T_COUNTRY'; .... RDB$FIELD_NAME CF_CNR_NAME RDB$COLLATION_ID 3 <------ Again, it's in rdb$relation_fields not in rdb$fields. > Now I later the above mentioned field to the same expression. > ALTER TABLE T_COUNTRY ALTER CF_CNR_NAME COMPUTED BY (((select > F_TRM_CODE > from T_TRANSLATION_META > where F_TRM_ID = F_TRM_NAME_ID))); > > But now the query for rdb$collation_id returns 3. What the hell? Probably Mark would say your way of formulating the question is not very polite. ;-) I sent an email to the master of hell and he answered it's the result of the complex logic to alter tables and fields. SQL> select rdb$field_name, rdb$collation_id from rdb$relation_fields where rdb$relation_name = 'T_COUNTRY' and rdb$collation_id is not null union all select rdb$field_name, rdb$collation_id from rdb$fields where rdb$field_name = 'RDB$5'; RDB$FIELD_NAME CF_CNR_NAME RDB$COLLATION_ID 3 RDB$FIELD_NAME RDB$5 RDB$COLLATION_ID 3 Now it's in both sys tables. C. ------------------------------------------------------------------------------ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel