Re: Bug #18326

Larry writes: } } Richard Ridley asks if anyone in the real world has seen the evil done by bug 18326. } You bet, and its bad news. We recently upgraded a customer from 4.1 to 5.0, and were bit } by this bug. The way it manifested itself in our application may not be typical, however, } the way we solved the problem may be of interest. } } In our application, a query from a certain screen is done using a three table join. } We select and store the rowid of the rows returned from the query, then use it to retrieve } the actual record when the user does a "Next", "Previous", and so forth. Anyway, this } query worked find under 4.1 for about a year, but with 5.0, it broke immediately. The } bug caused the rowids to be returned in the wrong order. So a query where the user } was looking for an address on "Budd Road" returned a record on "Smith Lane". Talk about } confused users! } } The way we tracked this little devil down to use the "set explain on" and see what } the optimizer was doing. The trick in fixing this is to somehow change your query to } cause the optmizer to choose something other than a SORT MERGE. The SORT MERGE is the } thing that is really screwed up! We were very lucky because we found that adding an } index to one of the fields involved in the join, we were able to cause the optimizer to } drop the SORT MERGE. Had this not worked, we would have had to do a serious re-write. } } So if you are upgrading to 5.0, and you application has some multi-table joins, you may } want to do some testing with "set explain on" and look for SORT MERGEs. If you find any, } look at the output very carefully and be sure it is what you expect! } We did not get bitten by this one per se, but did hit something simliar. Join 2 tables - load the results into an array, when the user selects a row take some action against the table based on the ROWID. Guess what? The rowid returned from the join statement for the second table was the same rowid for every row. We had to turn around and use three or four fields from the row to read the table instead of the rowid. Supposedly fixed in 5.01 - don't know yet. cheers j. _____________________________________________________________________________ Jack Parker | Hewlett Packard, BSMC Boise, Idaho, USA| Your .sig has expired, please enter jparker@hpbs2561.boi.hp.com | a new one. (208) 396-5388 (W) (208) 384-1623 (H) | _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________