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-issue-importer opened this issue Mar 4, 2011 · 16 comments

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Mar 4, 2011

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 6, 2011

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 6, 2011

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 18, 2011

Modified by: @asfernandes

Link: This issue is duplicated by CORE3669 [ CORE3669 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 23, 2012

Modified by: Sean Leyne (seanleyne)

Link: This issue is duplicated by CORE3828 [ CORE3828 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 29, 2014

Modified by: @pcisar

Link: This issue is related to QA560 [ QA560 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 2, 2015

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 3, 2015

Commented by: @asfernandes

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 3, 2015

Modified by: @asfernandes

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

resolution: Fixed [ 1 ] =>

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 3, 2015

Modified by: @asfernandes

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 3, 2015

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 3, 2015

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 28, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 8, 2015

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 8, 2015

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-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 23, 2015

Commented by: @pcisar

Test created.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 23, 2015

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