How to apply 3-valued-logic to SQL queries?

Question

I've been doing past paper questions and keep coming up against these questions that deal with 3 valued logic. My notes mention it but don't give examples that relate to those asked in exams.

I understand the basis that True = 1, False = 0 & Unknown = 1/2 as well as And = Min, Or = Max and Not(x) = 1-x. However I do not know how to apply it to questions such as those below:

In SQL, discuss the possible truth values of the following expression:
R.a > R.b OR R.a <= 0 OR R.b >= 0
Justify your answer.

And:

The phone and age fields of the Owner table might have null values in them. Considering all possible combinations, show which of the three truth values might be returned by the expression:
phone = ’141-3304913’ OR age <50 OR age >= 50

Any help in clarifying these for me would be really appreciated :)

Answer 1

I will focus on the concrete example, which is more proper for clarifying things. Put simply, your logical expression is made of a conjunction of three clauses

C1: phone = '141-3304913'
C2: age < 50
C3: age >= 50

for which tri-boolean logic states that the result is

True, if any clause is true
False, if all clauses are false
Unknown, in all the other cases

Consequently, if the value associated with True is the largest, with False is the smallest, and with Unknown is any intermediate value, then taking the MAX for a conjunction proves correct. Similarly, a disjunction works with the MIN function. Negation works as long as we interpret any value between 0 and 1 (excluded) as Unknown; clearly, if we take 1/2 then the negation function is "stable", but that does not really matter in mathematical terms.

More operatively, the clauses clearly react to the following values (instances) of your phone variable P and your age variable A:

P1 such that P1 = '141-3304913'
P2 such that P2 <> '141-3304913'
P3 such that P3 = NULL
A1 such that A1 < 50
A2 such that A2 >= 50
A3 such that A3 = NULL

In terms of satisfaction of the clauses, we have

P1 -> C1 = 1
P2 -> C1 = 0
P3 -> C1 = 1/2
A1 -> C2 = 1, C3 = 0
A2 -> C2 = 0, C3 = 1
A3 -> C2 = C3 = 1/2

In general there exist 3*3 possible combinations, since each of your two variables takes three possible values:

P1 A1: C1 = 1, C2 = 1, C3 = 0 -> MAX(1,1,0) = 1 -> true
P1 A2: C1 = 1, C2 = 0, C3 = 1 -> MAX(1,0,1) = 1 -> true
P1 A3: C1 = 1, C2 = 1/2, C3 = 1/2 -> MAX(1,1/2,1/2) = 1 -> true
P2 A1: C1 = 0, C2 = 1, C3 = 0 -> MAX(0,1,0) = 1 -> true
P2 A2: C1 = 0, C2 = 0, C3 = 1 -> MAX(0,0,1) = 1 -> true
P2 A3: C1 = 0, C2 = 1/2, C3 = 1/2 -> MAX(0,1/2,1/2) = 1/2 -> unknown
P3 A1: C1 = 1/2, C2 = 1, C3 = 0 -> MAX(1/2,1,0) = 1 -> true
P3 A2: C1 = 1/2, C2 = 0, C3 = 1 -> MAX(1/2,0,1) = 1 -> true
P3 A3: C1 = 1/2, C2 = 1/2, C3 = 1/2 -> MAX(1/2,1/2,1/2) = 1/2 -> unknown

In particular, since C2 and C3 are mutually exclusive, you never get False as a result of the conjunction.

The expression R.a > R.b OR R.a <= 0 OR R.b >= 0 instead presents these cases:

R.a <= 0, R.a > 0, R.a = unknown
R.b >= 0, R.b < 0, R.b = unknown
R.a - R.b > 0, R.a - R.b <= 0, R.a - R.b = unknown

Apparently we have three variables and 27 possible cases, but several related to R.a - R.b can be trivially ruled out.