rudb_csv/

infer.rs

//! What type a column of text is.
//!
//! A CSV file has no types in it, so a reader that wants to hand a `BIGINT` to an aggregate has to
//! decide, and a wrong decision is a wrong answer rather than a slow query. The rule is DuckDB's
//! and it is a ladder: every value in the column is tried against each type in turn and the first
//! type every value fits is the column's type, with `VARCHAR` at the bottom because everything fits
//! it. A column of nothing but nulls is `VARCHAR` too, which is the bottom of the same ladder.
//!
//! The order was read off duckdb v1.4.1 rather than chosen. `BOOLEAN` before `BIGINT` matters,
//! because a column of `true` and `false` is a boolean and not a pair of words. `BIGINT` before
//! `DOUBLE` matters, because a column of whole numbers should be whole. `DATE` before `TIMESTAMP`
//! is the order the binary uses, and those two do overlap because a cast to `DATE` reads a time on
//! the end and throws it away, so `timed` below keeps a timestamp off the `DATE` rung.
//!
//! `TIME` sits between `DOUBLE` and `DATE`, which is where the binary has it. Nothing it takes is
//! anything `DATE` takes, so the position between those two is not observable and the rung above it
//! is, which is the one that matters.
//!
//! Six of the rules are the sniffer's rather than the cast's, and all six were measured. `007`
//! is a `VARCHAR` here although `CAST('007' AS BIGINT)` is 7, because a column of zero padded
//! numbers is a column of codes and adding them up is not what anybody meant. `+1` is a `VARCHAR`
//! for the same sort of reason. A day with a time on it is a `TIMESTAMP` and not a `DATE`, and a
//! clock with anything else around it is a `VARCHAR` and not a `TIME`, although the casts to `DATE`
//! and to `TIME` take both. A point or an exponent keeps a value off the `BIGINT` rung, so a column
//! of `1.5` is a `DOUBLE` column although the cast reads that as two, and a separator keeps it off
//! both number rungs, so a column of `1_000` is a `VARCHAR` column although the cast reads that as a
//! thousand. Everything else defers to the cast, which is the point: a string the sniffer calls a
//! `BIGINT` is a string the reader then casts to `BIGINT`, so a test that disagreed with the cast
//! would produce a column whose declared type its own values do not fit.
//!
//! There is one column where upstream does disagree with itself and this does not follow it. A
//! column holding both `0x10` and `1.5` sniffs as `DOUBLE` there, because its `DOUBLE` rung takes a
//! radix that its cast to `DOUBLE` then refuses, so reading the file raises. Here the rung defers to
//! the cast, the column comes out `VARCHAR`, and the file reads.

use rudb_common::{LogicalType, Value};
use rudb_kernels::cast_value;

/// The types tried, in the order they are tried. `VARCHAR` is the bottom and is not in here because
/// it never fails.
pub const LADDER: [LogicalType; 6] = [
    LogicalType::Boolean,
    LogicalType::BigInt,
    LogicalType::Double,
    LogicalType::Time,
    LogicalType::Date,
    LogicalType::Timestamp,
];

/// How many rows the sniffer looks at.
///
/// DuckDB's `sample_size` default, which it prints in the block under a conversion error. A value
/// past this that does not fit the type the sample chose is an error at read time rather than a
/// wider type, because widening would mean going back and rewriting the chunks already handed out.
pub const SAMPLE: usize = 20480;

/// The type of a column, given every value the sample had for it.
///
/// A `None` is a null and is skipped, because a null fits every type and a column that is all nulls
/// has nothing to go on.
#[must_use]
pub fn column(values: &[Option<&str>]) -> LogicalType {
    if values.iter().all(Option::is_none) {
        // Otherwise every rung is satisfied vacuously and the column comes back as the first one.
        return LogicalType::Varchar;
    }
    for candidate in LADDER {
        if values.iter().flatten().all(|text| fits(text, &candidate)) {
            return candidate;
        }
    }
    LogicalType::Varchar
}

/// Whether one value would read as `candidate`.
#[must_use]
pub fn fits(text: &str, candidate: &LogicalType) -> bool {
    match candidate {
        LogicalType::Boolean => is_boolean(text),
        LogicalType::BigInt if !numeric(text) || !whole(text) => false,
        LogicalType::Double if !numeric(text) => false,
        LogicalType::Date if timed(text) => false,
        LogicalType::Time if !clock(text) => false,
        _ => {
            let value = Value::Varchar(text.to_string());
            matches!(cast_value(&value, candidate, true), Ok(converted) if !converted.is_null())
        }
    }
}

/// Whether a written day carries a time on the end of it.
///
/// The third rule that is not the cast's. `CAST('2013-07-15 10:00:00' AS DATE)` is a date upstream,
/// the time is read and thrown away, so the two rungs do overlap and the sniffer has to tell them
/// apart itself or every timestamp column would come back as a `DATE`.
fn timed(text: &str) -> bool {
    text.trim().contains([' ', 'T'])
}

/// Whether a value is nothing but a clock.
///
/// The fourth rule that is not the cast's, and the same sort of rule as `timed`. The cast to `TIME`
/// takes a date in front and any amount of rubbish behind, so it would take a whole timestamp
/// column and every value in it would lose its day. A column of `12:34:56 UTC` is a `VARCHAR` to
/// the binary, which is what this refuses it for.
fn clock(text: &str) -> bool {
    let text = text.trim();
    !text.is_empty()
        && text.bytes().all(|byte| byte.is_ascii_digit() || byte == b':' || byte == b'.')
}

/// The spellings DuckDB's sniffer reads as a boolean.
///
/// Not `1` and `0`, although the cast takes both, because a column of ones and zeroes is a column of
/// numbers far more often than it is a column of flags and the binary agrees. Not `y` and `n`
/// either, and not `on` and `off`, both of which were tried against it.
fn is_boolean(text: &str) -> bool {
    ["true", "false", "t", "f", "yes", "no"]
        .iter()
        .any(|spelling| text.trim().eq_ignore_ascii_case(spelling))
}

/// Whether a number written like this is a number to the sniffer.
///
/// Three of the rules that are not the cast's. A leading `+` is refused, and so is a leading zero
/// with another digit behind it, which is how a column of `007` stays a column of `007` rather than
/// becoming a column of sevens. The sign is looked past for neither of them, because the binary does
/// not look past it either: `-007` really is a `BIGINT` there and this reproduces that rather than
/// tidying it up. A separator is refused on both rungs, so a column of `1_000` is a `VARCHAR` column
/// although `CAST('1_000' AS BIGINT)` is a thousand.
fn numeric(text: &str) -> bool {
    let text = text.trim();
    if text.contains('_') {
        return false;
    }
    let mut bytes = text.bytes();
    match bytes.next() {
        Some(b'+') => false,
        Some(b'0') => !matches!(bytes.next(), Some(byte) if byte.is_ascii_digit()),
        _ => true,
    }
}

/// Whether a whole number written like this is one to the sniffer.
///
/// The sixth rule that is not the cast's, and the one the rest of #369 turned up. The cast reads
/// `'1.5'` as two and `'1e3'` as a thousand, and upstream sniffs a column of either as `DOUBLE`, so
/// the rung above `DOUBLE` has to refuse a point and an exponent itself or every column of written
/// decimals would come back rounded. A radix is looked at first, because `0x1e` is thirty and the
/// `e` in the middle of it is a digit.
fn whole(text: &str) -> bool {
    let text = text.trim();
    matches!(text.get(..2), Some("0x" | "0X" | "0b" | "0B")) || !text.contains(['.', 'e', 'E'])
}

#[cfg(test)]
mod tests {
    use super::*;

    fn of(values: &[&str]) -> LogicalType {
        let values: Vec<Option<&str>> = values.iter().map(|text| Some(*text)).collect();
        column(&values)
    }

    #[test]
    fn each_rung_of_the_ladder_is_the_type_duckdb_sniffs_for_it() {
        assert_eq!(of(&["true", "false"]), LogicalType::Boolean);
        assert_eq!(of(&["1", "2"]), LogicalType::BigInt);
        assert_eq!(of(&["1.5", "2"]), LogicalType::Double);
        assert_eq!(of(&["2020-01-02", "2021-03-04"]), LogicalType::Date);
        assert_eq!(of(&["2020-01-02 03:04:05"]), LogicalType::Timestamp);
        assert_eq!(of(&["1", "x"]), LogicalType::Varchar);
    }

    /// The spellings the cast learned in #369 and what the sniffer does with each of them, all of
    /// it read off the pinned binary one file at a time. A radix stays on the `BIGINT` rung, a
    /// point and an exponent drop to `DOUBLE`, and a separator drops all the way.
    #[test]
    fn a_column_of_numbers_written_the_other_ways_lands_on_the_rung_duckdb_puts_it_on() {
        assert_eq!(of(&["0x10"]), LogicalType::BigInt);
        assert_eq!(of(&["0X10"]), LogicalType::BigInt);
        assert_eq!(of(&["0x1e"]), LogicalType::BigInt);
        assert_eq!(of(&["0b101"]), LogicalType::BigInt);
        assert_eq!(of(&["1.5"]), LogicalType::Double);
        assert_eq!(of(&["1."]), LogicalType::Double);
        assert_eq!(of(&[".5"]), LogicalType::Double);
        assert_eq!(of(&["1e3"]), LogicalType::Double);
        assert_eq!(of(&["1E3"]), LogicalType::Double);
        assert_eq!(of(&["1e-3"]), LogicalType::Double);
        assert_eq!(of(&["1e18"]), LogicalType::Double);
        assert_eq!(of(&["1_000"]), LogicalType::Varchar);
        assert_eq!(of(&["1_000", "1"]), LogicalType::Varchar);
        assert_eq!(of(&["1.5", "1_0.5"]), LogicalType::Varchar);
        assert_eq!(of(&["1e"]), LogicalType::Varchar);
    }

    /// The `TIME` rung, and the two values next to it that the binary leaves alone although the
    /// cast underneath takes both.
    #[test]
    fn a_column_of_clocks_is_a_time_and_a_column_of_anything_else_is_not() {
        assert_eq!(of(&["03:04:05", "12:34:56"]), LogicalType::Time);
        assert_eq!(of(&["12:34"]), LogicalType::Time);
        assert_eq!(of(&["12:34:56.5"]), LogicalType::Time);
        assert_eq!(of(&["12:34:56 UTC"]), LogicalType::Varchar);
        assert_eq!(of(&["2020-01-02 03:04:05"]), LogicalType::Timestamp);
        for taken in ["12:34:56 UTC", "2020-01-02 03:04:05"] {
            let value = Value::Varchar(taken.into());
            assert!(
                cast_value(&value, &LogicalType::Time, true).is_ok_and(|time| !time.is_null()),
                "{taken}: the cast takes it, which is why the rung has a rule of its own"
            );
        }
    }

    /// The `DATE` rung would swallow this column otherwise, because the cast under it takes a time
    /// on the end of a day and throws it away, so the column would be declared a `DATE` and every
    /// value in it would lose its time.
    #[test]
    fn a_column_of_timestamps_does_not_stop_at_the_date_rung() {
        assert_eq!(of(&["2020-01-02 03:04:05", "2020-01-03 00:00:00"]), LogicalType::Timestamp);
        assert_eq!(of(&["2020-01-02T03:04:05"]), LogicalType::Timestamp);
        assert_eq!(of(&["2020-01-02", "2020-01-03 03:04:05"]), LogicalType::Timestamp);
        let date = Value::Varchar("2020-01-02 03:04:05".into());
        assert!(
            cast_value(&date, &LogicalType::Date, true).is_ok_and(|value| !value.is_null()),
            "the cast still takes it, which is why the rung has a rule of its own"
        );
    }

    #[test]
    fn a_column_of_nothing_but_nulls_is_a_varchar() {
        assert_eq!(column(&[None, None]), LogicalType::Varchar);
        assert_eq!(column(&[]), LogicalType::Varchar);
    }

    #[test]
    fn a_null_in_a_column_does_not_change_what_the_rest_of_it_is() {
        assert_eq!(column(&[Some("1"), None, Some("2")]), LogicalType::BigInt);
    }

    #[test]
    fn ones_and_zeroes_are_numbers_rather_than_flags() {
        // Measured. `CAST('1' AS BOOLEAN)` is true, so a ladder that asked the cast would call this
        // column a boolean, and duckdb v1.4.1 calls it a BIGINT.
        assert_eq!(of(&["0", "1"]), LogicalType::BigInt);
    }

    #[test]
    fn the_boolean_spellings_are_the_six_the_binary_takes_and_no_more() {
        for yes in ["true", "TRUE", "True", "t", "T", "yes", "Yes"] {
            assert_eq!(of(&[yes]), LogicalType::Boolean, "{yes}");
        }
        for no in ["on", "off", "y", "n"] {
            assert_eq!(of(&[no]), LogicalType::Varchar, "{no}");
        }
    }

    #[test]
    fn a_zero_padded_number_stays_the_text_it_was_written_as() {
        assert_eq!(of(&["007", "008"]), LogicalType::Varchar);
        assert_eq!(of(&["00"]), LogicalType::Varchar);
        // And the two that go the other way, both measured against the binary.
        assert_eq!(of(&["0"]), LogicalType::BigInt);
        assert_eq!(of(&["-007"]), LogicalType::BigInt);
    }

    #[test]
    fn a_leading_plus_is_not_a_number_to_the_sniffer() {
        assert_eq!(of(&["+1"]), LogicalType::Varchar);
    }

    #[test]
    fn space_around_a_number_does_not_stop_it_being_one() {
        assert_eq!(of(&[" 1", "2 "]), LogicalType::BigInt);
    }

    #[test]
    fn a_whole_number_too_big_for_a_bigint_widens_to_a_double() {
        assert_eq!(of(&["99999999999999999999"]), LogicalType::Double);
    }
}