Informix 7.30UC3 Bug
Hello all
A waning to all people who create views with SQL Version 7.30UC3
The following query produces a view which does not match its definition:
create view "znt_dba".v_attribut1
(actrowid,c_attribut_id,c_entitaet_id,c_attribut_lnr,c_name,c_col_typ,
c_col_muss,c_g_ab,c_g_bis,c_erf_zeitp,c_erf_user_id,c_erf_prog,c_ers_zeitp,c_status)
as
select x0.rowid ,x0.c_attribut_id ,x0.c_entitaet_id ,x0.c_attribut_lnr
,x0.c_name ,x0.c_col_typ ,x0.c_col_muss ,x0.c_g_ab ,x0.c_g_bis
,x0.c_erf_zeitp ,x0.c_erf_user_id ,x0.c_erf_prog ,x0.c_ers_zeitp
,x0.c_status from "znt_dba".p_attribut x0 where ((((CURRENT
year to fraction(3) >= x0.c_g_ab ) AND (CURRENT year to fraction(3)
<= x0.c_g_bis ) ) AND (x0.c_ers_zeitp = datetime(9999-12-31
23:59:59.999) year to fraction(3) ) ) AND (x0.c_status =
0 ) ) ;
If i do a dbschema after the creation of the view is gives:
create view "znt_dba".v_attribut1
(actrowid,c_attribut_id,c_entitaet_id,c_attribut_lnr,c_name,c_col_typ,
c_col_muss,c_g_ab,c_g_bis,c_erf_zeitp,c_erf_user_id,c_erf_prog,c_ers_zeitp,c_status)
as
select x0.rowid ,x0.c_attribut_id ,x0.c_entitaet_id ,x0.c_attribut_lnr
,x0.c_name ,x0.c_col_typ ,x0.c_col_muss ,x0.c_g_ab ,x0.c_g_bis
,x0.c_erf_zeitp ,x0.c_erf_user_id ,x0.c_erf_prog ,x0.c_ers_zeitp
,x0.c_status from "znt_dba".p_attribut x0 where ((((CURRENT
year to fraction(3) >= x0.c_g_ab ) AND (CURRENT year to fraction(3)
<= x0.c_g_bis ) ) AND (x0.c_ers_zeitp = datetime(9999-12-31
03:59:59.999) year to fraction(3) ) ) AND (x0.c_status =
0 ) ) ;
So datetime(9999-12-31 23:59:59.999) was stored as
datetime(999-12-31 03:59:59.999)!
It seams that the linefeed in the datetime constant expression is the
reason of this bug.
In Version 7.12 dbschema gave the correct output (23) but it was not
possible to
rerun the generated sql-File without removing the linefeed.
In Version 7.30 I can run the sql-File but it creates a wrong view.
Not a big improvement. ;-)
Markus Ochsner
--------------------------------------------------------------------
/\\ Tel: +41 32 329 90 51
Markus Ochsner /--\\KROS AG Firma: +41 32 329 90 30
dipl. Inf. Ing. ETH/HTL unterer Quai 37 Fax: +41 32 329 90 35
CH-2502 Biel email: ochsner@akros.ch
--------------------------------------------------------------------