Treat a leading CSV null as the first Tuple element

Project: ClickHouse/ClickHouse | Language: C++ | Concepts: serialization, backwards-compatibility, error-handling, testing-strategy | Difficulty: mid
Link: https://github.com/ClickHouse/ClickHouse/pull/109744

The problem

When reading CSV data into a Tuple column in ClickHouse, a leading \N (representing NULL) was incorrectly interpreted as the entire tuple being NULL, rather than just the first element. This caused the whole row to be replaced by the column default, and any remaining elements would fail to parse. This behavior broke round-tripping and made it impossible to insert tuples with a NULL in the first position using CSV.

Why it's non-trivial

A naive fix might simply treat every \N as a NULL element, but this would break cases where a single \N is meant to represent the whole tuple (e.g., for a nullable tuple or an empty tuple). The logic must distinguish between when a \N is a top-level tuple element and when it is the value for the entire column. The handling also needs to respect settings like input_format_csv_deserialize_separate_columns_into_tuple and not break other formats (like HiveText) or existing defaults.

The approach

1. Update the CSV field reader to distinguish tuple element nulls from whole-tuple nulls.

The core logic is in CSVFormatReader::readFieldImpl. The patch adds a check to see if the type is a tuple and if the tuple should be deserialized from separate columns:

const auto * tuple_type = typeid_cast<const DataTypeTuple *>(type.get());
const bool is_separate_columns_tuple = tuple_type && !tuple_type->getElements().empty()
    && format_settings.csv.deserialize_separate_columns_into_tuple
    && format_settings.csv.tuple_delimiter == format_settings.csv.delimiter;

if (format_settings.null_as_default && !isNullableOrLowCardinalityNullable(type) && !is_separate_columns_tuple)
{
    /// If value is null but type is not nullable then use default value instead.
    return SerializationNullable::deserializeNullAsDefaultOrNestedTextCSV(column, istr, format_settings, serialization);
}

What this does:

• If the column is a tuple, and the setting to deserialize separate columns into tuple elements is enabled, and the tuple delimiter matches the field delimiter, then skip the whole-column null-as-default path.
• This ensures that a leading \N is handled as the first element of the tuple, not as the entire tuple being NULL.

2. Update documentation and tests to reflect the new behavior.

The setting documentation is updated to clarify that, with separate columns enabled, a \N in a tuple field is now treated as that element and not the whole tuple.

+Because a bare `Tuple` then occupies one field per element, a `\N` in the field of a direct top-level element is that element and not the whole column, so [input_format_null_as_default](#input_format_null_as_default) applies to that element. A row that supplies a single field for the whole tuple is short by the remaining elements and is rejected instead of taking the column default. Set this setting to `0` to read such a field as the whole column again. A `\N` in the field of an element of a nested `Tuple` is still read as that whole nested element.

3. Add and update tests to cover the edge cases and new behavior.

A new test file 04405_csv_tuple_leading_null_null_as_default.sql is added, covering:

• Leading null in tuples with nullable and non-nullable elements.
• Tuples with multiple elements and combinations of nulls.
• One-element and empty tuples.
• The difference in behavior when the separate columns setting is off.
• Ensuring round-trip correctness.

Example from the test:

SELECT * FROM format(CSV, 'x Tuple(Nullable(Int32), Int32)', $$\N,1$$);

This now correctly returns (NULL,1) instead of failing or defaulting the entire tuple.

Existing tests like 01016_input_null_as_default are updated to match the new, correct behavior.

From the review thread

| b | Root cause explained? | CSVFormatReader::readFieldImpl enters the null_as_default branch for the non-Nullable Tuple; deserializeNullAsDefaultOrNestedTextCSV sees \N + delimiter , and matches the whole Tuple as NULL, so the row is defaulted and ,1 errors/drops. |

| c | Fix matches root cause? | Yes. Skips the whole-column null_as_default check for Tuple types when deserialize_separate_columns_into_tuple is on, so per-element null_as_default in SerializationTuple::deserializeTextCSV runs. |

Takeaways

• Always distinguish between whole-column and per-element null handling in composite types when deserializing tabular data.
• Guard new logic with feature flags or settings to avoid breaking existing behaviors and to allow opt-out for legacy compatibility.
• Comprehensive tests, including round-trip and edge cases, are essential when changing serialization/deserialization logic for complex types.