Example on how to read an ENUM from duckdb

[thumbert]

Hi,

I have not seen an example on how to do this in the docs or the tests. Given how pervasive enums are in Rust, maybe an explicit example will be useful.

For example given this table

CREATE TABLE stats (
    name ENUM('CA', 'NY'),
    value INTEGER,
);
INSERT INTO stats VALUES ('CA', 10), ('CA', 20), ('NY', 4);

How do I read it back into Rust?

        let query = "SELECT * FROM stats;";
        let mut stmt = conn.prepare(query).unwrap();

        let res_iter = stmt.query_map([], |row| {
            println!("{:?}", row.get_ref_unwrap(0));
            let state = match row.get_ref_unwrap(0) {
                ValueRef::Enum(e, 2) => println!("{:?}", e),
                _ => panic!(),
            };
            Ok(Data {state: State::CA, value:  row.get(1)?})
        })?;
        let vs: Vec<Data> = res_iter.map(|e| e.unwrap()).collect();

where

    enum State {
        CA,
        NY,
    }

    #[derive(Debug)]
    struct Data {
        state: State,
        value: i32,
    }

In the error message, I see

Enum(UInt8(DictionaryArray {keys: PrimitiveArray<UInt8>
[
  0,
  0,
  1,
] values: StringArray
[
  "CA",
  "NY",
]}
), 0)

which has what I want, but how to do it in the code?

Thank you for your help. Yes, I'm a beginner in Rust.
Tony

[thumbert]

Still stumped by this. I found duckdb/src/test_all_types.rs and tried to replicate the enum test. Still not successful.

Here's a complete program that highlights the error:

use duckdb::{
    params,
    types::Value,
    Connection, Result,
};

#[derive(Debug)]
enum State {
    CA,
    NY,
}

#[derive(Debug)]
struct Data {
    state: State,
    value: i32,
}

fn main() -> Result<()> {
    let conn = Connection::open_in_memory().unwrap();
    let _ = conn.execute(
        r#"
CREATE TABLE stats (
    name ENUM('CA', 'NY'),
    value INTEGER,
);
"#,
        [],
    );
    let _ = conn.execute("INSERT INTO stats VALUES (?, ?);", params!["CA", 10]);
    let _ = conn.execute("INSERT INTO stats VALUES (?, ?);", params!["CA", 20]);
    let _ = conn.execute("INSERT INTO stats VALUES (?, ?);", params!["NY", 4]);

    let query = "SELECT * FROM stats;";
    let mut stmt = conn.prepare(query).unwrap();
    let res_iter = stmt.query_map([], |row| {
        let state = match row.get_ref_unwrap(0).to_owned() {
            Value::Enum(e) => match e.as_str() {
                "CA" => State::CA,
                "NY" => State::NY,
                _ => panic!("Unknown state"),
            },
            _ => panic!("Oops, first column should be an enum"),
        };
        println!("{:?}", state);

        Ok(Data {
            state: State::CA,
            value: row.get(1)?,
        })
    })?;
    let vs: Vec<Data> = res_iter.map(|e| e.unwrap()).collect();
    println!("\n\n\nDone");
    println!("{:?}", vs);

    Ok(())
}

The error is:

CA
NY
thread 'main' panicked at /home/xxx/.cargo/registry/src/index.crates.io-6f17d22bba15001f/arrow-array-52.1.0/src/array/byte_array.rs:305:9:
Trying to access an element at index 2 from a StringArray of length 2
stack backtrace:
   0: rust_begin_unwind
             at /rustc/9b00956e56009bab2aa15d7bff10916599e3d6d6/library/std/src/panicking.rs:645:5
   1: core::panicking::panic_fmt
             at /rustc/9b00956e56009bab2aa15d7bff10916599e3d6d6/library/core/src/panicking.rs:72:14
   2: arrow_array::array::byte_array::GenericByteArray<T>::value
             at /home/xxx/.cargo/registry/src/index.crates.io-6f17d22bba15001f/arrow-array-52.1.0/src/array/byte_array.rs:305:9
   3: duckdb::row::Row::value_ref_internal
             at /home/xxx/.cargo/registry/src/index.crates.io-6f17d22bba15001f/duckdb-1.0.0/src/row.rs:358:32
   4: duckdb::types::value_ref::<impl core::convert::From<duckdb::types::value_ref::ValueRef> for duckdb::types::value::Value>::from
             at /home/xxx/.cargo/registry/src/index.crates.io-6f17d22bba15001f/duckdb-1.0.0/src/types/value_ref.rs:242:29
...

I would have expected to see CA, CA, NY, not CA, NY. It looks like the code loops over the enum values instead of the column values.

[thumbert]

I made it to work but it isn't pretty, it definitely doesn't live up to the headline of "an ergonomic wrapper".

let state = match row.get_ref_unwrap(0) {
    ValueRef::Enum(e, idx) => match e {
        UInt8(v) => v
            .values()
            .as_any()
            .downcast_ref::<StringArray>()
            .unwrap()
            .value(v.key(idx).unwrap()),
        _ => panic!("Unknown state"),
    },
    _ => panic!("Oops, first column should be an enum"),
};
Ok(Data {
    state: match state {
        "CA" => State::CA,
        "NY" => State::NY,
        _ => panic!("Unknown state"),                
    },
    value: row.get(1)?,
})

I think the code that deals with duckdb enums is not working as intended. For example, I think the program below should not panic, but it does.

use duckdb::{
    params,
    Connection, Result,
};

#[derive(Debug)]
enum State {
    CA,
    NY,
}

#[derive(Debug)]
struct Data {
    state: State,
    value: i32,
}

fn main() -> Result<()> {
    let conn = Connection::open_in_memory().unwrap();
    let _ = conn.execute(
        r#"
CREATE TABLE stats (
    name ENUM('CA', 'NY'),
    value INTEGER,
);
"#,
        [],
    );
    let _ = conn.execute("INSERT INTO stats VALUES (?, ?);", params!["CA", 10]);
    let _ = conn.execute("INSERT INTO stats VALUES (?, ?);", params!["CA", 20]);
    let _ = conn.execute("INSERT INTO stats VALUES (?, ?);", params!["NY", 4]);

    let mut idx = -1;
    let query = "SELECT * FROM stats;";
    let mut stmt = conn.prepare(query).unwrap();
    let res_iter = stmt.query_map([], |row| {
        let value = row.get_ref_unwrap(0).to_owned();
        println!("state: {:?}", value);
        Ok(Data {
            state: State::CA,
            value: row.get(1)?,
        })
    })?;
    let vs: Vec<Data> = res_iter.map(|e| e.unwrap()).collect();
    println!("{:?}", vs);
    Ok(())
}

If I'm doing something wrong, please comment.

Thank you,
Tony