Garbage stored when writing timestamp with time zone to (external) table

[Mark Rotteveel]

I was checking how a TIMESTAMP WITH TIME ZONE ended up in an external
table, and it looks to me like it stores garbage
(uninitialized/non-zeroed memory?) into the last two bytes after the
time zone id/offset.

I guess this will also affect normal tables, but I haven't checked that.

For example

```
recreate table TIMESTAMPTZ_1 external 'C:\data\TIMESTAMPTZ_1.dat' (
col_timestamptz TIMESTAMP WITH TIME ZONE default current_timestamp,
col_end char(2) character set ASCII default '--'
);

insert into timestamptz_1 (col_timestamptz) values ('2026-02-08 14:32
+00:00');
insert into timestamptz_1 (col_timestamptz) values ('2026-02-08 14:33
+00:00');
insert into timestamptz_1 (col_timestamptz) values ('2026-02-08 14:34
+00:00');
insert into timestamptz_1 (col_timestamptz) values ('2026-02-08 14:35
+00:00');
insert into timestamptz_1 (col_timestamptz) values ('2026-02-08 14:36
+00:00');
```

This results in a file with the following hex:

97 EE 00 00 00 66 2F 1F 9F 05 EA 00 2D 2D 97 EE 00 00 C0 8D 38 1F 9F 05
EA 00 2D 2D 97 EE 00 00 80 B5 41 1F 9F 05 EB 00 2D 2D 97 EE 00 00 40 DD
4A 1F 9F 05 EC 00 2D 2D 97 EE 00 00 00 05 54 1F 9F 05 EC 00 2D 2D

The `9F 05` is the 00:00 offset, and `2D 2D` is the `col_end` value. You
can see the garbage values between those two:

* EA 00 (2x)
* EB 00
* EC 00 (2x)

These should all be 00 00 (which is what happens if you do something
similar, but with a TIME WITH TIME ZONE).

This seems a bug to me, but before I enter this into the tracker, is
that correct, or am I missing something?

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-devel wrote 08.02.2026 16:33:
> This seems a bug to me, but before I enter this into the tracker, is that
> correct, or am I missing something?

Perhaps, you are missing alignment which affects not only unused bytes before
next field but also unused bytes at the end of current field to expand its size.

--
WBR, SD.

[Mark Rotteveel]

I'm not missing alignment; this is written by the server, not by me.

And if alignment is written, it is 0x00, not random memory contents. On
top of that, CHAR fields have no alignment. And as I said, when writing
TIME WITH TIME ZONE, those two bytes are correctly zeroed.

Mark
--
Mark Rotteveel

[Dmitry Yemanov]

08.02.2026 18:33, 'Mark Rotteveel' via firebird-devel wrote:

> I was checking how a TIMESTAMP WITH TIME ZONE ended up in an external
> table, and it looks to me like it stores garbage (uninitialized/non-
> zeroed memory?) into the last two bytes after the time zone id/offset.
>
> I guess this will also affect normal tables, but I haven't checked that.

Yes, it does.

> This seems a bug to me, but before I enter this into the tracker, is
> that correct, or am I missing something?

Maybe not really a bug, but something sub-optimal -- as it does not
affect anything except storage size.

In C++, sizeof(ISC_TIME_TZ) == 8 and sizeof(ISC_TIMESTAMP_TZ) == 12, so
both have 2 bytes stored but unused. They're not explicitly zeroed,
because from the engine POV they're part of the data value, although
internally this is just a padding -- but this is a purely compiler
thing. Whether it contains garbage or zeroes is IMHO just a matter of
luck. But whatever bytes are found in the tail, they're ignored and do
not affect the stored value.


Dmitry

[Mark Rotteveel]

Well, it is exposing two bytes of memory contents. Though it would
probably be hard to gain anything useful that way, it is in theory a
security issue, on top of being confusing, and potentially reducing
effectiveness of RLE.

In any case, shall I create an issue for it?

Mark
--
Mark Rotteveel

[Dmitry Yemanov]

I believe this deserves a ticket, even if only from the storage
perspective. Tables containing billions of rows are not so rare nowadays
and we cannot afford to store more than actually needed.


Dmitry

[Mark Rotteveel]

On 09/02/2026 08:46, Dmitry Yemanov wrote:
> I believe this deserves a ticket, even if only from the storage
> perspective. Tables containing billions of rows are not so rare nowadays
> and we cannot afford to store more than actually needed.

I created https://github.com/FirebirdSQL/firebird/issues/8890

Mark
--
Mark Rotteveel