Path: utzoo!utgpu!news-server.csri.toronto.edu!rutgers!apple!mips!prls!pyramid!infmx!aland From: aland@informix.com (alan denney) Newsgroups: comp.databases Subject: Re: Outerjoin implementation? Summary: NULL issues Message-ID: <1990Oct1.070456.17087@informix.com> Date: 1 Oct 90 07:04:56 GMT References: <1990Sep20.161204.13014@tfic.bc.ca> <42743@sequent.UUCP> Sender: news@informix.com (Usenet News) Distribution: comp Organization: INFORMIX Professional Services ("Peace thru Normalization") Lines: 55 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, |>instead of writing a statement like |> update foo set bar = 37 where qqsv != 0 |>the SQLer has to decide if |> update foo set bar = 37 where qqsv != 0 or qqsv is null |>is what s/he REALLY means. Put another way, |> update foo set bar = 37 where qqsv != 0 and qqsv isnt null |>provides no extra degree of refinement over the first update statement, |> even though it appears to (for the casual observer, at least). 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). |>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. |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. -- Alan S. Denney # Informix # aland@informix.com # {pyramid|uunet}!infmx!aland "The driver says, 'One more cup of coffee and I'll be all right...' 'Pop a Bennie, another Bennie'..." - The Bobs, "Bus Plunge"