
Three-Valued Logic - okket
http://modern-sql.com/concept/three-valued-logic
======
dvt
It's noteworthy that SQL's 3VL follows Kleene's K3 logic (or, equivalently,
Łukasiewicz' Ł3 logic) and not Priest's P3 logic. That is, in K3 and Ł3, an
unknown (or indeterminate/null) value resolves to _neither_ true _nor_ false;
whereas in K3, an unknown (or indeterminate/null) value resolves to _both_
true _and_ false.

There are some interesting ramifications of these properties, including a lack
of tautologies in K3 and Ł3.

~~~
lifepillar
K3 has no tautologies, but Ł3 does have tautologies, e.g. P->P.

~~~
dvt
True. I should've mentioned that I was barring implication (as the context was
SQL).

------
kurtisc
IEEE 1164, used for VHDL (VHSIC Hardware Description Language) has 9 distinct
logic levels:

'U' uninitialized

'X' strong drive, unknown logic value

'0' strong drive, logic zero

'1' strong drive, logic one

'Z' high impedance

'W' weak drive, unknown logic value

'L' weak drive, logic zero

'H' weak drive, logic one

'-' don't care

~~~
masklinn
What does it mean by "drive", if you don't mind?

~~~
Symmetry
In this case "drive" is short for "driver current," how much current is being
used to direct the voltage in a particular way. For instance, in classic RTL
you have fairly big resistors between power and all your outputs driving them
weakly up. But you have NFET transistors that may or may not strongly drive
your outputs down. So you've always got a 'H' leading into the outputs but
sometimes also a '0'.

------
ak39
From the article: "The SQL null value basically means “could be anything”."

I wouldn't call the NULL an unknown or as a "could be anything". It's tempting
to think it's the Schrodinger's Cat phenomenon, but I choose to interpret the
NULL in a less sexy way: Unknown means that the state DOES NOT EXIST. It's as
simple as that. Remember in relational modeling (cardinality & ordinality) the
thinking is always: zero, 1 or more than 1. The NULL is simply the zero here (
which means the state does NOT exist). Interpret it as "irrelevant".

Interpreting it as either true or false (because it is unknown) is over
explaining.

~~~
snarfy
I like to think of it as 'outside the current set'. Saying it doesn't exist is
like saying sqrt(-1) doesn't exist, yet it does, it's just 'outside' the set
of real numbers.

In a strongly typed language with a boolean type, a comparison to null would
require a type change. It's 'outside' the set of the boolean type.

~~~
techno_modus
> I like to think of it as 'outside the current set'.

Yes, if it exists (as an element of some set), then it is definitely outside
the declared set of values. The main question is how we can formalize it,
particularly, by defining the set NULL is a member of. For example, the
following general approaches are possible:

o Extend (any or some) set of values with a special element (NULL)

o NULL = 0 (empty set)

o NULL = <> (empty tuple)

o Treat NULL as non-existence which entails having no-op if it is met, for
example, 2 + NULL + 3 = 5 (just delete NULL which means skip it).

------
dghf
There are 16 possible dyadic operators for two-valued logic (two inputs, each
with one of two possible values, mapping to a result with two possible values
= 2^(2^2)). If you have either NAND or NOR as a primitive, you can construct
the others from it.

There are 19,683 possible dyadic operators for three-valued logic (3^(3^2)). I
don't know if there's an equivalent minimal set of primitives from which you
can construct those.

~~~
Simon_says
The answer is yes; either NAND or NOR will work. You're just mapping a finite
list of input configurations to a finite number of outputs. Any Turing
complete system will be able to emulate it just by enumerating all of the
possibilities.

~~~
lifepillar
Surely there are minimal subsets of functionally complete operators, in
general different ones for different three-valued logics. E.g., for
Łukasiewicz's logic (L3), ∧ (conjunction) and ⟶ (implication) will do
(implication is not definable using other operators).

I doubt that you could define, L3's operators just with NAND (or NOR). In L3,
"N implies N" is T, and "not N" is N (where N is the third truth value). So,
in one case, a formula whose components are all evaluated with N has truth
value T, and in the other has truth value N. This is not a proof, of course,
but I'd like to see such a definition of NAND :)

Interestingly enough, with F571 SQL's propositional fragment is functionally
complete: any three-valued truth table can be defined. For instance,
Łukasiewicz's P ⟶ Q can be expressed with

    
    
        (not P or Q) or (P is unknown and Q is unknown)
    

and the Słupecki operator, which is needed to obtain a functional complete
system from L3, can be expressed with:

    
    
        (P or unknown) and unknown

------
andreareina
Working on a database-driven application. I'm pretty ok with how SQL treats
NULL, except for one thing: the empty sum being NULL instead of 0.

~~~
danielbarla
I'm also curious as to why this is the case [?]. A quick search yielded a
discussion on StackOverflow [1], where people were similarly surprised, but at
least pointed out that this is spelled out in the SQL 92 spec [2] (page 126).
The relevant part:

    
    
        b) If AVG, MAX, MIN, or SUM is specified, then
          Case:
            i) If TXA is empty, then the result is the null value.
    

[1] [https://stackoverflow.com/questions/12730457/why-does-sum-
on...](https://stackoverflow.com/questions/12730457/why-does-sum-on-an-empty-
recordset-return-null-instead-of-0)

[2]
[http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt](http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt)

[?] As in, the design decision behind it. By comparison, the NULL + 10 = NULL
concept seems obvious.

~~~
mannykannot
Indeed. The cardinality of the empty set is zero, right? - and would there not
be all sorts of problems in the formalization of number theory if this were
not the case?

Now you have me wondering if, for any given database containing nulls, there
is an equivalent normalized schema that represents the same information
without nulls - for example, if you have a Student table with a Date of
Graduation attribute that might be null, use instead an additional table of
Graduated Students. It looks to me like there might be some tricky corner
cases in defining 'equivalent' (you cannot query the second database for
students with null graduation dates, but you can query for those without
graduation dates.)

If so, then the follow-up question is whether there is a three-valued logic
that always gives the same result as a particular null-free representation (my
guess is yes and no, respectively.)

~~~
jessaustin
Another difference is that the latter schema would allow students to have
multiple graduations. Which could be totally valid, but in that sense it's not
equivalent to the former schema.

~~~
mannykannot
For the purposes my question, it would be a complication, but resolvable by
using a unique identifier. Perhaps I should have limited my speculation to
databases that are in at least first normal form.

~~~
jessaustin
I'm not sure how the 1NF limitation helps the question. One-to-many relations
can certainly exist in 1NF. If the question is amended to specify that the
table that represents the nullable column must have its primary key also be
its foreign key to the main table, then I agree that the answer is "yes".

~~~
mannykannot
I thought 1NF was a solution to a follow-on problem with handling repeating
groups, that, on reflection, I see does not exist.

------
jjgreen
True, False, and
[https://thedailywtf.com/articles/What_Is_Truth_0x3f_](https://thedailywtf.com/articles/What_Is_Truth_0x3f_)

------
chx
Drupal 8 access check system uses 3VL as well: Allowed, Forbidden, Neutral.
What I found interesting is the only difference between AND / OR is the
Allowed - Neutral case: OR resolves that as Allowed while AND resolves that as
Neutral. Otherwise, X AND/OR X is obviously X and F AND/OR X is F. Sounds
quite logical but the truth table does look weird with so little difference.

In other words, at the end of the day, when making a decision to deny or grant
access using a bunch of these 3VL checkers, you use two rules: 1. If anyone
forbid, then it's a deny. 2. If no one forbid, if running OR then you need at
least one Allowed when running AND then you need to have all of them saying
Allowed.

------
schindlabua
Interesting that NULL = NULL is Unknown. I'm not a fan of random values
trashing my equivalence relations (looking at you, javascript NaN), but I'm
sure there are practical reasons.

~~~
dagw
_looking at you, javascript NaN_

That's not a JavaScript thing, it's an IEEE 754 floating point standard thing
and is the same in every programming language that implements that standard
(which is basically all of them). But for some reason only JavaScript
developers seem to be bothered by this.

Furthermore IEEE 754 floating point numbers don't have many properties that
you'd expect from 'real' numbers. For example addition is not always
associative and the distributive law does not always hold.

~~~
vorotato
it's because javascript is unityped.

~~~
sgustard
var x; 0+x => NaN is the gateway to frustration.

------
oftenwrong
E. F. Codd believed in having (at least) two special values in a relational
model: one to signify an inapplicable column, and another to represent an
unknown value.

For example, if you had a "person" table, the "name" column could be
INAPPLICABLE if the person does not have a name, and UNKNOWN if the person's
name is not known.

------
kuwze
I was hoping this would be about three-way decisions[0].

[0]: [http://www2.cs.uregina.ca/~twd/](http://www2.cs.uregina.ca/~twd/)

------
jeffdavis
[http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-
wi...](http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-with-nulls/)

A lot of people make the mistake of trying to understand SQL NULL, like it's
some kind of intelligent system with an underlying model behind it. It's not.
It's a collection of various special-case behaviors that "make sense" in some
contexts and cause confusion in many others.

------
abraae
Tony Hoare, who introduced null in Algol in 1965, calls it his $1b mistake
[https://www.infoq.com/presentations/Null-References-The-
Bill...](https://www.infoq.com/presentations/Null-References-The-Billion-
Dollar-Mistake-Tony-Hoare).

~~~
MarkusWinand
Yes, because it is a null _reference_.

SQL's null is a _value_. The difference is that processing null values does
not cause exceptions in SQL.

~~~
lmm
Which is worse. Null references are at least relatively fail-fast. SQL null
propagates and so you get the error a long way away from the original source,
like with Javascript's "foo is not a property of undefined".

~~~
default-kramer
Exactly. I've wondered why no SQL implementation (that I know of) has optional
assertions like "join exactly 1 some_table" or "select assert-not-
null(some_column)". I don't see any reason why this would be a performance
killer; in fact, it might even be possible to prove and then cache with the
query plan.

