SEC$GLOBAL_AUTH_MAPPING wrong field sizes

[livius2]

Open anyd Firebird 3 database. Go to System tables and open properties of SEC$GLOBAL_AUTH_MAPPING. There are wrong field sizes like Char(0) othere sizes are also wrong

SEC$MAP_NAME CHAR(10) CHARACTER SET UNICODE_FSS,
  SEC$MAP_USING CHAR(0) CHARACTER SET UNICODE_FSS,
  SEC$MAP_PLUGIN CHAR(10) CHARACTER SET UNICODE_FSS,
  SEC$MAP_DB CHAR(10) CHARACTER SET UNICODE_FSS,
  SEC$MAP_FROM_TYPE CHAR(10) CHARACTER SET UNICODE_FSS,
  SEC$MAP_FROM CHAR(85) CHARACTER SET UNICODE_FSS,
  SEC$MAP_TO_TYPE SMALLINT,
  SEC$MAP_TO CHAR(10) CHARACTER SET UNICODE_FSS

when you do select, descriptions of fields are ok:

Preparing statement: SELECT r.RDB$DB_KEY, r.SEC$MAP_NAME, r.SEC$MAP_USING, r.SEC$MAP_PLUGIN,
    r.SEC$MAP_DB, r.SEC$MAP_FROM_TYPE, r.SEC$MAP_FROM, r.SEC$MAP_TO_TYPE,
    r.SEC$MAP_TO
FROM SEC$GLOBAL_AUTH_MAPPING r
Statement prepared (elapsed time: 0.009s).
Field #01: SEC$GLOBAL_AUTH_MAPPING.DB_KEY Alias:DB_KEY Type:STRING(8)
Field #02: SEC$GLOBAL_AUTH_MAPPING.SEC$MAP_NAME Alias:SEC$MAP_NAME Type:STRING(31)
Field #03: SEC$GLOBAL_AUTH_MAPPING.SEC$MAP_USING Alias:SEC$MAP_USING Type:STRING(1)
Field #04: SEC$GLOBAL_AUTH_MAPPING.SEC$MAP_PLUGIN Alias:SEC$MAP_PLUGIN Type:STRING(31)
Field #05: SEC$GLOBAL_AUTH_MAPPING.SEC$MAP_DB Alias:SEC$MAP_DB Type:STRING(31)
Field #06: SEC$GLOBAL_AUTH_MAPPING.SEC$MAP_FROM_TYPE Alias:SEC$MAP_FROM_TYPE Type:STRING(31)
Field #07: SEC$GLOBAL_AUTH_MAPPING.SEC$MAP_FROM Alias:SEC$MAP_FROM Type:STRING(255)
Field #08: SEC$GLOBAL_AUTH_MAPPING.SEC$MAP_TO_TYPE Alias:SEC$MAP_TO_TYPE Type:SMALLINT
Field #09: SEC$GLOBAL_AUTH_MAPPING.SEC$MAP_TO Alias:SEC$MAP_TO Type:STRING(31)

[arvanus]

Hello, which Fr version are you using? I tried in the latest snapshot, and got this: (I'm using Firebird 5 RC1)

[livius2]

Firebird 3

[arvanus]

Please, confirm which charset you are using to connect, and what are the default from your DB

[livius2]

[livius2]

Firebird 3

remember that i post above that i connect to Firebird 3

[arvanus]

I'm trying to understand where this UNICODE_FSS charset came from. Any idea? I'm not an expert about charset, so no idea if it could be related. Anyway, can you send me a sample database to look here?

[arvanus]

Hi, can you provide an reproducible database for me to take a look?

[livius2]

CORE-5849.zip

I added an attachment with the sample database. Register it with utf8 like here https://github.com/mariuz/flamerobin/issues/304#issuecomment-1753566933

[arvanus]

So, looks like Fb3 and 4+ handles SEC$GLOBAL_AUTH_MAPPING field_lenght differently in Fb3

Fb3 size = 31 bytes
Fb4 size = 252 bytes
Fb5 size = 252 bytes

Note that there is another field rdb$character_length, but Flamerobin uses rdb$field_length/rdb$bytes_per_character to retrieve field size I don't know if its a bug from Firebird or on purpose or not, but if you run this query you'll see that it has different results from Fb3 to Fb4 and 5

select 
             f.rdb$field_name,            --  1
             f.rdb$field_type,            --  2
             f.rdb$field_sub_type,        --  3
             f.rdb$field_length,          --  4
             f.rdb$field_precision,       --  5
             f.rdb$field_scale,           --  6
             c.rdb$character_set_name,    --  7
             f.rdb$character_length,      --  8
             f.rdb$null_flag,             --  9
             f.rdb$default_source,        -- 10
             l.rdb$collation_name,        -- 11
             f.rdb$validation_source,     -- 12
             f.rdb$computed_blr,          -- 13
             c.rdb$bytes_per_character    -- 14
         from rdb$fields f
         left outer join rdb$character_sets c
             on c.rdb$character_set_id = f.rdb$character_set_id
         left outer join rdb$collations l
             on l.rdb$collation_id = f.rdb$collation_id
             and l.rdb$character_set_id = f.rdb$character_set_id
         left outer join rdb$types t on f.rdb$field_type=t.rdb$type
         where t.rdb$field_name = 'RDB$FIELD_TYPE' and f.RDB$FIELD_NAME='RDB$MAP_NAME'

[arvanus]

Looks like it's something related to Fb3 itself, running this SQL in Fb3 brings tons of records, for Fb2.5,4 and 5 none:

select 
             f.rdb$field_name,            --  1
             f.rdb$field_type,            --  2
             f.rdb$field_sub_type,        --  3
             f.rdb$field_length,          --  4
             f.rdb$field_precision,       --  5
             f.rdb$field_scale,           --  6
             c.rdb$character_set_name,    --  7
             f.rdb$character_length,      --  8
             f.rdb$null_flag,             --  9
             f.rdb$default_source,        -- 10
             l.rdb$collation_name,        -- 11
             f.rdb$validation_source,     -- 12
             f.rdb$computed_blr,          -- 13
             c.rdb$bytes_per_character    -- 14
         from rdb$fields f
         left outer join rdb$character_sets c
             on c.rdb$character_set_id = f.rdb$character_set_id
         left outer join rdb$collations l
             on l.rdb$collation_id = f.rdb$collation_id
             and l.rdb$character_set_id = f.rdb$character_set_id
         left outer join rdb$types t on f.rdb$field_type=t.rdb$type
         --where t.rdb$field_name = 'RDB$FIELD_TYPE' and f.RDB$FIELD_NAME='RDB$MAP_NAME'
         where 1=1 and nullif(f.RDB$CHARACTER_LENGTH,0) is not null 
         and f.RDB$CHARACTER_LENGTH<>f.RDB$FIELD_LENGTH/c.RDB$BYTES_PER_CHARACTER

[mrotteveel]

Until Firebird 4.0, the system columns for identifiers with character set UNICODE_FSS are special, in that they are CHAR(31), but also only max 31 bytes long (and not 3 * 31 = 93 bytes like normal CHAR(31) CHARACTER SET UNICODE_FSS columns). Only for system columns, dividing by RDB$CHARACTER_SET.RDB$BYTES_PER_CHARACTER is the wrong way to derive their length (because you end up with 10 instead of 31).

The reason that the query by @arvanus is different between Firebird 2.5 and Firebird 3.0 is that in Firebird 2.5, the RDB$CHARACTER_LENGTH column is NULL for those columns, while in Firebird 3.0 it reports 31. The reason it is different in Firebird 4.0 and higher is because those columns are now CHAR(63) CHARACTER SET UTF8 with RDB$FIELD_LENGTH = 252 and RDB$CHARACTER_LENGTH = 63.

[arvanus]

Hello Mark, first of all thank you for your detailed answer! So, to fix Flamerobin execution what do you suggest? For Fb3<, keep as is, and for Fb3> use the new filled field RDB$CHARACTER_LENGTH? Thanks!

[arvanus]

Also, why does if I create an table in the employee DB (FB3), with a char(30) charset UNICODE_FSS, it will display correctly as 90 bytes and 30 char length? This is a strange thing at my POV. Looks like there is something very specific to SEC$GLOBAL_AUTH_MAPPING.

CREATE TABLE NEW_TABLE (
    NEW_FIELD CHAR(30) CHARACTER SET UNICODE_FS
)

[mrotteveel]

Also, why does if I create an table in the employee DB (FB3), with a char(30) charset UNICODE_FSS, it will display correctly as 90 bytes and 30 char length? This is a strange thing at my POV. Looks like there is something very specific to SEC$GLOBAL_AUTH_MAPPING.

As I said, system columns for identifiers are special: they are 31 characters and 31 bytes long, while a "normal" CHAR(31) CHARACTER SET UNICODE_FSS column is 31 characters and 93 bytes. As I understand it, this was done when Unicode support for identifiers was added, to ensure the maximum byte length of a field name in the XSQLDA didn't change.

This isn't specific to SEC$GLOBAL_AUTH_MAPPING, it applies to all system columns that are CHAR(31) (and FlameRobin does it wrong for all of them)!

For Fb3<, keep as is, and for Fb3> use the new filled field RDB$CHARACTER_LENGTH?

Yes, that sounds OK to me.