How to append decimals using the C API

[vbmithr]

What happens?

When using appender with the C API, Duckdb refuses to insert value 933871 in a Decimal(10, 5) column, although I is a compatible value. When using Decimal(11, 5) instead, there is no error.

To Reproduce

Use C API, create a table with a DECIMAL(10, 5) column, and try to insert value inside either in with the int64 or int32 API.

OS:

Linux

DuckDB Version:

1.0.0

DuckDB Client:

C API

Full Name:

Vincent Bernardoff

Affiliation:

Deepmarker

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[szarnyasg]

Hi @vbmithr this indeed seems to be a bug based on your description. Could please provide the C code to reproduce it? Thanks!

[vbmithr]

#include <stdio.h>
#include <stdlib.h>
#include <duckdb.h>

int main() {
    duckdb_database db;
    duckdb_connection con;
    int ret;

    if (duckdb_open(NULL, &db) == DuckDBError) {
        // handle error
    }
    if (duckdb_connect(db, &con) == DuckDBError) {
        // handle error
    }

    // run queries...
    duckdb_query(con, "CREATE TABLE x (a DECIMAL(10, 5))", NULL);

    duckdb_appender appender;
    if (duckdb_appender_create(con, NULL, "x", &appender) == DuckDBError) {
        return EXIT_FAILURE;
    }
    // append the first row (1, Mark)
    ret = duckdb_append_int32(appender, 933871);
    if (ret!=0) {
        const char *msg = duckdb_appender_error(appender);
        fprintf(stderr, "%s\n", msg);
        return EXIT_FAILURE;
    }
    duckdb_appender_end_row(appender);

    // finish appending and flush all the rows to the table
    duckdb_appender_destroy(&appender);

    // cleanup
    duckdb_disconnect(&con);
    duckdb_close(&db);

    return EXIT_SUCCESS;
}

cc -lduckdb bugrepro.c
./a.out
Could not cast value 933871 to DECIMAL(10,5)

[carlopi]

I think there is no bug here, 933871 needs 6 decimals to represent its integral part, while DECIMAL(10,5) has only 5 of them. Decimal(10,5) means 10 decimals in total, of which 5 for the fractional part (meaning only numbers up to 99999.99999 can be handled).

D SELECT '933871'::DECIMAL(10,5);
Conversion Error: Could not convert string "933871" to DECIMAL(10,5)
LINE 1: SELECT '933871'::DECIMAL(10,5);

reproduces also in the CLI, so it's not C specific.

[vbmithr]

Ok, that's interesting. I understand that it fails on the CLI. (Haven't even tested), because in the CLI, 933871 means an integer.
But, using C interface, I wanted to insert the value 9.33871, not 933871.00000. The int32 value is supposed to be the "raw" value, otherwise how to insert small values?

[Giorgi]

You can use duckdb_append_varchar to insert decimal values.

You can also use duckdb_append_data_chunk to write decimal data to the underlying vector.

[Tishj]

I'd argue that this needs a method to create a duckdb_value that's a DECIMAL, the appender can already append duckdb_value objects if I'm not mistaken

[Giorgi]

There is no C API function to append duckdb_value

[Tishj]

Interesting, I guess it's not that useful currently anyways until #12140 lands

[Giorgi]

Not sure why we would need that when duckdb_append_data_chunk is already there and can append everything.

[vbmithr]

Just a precision, do I need to use append_data_chunk to insert a full row or can I append just a cell with it? It's not clear in the doc.

[Giorgi]

You can't append a single cell with it, you should append all rows with append_data_chunk .

[Mytherin]

Moving this to a discussion as there does not seem to be a bug here

[vbmithr]

Yeah. I finally managed to do what I wanted through trial and error. The doc is not amazing though, especially it's confusing to have the append_chunk function alongside other append_ functions that does not have the same semantics at all (insert a bunch of rows VS append one cell to one row). But, well…

[Tishj]

Yea I don't agree with Giorgi, DataChunk appending is not for the faint of heart, when performance matters it makes the most sense, but there should be a simple alternative

[Giorgi]

To be honest the data chunk API is quite simple once you implement reading query results with it.