The idea is that optional values are either some value or no value, but sometimes the inner "some value" bit can itself be an optional value (in Rust this would be Option<Option<T>>, in C++ it'd be std::optional<std::optional<T>>, in Python it'd hypothetically be Option[Option[T]], etc.). Such a situation gives you three possible states:

- You have an inner value and the inner value is something non-null (an integer, a string, another optional, etc.)

- You have an inner value and the inner value is null

- You do not have an inner value

The problem, from what I understand, is that Option[T] in Python is equivalent to the union type T | None, and union types are flattened (e.g., (T | U) | V is equivalent to T | U | V) and duplicates are ignored (e.g., T | U | T is equivalent to T | U). As a result, Option[Option[T]] is equivalent to (T | None) | None, which is equivalent to T | None | None, which is finally equivalent to T | None, which means you lose the distinction between "No inner value" and "Inner value is present but is None".

As a more concrete (albeit somewhat contrived) example, say there's some API you can query for the owner of a particular document ID where the document ID may or may not refer to an actual document (e.g., maybe you got it from a cache and the document has since been deleted). One way to express this would be (using Rust syntax)

    fn get_owner(id: DocumentId) -> Option<Option<UserId>> { ... }

(This can probably be done better by creating new enums, but it's an example so give me a bit of slack)

A naive port of this signature to Python would be

    def get_owner(id: DocumentId) -> Option[Option[UserId]]: pass

    def get_owner(id: DocumentId) -> UserId | None: pass

Say you select nullable column into vec, Vec<Option<T>>. vec.get returns Option<Option<T>>, which distinguishes null from out of bounds

I'd like nested Nullable in ClickHouse to use CoalescingMergeTree to support postgres unchanged TOAST values easily without getting mixed up on nulls

The answer is they don't in practical code but they come up in generic code, where you want to wrap something that is already returning an optional in your optional. In languages that do support optional[optional[whatever]], if you every actually write that you know you've messed up your API.

If you have a map, get(key) returns Option[ValueT]: None if the key is missing, Some(value) otherwise. If the value type itself is, say, Optional[String], your return type is Optional[Optional[String]]. Here None still always means the key is missing, while Some(None) means the value is just None.