Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Maximal length of user attribute (first-, middle- and lastname) can not exceed 32 OCTETS (rather than CHARACTERS) on FB-3, 31 octets on FB 2.5 [CORE5079] #5366

Open
firebird-automations opened this issue Jan 21, 2016 · 1 comment

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Consider following script:
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
shell del C:\MIX\firebird\QA\fbt-repo\c0856.fdb 2>nul;
set names utf8;
create database 'localhost/3333:C:\MIX\firebird\QA\fbt-repo\c0856.fdb' user 'SYSDBA' password 'masterke';
-- show table sec$users;
commit;

set echo on;
create or alter user tmp$c0856 password '123'
firstname '....:....1....:....2....:....3è' -- octet_length = 32
;

create or alter user tmp$c0856 password '123'
firstname '....:....1....:....2....:....3∑'-- octet_length = 33
;

create or alter user tmp$c0856 password '123'
firstname '....:....1....:....2....:....3.è' -- octet_length = 33
;
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

Its running will issue:

1) on WI-V2.5.6.26963:

Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
-unknown ISC error 335545033
After line 3 in file c0856.sql
Command error: show table sec$users
create or alter user tmp$c0856 password '123'
firstname '....:....1....:....2....:....3è' -- octet_length = 32
;

create or alter user tmp$c0856 password '123'
firstname '....:....1....:....2....:....3∑'-- octet_length = 33
;
Statement failed, SQLSTATE = HY000
modify record error

create or alter user tmp$c0856 password '123'
firstname '....:....1....:....2....:....3.è' -- octet_length = 33
;
Statement failed, SQLSTATE = HY000
modify record error

2) on WI-V3.0.0.32281:

create or alter user tmp$c0856 password '123'
firstname '....:....1....:....2....:....3è' -- octet_length = 32
;

create or alter user tmp$c0856 password '123'
firstname '....:....1....:....2....:....3∑'-- octet_length = 33
;
Statement failed, SQLSTATE = HY000
modify record error

create or alter user tmp$c0856 password '123'
firstname '....:....1....:....2....:....3.è' -- octet_length = 33
;
Statement failed, SQLSTATE = HY000
modify record error

But:

1) on 2.5:
SQL> show table rdb$users;

RDB$USER_NAME (RDB$USER_NAME) VARCHAR(128) CHARACTER SET UNICODE_FSS Not Null
. . .
RDB$FIRST_NAME (RDB$NAME_PART) VARCHAR(32) CHARACTER SET UNICODE_FSS Nullable DEFAULT _UNICODE_FSS ''
RDB$MIDDLE_NAME (RDB$NAME_PART) VARCHAR(32) CHARACTER SET UNICODE_FSS Nullable DEFAULT _UNICODE_FSS ''
RDB$LAST_NAME (RDB$NAME_PART) VARCHAR(32) CHARACTER SET UNICODE_FSS Nullable DEFAULT _UNICODE_FSS ''
. . .

2) on 3.0:

SQL> show table sec$users;

SEC$USER_NAME (RDB$USER) CHAR(31) CHARACTER SET UNICODE_FSS Nullable
SEC$FIRST_NAME (SEC$NAME_PART) VARCHAR(32) CHARACTER SET UNICODE_FSS Nullable
SEC$MIDDLE_NAME (SEC$NAME_PART) VARCHAR(32) CHARACTER SET UNICODE_FSS Nullable
SEC$LAST_NAME (SEC$NAME_PART) VARCHAR(32) CHARACTER SET UNICODE_FSS Nullable
. . .

PS. As reference.
Each of following characters requires:

1) čřžéèêάëïüäöüêŝçęđøłŲĖŪґůőűčž -- two bytes for symbol;

2) ∑∫≈ -- three bytes for symbol.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Something similar can be seen here:

===
set sqlda_display on;
select rdb$relation_name from rdb$relations rows 0;
set sqlda_display off;

set list on;
set echo on;

select
s1,s2,s3
,char_length(s1)
,char_length(s2)
,char_length(s3)
,octet_length(s1)
,octet_length(s2)
,octet_length(s3)
from(
select
cast('....:....1....:....2....:....3.' as char(31) character set unicode_fss) as s1
,cast('....:....1....:....2....:....3è' as char(31) character set unicode_fss) as s2
,cast('....:....1....:....2....:....3∑' as char(31) character set unicode_fss) as s3
from rdb$database
);

recreate table "....:....1....:....2....:....3."(x int); -- octet_length = 31
recreate table "....:....1....:....2....:....3è"(x int); -- octet_length = 32
recreate table "....:....1....:....2....:....3∑"(x int); -- octet_length = 33

Running this script (with -ch utf8) produces:

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 452 TEXT Nullable scale: 0 subtype: 0 len: 124 charset: 4 UTF8
: name: RDB$RELATION_NAME alias: RDB$RELATION_NAME
: table: RDB$RELATIONS owner: SYSDBA

select
s1,s2,s3
,char_length(s1)
,char_length(s2)
,char_length(s3)
,octet_length(s1)
,octet_length(s2)
,octet_length(s3)
from(
select
cast('....:....1....:....2....:....3.' as char(31) character set unicode_fss) as s1
,cast('....:....1....:....2....:....3è' as char(31) character set unicode_fss) as s2
,cast('....:....1....:....2....:....3∑' as char(31) character set unicode_fss) as s3
from rdb$database
);

S1 ....:....1....:....2....:....3.
S2 ....:....1....:....2....:....3è
S3 ....:....1....:....2....:....3∑
CHAR_LENGTH 31
CHAR_LENGTH 31
CHAR_LENGTH 31
OCTET_LENGTH 31
OCTET_LENGTH 32
OCTET_LENGTH 33

recreate table "....:....1....:....2....:....3."(x int); -- octet_length = 31
recreate table "....:....1....:....2....:....3è"(x int); -- octet_length = 32
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Name longer than database column size

recreate table "....:....1....:....2....:....3∑"(x int); -- octet_length = 33
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Name longer than database column size

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant