Issue Details (XML | Word | Printable)

Key: CORE-3373
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Adriano dos Santos Fernandes
Reporter: Eurosoftware
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

It is possible to store string with lenght 31 chars into column varchar(25)

Created: 04/Mar/11 01:31 PM   Updated: 23/Sep/15 12:55 PM
Component/s: None
Affects Version/s: 2.1.3
Fix Version/s: 3.0 Beta 2

Issue Links:
Duplicate
 
Relate
 

QA Status: Done successfully


 Description  « Hide
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


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Zotov added a comment - 02/Apr/15 05:24 PM
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.

Adriano dos Santos Fernandes added a comment - 02/Apr/15 09:52 PM
Looks like you mean: "the very simple original test case is failing again", right?

Pavel Zotov added a comment - 02/Apr/15 11:35 PM
> 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').

Pavel Zotov added a comment - 07/Jun/15 09:20 PM
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

Adriano dos Santos Fernandes added a comment - 07/Jun/15 09:28 PM
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.

Pavel Cisar added a comment - 23/Sep/15 12:55 PM
Test created.