Re: Good Informix bug found

David Williams (djw@smooth1.demon.co.uk) wrote: : In article <3219FC57.5AC4@cyberramp.net>, Greg <greg@cyberramp.net> : writes : >Hey - at least you're getting a result. I get a locked up instance I have to : >kill -9 : >to get it's attention. I'm joining two tables, one join col is a char(6) to : >smallint. : > Yeah, I know it's hokey - I didn't design this critter. Tech Support says : >don't join : >dissimilar columns. Never mind that the engine locks up. Thanks Guys. One : >month : >ago, this join worked. Now it barfs, only difference I can see is data content : >in : >tables, and that looks pretty normal. 7.13.UC2 is my release. 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. 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 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). See? And you thought Tech Support was lying to you... June ---- June Tong Informix Software ---- ---- Senior Consultant (415) 926-6140 ---- ---- International Support junet@informix.com ---- ---- Location-du-jour: Oakland ---- * * Standard disclaimers apply * - Please do not send me requests/questions by mail. When I have the knowledge - and time permits, I try to answer questions on comp.databases.informix, but - travel schedule, time, and volume make responding to personal requests - difficult and often slow. Please call your local Informix Technical Support - organization for assistance with technical issues.