Variant shredding spec ambiguously refers to both SQL NULL and Variant null as "null"

[scovich]

Describe the enhancement requested

The current variant shredding spec wording is confusing, because it's not always easy to tell whether a value is Variant null or missing (SQL NULL). It would improve readability if the two concepts were more clearly separated.

[Tishj]

I did some thinking on this, and I think I understand the distinction between the two null types. Feel free to correct me if I'm wrong however.

VARIANT_NULL only exists because of the need to differentiate between a NULL value for a field, and the field value being missing, in the case of shredding. This is explained in the spec, here

A field's value and typed_value are set to null (missing) to indicate that the field does not exist in the variant. To encode a field that is present with a null value, the value must contain a Variant null: basic type 0 (primitive) and physical type 0 (null).

I don't think there should be a distinction between SQL NULL and VARIANT NULL in the emitted value when the Variant column is scanned. The distinction should only exist at the storage level.

[scovich]

I did some thinking on this, and I think I understand the distinction between the two null types. Feel free to correct me if I'm wrong however.

VARIANT_NULL only exists because of the need to differentiate between a NULL value for a field, and the field value being missing, in the case of shredding. This is explained in the spec, here

A field's value and typed_value are set to null (missing) to indicate that the field does not exist in the variant. To encode a field that is present with a null value, the value must contain a Variant null: basic type 0 (primitive) and physical type 0 (null).

I don't think there should be a distinction between SQL NULL and VARIANT NULL in the emitted value when the Variant column is scanned. The distinction should only exist at the storage level.

From what I understand, variant null is equivalent to the JSON null, which is one of the seven official JSON values:

A value can be a string in double quotes, or a number, or true or false or null, or an object or an array.

The null value is very much present. It is neither missing (a state the variant spec recognizes) nor SQL NULL/UNKNOWN (a state the variant spec actually says nothing about).

Put another way -- although casting JSON/variant null to any other type produces a SQL NULL value, it is still a well-defined value and thus cannot itself be SQL NULL. For example, in spark the expression v IS NULL returns false for rows where v is a variant null, and true for rows where v is missing, but v::INT IS NULL returns true for both cases (because casting null to int produces NULL). There's a separate is_variant_null function to distinguish the two situations.

The specific bit of spec you quoted tells readers what to do if they encounter physically valid parquet that encodes a logically invalid flavor of shredded variant storage because a value column (or column value) is wrongly missing.

For example, suppose that variant column v is shredded as an array with struct elements, where each struct contains a single field f of type int32:

optional group v (VARIANT) {
  required binary metadata;
  optional binary value;
  optional group typed_value (LIST) {   # must be optional to allow a null list
    repeated group list {
      required group element {          # shredded element of the variant array
        optional binary value;            # a variant, expected to be an object
        optional group typed_value {      # shredded fields for the variant object
          required group f {              # shredded field for f
            optional binary value;
            optional int32 typed_value;
          }
        }
      }
    }
  }
}    

With the above shredding schema:

1. It is illegal for both v.value and v.typed_value to be missing -- a NULL value of v must be encoded as missing group v.
2. It is illegal for v.typed_value.list.element to be missing -- variant arrays cannot encode missing values
3. It is illegal for both v.typed_value.list.element.value and v.typed_value.list.element.typed_value to be missing -- variant arrays cannot encode missing values
4. It is illegal for v.typed_value.list.element.typed_value.f to be missing. Instead, a missing field f is inferred when both v.typed_value.list.element.typed_value.f.value and v.typed_value.list.element.typed_value.f.typed_value are missing. This trickery is needed because parquet writers often omit all-missing columns from the physical schema. If the column for f were entirely missing, then readers would have no way to know f was part of the shredding schema and would wrongly check the value column instead. By requiring f and encoding "missing" as f.value and f.typed_value both missing, we avoid the ambiguity.

For all four illegal cases above, the shredding spec requires a reader to compensate by replacing the wrongly-missing value with a variant null value -- not missing or SQL NULL.

It's not clear to me whether the spec forbids a reader to error out in these cases, or if it merely constrains the behavior of a reader who chooses not to error out?