VARCHAR, Trailing Spaces, and Unique Constraint

Another look at VARCHAR. This time, the implication of the 'important-ness' of trailing space(s) and constraint.

Let's start from a table with 3 rows:

db2 => select c1, length(c1) from smallt ;

C1   2
---- -----------
B              1
B              2
B              3

  3 record(s) selected.


As you can see, these three rows are 'unique' in the sense that they have different length. In Oracle, they are three distinct rows with all the 'expected' properties.

Can we create a unique index on it?

Let's try:


db2 => create unique index uqi on smallt (c1 );
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0603N  A unique index cannot be created because the table contains data
that would result in duplicate index entries.  SQLSTATE=23515


NOPE!

Another example of the 'useless' trailing spaces in VARCHAR.

VARCHAR and Equality Comparison

This is a follow-up on the previous post.

Now that we know trailing space(s) is 'not that important' as far as DB2 is concerned, what would you expect in a simple '=' comparison?

Let's do a little test:



db2 => create table smallt (c1 varchar(4)) ;
DB20000I  The SQL command completed successfully.

db2 => insert into smallt values 'B', 'B ', 'B  ';
DB20000I  The SQL command completed successfully.

db2 => select length(c1), c1 from smallt ;

1           C1
----------- ----
          1 B
          2 B
          3 B

  3 record(s) selected.


We have three rows with different 'length' due to the trailing space(s) we inserted. If we compare this column to itself, ie, c1 = c1, how many rows would you expect?

Three?

Let's see ...



db2 => select * from smallt t1, smallt t2 where t1.c1 = t2.c1 ;

C1   C1
---- ----
B    B
B    B
B    B
B    B
B    B
B    B
B    B
B    B
B    B

  9 record(s) selected.


Need I say more? ;)

Varchar: When a limit is unlimited

How much do you know about 'varchar' type in DB2?

I thought I knew, until I ran a little test and got a nasty surprise.

Here's a simple test case to illustrate:


db2 => create table smallt ( c1 varchar(1));
DB20000I  The SQL command completed successfully.

db2 => insert into smallt values('B             ') ;
DB20000I  The SQL command completed successfully.


db2 => select * from smallt ;

C1
--
B

  1 record(s) selected.



See the 'problem'?


This behaviour can be very disturbing for those used to Oracle DB. Another potential 'hidden' bug for Oracle developer doing DB2.