What are use cases for non-nullable types?

[mbasmanova]

Folks, the type systems allows to specify nullability for each type. In SQL, all types are nullable, hence, I'm curious what's the use case for non-nullable types.

https://substrait.io/types/type_system/

Describes whether values of this type can be null. Note that null is considered to be a special value of a nullable type, rather than the only value of a special null type.

Context: substrait-io/substrait-validator#52 (comment)

[jvanstraten]

I think you meant "non-nullable" in your issue title. Also, link to the thread that spawned this: substrait-io/substrait-validator#52 (comment)

[mbasmanova]

@jvanstraten You are correct. Thank you for pointing this out. Updated the title and description.

[jacques-n]

There are a number of cases where optimizers can make smarter decisions if they can know that the data cannot have nulls. Additionally, many systems have representation and execution optimizations with non-nullable scenarios. Most databases have the ability to declare fields as non-nullable.

[julianhyde]

Calcite allows nullable and non-nullable types. But it might have been better if it had represented nullability as a constraint, rather than part of the type. A column that goes into a filter nullable can come out not-nullable. That's more of a change of constraint rather than a change in representation.

[westonpace]

Can we close this? I think the answer has been given (it's not classic SQL but it can allow optimizers and engines to make more intelligent decisions and some engines support it).

[cpcloud]

ClickHouse is an engine that makes rather heavy use of non nullable types. All naked types in clickhouse e.g., Int64 are not nullable.