Re: Informix logs read-only queries? can't handle 'not-exists'?

Ron Choi writes: >i'm running Online 5.0 and seemed to have discovered a bug (or bugs). >i ran the following query overnight: > >select count(*) >from relA >where (relA.x10A = 0 or relA.x10A = 5) and not exists ( > select relB.evenAcct > from relB > where (relB.x100B = 0 or relB.x100B = 49) and> relB.evenAcct = relA.acct > ); > >and got hundreds of "Logical Log Files are Full -- Backup is Needed". >the worst thing is that this corrupted the database. first of all, the above >query is read-only, so it should not create log records (unless informix >is doing something weird and supporting cascade aborts). secondly, The reason log entries are being generated is that the sub-query is generating a temporary table, and the creation of this temp table is being logged. Because you are using a correlated sub-query (the where clause of the sub-query references a table in the dominant query), the subquery must be executed for every tuple in the dominant query. If many rows are being examined by that dominant query, then many temp table are going to be generated for processing the sub-query. These tables do not hang around, but the log entries will fill up the logs. >the database had logging turned off (with 'tbtape -N databasename'), but >it still logs anyway! and a query sure shoudn't corrupt the database. The log records are still being written because OnLine logs all DDL statements (whether logging is on or not) and the creation of even an implicit temp table is, at the rsam level, a DDL action. I agree wholeheartedly that a query should not corrupt the database. What exactly become corrupted and how? >has anyone seen this before? i can reproduce it; i ran this twice already >with the same result. and yes, i was the only person accessing the database >that night. I cannot say for sure if this is considered a "bug". There are reasons why DDL statements are always logged (it is the only way that they can be "undone" in OnLine, in the event that the statement should fail for some reason), but it is not entirely clear that implicit temp tables should have log records written to the log on disk (believe me, there are many complicated elements around this, relating to recovery issues). Dave -- Disclaimer: These opinions are not those of Informix Software, Inc. ************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney