## Re: [Firebird-docs] New Null Guide - please comment

 Re: [Firebird-docs] New Null Guide - please comment From: Paul Vinkenoog - 2006-12-17 00:32:38 ```Hi Mimmo, >> The second one is (as I've intended it) >> (A<>Expr1) AND (A<>Expr2) AND ... >> which is NULL AND NULL AND NULL... >> then the result is NULL directly, >> and not "not NULL" that is "NULL" > Maybe this are only academic thoughts, Yes, but interesting enough! > ...but the only thing that I must observe, is that Firebird is > consistent if and only if always A NOT IN () <=> NOT (A IN()) > If they differs sometimes or somehow, or better, this is not as > defined, then in case of > ? If none of the expressions in the list equal A (and list contains > 1 or more NULLs): You're right that this is the trickiest one: A not null && A not in list && list contains null > - ?A IN( Expr1, Expr2, ..., ExprN )? returns NULL > - ?A NOT IN( Expr1, Expr2, ..., ExprN )? returns NULL > the second expression should then return False if any of the ExprN > is not null. No, let's write it out (for this particular situation): IN( e1, e2 ... en ) <==> A=e1 or A=e2 ... or A=en <==> false ... or null ... or false // never true, A not in list <==> null NOT( IN( e1, e2 ... en )) <==> NOT( null ) <==> null A<>e1 and A<>e2 ... and A<>e // "your" interpretation of NOT IN <==> true ... and null ... and true // never false, A not in list <==> null So whether you interpret NOT IN() as NOT( IN() ), or as a conjunction of inequality tests, the outcome is the same. Greetings, Paul Vinkenoog ```

 [Firebird-docs] New Null Guide - please comment From: Paul Vinkenoog - 2006-12-16 16:40:52 ```Hi all, The new Firebid Null Guide is finally ready, and it's about 2,5 times as big as the old one. Please have a look at it and let me know if you find any errors, omissions, etc. You can find it here: http://www.firebirdsql.org/devel/doc/manual/temp/nullguide/ The documents you want are nullguide.pdf and nullguide.html (both near the bottom of the listing). All the other HTMLs are individual level-1 sections. The source is in nullguide_nw.xml (not yet in CVS). You don't have to report any widowed headers, I know they're there. The history entry for this version is also still incomplete. Greetings, Paul Vinkenoog ```
 Re: [Firebird-docs] New Null Guide - please comment From: Mimmo - 2006-12-16 19:26:14 ```Hi Paul, Paul Vinkenoog wrote: > The new Firebid Null Guide is finally ready, and it's about 2,5 times > as big as the old one. I know what to do this winter... > Please have a look at it and let me know if you find any errors, > omissions, etc. Thinking to myself, maybe useful that bugs will be cross referenced with chapter they belong. As example, in nullguide-alter-pop-tables.html#nullguide-add-not-null-field in the "important" note, put a link to bug list (something like "see bug detail") and vice versa. Only a suggestion. Un gran bel lavoro, davvero! Complimenti! Ciao. Mimmo. ```
 Re: [Firebird-docs] New Null Guide - please comment From: Paul Vinkenoog - 2006-12-16 23:54:14 ```Hi Mimmo, > Thinking to myself, maybe useful that bugs will be cross referenced > with chapter they belong. As example, in > nullguide-alter-pop-tables.html#nullguide-add-not-null-field in the > "important" note, put a link to bug list (something like "see bug > detail") and vice versa. I've done it in one or two places, but on most occasions the bugs list entry contains exactly the same information as the main text, so I thought it was rather pointless in those cases. Anyway I'll look at it again and maybe make some more crosslinks. > Un gran bel lavoro, davvero! Complimenti! Grazie! Infatti ci ho messo molto tempo. Greetings, Paul Vinkenoog ```
 Re: [Firebird-docs] New Null Guide - please comment From: Mimmo - 2006-12-16 20:01:04 ```Hi Paul, About the IN() predicate, =95 A IN ( Expr1, Expr2, ..., ExprN ) =95 A NOT IN ( Expr1, Expr2, ..., ExprN ) the first one is correctly (A =3D Expr1) or (A=3DExpr2) or .... and then NULL or NULL or NULL or ... Ok. The second one is (as I've intended it) (A<>Expr1) AND (A<>Expr2) AND ... which is NULL AND NULL AND NULL... then the result is NULL directly, and not "not NULL" that is "NULL" Or not? Ciao. Mimmo. ```
 Re: [Firebird-docs] New Null Guide - please comment From: Paul Vinkenoog - 2006-12-17 00:32:38 ```Hi Mimmo, >> The second one is (as I've intended it) >> (A<>Expr1) AND (A<>Expr2) AND ... >> which is NULL AND NULL AND NULL... >> then the result is NULL directly, >> and not "not NULL" that is "NULL" > Maybe this are only academic thoughts, Yes, but interesting enough! > ...but the only thing that I must observe, is that Firebird is > consistent if and only if always A NOT IN () <=> NOT (A IN()) > If they differs sometimes or somehow, or better, this is not as > defined, then in case of > ? If none of the expressions in the list equal A (and list contains > 1 or more NULLs): You're right that this is the trickiest one: A not null && A not in list && list contains null > - ?A IN( Expr1, Expr2, ..., ExprN )? returns NULL > - ?A NOT IN( Expr1, Expr2, ..., ExprN )? returns NULL > the second expression should then return False if any of the ExprN > is not null. No, let's write it out (for this particular situation): IN( e1, e2 ... en ) <==> A=e1 or A=e2 ... or A=en <==> false ... or null ... or false // never true, A not in list <==> null NOT( IN( e1, e2 ... en )) <==> NOT( null ) <==> null A<>e1 and A<>e2 ... and A<>e // "your" interpretation of NOT IN <==> true ... and null ... and true // never false, A not in list <==> null So whether you interpret NOT IN() as NOT( IN() ), or as a conjunction of inequality tests, the outcome is the same. Greetings, Paul Vinkenoog ```
 Re: [Firebird-docs] New Null Guide - please comment From: Mimmo - 2006-12-16 20:40:57 ```Mimmo wrote: > Hi Paul, >=20 > About the IN() predicate, >=20 > =95 A IN ( Expr1, Expr2, ..., ExprN ) > =95 A NOT IN ( Expr1, Expr2, ..., ExprN ) >=20 > the first one is correctly > (A =3D Expr1) or (A=3DExpr2) or .... > and then NULL or NULL or NULL or ... > Ok. >=20 > The second one is (as I've intended it) > (A<>Expr1) AND (A<>Expr2) AND ... > which is NULL AND NULL AND NULL... > then the result is NULL directly, > and not "not NULL" that is "NULL" >=20 Maybe this are only academic thoughts, but the only thing that I must=20 observe, is that Firebird is consistent if and only if always A NOT IN () <=3D> NOT (A IN()) If they differs sometimes or somehow, or better, this is not as defined,=20 then in case of =95 If none of the expressions in the list equal A (and list contains 1 o= r=20 more NULLs): - =93A IN( Expr1, Expr2, ..., ExprN )=94 returns NULL - =93A NOT IN( Expr1, Expr2, ..., ExprN )=94 returns NULL the second expression should then return False if any of the ExprN is=20 not null. Ciao. Mimmo. ```
 Re: [Firebird-docs] New Null Guide - please comment From: Mimmo - 2006-12-17 11:45:01 ```Hi Paul, Paul Vinkenoog wrote: > No, let's write it out (for this particular situation): > > IN( e1, e2 ... en ) > <==> A=e1 or A=e2 ... or A=en > <==> false ... or null ... or false // never true, A not in list > <==> null > > NOT( IN( e1, e2 ... en )) > <==> NOT( null ) > <==> null > > A<>e1 and A<>e2 ... and A<>e // "your" interpretation of NOT IN > <==> true ... and null ... and true // never false, A not in list > <==> null > > So whether you interpret NOT IN() as NOT( IN() ), or as a conjunction > of inequality tests, the outcome is the same. You're right. My fault: I switched my notes. Anyway it's interesting: "my" interpretation of NOT IN() is equivalent, only if De Morgan "rules" and other principles of classic logic remain valid in NULL-able logic. NOT ( IN()) // by definition of NOT IN () NOT ( (A=Ex1) or (A=Ex2) ) // by definition of IN() (NOT(A=Ex1)) and (NOT(A=Ex2)) // De Morgan (!) (A<>Ex1) and (A<>Ex2) // why this? "tertium non datur"? At the end, I must verify all my queries and stored procedures (and prepare myself to translate this new version when it'll be available...) Ciao. Mimmo. ```
 Re: [Firebird-docs] New Null Guide - please comment From: Paul Vinkenoog - 2007-01-02 23:02:15 ```Hi Mimmo, >> So whether you interpret NOT IN() as NOT( IN() ), or as a >> conjunction of inequality tests, the outcome is the same. > You're right. My fault: I switched my notes. > Anyway it's interesting: "my" interpretation of NOT IN() is > equivalent, only if De Morgan "rules" and other principles of > classic logic remain valid in NULL-able logic. Yes. I guess they do because with NULL added, the symmetry is preserved: not(null) == null, and most other operations return null. The only exceptions are "null and false" and "null or true", and there's symmetry there too. You can also prove either one from the other: null and false == false <=> not(null and false) == not(false) <=> not(null) or not(false) == true <=> null or true == true OK, I cheated: I used De Morgan in this "proof" ;-) The proper thing to do is work out the truth tables, but that's boring. Greetings, Paul Vinkenoog ```