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

It is possible to store string with lenght 31 chars into column varchar(25) [CORE3373] #3739

Closed
firebird-automations opened this issue Mar 4, 2011 · 16 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Eurosoftware (eurosoftware)

Is duplicated by CORE3669
Is duplicated by CORE3828
Is related to QA560

Test case:

SQL> CREATE DATABASE 'localhost:d:\temp\test1.fdb' user 'sysdba' password 'masterkey' default character set UTF8;
SQL> create table t (c varchar(25));
SQL> insert into t(c) values ('1234567890123456789012345xxxxxx');
SQL> select * from t;

C

1234567890123456789012345xxxxxx

Commits: a1a745b c7ea4a1 FirebirdSQL/fbt-repository@326962a

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue is duplicated by CORE3669 [ CORE3669 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue is duplicated by CORE3828 [ CORE3828 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA560 [ QA560 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

This ticket has been marked as 'fixed' too early.
When CONNECTION character set is NONE one may still do the action described above.
It is possible to insert string with length up to 31 characters (and no more) in any of these ways:
1) as literal
2) as result of expression, e.g. RPAD( ... )
3) from another table.

Also, its no matter how table field is defined: direct definition or via domain.

Consider the following script (correct port=3330 and path = 'C:\MIX\firebird\QA\fbt-repo\tmp\c3373-30.fdb' to yours):

-------------- start ----------------
shell del C:\MIX\firebird\QA\fbt-repo\tmp\c3373-30.fdb 2>nul;

--set names utf8; -- ::: NB ::: this line is *** COMMENTED *** in order to have connection charset = NONE
create database 'localhost/3330:C:\MIX\firebird\QA\fbt-repo\tmp\c3373-30.fdb' default character set utf8;

show version;

set width connection_cset 20;
set width db_default_cset 20;

select c.rdb$character_set_name as connection_cset, r.rdb$character_set_name as db_default_cset
from mon$attachments a
join rdb$character_sets c on a.mon$character_set_id = c.rdb$character_set_id
cross join rdb$database r where a.mon$attachment_id=current_connection;
commit;

--recreate table t1(c varchar(25) character set utf8);
recreate table t1(c varchar(25));
commit;

set term ^;
execute block as
begin
execute statement 'drop domain dm_vc25';
when any do begin end
end
^
set term ;^
commit;

create domain dm_vc25 varchar(25) character set utf8;
commit;
recreate table t2(c dm_vc25);
commit;

set width t1_c 32;
set width t2_c 32;

set count on;
set echo on;
show table t1;
show table t2;

insert into t1(c) values ('1234567890123456789012345xxxxxx');
insert into t1(c) values ( rpad( '1234567890123456789012345',31, 'x' ) );

select c as t1_c, char_length(c) t1_charlen, octet_length(c) t1_octlen from t1;

insert into t2(c) values ('1234567890123456789012345xxxxxx');
insert into t2(c) values ( rpad( '1234567890123456789012345',31, 'x' ) );
select c as t2_c, char_length(c) t2_charlen, octet_length(c) t2_octlen from t2;

insert into t1 select * from t2;
select c as t1_c, char_length(c) t1_charlen, octet_length(c) t1_octlen from t1;

----------- finish ---------------

Run it:

C:\MIX\firebird\fb30sc\isql.exe -q -i c3373-30.sql 1>c3373-30.none.log 2>&1

Result:

ISQL Version: WI-T3.0.0.31767 Firebird 3.0 Beta 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.31767 Firebird 3.0 Beta 2"
Firebird/Windows/Intel/i386 (remote server), version "WI-T3.0.0.31767 Firebird 3.0 Beta 2/tcp (csprog)/P13"
Firebird/Windows/Intel/i386 (remote interface), version "WI-T3.0.0.31767 Firebird 3.0 Beta 2/tcp (csprog)/P13"
on disk structure version 12.0

CONNECTION_CSET DB_DEFAULT_CSET
==================== ====================
NONE UTF8

show table t1;
C VARCHAR(25) Nullable
show table t2;
C (DM_VC25) VARCHAR(25) Nullable

insert into t1(c) values ('1234567890123456789012345xxxxxx');
Records affected: 1
insert into t1(c) values ( rpad( '1234567890123456789012345',31, 'x' ) );
Records affected: 1

select c as t1_c, char_length(c) t1_charlen, octet_length(c) t1_octlen from t1;

T1_C T1_CHARLEN T1_OCTLEN
================================ ============ ============
1234567890123456789012345xxxxxx 31 31
1234567890123456789012345xxxxxx 31 31

Records affected: 2

insert into t2(c) values ('1234567890123456789012345xxxxxx');
Records affected: 1
insert into t2(c) values ( rpad( '1234567890123456789012345',31, 'x' ) );
Records affected: 1
select c as t2_c, char_length(c) t2_charlen, octet_length(c) t2_octlen from t2;

T2_C T2_CHARLEN T2_OCTLEN
================================ ============ ============
1234567890123456789012345xxxxxx 31 31
1234567890123456789012345xxxxxx 31 31

Records affected: 2

insert into t1 select * from t2;
Records affected: 2
select c as t1_c, char_length(c) t1_charlen, octet_length(c) t1_octlen from t1;

T1_C T1_CHARLEN T1_OCTLEN
================================ ============ ============
1234567890123456789012345xxxxxx 31 31
1234567890123456789012345xxxxxx 31 31
1234567890123456789012345xxxxxx 31 31
1234567890123456789012345xxxxxx 31 31

Records affected: 4

PS. Good news: if UNCOMMENT line "--set names utf8; " then all OK, no such oddities.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Looks like you mean: "the very simple original test case is failing again", right?

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> Looks like you mean: "the very simple original test case is failing again", right?

Yes, but only when charset of connection is not specified (i.e. = 'NONE').

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Adriano,

is it possibleto backport fix for this ticket into 2.5.5 ?
Currently there is the same result as it was for WI-T3.0.0.31767:

ISQL Version: WI-V2.5.5.26878 Firebird 2.5
Server version:
Firebird/x86/Windows NT (access method), version "WI-V2.5.5.26878 Firebird 2.5"
Firebird/x86/Windows NT (remote server), version "WI-V2.5.5.26878 Firebird 2.5/tcp (balaha)/P12"
Firebird/x86/Windows NT (remote interface), version "WI-V2.5.5.26878 Firebird 2.5/tcp (balaha)/P12"
on disk structure version 11.2

CONNECTION_CSET DB_DEFAULT_CSET
==================== ====================
NONE UTF8

show table t1;
C VARCHAR(25) Nullable
show table t2;
C (DM_VC25) VARCHAR(25) Nullable

insert into t1(c) values ('1234567890123456789012345xxxxxx');
Records affected: 1
insert into t1(c) values ( rpad( '1234567890123456789012345',31, 'x' ) );
Records affected: 1

select c as t1_c, char_length(c) t1_charlen, octet_length(c) t1_octlen from t1;

T1_C T1_CHARLEN T1_OCTLEN
================================ ============ ============
1234567890123456789012345xxxxxx 31 31
1234567890123456789012345xxxxxx 31 31

Records affected: 2

insert into t2(c) values ('1234567890123456789012345xxxxxx');
Records affected: 1
insert into t2(c) values ( rpad( '1234567890123456789012345',31, 'x' ) );
Records affected: 1
select c as t2_c, char_length(c) t2_charlen, octet_length(c) t2_octlen from t2;

T2_C T2_CHARLEN T2_OCTLEN
================================ ============ ============
1234567890123456789012345xxxxxx 31 31
1234567890123456789012345xxxxxx 31 31

Records affected: 2

insert into t1 select * from t2;
Records affected: 2
select c as t1_c, char_length(c) t1_charlen, octet_length(c) t1_octlen from t1;

T1_C T1_CHARLEN T1_OCTLEN
================================ ============ ============
1234567890123456789012345xxxxxx 31 31
1234567890123456789012345xxxxxx 31 31
1234567890123456789012345xxxxxx 31 31
1234567890123456789012345xxxxxx 31 31

Records affected: 4

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

It's risky. Recently second fix for this broke tests, and then required changes to gpre and epp preprocessing. This changes many things that could not just be completely checked, but just guessing it's now correct.

3.0 was very bad on this, and I didn't look in 2.5. Linux and Windows were working differently after initial build. And after changes and rebuild in Linux, things works different than initial build.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Test created.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

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

2 participants