Re: NULLs in columns referencing foreign keys

On Mon, 28 Apr 1997, Nils Myklebust wrote: > Except for the flaw in SQL that it doesn't realy know about unknown. > You have to use "IS (NOT) NULL" to explisitly test for unknown. In all > other cases NULL is the same as false for testing purposes. In implicit, but not explicit tests. The code (attached below) will produce, for instance, this output: true = true false = false false = 0 true = 1 null = The truth tables were correct, but even 4gl is clever enough not to assume that two unknowns are equal, whereas two falses are equal. Therefore they're not treated in quite the same way. The interesting part is at the end -- just because (false = null) doesn't evaluate to true does not mean that false != null. Since null is, by definition, unknown, any equality tests will return false (implying "not true"). However, that does not mean that false and null may be treated in the same way (as shown by the second test in the last block). Indeed, it should be treated as a very special case -- and I don't believe that this is a bug of any sort, merely good practice. Any assumption that the language makes about unknown values will be wrong for /someone/, therefore it is left to the developer. Bear in mind, Nils, that I think that you know this well enough, I just wanted to make the point as there are other people watching this discussion who may /not/ have realized that. No offence was meant. - - Sample truth table 4gl code - - MAIN DEFINE f_null SMALLINT DEFINE f_true SMALLINT DEFINE f_false SMALLINT INITIALIZE f_null TO NULL LET f_true = TRUE LET f_false = FALSE IF f_true = f_true THEN DISPLAY "true = true " END IF IF f_true = f_false THEN DISPLAY "true = false" END IF IF f_true = f_null THEN DISPLAY "true = null " END IF IF f_false = f_false THEN DISPLAY "false = false" END IF IF f_false = f_null THEN DISPLAY "false = null" END IF IF f_null = f_null THEN DISPLAY "null = null" END IF DISPLAY " " DISPLAY "false = ", f_false DISPLAY "true = ", f_true DISPLAY "null = ", f_null DISPLAY " " IF (f_false = f_null) THEN DISPLAY "(false = null)" END IF IF NOT (f_false = f_null) THEN DISPLAY "not (false = null)" END IF IF (f_false <> f_null) THEN DISPLAY "(false <> null)" END IF DISPLAY " " END MAIN