> -----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

Reply via email to