4 Nullability
4.1 Introduction to Nullability
> (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
; 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.
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
(require plisqin-examples/adventure-works plisqin-examples/adventure-works/schema)
> (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: ".")
> (nullability (TotalQtySold Product)) yes
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.
> (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>
4.2.2 Fallback Meanings
/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.
/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.
/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.
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)])