[FB5] SUM() breaks query for BIGINT columns and arithmetic operations

[janzikmund]

Firebird version: 5.0.0 in Docker container

When connecting from Python, PDO or php_interbase, an error is thrown when:

1. SUM() column contains arithmetic operation, eg.

SELECT SUM(1+1) FROM RDB$DATABASE

Expected result: 2

Result: Error (from Python script):
fdb.fbcore.DatabaseError: ('Cursor.fetchone:\n- SQLCODE: -804\n- Incorrect values within SQLDA structure\n- empty pointer to data\n- at SQLVAR index 0', -804, 335544713)

2. SUM() is run on BIGINT column type

Example using demo employee database:

ALTER TABLE EMPLOYEE ADD TEST BIGINT;
UPDATE EMPLOYEE SET TEST=1;
SELECT SUM(TEST) FROM EMPLOYEE;

Expected result: (int) number of rows in table

Result: Error
fdb.fbcore.DatabaseError: ('Cursor.fetchone:\n- SQLCODE: -804\n- Incorrect values within SQLDA structure\n- empty pointer to data\n- at SQLVAR index 0', -804, 335544713)

For a reference, below is the Python3 script I am testing with:

import fdb
con = fdb.connect(dsn='localhost:/usr/local/firebird/examples/empbuild/employee.fdb', user='SYSDBA', password='masterkey')
cur = con.cursor()

cur.execute("SELECT SUM(1+1) FROM RDB$DATABASE")
print(cur.fetchall())

cur.execute("SELECT SUM(TEST) from EMPLOYEE")
print(cur.fetchall())

[hmoffatt]

Firebird 4 and later return BIGINT INT128 for SUM() etc which seems to be missing from fdb. You can tell Firebird to use BIGINT instead:

cur.execute('set bind of int128 to bigint')

TIMESTAMP WITH TIME ZONE also seems to be unsupported (which is also true in php-firebird).

[EPluribusUnum]

@janzikmund , firebird.connf : DataTypeCompatibility = 3.0 might help.

[janzikmund]

Thanks so much, I can confirm both ways above fix the problem.

@hmoffatt - you probably meant "..return INT128 for SUM", correct? Then it makes complete sense.

But do you assume this might be fixed with future versions of fdb/php-firebird? I understand they both depend on libfbclient.so , but even latest stable version gives this error, so not sure if it's any related.

What I am after is- will we be able to use SUM() reliably with default settings, or is it assumed we will need this returned type override or be locked in DataTypeCompatibility mode?

[aafemt]

According to change log the driver supports int128 staring from version 0.7.0.

[dyemanov]

fdb is deprecated, firebird-driver should be used instead.

[hmoffatt]

What I am after is- will we be able to use SUM() reliably with default settings, or is it assumed we will need this returned type override or be locked in DataTypeCompatibility mode?

php-firebird has an open issue FirebirdSQL/php-firebird#58 ; development seems to be stalled though, timezone support is also missing FirebirdSQL/php-firebird#26 .

I also use Qt which is also missing INT128 support across all platforms, so I just add the 'set bind' to my connect string. I don't see how to do that in Python with fdb though.

[janzikmund]

@aafemt , @dyemanov : Nice, that takes care of it for Python, then 👍

As for PHP, it also has this ticket, so hopefully it will get there as well:
FirebirdSQL/php-firebird#26

Thanks so much for all help

[janzikmund]

Closing.

[hmoffatt]

firebird-driver should be used instead.

I think it requires at least Firebird 3, doesn't it? Can I still talk to 2.5 servers, as long as I use a recent fbclient library?

[AlexPeshkoff]

On 5/28/24 13:58, Hamish Moffatt wrote: Can I still talk to 2.5 servers, as long as I use a recent fbclient library

Yes.