You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Steve Bosman <st...@gmail.com> on 2006/05/03 23:27:56 UTC
How should trailing spaces be treated in key fields
Hi,
The application I'm writing is copying records from an Oracle database
to a Derby database and today I have been getting error 23505 (showing
a primary key constraint violation) when two records have a key value
differing by a trailing space, e.g. one record has the value 'treat 3'
in one of its key fields and the other record has the value 'treat 3 '
in the same key field. Can anyone tell me if this the correct Derby
behaviour and I'll have to learn to live with it of if this is a bug.
thanks
Steve
Re: How should trailing spaces be treated in key fields
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Stanley Bradbury wrote (2006-05-03 16:21:08):
> Steve Bosman wrote:
>
> >Hi,
> >The application I'm writing is copying records from an Oracle database
> >to a Derby database and today I have been getting error 23505 (showing
> >a primary key constraint violation) when two records have a key value
> >differing by a trailing space, e.g. one record has the value 'treat 3'
> >in one of its key fields and the other record has the value 'treat 3 '
> >in the same key field. Can anyone tell me if this the correct Derby
> >behaviour and I'll have to learn to live with it of if this is a bug.
> >
> >thanks
> >
> >Steve
>
> Hi Steve
>
> This seems like a bug to me.
This is not a bug. According to the SQL standard comparision of two
strings of unequal length is done by padding the shortest string
before comparision. Thus, since, PRIMARY KEY is a UNIQUE NOT NULL
contsraint and UNIQUE is defined by comparision, this is strictly
according to the SQL standard (2003). I can provide the pointers to
the relevant chapters in the standard if needed.
You should rather file a bug against Oracle.
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: How should trailing spaces be treated in key fields
Posted by Stanley Bradbury <St...@gmail.com>.
Steve Bosman wrote:
> Hi,
> The application I'm writing is copying records from an Oracle database
> to a Derby database and today I have been getting error 23505 (showing
> a primary key constraint violation) when two records have a key value
> differing by a trailing space, e.g. one record has the value 'treat 3'
> in one of its key fields and the other record has the value 'treat 3 '
> in the same key field. Can anyone tell me if this the correct Derby
> behaviour and I'll have to learn to live with it of if this is a bug.
>
> thanks
>
> Steve
Hi Steve
This seems like a bug to me. I did a quick test using the varchar
datatype and Derby does preserve the trailing spaces in the data but, as
you report does not see these values as different when checking for
uniqueness. Also when searching for values the tailing space appears to
be ignored. I recommend posting this question to the development
maillist derby-dev@db.apache.org and see what the experts have to say.
They can direct you on how to file a bug for this issue if it turns out
to be non-standard handling of trailing spaces.
EXAMPLES:
>> Shows the spaces in data (between 'dat' and the dash: none, one and
two)
ij> select col1 ||'-'
1
------------------
dat-
dat -
dat -
>> Indicates the length with spaces:
ij> select length(col1) from tst1;
1
-----------
3
4
5
>> Can't ccreate unique index
ij> create unique index idx1 on tst1(col1);
ERROR 23505: The statement was aborted because it would have caused a
duplicate key value
in a unique or primary key constraint or unique index identified by
'IDX1' defined on 'TST1'.
>> And the trailing space is ignored in qualifications (only one row
really qualifies)
ij> select * from tst1 where col1 = 'dat ';
COL1
------
dat
dat
dat
Re: How should trailing spaces be treated in key fields
Posted by Rajesh Kartha <ka...@gmail.com>.
My earlier mail was with reference to primary keys/unique indexes.
ij> create table t4(id int, col1 varchar(6, primary key(col1));
ERROR 42X01: Syntax error: Encountered "," at line 1, column 39.
ij> create table t4(id int, col1 varchar(6), primary key(col1));
0 rows inserted/updated/deleted
ij> insert into t4 values(1,'t');
1 row inserted/updated/deleted
ij> insert into t4 values(1,'t ');
ERROR 23505: The statement was aborted because it would have caused a
duplicate
key value in a unique or primary key constraint or unique index identified
by 'S
QL060503050258840' defined on 'T4'.
ij> insert into t4 values(1,' t');
1 row inserted/updated/deleted
ij> insert into t4 values(1,' t');
1 row inserted/updated/deleted
ij> select '-'||col1||'-' from t4;
1
--------
- t-
- t-
-t-
3 rows selected
-Rajesh
On 5/3/06, Rajesh Kartha <ka...@gmail.com> wrote:
>
> Steve Bosman wrote:
>
> > Hi,
> > The application I'm writing is copying records from an Oracle database
> > to a Derby database and today I have been getting error 23505 (showing
> > a primary key constraint violation) when two records have a key value
> > differing by a trailing space, e.g. one record has the value 'treat 3'
> > in one of its key fields and the other record has the value 'treat 3 '
> > in the same key field. Can anyone tell me if this the correct Derby
> > behaviour and I'll have to learn to live with it of if this is a bug.
> >
> > thanks
> >
> > Steve
>
>
> As I understand, Derby preserves leading spaces but trims trailing ones.
> So
> I think what you are observing is expected.
>
> Example;
> ij> select * from t1;
> ID |COL1
> ------------------
> 1 |te 3
> 1 | te 3
> 1 | te 3
>
>
> Regards,
> Rajesh
>
Re: How should trailing spaces be treated in key fields
Posted by Rajesh Kartha <ka...@gmail.com>.
Steve Bosman wrote:
> Hi,
> The application I'm writing is copying records from an Oracle database
> to a Derby database and today I have been getting error 23505 (showing
> a primary key constraint violation) when two records have a key value
> differing by a trailing space, e.g. one record has the value 'treat 3'
> in one of its key fields and the other record has the value 'treat 3 '
> in the same key field. Can anyone tell me if this the correct Derby
> behaviour and I'll have to learn to live with it of if this is a bug.
>
> thanks
>
> Steve
As I understand, Derby preserves leading spaces but trims trailing ones. So
I think what you are observing is expected.
Example;
ij> select * from t1;
ID |COL1
------------------
1 |te 3
1 | te 3
1 | te 3
Regards,
Rajesh