Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!uunet!zephyr.ens.tek.com!tektronix!sequent!lugnut From: lugnut@sequent.UUCP (Don Bolton) Newsgroups: comp.databases Subject: Re: Outerjoin implementation? Message-ID: <43637@sequent.UUCP> Date: 5 Oct 90 22:16:59 GMT References: <1990Sep20.161204.13014@tfic.bc.ca> <42743@sequent.UUCP> <1990Oct1.070456.17087@informix.com> Reply-To: lugnut@sequent.UUCP (Don Bolton) Distribution: comp Organization: Sequent Computer Systems, Inc Lines: 58 In article <1990Oct1.070456.17087@informix.com> aland@informix.com (alan denney) writes: >In article <42743@sequent.UUCP> lugnut@sequent.UUCP (Don Bolton) writes: >|In article <1990Sep20.161204.13014@tfic.bc.ca> clh@tacitus.UUCP (Chris Hermansen) writes: >|>In article cimshop!davidm@uunet.UU.NET (David S. Masterson) writes: >|>>In article pcg@cs.aber.ac.uk >|>>(Piercarlo Grandi) writes: >|> >|>NULLs almost guarantee strange `gotchas', don't you think? For instance, A whole lot of stuff deleted. >This has to do with the very definition of NULL -- it requires that >NULL values fail any "not equal" filter. Since a NULL is by definition >an "unknown" value, then you don't know that it's not 0, right? > >In any case, you can avoid the whole issue by disallowing NULLs in the >column(s) in question (by creating the column with NOT NULL). THis works great in many applications, however a leads tracking system or something that has many possibilities for empty columns like that is still a problem. NOT NULL is sometimes NOT FEASABLE :-) >|>It's also really handy that >|> >|> select qqsv,count(*) from foo >|> group by qqsv >|> >|>produces a separate line for every row in foo that has a null in qqsv, >|>don't you think? >|> >|ARRGGH, I hate it when that happens, have to update the nulls with the >|string value "NULL" run the select and then "unupdate" or run several >|selects and cut and paste. Either way its messy. > >There was quite a bit of theoretical debate about which method was >"proper". Now that ANSI has spoken, Informix engines have been changed >to group NULL values together (the way you apparently prefer). This >change is effective in the current release - 4.0. > And there was *much* rejoicing. >|Also with informix, a select count(*) from foo where qqsv is null >|will produce a SINGLE row with the count. Can you say inconsistant? >|I knew you could :-) > >Uh, can you say "pilot error"? [-: > >How can you call that inconsistent? The response you complain about >is exactly what you asked for: how many rows have NULL for qqsv. >No GROUPing is involved. > Neither was any grouping involved in the other example where nulls were concerned. :-) But you see the result from the select count(*) from where null example was what I had been raised to expect on the group by result. To my primal training they BOTH should have returned a SINGLE row with the count. anyways its moot since its now fixed in the curent release.