▼Plisqin
►4 Nullability
On this page:
4.1 Introduction to Nullability
4.2 Avoiding Three-Valued Logic
4.2.1 An Example
4.2.2 Fallback Meanings
4.2.3 Truth Table
8.12
  top  contents    up   

4 Nullability🔗ℹ

4.1 Introduction to Nullability🔗ℹ

Every token in Plisqin has a property called nullability, which may be one of three values:

  • yes - Plisqin knows that the token is nullable

  • no - Plisqin knows that the token is not nullable

  • maybe - Plisqin doesn’t know whether the token is nullable

The most common way to set a nullability is to use the #:null option of define-schema, as in the following example:
> (define-schema my-test-schema
    (table Album
           #:column
           [AlbumID #:type Number? #:null no]
           [ReleaseYear #:type Number? #:null yes]))
> (nullability (AlbumID Album))

no

> (nullability (ReleaseYear Album))

yes

When you build a larger token out of smaller ones, Plisqin usually determines the correct nullability, as in the following example:
; Neither operand is nullable, so the result is not nullable
> (nullability (.+ (AlbumID Album)
                   (val 1)))

no

; (ReleaseYear Album) is nullable, so the result is nullable
> (nullability (.+ (ReleaseYear Album)
                   (val 1)))

yes

If Plisqin does not or cannot determine the correct nullability, you can use >> to override it.

4.2 Avoiding Three-Valued Logic🔗ℹ

In SQL, a boolean expression may have three values: true, false and unknown. This makes it very easy for a programmer to make a mistake. The most common mistakes occur with the comparison operators. A programmer might write "x <= y" but fail to consider what should happen if x is null or y is null or both are null.

The strict variant of Plisqin prevents these kinds of mistakes. It does this in two ways:

  • It uses the 2bool? contract to ensure that the unknown boolean value will not be present in "decision making positions." For example, where requires a 2bool? because it is deciding which rows to filter from the result set and will not tolerate any ambiguity. On the other hand, select isn’t making a decision, so it will accept a nullable Boolish? and just propogate any dbnulls that may be present.

  • The return value of all the comparison operators (such as .<=) could be better documented as (and/c Bool? 2bool?). This is a limitation of Plisqin’s documentation mechanism that could be improved.

    Anywhere that it returns a Bool?, it promises that the return value will also be a 2bool?. In order to fulfill this promise, all the comparison operators (such as .<=) require the caller to disambiguate what should happen when dbnull is encountered. This disambiguation is accomplished using fallbacks, which the rest of this section will explain.

4.2.1 An Example🔗ℹ

This section uses the Adventure Works example database:
(require plisqin-examples/adventure-works
         plisqin-examples/adventure-works/schema)

Now let’s imagine that we have been tasked with producing a list of Products that have sold less than 20 units all-time. The following example might be a first attempt at such a query. It produces an error message that we can learn from:
> (from p Product
        (where (.<= (TotalQtySold p)
                    (val 20))))

<=: contract violation

  expected: a token that is non-nullable or has an

acceptable fallback

  given: a token with nullability: yes

  likely argument position: 1

  acceptable fallbacks: (/void /minval /maxval /any)

  possible solutions:

    If the token's true nullability is `yes`, you should use

`??` to attach an

    acceptable fallback immediately before passing it into

this function.

    If the token's true nullability is not `yes`, you may

need to use `>>` to

    override the inferred nullability wherever the token is

created.

  argument value: (sum (scalar '#<join: detailsG/0

"SalesOrderDetail" #:to #<tuple: "Product"> #<join-type:

left> #<group-by: #<scalar: detailsG/0 #<sql: "."

ProductID>>> #<join-on: #<=: #<scalar: detailsG/0 #<sql: "."

ProductID>> #<scalar: #<tuple: "Product"> #<sql: ".")

This error message is saying that .<= wants non-nullable tokens. In fact, all the strict comparison operators want non-nullable tokens. This is because comparing dbnull to anything produces the unknown boolean value, which the strict variant of Plisqin has promised to eradicate. We can confirm that the nullability of the first argument is yes:
> (nullability (TotalQtySold Product))

yes

So in order to perform this comparison, we will use a fallback. A fallback is a value that can be attached to any token. The fallback tells the comparison operators how dbnull should be handled. In the following example, we attach the /minval fallback using ??:
(from p Product
      (where (.<= (?? (TotalQtySold p) /minval)
                  (val 20))))

Using the fallback makes the comparison unambigous. The programmer has explicitly told Plisqin that whenever (TotalQtySold p) is dbnull, it should fall back to /minval, an artificial value that is less than every value your database can hold. Since /minval is obviously less than or equal to (val 20), the comparison will be true whenever (TotalQtySold p) is dbnull.

Let’s peek at the result set for completeness:

> (aw:show-table
   (from p Product
         (limit 5)
         (select (ProductName p))
         (select (ProductNumber p))
         (select (TotalQtySold p))
         (where (.<= (?? (TotalQtySold p) /minval)
                     (val 20)))))

Show TableShow SQL

select p.Name as ProductName

  , p.ProductNumber as ProductNumber

  , detailsG.__INJECT1 as TotalQtySold

from Product p

left join (

  select detailsG.ProductID as __INJECT0

    , sum(detailsG.OrderQty) as __INJECT1

  from SalesOrderDetail detailsG

  group by detailsG.ProductID

) detailsG

   on (detailsG.__INJECT0 = p.ProductID)

where (detailsG.__INJECT1 is null or (detailsG.__INJECT1 <= 20))

limit 5

ProductName

ProductNumber

TotalQtySold

Adjustable Race

AR-5381

#<sql-null>

Bearing Ball

BA-8327

#<sql-null>

BB Ball Bearing

BE-2349

#<sql-null>

Headset Ball Bearings

BE-2908

#<sql-null>

Blade

BL-2036

#<sql-null>

Recap
At first we tried the following comparison:
(.<= (TotalQtySold p)
     (val 20))
But the strict variant of Plisqin gave us an error, because it refuses to produce boolean expressions that might contain the unknown value. We used the /minval fallback to make our intention explicit. This avoided the mistake of accidentally filtering out records where TotalQtySold is null.

4.2.2 Fallback Meanings🔗ℹ

The meaning of each fallback is as follows:

  • /minval represents a set containing exactly one value. This value is an artificial value that is less than every value that your database can hold. It is equal only to itself.

  • /maxval represents a set containing exactly one value. This value an artificial value that is greater than every value that your database can hold. It is equal only to itself.

  • /void represents an empty set of values. Comparing /void against anything produces false.

  • /any represents a set of values that includes every value your database can hold, plus /minval and /maxval.

Notice that each fallback represents a "set of values." When a fallback is considered, the comparison will be true if any value from this "set of values" makes it true (sort of like a boolean "or" over a Cartesian product of comparisons). Because /void represents an empty set, comparing it against anything always produces false. The comparison says "We’ve tried nothing and we’re all out of ideas." This is true even when comparing /void against /any.

The comparison behavior of /minval and /maxval is pretty straightforward:

  • /minval is considered less than 40 by definition.

  • /minval is not considered greater than 40 because it is less than 40.

  • /maxval is considered greater than 40 by definition.

  • /maxval is not considered less than 40 because it is greater than 40.

  • /maxval is considered greater than infinity by definition, assuming that "infinity" is a value your database can hold.

  • /minval is not considered less than /void because a set of one value compared against a set of zero values produces zero comparisons. Trivially, none of these comparisons are true.

The comparison behavior of /any is a little more tricky. Because /any represents such a large set, comparing it against something other than /void often (but not always) produces true. Some examples:

  • /any is considered equal to 40 because it contains 40.

  • /any is considered not-equal to 40 because it contains many values which are not-equal to 40, such as 42.

  • /any is considered less than "aardvark" because it contains many values which are less than "aardvark", such as "aaa" and /minval.

  • /any is considered equal to /minval because it contains /minval.

  • /any is not considered less than /minval because there are no values which are less than /minval.

  • /any is not considered less than /void because an infinite set of values compared against a set of zero values produces zero comparisons. Trivially, none of these comparisons are true.

Remember that fallbacks are only considered when the primary value is dbnull. The following example demonstrates a useless fallback. Even though /minval is less than zero, (val 42) is never dbnull so the fallback is never considered and the comparison always checks whether 42 is less than zero.

> (aw:show-table
   (from cat ProductCategory
         (where (.< (?? (val 42) /minval)
                    (val 0)))))

Show TableShow SQL

select cat.*

from ProductCategory cat

where (42 is null or (42 < 0))

ProductCategoryID

Name

rowguid

ModifiedDate

4.2.3 Truth Table🔗ℹ

There is no significance to writing #true instead of #t here. It is just for the visual effect.

([#f      (= /void /void)]
 [#f      (= /void /minval)]
 [#f      (= /void /maxval)]
 [#f      (= /void /any)]
 [#f      (= /void 42)]
 
 [#f      (= /minval /void)]
 [#true   (= /minval /minval)]
 [#f      (= /minval /maxval)]
 [#true   (= /minval /any)]
 [#f      (= /minval 42)]
 
 [#f      (= /maxval /void)]
 [#f      (= /maxval /minval)]
 [#true   (= /maxval /maxval)]
 [#true   (= /maxval /any)]
 [#f      (= /maxval 42)]
 
 [#f      (= /any /void)]
 [#true   (= /any /minval)]
 [#true   (= /any /maxval)]
 [#true   (= /any /any)]
 [#true   (= /any 42)]
 
 [#f      (<> /void /void)]
 [#f      (<> /void /minval)]
 [#f      (<> /void /maxval)]
 [#f      (<> /void /any)]
 [#f      (<> /void 42)]
 
 [#f      (<> /minval /void)]
 [#f      (<> /minval /minval)]
 [#true   (<> /minval /maxval)]
 [#true   (<> /minval /any)]
 [#true   (<> /minval 42)]
 
 [#f      (<> /maxval /void)]
 [#true   (<> /maxval /minval)]
 [#f      (<> /maxval /maxval)]
 [#true   (<> /maxval /any)]
 [#true   (<> /maxval 42)]
 
 [#f      (<> /any /void)]
 [#true   (<> /any /minval)]
 [#true   (<> /any /maxval)]
 [#true   (<> /any /any)]
 [#true   (<> /any 42)]
 
 [#f      (< /void /void)]
 [#f      (< /void /minval)]
 [#f      (< /void /maxval)]
 [#f      (< /void /any)]
 [#f      (< /void 42)]
 
 [#f      (< /minval /void)]
 [#f      (< /minval /minval)]
 [#true   (< /minval /maxval)]
 [#true   (< /minval /any)]
 [#true   (< /minval 42)]
 
 [#f      (< /maxval /void)]
 [#f      (< /maxval /minval)]
 [#f      (< /maxval /maxval)]
 [#f      (< /maxval /any)]
 [#f      (< /maxval 42)]
 
 [#f      (< /any /void)]
 [#f      (< /any /minval)]
 [#true   (< /any /maxval)]
 [#true   (< /any /any)]
 [#true   (< /any 42)]
 
 [#f      (> /void /void)]
 [#f      (> /void /minval)]
 [#f      (> /void /maxval)]
 [#f      (> /void /any)]
 [#f      (> /void 42)]
 
 [#f      (> /minval /void)]
 [#f      (> /minval /minval)]
 [#f      (> /minval /maxval)]
 [#f      (> /minval /any)]
 [#f      (> /minval 42)]
 
 [#f      (> /maxval /void)]
 [#true   (> /maxval /minval)]
 [#f      (> /maxval /maxval)]
 [#true   (> /maxval /any)]
 [#true   (> /maxval 42)]
 
 [#f      (> /any /void)]
 [#true   (> /any /minval)]
 [#f      (> /any /maxval)]
 [#true   (> /any /any)]
 [#true   (> /any 42)]
 
 [#f      (<= /void /void)]
 [#f      (<= /void /minval)]
 [#f      (<= /void /maxval)]
 [#f      (<= /void /any)]
 [#f      (<= /void 42)]
 
 [#f      (<= /minval /void)]
 [#true   (<= /minval /minval)]
 [#true   (<= /minval /maxval)]
 [#true   (<= /minval /any)]
 [#true   (<= /minval 42)]
 
 [#f      (<= /maxval /void)]
 [#f      (<= /maxval /minval)]
 [#true   (<= /maxval /maxval)]
 [#true   (<= /maxval /any)]
 [#f      (<= /maxval 42)]
 
 [#f      (<= /any /void)]
 [#true   (<= /any /minval)]
 [#true   (<= /any /maxval)]
 [#true   (<= /any /any)]
 [#true   (<= /any 42)]
 
 [#f      (>= /void /void)]
 [#f      (>= /void /minval)]
 [#f      (>= /void /maxval)]
 [#f      (>= /void /any)]
 [#f      (>= /void 42)]
 
 [#f      (>= /minval /void)]
 [#true   (>= /minval /minval)]
 [#f      (>= /minval /maxval)]
 [#true   (>= /minval /any)]
 [#f      (>= /minval 42)]
 
 [#f      (>= /maxval /void)]
 [#true   (>= /maxval /minval)]
 [#true   (>= /maxval /maxval)]
 [#true   (>= /maxval /any)]
 [#true   (>= /maxval 42)]
 
 [#f      (>= /any /void)]
 [#true   (>= /any /minval)]
 [#true   (>= /any /maxval)]
 [#true   (>= /any /any)]
 [#true   (>= /any 42)])
  top  contents    up