Re: IDS 7.24,7.30 Bug in data selection (Now it is a FEATURED BUG)
Felix Koshy Mathews wrote:
>
> Hi - Folks,
>
> I remember someone asking whether 7.24 ( or 7.30 ) is satble enough to
> migrate to that. After moving some of our installations to 7.24 we
> found a problem, which we expected to be eliminated in 7.30. Yes you
> gussed it, 7.30 inherited the bug from 7.24. This may not be a problem
> for everybody, beacuse it depends on the usage.
>
> It was a little more easy this time, to make Informix accept this as a
> bug, because they translated "" to |\\ | whenever the engine performed
> a select ( which will be used in unload ). Eventhough it initially was
> defended as a new feature, the engine was not doing the reverse
> translation during dbload/load ( where they loaded it as a blank
> space, not null ). Finally it was accepted as a bug, the case number
> is 738430.
>
> --
> Have a nice day
>
> Felix K. Mathews
> mailto:fmathews@systems.dhl.com
>
> -------------------------------------------------------- Test case
> -------------------------------------------------------------
>
> create table original(col1 char(3) not null,
> col2 char(25) not null);
> insert into original values ("ABC","Description for ABC");
> insert into original values ("DEF","Description for DEF");
> insert into original values ("GHI","Description for GHI");
> insert into original values ("JKL","Description for JKL");>
> create table new_table(col1 char(3),
> col3 char(1)
> check(col3 in ('A','B','C'))
> constraint ntb_col3,>
> col2 char(25),
> col4 char(2),
> check(col4 in ('A1','B1','C3'))
> constraint ntb_col4,
>
> col5 char(3)
> check(col5 in ('123','A23','B23'))
> constraint ntb_col5);
>
> unload to new_table.dat select col1,"",col2,"","" from original;> -- This is for creating a datafile to be used in dbload.
>
> -- IDS 7.24 & 7.30 creates new_table.dat as below :
> -- ABC|\\ |Description for ABC|\\ |\\ |
> -- DEF|\\ |Description for DEF|\\ |\\ |
> -- GHI|\\ |Description for GHI|\\ |\\ |
> -- JKL|\\ |Description for JKL|\\ |\\ |
>
> -- dbload will/could give constraint errors on ntb_col3,ntb_col4 &
> -- ntb_col5
> -- As Informix claims, if this is a new standard for null string in
> -- select then dbload must be enhanced to translate this new
> -- standard for null string, back to a null instead of a blank value.
>
> -- IDS 7.23 & earlier, it creates new_table.dat as below :
> -- ABC||Description for ABC|||
> -- DEF||Description for DEF|||
> -- GHI||Description for GHI|||
> -- JKL||Description for JKL|||
>
> -- This data file works perfectly with dbload or ISQL-load
This is just a re-cap. After accepting the case, Informix classified
this as a new feature ( one way translation of null string-constants
).
So with this featured bug we are forced use one or the other work
around.
--
Have a nice day
Felix K. Mathews
mailto:fmathews@systems.dhl.com