KeyError: 0 when reading TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE columns

[MrTeeett]

Description

I encountered a KeyError: 0 when trying to read columns with TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE types using the firebirdsql library.

The error occurs during the execution of the fetchall() method after running a SELECT query.

---

Steps to Reproduce

1. Create a Firebird table with columns of type TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE.
2. Insert valid data into these columns.
3. Execute a SELECT * FROM table_name; query using firebirdsql and attempt to fetch all rows.

---

Example Code

import firebirdsql
import os

def create_database(dsn, user, password, db_name):
    if not os.path.exists(db_name):
        firebirdsql.create_database(
            dsn=f"{dsn}:{db_name}",
            user=user,
            password=password,
            charset='UTF8'
        )
        print(f"Database '{db_name}' created successfully.")
    else:
        print(f"Database '{db_name}' already exists.")

def create_tables(dsn, user, password, db_name, table_name):
    con = firebirdsql.connect(
        dsn=f"{dsn}:{db_name}",
        user=user,
        password=password
    )
    cur = con.cursor()

    cur.execute(f"""
        SELECT COUNT(*)
        FROM rdb$relations
        WHERE rdb$relation_name = '{table_name.upper()}';
    """)
    exists = cur.fetchone()[0]

    if exists == 0:
        cur.execute(f"""
            CREATE TABLE {table_name} (
                id INTEGER PRIMARY KEY,
                bigint_value BIGINT,
                boolean_value BOOLEAN,
                binary_fixed BINARY(16),
                blob_data BLOB,
                char_fixed CHAR(50),
                date_value DATE,
                decimal_value DECIMAL(18, 4),
                decfloat_value DECFLOAT(16),
                double_value DOUBLE PRECISION,
                float_value FLOAT,
                int_value INTEGER,
                int128_value INT128,
                national_char_fixed NATIONAL CHARACTER(50),
                national_char_varying NATIONAL CHARACTER VARYING(50),
                numeric_value NUMERIC(18, 4),
                smallint_value SMALLINT,
                time_value TIME,
                time_with_tz TIME WITH TIME ZONE,
                timestamp_value TIMESTAMP,
                timestamp_with_tz TIMESTAMP WITH TIME ZONE,
                varbinary_data VARBINARY(50),
                varchar_data VARCHAR(50)
            );
        """)
        print(f"Table '{table_name}' with all data types created successfully.")
    else:
        print(f"Table '{table_name}' already exists.")

    con.commit()
    con.close()

def insert_data(dsn, user, password, db_name, table_name):
    con = firebirdsql.connect(
        dsn=f"{dsn}:{db_name}",
        user=user,
        password=password,
        charset='UTF8'
    )
    cur = con.cursor()

    data = (
        1,
        123456789012345,
        True,
        b'example_bin',
        b'binary_data_here',
        'C'.ljust(50)[:50],
        '2024-12-08',
        12345.678,
        12345.6789,
        12345.67,
        1234.56,
        42,
        123456789012345678,
        'NATIONAL_CHAR_1'.ljust(50)[:50],
        'NATIONAL_CHAR_2',
        98765.4321,
        123,
        '12:34:56',
        '12:34:56+02:00',
        '2024-12-08 12:34:56',
        '2024-12-08 12:34:56+02:00',
        b'varbinary_data',
        'varchar_data'
    )

    cur.execute(f"""
        INSERT INTO {table_name} (
            id, bigint_value, boolean_value, binary_fixed, blob_data, char_fixed,
            date_value, decimal_value, decfloat_value, double_value, float_value,
            int_value, int128_value, national_char_fixed, national_char_varying,
            numeric_value, smallint_value, time_value, time_with_tz,
            timestamp_value, timestamp_with_tz, varbinary_data, varchar_data
        ) VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        );
    """, data)

    con.commit()
    print(f"Data successfully inserted into table '{table_name}'.")
    con.close()

def read_data(dsn, user, password, db_name, table_name):
    con = firebirdsql.connect(
        dsn=f"{dsn}:{db_name}",
        user=user,
        password=password
    )
    cur = con.cursor()
    cur.execute(f"SELECT * FROM {table_name};")
    rows = cur.fetchall()

    for row in rows:
        print(row)

    con.close()
    
def read_data_no_with_time_zone(dsn, user, password, db_name, table_name):
    con = firebirdsql.connect(
        dsn=f"{dsn}:{db_name}",
        user=user,
        password=password,
        charset='UTF8'
    )
    cur = con.cursor()

    cur.execute(f"""
        SELECT
            id, bigint_value, boolean_value, binary_fixed, blob_data, char_fixed,
            date_value, decimal_value, decfloat_value, double_value, float_value,
            int_value, int128_value, national_char_fixed, national_char_varying,
            numeric_value, smallint_value, time_value, varbinary_data, varchar_data
        FROM {table_name};
    """)
    rows = cur.fetchall()

    for row in rows:
        print(row)

    con.close()

dsn = "localhost"
db_name = "/home/mrteeett/work/drivers/pyfirebirdsql/test.fdb"
user = "SYSDBA"
password = "masterkey"
table_name = "all_types_table"

create_database(dsn, user, password, db_name)
create_tables(dsn, user, password, db_name, table_name)
insert_data(dsn, user, password, db_name, table_name)
read_data(dsn, user, password, db_name, table_name)
read_data_no_with_time_zone(dsn, user, password, db_name, table_name)

[nakagami]

Cannot test right now.
Please wait.

By the way, can you paste the Traceback here?

[MrTeeett]

Traceback

Traceback (most recent call last):
  File "/home/mrteeett/work/drivers/pyfirebirdsql/main.py", line 159, in <module>
    read_data(dsn, user, password, db_name, table_name)
  File "/home/mrteeett/work/drivers/pyfirebirdsql/main.py", line 140, in read_data
    rows = cur.fetchall()
  File "/home/mrteeett/work/drivers/pyfirebirdsql/.venv/lib/python3.10/site-packages/firebirdsql/fbcore.py", line 348, in fetchall
    return [tuple(r) for r in self._fetch_records]
  File "/home/mrteeett/work/drivers/pyfirebirdsql/.venv/lib/python3.10/site-packages/firebirdsql/fbcore.py", line 348, in <listcomp>
    return [tuple(r) for r in self._fetch_records]
  File "/home/mrteeett/work/drivers/pyfirebirdsql/.venv/lib/python3.10/site-packages/firebirdsql/fbcore.py", line 90, in fetch_generator
    (rows, more_data) = connection._op_fetch_response(self.handle, self.xsqlda)
  File "/home/mrteeett/work/drivers/pyfirebirdsql/.venv/lib/python3.10/site-packages/firebirdsql/fbcore.py", line 879, in _op_fetch_response
    r[i] = x.value(raw_value)
  File "/home/mrteeett/work/drivers/pyfirebirdsql/.venv/lib/python3.10/site-packages/firebirdsql/xsqlvar.py", line 191, in value
    tz = self._parse_time_zone(raw_value[4:6])
  File "/home/mrteeett/work/drivers/pyfirebirdsql/.venv/lib/python3.10/site-packages/firebirdsql/xsqlvar.py", line 153, in _parse_time_zone
    return get_tzinfo_by_id(bytes_to_bint(raw_value, u=True))
  File "/home/mrteeett/work/drivers/pyfirebirdsql/.venv/lib/python3.10/site-packages/firebirdsql/tz_utils.py", line 41, in get_tzinfo_by_id
    return get_tzinfo_by_name(timezone_name_by_id[timezone_id])
KeyError: 0
Exception ignored in: <function ConnectionBase.__del__ at 0x7f0ffed74550>
Traceback (most recent call last):
  File "/home/mrteeett/work/drivers/pyfirebirdsql/.venv/lib/python3.10/site-packages/firebirdsql/fbcore.py", line 1176, in __del__
  File "/home/mrteeett/work/drivers/pyfirebirdsql/.venv/lib/python3.10/site-packages/firebirdsql/fbcore.py", line 1153, in close
  File "/home/mrteeett/work/drivers/pyfirebirdsql/.venv/lib/python3.10/site-packages/firebirdsql/fbcore.py", line 467, in _cleanup
  File "/home/mrteeett/work/drivers/pyfirebirdsql/.venv/lib/python3.10/site-packages/firebirdsql/fbcore.py", line 655, in _op_response
firebirdsql.err.InternalError: _op_response:op_code = 60652

[nakagami]

The file conversion from timezone_id to TimeZone name isbased on this map.
https://github.com/nakagami/pyfirebirdsql/blob/master/firebirdsql/tz_map.py

It is defined bellow
https://raw.githubusercontent.com/FirebirdSQL/firebird/master/src/common/TimeZones.h

Neither of them have 60652.
I wonder if the process of converting from timezone id to TimeZone is not working.
Does anyone know?

[nakagami]

3574756
Has this commit fixed it?
I believe it has been fixed, is there anything still wrong?