Re: RE: IDS 10 Bug? The Mystery of the Missing Data

BTW this bug is aknowledge by IBM and adressed in 10.0.x6 J. -----Original Message----- From: "Doug Conrey" <doug_conrey@oci.com> To: "Tom Girsch" <tgirsch@NOSPAM.midsouth.rr.com>, <informix-list@iiug.org> Date: Thu, 7 Sep 2006 07:49:04 -0600 Subject: RE: IDS 10 Bug? The Mystery of the Missing Data Yes, using the AIX build of 10.0.FC5. DC > -----Original Message----- > From: informix-list-bounces@iiug.org [mailto:informix-list- > bounces@iiug.org] On Behalf Of Tom Girsch > Sent: Wednesday, September 06, 2006 8:39 PM > To: informix-list@iiug.org > Subject: Re: IDS 10 Bug? The Mystery of the Missing Data > > Has anyone else been able to replicate this? > > Thomas J. Girsch wrote: > > I believe I've found a disturbing bug in IDS 10 involving rows inserted > > into a table mysteriously disappearing. And I even have the Informix > > Holy Grail: An easy-to-reproduce test case! > > > > I have replicated this behavior on Solaris 8, Solaris 10, Linux 2.4, and > > AIX 5.2 on IDS 10.00.UC4, 10.00.UC5, 10.00.FC4 and 10.00.FC5. I was > > hoping others out there who are running IDS 10 could reproduce this, > > especially on other platforms. > > > > Here's how to reproduce: > > > > Build the following table: > > > > CREATE TABLE testtab ( > > col1 INTEGER > > );> > > > Now create this procedure: > > > > create procedure testproc()> > > > DEFINE v_Outer smallint; > > DEFINE v_Inner smallint; > > DEFINE v_Min smallint; > > > > FOR v_Outer = 1 TO 2 > > > > truncate table testtab; > > > > FOR v_Inner = 1 TO 2 > > > > insert into testtab > > VALUES ( 42 );> > > > -- The following can be ANY select that reads > the > > -- above table. > > select min(col1) > > into v_Min > > from testtab; > > > > end FOR; > > > > END FOR; > > > > end procedure; > > > > Now, execute the procedure: > > > > EXECUTE PROCEDURE testproc();> > > > Following execution, if everything was successful, there should be two > > records (both value 42) in the testtab table at the end of execution. > > And, in fact, it looks like there are: > > > > SELECT COUNT(*) > > FROM testtab;> > > > (count(*)) > > > > 2 > > > > 1 row(s) retrieved. > > > > However, things go awry when we try to retrieve the data: > > > > SELECT * > > FROM testtab;> > > > col1 > > > > > > No rows found. > > > > Also, a count forcing sequential scan disagrees with the regular count: > > > > SELECT COUNT(*) > > FROM testtab > > WHERE 1 = 1;> > > > (count(*)) > > > > 0 > > > > 1 row(s) retrieved. > > > > So somehow, the rows we inserted in the stored procedure are simply > > "missing." > > > > Based on my testing, I see a few necessary elements in reproducing the > > problem: > > > > - The TRUNCATE TABLE statement must be used in the OUTER loop of the > > procedure. If you use DELETE FROM instead, the problem goes away. > > - The INSERT must occur in the INNER loop. > > - Within the INNER loop, we must SELECT data from the table immediately > > after inserting it; it must ACTUALLY hit the table, so SELECT COUNT(*) > > won't do it. > > > > With these characteristics met, I've done some tracing, and have found > > that the problem first occurs the second time the inner loop is entered. > > That is, on the second iteration of the outer loop. Clearly, there's > > something squirrelly going on with the combination of stored procedures, > > TRUNCATE TABLE, and nested loops. > > > > - Tom Girsch > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list Jean Sagi jeansagi@myrealbox.com jeansagi@gmail.com