Re: perl+varchar in informix and oracle

On Feb 9, 5:44 am, dcrunch...@aim.com wrote: > In article <13qqiaio1m95...@corp.supernews.com>, Jonathan Leffler says... > > >First of all, Informix distinguishes between a null value in a varchar > >and a zero-length non-null varchar. Only Oracle thinks zero-length > >strings are null automatically. > > >Secondly, DBD::Informix distinguishes between nulls (represented by > >Perl's undef) and values (represented by strings of the appropriate length). > > OK I did some testing and found out that the data itself contained > ''. So perl did right. Thank you. > However that prompted to look for something else and I am even > more baffled this time. Here it is: > > create table "informix".testvarchar > ( > fld1 date not null , > fld2 varchar(80) not null , > fld3 integer not null > ) extent size 16 next size 16 lock mode page; > > Same structure in oracle also, except that varchar is varchar2 > and integer is NUMBER. > > This one in Informix: > > $i_dbh->do("delete from testvarchar"); > $i_dbh->do("insert into testvarchar values('01/01/2007',' ',1)"); > $i_dbh->do("insert into testvarchar values('01/03/2007','',2)"); > $i_dbh->do("insert into testvarchar values('01/04/2007',' ',3)"); > # I inserted three rows, one with 5 spaces, one with no space and one with 1 > space. > > my $i_sth = $i_dbh->prepare("select * from testvarchar where fld2 = ''"); > > # the above should fetch only the second row. > > $i_sth->execute() ; > while ( my @row_data = $i_sth->fetchrow_array() ) > { > print "$row_data[0],$row_data[1],$row_data[2],\\n" ; > } > $i_dbh->disconnect(); > exit(0); > > Output of the query > > 01/01/2007, ,1, > 01/03/2007,,2, > 01/04/2007, ,3, > > Why is it retrieving all 3 rows when only one row has '' . > Does Informix make no distinction between 0 space, 1 space and 5 spaces > string. Unfortunately, here you are running into a difference of opinion between me and most of the rest of the IDS development team, and unfortunately the rest of the team has history and code base (and inertia) on their side. In my view, the query you showed illustrates a bug in IDS. In the view of the rest of the team, it is "the way it is supposed to work". You can partially demonstrate the problem by creating a unique or primary key constraint on the varchar column and then try to insert two values that differ by a single trailing blank; you can't do it. In my view, the varchar comparison algorithm is faulty -- in the alternative, that is the way IDS does behave, always has behaved, and therefore should behave in the future. (And I do recognize that fixing this would have far-reaching consequences - look at the data in the system catalog if you want to start seeing some of them.) > Oracle does not allow '' to be saved in not null column. > So I modified it accordingly. > > $o_dbh->do("insert into testvarchar values('01-JAN-2007',' ',1)"); > $o_dbh->do("insert into testvarchar values('01-MAR-2007',' ',2)"); > $o_dbh->do("insert into testvarchar values('01-APR-2007',' ',3)"); > my $o_sth = $o_dbh->prepare("select * from testvarchar where fld2 = ' '"); > > Output:- > > 01-APR-07, ,3, > > which is correct. I agree; IDS doesn't. -=JL=-