Re: Outer Join Bug (was It's Incredible); also, Informix vs. Oracle

alan@po.den.mmc.com (Alan Popiel) wrote: > > I have occasionally used outer joins in both Informix and Oracle, and > after initially debugging my own code, I have never had either product > give me "wrong" answers. The original poster claimed that Informix > goofed up on him two years ago, but would give no other details, not > even an example of a query that produced erroneous results. This sounds > like FUD (spreading Fear, Uncertainty, and Doubt) to me, rather than > any sort of useful communication. > > I don't know what the SQL standard (if any) for outer join syntax is. > I DO know that since these two products use quite different syntax to > specify outer joins, such queries are decidedly non-portable without > manual conversion. > > I also know that Oracle will not do two-stage outer joins in one query. > By this I mean parent-child-grandchild reports, where either the child > or grandchild (and child) may be missing. To accomplish this, I needed > to create two views: one for the parent-child join with outer join on the > child, one with the view-grandchild join with outer join on the grandchild. > (I know, I could have just done the second query, rather than creating a > view for it; but I was building a script for use by others and was able to > have the script contain a simple, unjoined query on the view.) Once I used > this two step approach, Oracle did just fine. I don't recall needing to do > something quite that complex in Informix. > > -- > R. Alan Popiel Internet: alan@den.mmc.com > Lockheed Martin, SLS Voice: 303-977-9998 > I would also be very interested in learning more about this infamous outer join bug in Informix, be it old or new. I have to add that I am constantly amazed by the flexibility of Informix's SQL. The only bug that I have encountered with joins was in an old version where an erroneous rowid was returned. Is this is the bug with all the fuss?