Nullable Fields & NULL Handling

-- Best practice: explicitly declare nullability intent
-- Geode has no CREATE NODE TYPE / CREATE EDGE TYPE: labels are
-- schema-free. The only typed DDL is CREATE GRAPH TYPE, whose field
-- list is `name TYPE [PRIMARY KEY] [@annotations]` in braces.
-- Not supported in a field definition: NOT NULL — enforce with CREATE CONSTRAINT or at write time.
CREATE GRAPH TYPE PersonType AS {
  id STRING,  -- Required: primary identifier
  name STRING,  -- Required: every person has a name
  middle_name STRING,  -- Explicitly nullable (optional)
  nickname STRING,  -- Implicitly nullable
  email STRING,  -- Required for communication
  phone STRING,  -- Optional contact method
  date_of_birth DATE,  -- May be unknown
  date_of_death DATE  -- NULL for living persons
};

-- Edge with required and optional properties
-- Geode has no CREATE NODE TYPE / CREATE EDGE TYPE: labels are
-- schema-free. The only typed DDL is CREATE GRAPH TYPE, whose field
-- list is `name TYPE [PRIMARY KEY] [@annotations]` in braces.
-- Not supported in a field definition: NOT NULL — enforce with CREATE CONSTRAINT or at write time.
CREATE GRAPH TYPE WORKS_FORType AS {
  start_date DATE,  -- Employment must have start date
  end_date DATE,  -- NULL = currently employed
  position STRING,
  salary DECIMAL,  -- May be confidential/unknown
  department STRING
};

-- Geode has no CREATE NODE TYPE / CREATE EDGE TYPE: labels are
-- schema-free. The only typed DDL is CREATE GRAPH TYPE, whose field
-- list is `name TYPE [PRIMARY KEY] [@annotations]` in braces.
-- Not supported in a field definition: NOT NULL — enforce with CREATE CONSTRAINT or at write time.
CREATE GRAPH TYPE ProductType AS {
  sku STRING,  -- Must have identifier
  name STRING,  -- Must be named
  price DECIMAL,  -- Must have price
  manufacturer STRING,  -- May be unknown
  discontinued_date DATE  -- NULL = still active
};

-- Geode has no CREATE NODE TYPE / CREATE EDGE TYPE: labels are
-- schema-free. The only typed DDL is CREATE GRAPH TYPE, whose field
-- list is `name TYPE [PRIMARY KEY] [@annotations]` in braces.
-- Not supported in a field definition: DEFAULT, NOT NULL — enforce with CREATE CONSTRAINT or at write time.
CREATE GRAPH TYPE UserType AS {
  id STRING,
  email STRING,
  created_at TIMESTAMP,
  status STRING,
  last_login TIMESTAMP  -- NULL until first login
};

-- Find people without middle names
MATCH (p:Person)
WHERE p.middle_name IS NULL
RETURN p.name;

-- Find people with known phone numbers
MATCH (p:Person)
WHERE p.phone IS NOT NULL
RETURN p.name, p.phone;

-- Find current employees (no end_date)
MATCH (p:Person)-[e:WORKS_FOR]->(c:Company)
WHERE e.end_date IS NULL
RETURN p.name, c.name, e.start_date;

-- WRONG: This will NEVER match anything, even NULL values
MATCH (p:Person)
WHERE p.middle_name = NULL  -- Always evaluates to NULL (unknown), never TRUE
RETURN p;

-- WRONG: This also doesn't work as expected
MATCH (p:Person)
WHERE p.middle_name != NULL  -- Also always NULL
RETURN p;

-- Use nickname if available, otherwise use formal name
MATCH (p:Person)
RETURN
    p.name,
    COALESCE(p.nickname, p.name) AS display_name;

-- Multi-level fallback
MATCH (p:Person)
RETURN
    COALESCE(p.mobile_phone, p.home_phone, p.work_phone, 'No phone') AS contact_number;

-- Default to zero for calculations
MATCH (p:Product)
RETURN
    p.name,
    p.base_price,
    COALESCE(p.discount_amount, 0) AS discount,
    p.base_price - COALESCE(p.discount_amount, 0) AS final_price;

# Python - COALESCE in queries
from geode_client import Client

client = Client(host="localhost", port=3141)

async with client.connection() as conn:
    # Get user display names with fallback logic
    result, _ = await conn.query("""
        MATCH (u:User)
        RETURN
            u.id,
            COALESCE(u.display_name, u.username, u.email) AS name,
            COALESCE(u.avatar_url, '/default-avatar.png') AS avatar
        ORDER BY name
    """)

    for row in result.bindings:
        print(f"{row['name']} - Avatar: {row['avatar']}")

-- Treat empty strings as NULL
MATCH (p:Person)
RETURN
    p.name,
    NULLIF(p.middle_name, '') AS middle_name,
    NULLIF(p.phone, 'N/A') AS phone;

-- Treat zero as NULL for optional numeric fields
MATCH (p:Product)
RETURN
    p.name,
    NULLIF(p.discount_pct, 0) AS discount;

-- Normalize empty strings to NULL, then provide default
MATCH (p:Person)
RETURN
    COALESCE(NULLIF(p.bio, ''), 'No bio provided') AS bio;

-- Three-valued logic in action
MATCH (p:Person)
WHERE p.age > 18  -- Excludes NULL ages (NULL > 18 = NULL, not TRUE)
RETURN p.name;

-- Include people with unknown age
MATCH (p:Person)
WHERE p.age > 18 OR p.age IS NULL
RETURN p.name, COALESCE(p.age, 'Unknown') AS age;

MATCH (p:Person)
RETURN
    p.name,
    p.age,
    p.age + 5,                    -- NULL if age is NULL
    p.age * 1.1,                  -- NULL if age is NULL
    p.age > 18,                   -- NULL if age is NULL
    p.first_name || ' ' || p.middle_name || ' ' || p.last_name
                                  -- NULL if any part is NULL
;

MATCH (p:Person)
RETURN
    -- Handles NULL middle names
    p.first_name || ' ' || COALESCE(p.middle_name || ' ', '') || p.last_name AS full_name;

-- These ignore NULL values
MATCH (p:Person)
RETURN
    COUNT(*) AS total_people,              -- Counts all nodes
    COUNT(p.age) AS people_with_age,       -- Counts non-NULL ages
    AVG(p.age) AS average_age,             -- Average of non-NULL ages
    MIN(p.age) AS youngest,                -- Minimum non-NULL age
    MAX(p.age) AS oldest;                  -- Maximum non-NULL age

-- Count NULL values explicitly
MATCH (p:Person)
RETURN
    COUNT(*) AS total,
    COUNT(p.phone) AS with_phone,
    COUNT(*) - COUNT(p.phone) AS without_phone;

// Go client - handling NULL in aggregations
package main

import (
    "context"
    "database/sql"
    "fmt"
    "geodedb.com/geode"
)

func analyzeUserData(ctx context.Context, db *geode.DB) error {
    var total, withAge, avgAge sql.NullFloat64

    err := db.QueryRowContext(ctx, `
        MATCH (u:User)
        RETURN
            COUNT(*) AS total,
            COUNT(u.age) AS with_age,
            AVG(u.age) AS avg_age
    `).Scan(&total, &withAge, &avgAge)

    if err != nil {
        return err
    }

    fmt.Printf("Total users: %.0f\n", total.Float64)
    fmt.Printf("Users with age: %.0f\n", withAge.Float64)

    if avgAge.Valid {
        fmt.Printf("Average age: %.1f\n", avgAge.Float64)
    } else {
        fmt.Println("Average age: No data")
    }

    return nil
}

-- NULL values sort last by default (in ascending order)
MATCH (p:Person)
RETURN p.name, p.age
ORDER BY p.age ASC;

-- NULL values first
MATCH (p:Person)
RETURN p.name, p.age
ORDER BY p.age ASC NULLS FIRST;

-- NULL values last
MATCH (p:Person)
RETURN p.name, p.age
ORDER BY p.age DESC NULLS LAST;

-- Use COALESCE to control NULL sorting
MATCH (p:Person)
RETURN p.name, p.age
ORDER BY COALESCE(p.age, 999) ASC;  -- Treat NULL as 999

// Rust client - type-safe NULL handling with Option<T>
use geode_client::{Client, Value};
use std::collections::HashMap;

#[derive(Debug)]
struct Person {
    id: String,
    name: String,
    middle_name: Option<String>,  // NULL maps to None
    age: Option<i32>,
    phone: Option<String>,
}

async fn get_person(client: &Client, id: &str) -> Result<Person, geode_client::Error> {
    let mut params = HashMap::new();
    params.insert("id", Value::String(id.to_string()));

    let result = client.execute(
        "MATCH (p:Person {id: $id}) RETURN p.id, p.name, p.middle_name, p.age, p.phone",
        &params
    ).await?;

    let row = result.bindings.first().ok_or(geode_client::Error::NotFound)?;

    Ok(Person {
        id: row["p.id"].as_str().unwrap().to_string(),
        name: row["p.name"].as_str().unwrap().to_string(),
        middle_name: row["p.middle_name"].as_str().map(String::from),  // NULL → None
        age: row["p.age"].as_i64().map(|i| i as i32),
        phone: row["p.phone"].as_str().map(String::from),
    })
}

fn format_full_name(person: &Person) -> String {
    match &person.middle_name {
        Some(middle) => format!("{} {} {}", person.name, middle, person.name),
        None => person.name.clone(),
    }
}

# Python - NULL handling with None
from typing import Optional
from dataclasses import dataclass
from geode_client import Client

@dataclass
class User:
    id: str
    email: str
    phone: Optional[str] = None
    bio: Optional[str] = None
    last_login: Optional[str] = None

async def create_user(client: Client, email: str, phone: Optional[str] = None):
    """Create user with optional phone number."""
    params = {"email": email}

    if phone is not None:
        params["phone"] = phone
        query = "INSERT (u:User {email: $email, phone: $phone}) RETURN u"
    else:
        query = "INSERT (u:User {email: $email}) RETURN u"

    result, _ = await client.query(query, params)
    return result.bindings[0]["u"]

async def get_contact_info(client: Client, user_id: str) -> str:
    """Get contact info with NULL-safe formatting."""
    result, _ = await client.query(
        "MATCH (u:User {id: $id}) RETURN u.email, u.phone",
        {"id": user_id}
    )

    row = result.bindings[0]
    email = row["u.email"]
    phone = row.get("u.phone")  # May be None

    if phone:
        return f"{email} (Phone: {phone})"
    else:
        return f"{email} (No phone)"

-- BAD: Empty string is not NULL
UPDATE Person SET middle_name = '' WHERE id = '123';  -- Use NULL instead

-- GOOD: NULL represents absence of middle name
UPDATE Person SET middle_name = NULL WHERE id = '123';

-- BAD: Never TRUE, always NULL
WHERE p.middle_name = NULL

-- GOOD: Use IS NULL
WHERE p.middle_name IS NULL

# BAD: age might be None, causing TypeError
def is_adult(person):
    return person.age >= 18

# GOOD: Handle NULL explicitly
def is_adult(person):
    return person.age is not None and person.age >= 18