T3.3: Handles Nulls

Overview

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

Empty String in ObjectScript

In ObjectScript, the empty string "" is the default initial value of any unset variable or property. It is a zero-length string, not null, not undefined in the SQL sense. 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 will contain an empty string, not NULL:

-- After saving the object above:
SELECT ID, MiddleName FROM MyApp.Patient WHERE ID = 1
-- Returns: MiddleName = '' (empty string)

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

$CHAR(0) as SQL NULL

$CHAR(0) (abbreviated $C(0)) is a single character with ASCII code 0. InterSystems IRIS uses this special value as the ObjectScript representation of SQL NULL. When a property is set to $C(0), it projects as NULL in SQL:

// Setting a property to SQL NULL via ObjectScript
SET obj.MiddleName = $CHAR(0)
SET sc = obj.%Save()

// Now in SQL:
// SELECT MiddleName FROM MyApp.Patient WHERE ID = 1
// Returns: NULL

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

Testing for $CHAR(0) in ObjectScript

// After opening an object that has NULL in SQL
SET obj = ##class(MyApp.Patient).%OpenId(1)

// Test if value is SQL NULL
IF obj.MiddleName = $C(0) {
    WRITE "Value is SQL NULL", !
}

// Test if value is empty string
IF obj.MiddleName = "" {
    // This is FALSE for $C(0) — they are different strings
    // $C(0) is a single character (ASCII 0), "" is zero-length
    WRITE "Value is truly empty", !
}

// To distinguish $C(0) from empty string and real values:
IF obj.MiddleName = $C(0) {
    WRITE "This is $C(0) - SQL NULL marker", !
} ELSEIF obj.MiddleName = "" {
    WRITE "This is truly empty string", !
} ELSE {
    WRITE "This has a 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
Empty string	""	0	'' (empty string)	= '' or = ""
SQL NULL	$C(0)	1	NULL	IS NULL
Space	" "	1	' ' (space)	= ' '
Unset property	"" (default)	0	'' (empty string)	= ''

Inserting NULL via SQL

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

-- Omitting a column also inserts the default (empty string, NOT NULL)
INSERT INTO MyApp.Patient (Name) VALUES ('Jones')
-- MiddleName will be '' (empty string), NOT NULL

Common Gotcha: WHERE Clause Differences

// This query finds rows with empty string MiddleName:
&sql(SELECT COUNT(*) INTO :cnt FROM MyApp.Patient WHERE MiddleName = '')
WRITE "Empty strings: ", cnt, !

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

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