Re: Good Informix bug found

June Tong wrote: > snip..... > > What gets locked up and what do you kill -9? I'd be really surprised if it's > really locked up; more likely it's just taking a long time to return a result. On 7.13.UC2 the whole instance is shot. The offending user gets no more responses, nor does anyone else. Kill -9 is only option I can find. On 7.12.UC5, offending user gets no more response, others still OK. Onmonitor;Mode;Immediate Shutdown works. Users thread appears to be active and consuming CPU, no pagereads though. I don't know what its doing, but it isn't productive. One month ago, this query ran almost instantaneously (on 7.13.UC2). It's been in the client for a long time, so it ran on v5 also. > > How big are your tables? How long did you wait before you kill -9'ed it? Is > your smallint indexed? If you turn on SET EXPLAIN, which table is being joined > to which? I'll bet it's reading the table with the smallint first, and then > joining that to the char(6). It doesn't matter whether your char(6) is indexed > because it won't use your index anyway. That's what happens when you compare a Tables are roughly 4K and 2K rows. A modest handful of rows changed in each table in the last month, probably no more than 100ish per table (some new, some disappeared, most stayed the same). I waited about 15 minutes a couple of times. There wasn't ever going to be a response. I tried "set explain on", nothing was written to "sqexplain.out". > char to a numeric. What it does is sequentially scan your whole table, > converting every row's char(6) column to an int, and then comparing it with > your smallint. That's because if you have > > SELECT t1.*, t2.* FROM t1, t2 WHERE t1.char6 = t2.smint { and t2.smint = 5 } > > all the following values of char6 should test true: > "5 " > " 5" > " 5 " > "00005" > "5.000" > { I could go on, but you get the idea. } > > Therefore, it can't use the index (if there is one). Thanks for the description, I hadn't quite realized just how nasty this was...I'm even more amazed this worked so fast last month. > > See? And you thought Tech Support was lying to you... I didn't think they were lying when they suggested we shouldn't do such joins, I fully agree it's poor practice, but I can't just change it immediatly without breaking lots of other stuff. What *did* irritate me was that they didn't seem to think this query causing total engine brain lock was much of a problem. Thanks for your interest. Greg > > Junesnip....