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

Jump to bottom

Varchar computed column length stores incorrect and invalid values for field [CORE5857] #6117

Closed
firebird-automations opened this issue Jun 22, 2018 · 12 comments
Closed

Varchar computed column length stores incorrect and invalid values for field [CORE5857] #6117

firebird-automations opened this issue Jun 22, 2018 · 12 comments
Labels
affect-version: 4.0 Alpha 1 component: charsets/collation component: engine priority: major qa: done with caveats resolution: cannot reproduce type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @mrotteveel

When creating a computed column, the wrong values are stored in RDB$FIELDS for RDB$FIELD_LENGTH, RDB$CHARACTER_SET_ID (and possibly RDB$COLLATION_ID). This produces an invalid length, and applies the wrong character set.

Reproduction (in a database with UTF8 as default characte set):

CREATE TABLE TEST_COLUMN_METADATA (
col_varchar_default_user VARCHAR(100) DEFAULT USER,
col_varchar_default_literal VARCHAR(100) DEFAULT 'literal',
col_varchar_generated VARCHAR(200) COMPUTED BY (col_varchar_default_user || ' ' || col_varchar_default_literal)
)

RDB$FIELDS for these columns have

For col_varchar_default_user and col_varchar_default_literal:
RDB$FIELD_LENGTH = 400 and RDB$CHARACTER_SET_ID = 4

For col_varchar_generated:
RDB$FIELD_LENGTH = -2 which will likely break any tools that uses the length, and RDB$CHARACTER_SET_ID = 0 (NONE) instead of the expected values of 800 (length) and 4 (character set).

In Firebird 3.0.3, this works as expected.

As an additional note, it would probably be better to expect a value of 804 for the length to account for the space added in the expression), but that doesn't match with the RDB$CHARACTER_LENGTH column that reports 200 (and not 201). A better fix would take that into account as well.

If this is the result of a change that allows for a wider width for computed varchars, then I'd strongly suggest not to use negative values here, and the RDB$CHARACTER_LENGTH should also get a value that is more in line with the length.

====== Test Details ======

See test code: temply disabled "select vc_generated from test where id = 2; "
@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

Component: Charsets/Collation [ 10001 ]

Component: Engine [ 10000 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

description: When creating a computed column, the wrong values are stored in RDB$FIELDS for RDB$FIELD_LENGTH, RDB$CHARACTER_SET_ID (and possibly RDB$COLLATION_ID). This produces an invalid length, and applies the wrong character set.

Reproduction (in a database with UTF8 as default characte set):

CREATE TABLE TEST_COLUMN_METADATA (
col_varchar_default_user VARCHAR(100) DEFAULT USER,
col_varchar_default_literal VARCHAR(100) DEFAULT 'literal',
col_varchar_generated VARCHAR(200) COMPUTED BY (col_varchar_default_user || ' ' || col_varchar_default_literal)
)

RDB$FIELDS for these columns have
RDB$FIELD_LENGTH = 400 and RDB$CHARACTER_SET_ID = 4 for col_varchar_default_user and col_varchar_default_literal

For col_varchar_generated, RDB$FIELD_LENGTH = -2 which will likely break any tools that uses the length, and RDB$CHARACTER_SET_ID = 0 (NONE) instead of the expected values of 400 (length) and 4 (character set).

In Firebird 3.0.3, this works as expected.

=>

When creating a computed column, the wrong values are stored in RDB$FIELDS for RDB$FIELD_LENGTH, RDB$CHARACTER_SET_ID (and possibly RDB$COLLATION_ID). This produces an invalid length, and applies the wrong character set.

Reproduction (in a database with UTF8 as default characte set):

CREATE TABLE TEST_COLUMN_METADATA (
col_varchar_default_user VARCHAR(100) DEFAULT USER,
col_varchar_default_literal VARCHAR(100) DEFAULT 'literal',
col_varchar_generated VARCHAR(200) COMPUTED BY (col_varchar_default_user || ' ' || col_varchar_default_literal)
)

RDB$FIELDS for these columns have

For col_varchar_default_user and col_varchar_default_literal:
RDB$FIELD_LENGTH = 400 and RDB$CHARACTER_SET_ID = 4

For col_varchar_generated:
RDB$FIELD_LENGTH = -2 which will likely break any tools that uses the length, and RDB$CHARACTER_SET_ID = 0 (NONE) instead of the expected values of 400 (length) and 4 (character set).

In Firebird 3.0.3, this works as expected.

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

When selecting values, the column is described as having length 32765.

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

description: When creating a computed column, the wrong values are stored in RDB$FIELDS for RDB$FIELD_LENGTH, RDB$CHARACTER_SET_ID (and possibly RDB$COLLATION_ID). This produces an invalid length, and applies the wrong character set.

Reproduction (in a database with UTF8 as default characte set):

CREATE TABLE TEST_COLUMN_METADATA (
col_varchar_default_user VARCHAR(100) DEFAULT USER,
col_varchar_default_literal VARCHAR(100) DEFAULT 'literal',
col_varchar_generated VARCHAR(200) COMPUTED BY (col_varchar_default_user || ' ' || col_varchar_default_literal)
)

RDB$FIELDS for these columns have

For col_varchar_default_user and col_varchar_default_literal:
RDB$FIELD_LENGTH = 400 and RDB$CHARACTER_SET_ID = 4

For col_varchar_generated:
RDB$FIELD_LENGTH = -2 which will likely break any tools that uses the length, and RDB$CHARACTER_SET_ID = 0 (NONE) instead of the expected values of 400 (length) and 4 (character set).

In Firebird 3.0.3, this works as expected.

=>

When creating a computed column, the wrong values are stored in RDB$FIELDS for RDB$FIELD_LENGTH, RDB$CHARACTER_SET_ID (and possibly RDB$COLLATION_ID). This produces an invalid length, and applies the wrong character set.

Reproduction (in a database with UTF8 as default characte set):

CREATE TABLE TEST_COLUMN_METADATA (
col_varchar_default_user VARCHAR(100) DEFAULT USER,
col_varchar_default_literal VARCHAR(100) DEFAULT 'literal',
col_varchar_generated VARCHAR(200) COMPUTED BY (col_varchar_default_user || ' ' || col_varchar_default_literal)
)

RDB$FIELDS for these columns have

For col_varchar_default_user and col_varchar_default_literal:
RDB$FIELD_LENGTH = 400 and RDB$CHARACTER_SET_ID = 4

For col_varchar_generated:
RDB$FIELD_LENGTH = -2 which will likely break any tools that uses the length, and RDB$CHARACTER_SET_ID = 0 (NONE) instead of the expected values of 800 (length) and 4 (character set).

In Firebird 3.0.3, this works as expected.

As an additional note, it would probably be better to expect a value of 804 for the length to account for the space added in the expression), but that doesn't match with the RDB$CHARACTER_LENGTH column that reports 200 (and not 201). A better fix would take that into account as well.

If this is the result of a change that allows for a wider width for computed varchars, then I'd strongly suggest not to use negative values here, and the RDB$CHARACTER_LENGTH should also get a value that is more in line with the length.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

The bug was temporary in not officially released kit, so I'm marking as cannot reproduce.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Cannot Reproduce [ 5 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Consider script (adjust path and DB file name for your env.):

set names utf8;
set bail on;
shell del C:\MIX\firebird\QA\fbt-repo\tmp\c5857.fdb 2>nul;
create database 'localhost:C:\MIX\firebird\QA\fbt-repo\tmp\c5857.fdb' default character set utf8;

recreate table test (
id int,
vc_default_user varchar(10) character set utf8 default user,
vc_default_literal varchar(10) character set utf8 default 'литерал',
vc_generated varchar(20) computed by (vc_default_user || ' ' || vc_default_literal)
);

insert into test values(1, 'австралия', 'антарктида');
insert into test values(2, 'антарктида', 'антарктида');
commit;

set list on;
select
rf.rdb$field_name
,ff.rdb$field_length
,ff.rdb$character_length
,ff.rdb$character_set_id
,ff.rdb$collation_id
,ff.rdb$field_type
,ff.rdb$field_sub_type
from rdb$relation_fields rf
join rdb$fields ff on rf.rdb$field_source = ff.rdb$field_name
where upper(rf.rdb$relation_name) = upper('test')
;

set count on;
set echo on;
select * from test where id = 1;
select * from test where id = 2;

Last statement will finish with error:

Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 20, actual 21

(checked in WI-V3.0.4.33000 and WI-T4.0.0.1040, on Win32; also in LI-T4.0.0.940 on CentOS 6.9 )

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

BTW, if we comment 'default character set utf8' in 'CREATE DATABASE ...' (and thus it will be NONE) then no record can be obtained from table:

select * from test where id = 1;

Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 20, actual 39

This maybe because of:

RDB$FIELD_NAME VC_GENERATED
RDB$FIELD_LENGTH 20
RDB$CHARACTER_LENGTH 20
RDB$CHARACTER_SET_ID 0 ------------------------ <<< ------------------------ [ ! ]
RDB$COLLATION_ID 0
RDB$FIELD_TYPE 37
RDB$FIELD_SUB_TYPE 0

Could engine define proper character set of computed field ( "vc_generated" ) from columns which are referenced in its definition ?

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done with caveats

Test Details: See test code: temply disabled "select vc_generated from test where id = 2; "

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Pavel, you pointed correct results. If one does not known what is doing, just let the type to be calculated automatically.

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

Sorry about 200 vs 201 confusion, I forgot that I explicitly specified the type to avoid another issue. I have now reported that issues as CORE5862.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: 4.0 Alpha 1 component: charsets/collation component: engine priority: major qa: done with caveats resolution: cannot reproduce type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations