IDS 10 Bug? The Mystery of the Missing Data

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

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