T3.3: Handles Nulls

Overview

One of the most frequently tested and commonly misunderstood topics in InterSystems IRIS is the relationship between the ObjectScript empty string, $CHAR(0), and SQL NULL. The three-way distinction is unique to InterSystems IRIS because of its unified object-SQL data model, and getting it backwards leads to subtle bugs in queries and data handling.

The two rules to remember (from the official InterSystems SQL reference):

1. ObjectScript "" ↔ SQL NULL — "Use NULL to represent the absence of a data value, which corresponds to the ObjectScript empty string ("")." 2. ObjectScript $CHAR(0) ↔ SQL '' — "The SQL zero-length string (empty string) is specified by two single quote characters. In ObjectScript, this corresponds to a string of length one that contains the $CHAR(0) character."

Empty String in ObjectScript is SQL NULL

In ObjectScript, the empty string "" is the default initial value of any unset variable or property. It is a zero-length string. When you access a property that has never been set, you get "":

SET obj = ##class(MyApp.Patient).%New()
WRITE obj.MiddleName            // "" (empty string - the default)
WRITE $LENGTH(obj.MiddleName)   // 0
WRITE (obj.MiddleName = "")     // 1 (true)

In SQL, when this object is saved, the MiddleName column is NULL, not a zero-length string:

-- After saving the object above:
SELECT ID, MiddleName FROM MyApp.Patient WHERE ID = 1
-- Returns: MiddleName = NULL

SELECT ID FROM MyApp.Patient WHERE MiddleName IS NULL
-- DOES return this row

SELECT ID FROM MyApp.Patient WHERE MiddleName = ''
-- Does NOT return this row (= '' tests for the SQL zero-length string, not NULL)

$CHAR(0) is the SQL Zero-Length String

$CHAR(0) (abbreviated $C(0)) is a single character with ASCII code 0. It is a distinct, non-NULL value of length 1. InterSystems IRIS uses $CHAR(0) as the ObjectScript representation of the SQL zero-length string ''. When a property is set to $C(0), it projects as '' (empty string, non-NULL) in SQL — never as NULL:

// Storing a SQL zero-length string via ObjectScript
SET obj.MiddleName = $CHAR(0)
SET sc = obj.%Save()

// Now in SQL:
// SELECT MiddleName FROM MyApp.Patient WHERE ID = 1
// Returns: '' (zero-length string), NOT NULL

// SELECT ID FROM MyApp.Patient WHERE MiddleName IS NULL
// Does NOT return this row

// SELECT ID FROM MyApp.Patient WHERE MiddleName = ''
// DOES return this row

Testing in ObjectScript

SET obj = ##class(MyApp.Patient).%OpenId(1)

// Test whether the value is SQL NULL
IF obj.MiddleName = "" {
    WRITE "Value is SQL NULL (empty string in ObjectScript)", !
}

// Test whether the value is the SQL zero-length string
IF obj.MiddleName = $C(0) {
    // True only if the column stores '' (a non-NULL zero-length string)
    // This is FALSE for the empty string "" — they are different strings
    WRITE "Value is the SQL zero-length string ''", !
}

// Distinguishing the three cases:
IF obj.MiddleName = "" {
    WRITE "SQL NULL", !
} ELSEIF obj.MiddleName = $C(0) {
    WRITE "SQL '' (zero-length, non-NULL)", !
} ELSE {
    WRITE "Real value: ", obj.MiddleName, !
}

SQL NULL Behavior in Queries

SQL NULL follows standard SQL three-valued logic (TRUE, FALSE, UNKNOWN):

-- NULL comparisons
SELECT * FROM MyApp.Patient WHERE MiddleName = NULL   -- WRONG! Always returns no rows
SELECT * FROM MyApp.Patient WHERE MiddleName IS NULL  -- Correct way to test for NULL

-- NULL in expressions
SELECT Name, MiddleName || ' ' || LastName FROM MyApp.Patient
-- If MiddleName is NULL, the concatenation result is NULL (NULL propagates)

-- COALESCE / IFNULL to handle NULLs
SELECT Name, COALESCE(MiddleName, 'N/A') AS MiddleName FROM MyApp.Patient
-- Returns 'N/A' if MiddleName is NULL

-- NULL in aggregates
SELECT COUNT(MiddleName) FROM MyApp.Patient
-- COUNT(column) excludes NULL values
-- COUNT(*) counts all rows including those with NULLs

Comparison Summary

Concept	ObjectScript Value	$LENGTH()	SQL Projection	SQL Test
SQL NULL	"" (also the default for unset)	0	NULL	IS NULL
SQL zero-length string	$C(0)	1	''	= ''
Space	" "	1	' '	= ' '
Unset property	"" (default)	0	NULL	IS NULL

Inserting via SQL

-- Explicit NULL insertion
INSERT INTO MyApp.Patient (Name, MiddleName) VALUES ('Smith', NULL)
-- MiddleName will be "" (empty string) when accessed via ObjectScript

-- Explicit zero-length string
INSERT INTO MyApp.Patient (Name, MiddleName) VALUES ('Smith', '')
-- MiddleName will be $C(0) (length 1) when accessed via ObjectScript

-- Omitting a column inserts the ObjectScript default (""), which is SQL NULL
INSERT INTO MyApp.Patient (Name) VALUES ('Jones')
-- MiddleName will be NULL in SQL

Common Gotcha: WHERE Clause Differences

// This query finds rows whose value is the SQL zero-length string '':
&sql(SELECT COUNT(*) INTO :cnt FROM MyApp.Patient WHERE MiddleName = '')
WRITE "Zero-length '': ", cnt, !

// This query finds rows whose value is SQL NULL (ObjectScript ""):
&sql(SELECT COUNT(*) INTO :cnt FROM MyApp.Patient WHERE MiddleName IS NULL)
WRITE "NULLs: ", cnt, !

// These are DIFFERENT result sets.
// The common bug is using = '' when you meant IS NULL, or vice versa.