db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: How should trailing spaces be treated in key fields
Date Wed, 03 May 2006 23:21:08 GMT
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   




Mime
View raw message