Numeric value is out of range for inserting Firebird 4.0 release candidate 1

[Margaret Lawson]

I am running into a persistent "numeric value is out of range" error that is triggered by adding an additional column (of type double precision, varchar, smallint, intger, int64, they all fail) without binding a value for this insert. It also fails if I try to bind a value to the column.

I have attached a minimal reproducible example (I apologize, if I insert fewer columns the error doesn't occur, or occurs only sporadically). The code below will run successfully, but if I add in an 11th column (small0), it fails.

###############################################################

#include <stdlib.h>
#include <string.h>
#include <ibase.h>
#include <stdio.h>
#include <set>
#include <iostream>

#define RC_OK 0

using namespace std;

#define SQL_DIALECT_VERSION 3
#define DB_HANDLE_COUNT 1
#define USE_XSQLDA 1


#ifndef NDEBUG
#   define ASSERT(condition, message) \
    do { \
        if (! (condition)) { \
            std::cerr << "Assertion `" #condition "` failed in " << __FILE__ \
                      << " line " << __LINE__ << ": " << message << std::endl; \
            std::terminate(); \
        } \
    } while (false)
#else
#   define ASSERT(condition, message) do { } while (false)
#endif

isc_db_handle db = NULL;
isc_stmt_handle stmt = NULL;
size_t num_cols;


int initialize_db() {
    int rc;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */
    string db_name = "correctness_test.fdb";
    string create_db_query = "CREATE DATABASE '" + db_name + "' PAGE_SIZE=8192";

    /*
     *    Create a new database.
     *    The database handle is zero.
     */
   
    cout << "about to isc_dsql_execute_immediate" << endl;
    if (isc_dsql_execute_immediate(status, &db, &trans, 0, create_db_query.c_str(), SQL_DIALECT_VERSION,
                                   NULL))
    {
        /* Extract SQLCODE from the status vector. */
        long sqlcode = isc_sqlcode(status);

        /* Print a descriptive message based on the SQLCODE. */
        if (sqlcode == -902)
        {
            printf("\nDatabase already exists.\n");
            // printf("Remove %s before running this program.\n\n", new_dbname);
            rc = isc_attach_database(status, 0, db_name.c_str(), &db, 0, NULL);
            ASSERT(rc == RC_OK, isc_print_status(status));
        }
    }
    cout << "about to isc_commit_transaction" << endl;

    isc_commit_transaction(status, &trans);
    printf("Created database '%s'.\n\n", db_name.c_str());

    return rc;
}


//designed to create one at a time, so we don't have to worry about cross-table interference
int create_table() {
    int rc;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */
    string create_table_query;

    create_table_query = "recreate table metadata_chunks "
        "(id bigint generated always as identity primary key, string0 varchar (50), int0 bigint, string2 varchar (50)"
        ", dbl0 double precision, dbl1 double precision, dbl2 double precision, dbl3 double precision, dbl4 double precision, dbl5 double precision, "

        //uncommenting this causes the insert to produce "numeric value is out of range" error
        // "small0 smallint, "

        //uncommenting this causes the insert to produce "numeric value is out of range" error
        // "small0 double precision, "

        //uncommenting this causes the insert to produce "numeric value is out of range" error
        // ", small0 varchar(256)"

        "string3 varchar(256)"
        ")";

    /* Create table. */
    isc_start_transaction(status, &trans, DB_HANDLE_COUNT, &db, 0, NULL);
    cout << "create_table_query: " << create_table_query << endl;
    rc = isc_dsql_execute_immediate(status, &db, &trans, 0, create_table_query.c_str(), SQL_DIALECT_VERSION, NULL);
    ASSERT(rc == RC_OK, isc_print_status(status));
    isc_commit_transaction(status, &trans);

    return rc;
}
        
int prepare_statements() {
    int rc;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */
    rc = isc_dsql_allocate_statement(status, &db, &stmt);

    // string query = "insert into metadata_chunks (string0, int0, string2, dbl0, dbl1, dbl2, dbl3, dbl4, dbl5, small0, string3) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    string query = "insert into metadata_chunks (string0, int0, string2, dbl0, dbl1, dbl2, dbl3, dbl4, dbl5, string3) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    num_cols = 10; //not including id
    // num_cols = 11; //not including id

    rc = isc_dsql_prepare(status, &trans, &stmt, 0, query.c_str(), SQL_DIALECT_VERSION, NULL);
    ASSERT(rc == RC_OK, isc_print_status(status));

    return rc;
}

int perform_write(char *name, size_t len_name, size_t *timestep, char *var_name, size_t len_var_name,
    double *dbl0, double *dbl1, double *dbl2, double *dbl3, double *dbl4, double *dbl5
    , unsigned short *data_type, char *data, size_t len_data

    ) {
    int rc;
    ISC_STATUS_ARRAY status; /* status vector */
    isc_tr_handle trans = NULL; /* transaction handle */
    short is_null[num_cols];
    size_t length[num_cols];

    XSQLDA *stmt_input_params = (XSQLDA *) malloc(XSQLDA_LENGTH(num_cols));
    stmt_input_params->sqln = stmt_input_params->sqld = num_cols;
    stmt_input_params->version = 1;

    int i = 0;
    is_null[i] = (len_name == 0);
    length[i] = len_name;
    stmt_input_params->sqlvar[i].sqltype = SQL_TEXT + 1;
    stmt_input_params->sqlvar[i].sqldata = name;
    stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    stmt_input_params->sqlvar[i].sqllen = length[i];
    i++;

    is_null[i] = 0;
    length[i] = sizeof(size_t);
    stmt_input_params->sqlvar[i].sqltype = SQL_INT64;
    stmt_input_params->sqlvar[i].sqldata = (char*)timestep;
    stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    stmt_input_params->sqlvar[i].sqllen = length[i];
    i++;
 
    is_null[i] = (len_var_name == 0);
    length[i] = len_var_name;
    stmt_input_params->sqlvar[i].sqltype = SQL_TEXT + 1;
    stmt_input_params->sqlvar[i].sqldata = var_name;
    stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    stmt_input_params->sqlvar[i].sqllen = length[i];
    i++;

    is_null[i] = 0;
    length[i] = sizeof(double);
    stmt_input_params->sqlvar[i].sqltype = SQL_DOUBLE;
    stmt_input_params->sqlvar[i].sqldata = (char*)dbl0;
    stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    stmt_input_params->sqlvar[i].sqllen = length[i];
    i++;

    is_null[i] = 0;
    length[i] = sizeof(double);
    stmt_input_params->sqlvar[i].sqltype = SQL_DOUBLE;
    stmt_input_params->sqlvar[i].sqldata = (char*)dbl1;
    stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    stmt_input_params->sqlvar[i].sqllen = length[i];
    i++;

    is_null[i] = 0;
    length[i] = sizeof(double);
    stmt_input_params->sqlvar[i].sqltype = SQL_DOUBLE;
    stmt_input_params->sqlvar[i].sqldata = (char*)dbl2;
    stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    stmt_input_params->sqlvar[i].sqllen = length[i];
    i++;

    is_null[i] = 0;
    length[i] = sizeof(double);
    stmt_input_params->sqlvar[i].sqltype = SQL_DOUBLE;
    stmt_input_params->sqlvar[i].sqldata = (char*)dbl3;
    stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    stmt_input_params->sqlvar[i].sqllen = length[i];
    i++;

    is_null[i] = 0;
    length[i] = sizeof(double);
    stmt_input_params->sqlvar[i].sqltype = SQL_DOUBLE;
    stmt_input_params->sqlvar[i].sqldata = (char*)dbl4;
    stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    stmt_input_params->sqlvar[i].sqllen = length[i];
    i++;

    is_null[i] = 0;
    length[i] = sizeof(double);
    stmt_input_params->sqlvar[i].sqltype = SQL_DOUBLE;
    stmt_input_params->sqlvar[i].sqldata = (char*)dbl5;
    stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    stmt_input_params->sqlvar[i].sqllen = length[i];
    i++;

    is_null[i] = (len_data == 0);
    length[i] = len_data;
    stmt_input_params->sqlvar[i].sqltype = SQL_TEXT + 1;
    stmt_input_params->sqlvar[i].sqldata = data;
    stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    stmt_input_params->sqlvar[i].sqllen = length[i];
    i++;

  

    //if I increase num_cols to 11 and add the extra column (small0) to the table with type double, varchar or smallint and uncomment one of the parameters below, it still produces a numeric overflow error

    // is_null[i] = 0;
    // length[i] = sizeof(double);
    // stmt_input_params->sqlvar[i].sqltype = SQL_DOUBLE;
    // stmt_input_params->sqlvar[i].sqldata = (char*)dbl5;
    // stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    // stmt_input_params->sqlvar[i].sqllen = length[i];
    // i++;

    // is_null[i] = (len_data == 0);
    // length[i] = len_data;
    // stmt_input_params->sqlvar[i].sqltype = SQL_TEXT + 1;
    // stmt_input_params->sqlvar[i].sqldata = data;
    // stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    // stmt_input_params->sqlvar[i].sqllen = length[i];
    // i++;

    // is_null[i] = 0;
    // length[i] = sizeof(unsigned short);
    // stmt_input_params->sqlvar[i].sqltype = SQL_SHORT;
    // stmt_input_params->sqlvar[i].sqldata = (char*)data_type;
    // stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    // stmt_input_params->sqlvar[i].sqllen = length[i];
    //  i++;

    /* Declare the cursor. */
    isc_dsql_set_cursor_name(status, &stmt, "write md", 0);

    rc = isc_start_transaction(status, &trans, DB_HANDLE_COUNT, &db, 0, NULL);
    ASSERT(rc == RC_OK, isc_print_status(status));

    cout << "inserting" << endl;
    rc = isc_dsql_execute(status, &trans, &stmt, SQL_DIALECT_VERSION, stmt_input_params);
    ASSERT(rc == RC_OK, isc_print_status(status));

    rc = isc_commit_transaction(status, &trans);
    ASSERT(rc == RC_OK, isc_print_status(status));

    free(stmt_input_params);
    cout << "finished successfully!" << endl;

    return rc;
}


int main() {

    char string0[256] = "string0";
    char string1[256] = "string1";
    char string2[256] = "string2";
    size_t int0 = 123;
    double dbl0 = 1.2, dbl1 = 2.3, dbl2 = 3.4, dbl3 = 4.5, dbl4 = 5.6, dbl5 = 6.7;
    unsigned short short0 = 1;

    initialize_db();
    create_table();
    prepare_statements();
    perform_write(string0, strlen(string0), &int0, string1, strlen(string1)
        , &dbl0, &dbl1, &dbl2, &dbl3, &dbl4, &dbl5
        , &short0, string2, strlen(string2)
    );
}
###############################################################

My best understanding is that this error indicates that one (or more) or my buffer sizes does not match the SQL datatype listed or the strlen listed. However I am failing to see where I am going wrong. Any ideas? My goal is to be able to add the 11th column in (ideally with type smallint) without having the numeric overflow error.

[Mark Rotteveel]

Questions or suspected bugs about pre-release versions are off-topic on
firebird-support. Please ask your question on firebird-devel (see
https://firebirdsql.org/en/mailing-lists/ for subscription info).

Mark (moderator)

--
Mark Rotteveel

[Dimitry Sibiryakov]

03.04.2021 09:02, Margaret Lawson wrote:
> Any ideas?

Your SQLDA propagation is mostly wrong in this example. If the real code is the same -
there is nothing to wonder at that it doesn't work.

You doesn't initialize subtype and precision.
Your sqllen for bigint is wrong for 32 bits platform.
You cast pointer from double value instead of pointer to double value.
You mixed positions of the last two parameters.

--
WBR, SD.

[Dimitry Sibiryakov]

03.04.2021 11:53, Dimitry Sibiryakov wrote:
>   You cast pointer from double value instead of pointer to double value.

Oops, here I was wrong because didn't pay attention to function's parameters types.

--
WBR, SD.

[Margaret Lawson]

Your bigint comment is correct, and I will adjust it, but this is not the problem, and I will be running the code only on a single 64 bit machine.

The last two columns in my code are dbl5 double precision and string3 varchar(256). The last two parameters are     stmt_input_params->sqlvar[i].sqltype = SQL_DOUBLE and stmt_input_params->sqlvar[i].sqldata = (char*)dbl5 and stmt_input_params->sqlvar[i].sqltype = SQL_TEXT + 1  stmt_input_params->sqlvar[i].sqldata = data, so this is not mixed. What I did not properly adjust in the example provided here was that the commented out values for small0 are meant to be above this final sqldata = data parameter. However, this does not explain why merely adding the small0 column (with any of the three data types), without even attempting to insert to it, results in a numeric overflow error.

Also, you state that I didn't initialize subtype and precision. My understanding is that subtype only is initialized for blob types and that precision are only indicated for decimal and numeric types. Is this incorrect?

I have added the slightly modified code below:

##############################################################

        // "small0 varchar(256), "

    //if I increase num_cols to 11 and add the extra column (small0) to the table with type double, varchar or smallint and uncomment one of the parameters below, it still produces a numeric overflow error
    // is_null[i] = 0;

    // length[i] = sizeof(unsigned short);
    // stmt_input_params->sqlvar[i].sqltype = SQL_SHORT;
    // stmt_input_params->sqlvar[i].sqldata = (char*)data_type;
    // stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    // stmt_input_params->sqlvar[i].sqllen = length[i];
    //  i++;

    // is_null[i] = 0;
    // length[i] = sizeof(double);
    // stmt_input_params->sqlvar[i].sqltype = SQL_DOUBLE;
    // stmt_input_params->sqlvar[i].sqldata = (char*)dbl5;
    // stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    // stmt_input_params->sqlvar[i].sqllen = length[i];
    // i++;

    // is_null[i] = (len_data == 0);
    // length[i] = len_data;
    // stmt_input_params->sqlvar[i].sqltype = SQL_TEXT + 1;
    // stmt_input_params->sqlvar[i].sqldata = data;
    // stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    // stmt_input_params->sqlvar[i].sqllen = length[i];
    // i++;

    is_null[i] = (len_data == 0);
    length[i] = len_data;
    stmt_input_params->sqlvar[i].sqltype = SQL_TEXT + 1;
    stmt_input_params->sqlvar[i].sqldata = data;
    stmt_input_params->sqlvar[i].sqlind = &is_null[i];
    stmt_input_params->sqlvar[i].sqllen = length[i];
    i++;

    cout << "is_null: " << is_null[i-1] << ", length: " << length[i-1] << endl;

    /* Declare the cursor. */
    isc_dsql_set_cursor_name(status, &stmt, "write md", 0);

    rc = isc_start_transaction(status, &trans, DB_HANDLE_COUNT, &db, 0, NULL);
    ASSERT(rc == RC_OK, isc_print_status(status));

    cout << "inserting" << endl;

    // cout << "about to isc_dsql_execute" << endl;

    rc = isc_dsql_execute(status, &trans, &stmt, SQL_DIALECT_VERSION, stmt_input_params);
    ASSERT(rc == RC_OK, isc_print_status(status));

    rc = isc_commit_transaction(status, &trans);
    ASSERT(rc == RC_OK, isc_print_status(status));

    free(stmt_input_params);
    cout << "finished successfully!" << endl;

    return rc;
}

int main() {

    char string0[256] = "string0";
    char string1[256] = "string1";
    char string2[256] = "string2";
    size_t int0 = 123;
    double dbl0 = 1.2, dbl1 = 2.3, dbl2 = 3.4, dbl3 = 4.5, dbl4 = 5.6, dbl5 = 6.7;
    unsigned short short0 = 1;

    initialize_db();
    create_table();
    prepare_statements();
    perform_write(string0, strlen(string0), &int0, string1, strlen(string1)
        , &dbl0, &dbl1, &dbl2, &dbl3, &dbl4, &dbl5
        , &short0, string2, strlen(string2)
    );
}

[Dimitry Sibiryakov]

03.04.2021 19:59, Margaret Lawson wrote:
> My understanding is that subtype only is initialized for blob types and that precision are
> only indicated for decimal and numeric types. Is this incorrect?

Yes, it is incorrect.
Subtype for text data indicates character set which is important. For decimals it
indicates INTEGER/DECIMAL/NUMERIC but probably isn't used in input SQLDAs.
Setting proper sqlscale is vital for decimal types (SQL_INT64 in your example).

Use "trace and audit services" to see actual values and types of parameters as they
reached server.
It is also possible that the error comes not from the statement itself but from some
trigger fired by it.

--
WBR, SD.