[Maria-developers] new timestamp fields
Hello all, In MySQL/MariaDB 5.1, when a timestamp field was used as a key, we used an integer compare to compare the fields. We assumed all integer fields (including date, time, and timestamp) could use an integer comparison to compare the fields, and that they were either 1, 2, 3, 4 or 8 bytes long. We now see in MariaDB 5.3 and beyond that changes have been made to the timestamp field, such that the length of the field may be something other than 1, 2, 3, 4,or 8 bytes. Given a timestamp field of N bytes, how are we meant to compare them? Are some meant to be integer comparisons? Are others supposed to be memory comparisons? Thanks -Zardosht
Hi, Zardosht! On Jan 15, Zardosht Kasheff wrote:
Hello all,
In MySQL/MariaDB 5.1, when a timestamp field was used as a key, we used an integer compare to compare the fields. We assumed all integer fields (including date, time, and timestamp) could use an integer comparison to compare the fields, and that they were either 1, 2, 3, 4 or 8 bytes long.
We now see in MariaDB 5.3 and beyond that changes have been made to the timestamp field, such that the length of the field may be something other than 1, 2, 3, 4,or 8 bytes.
Given a timestamp field of N bytes, how are we meant to compare them? Are some meant to be integer comparisons? Are others supposed to be memory comparisons?
It's stored as 4-byte bigendian number of the integer part of the timestamp, and then as 0- to 4-byte bigendian number for the fractional part. Because both parts are stored as bigendian numbers, I suppose, you can compare timestamps either as numbers or with memcmp. See Field_timestamp_hires::store_TIME(). But really the API-correct way is to use Field::key_type() method and compare the values using the specified method. It's more future-proof than to hard-code comparison method mapping based on the field types. Regards, Sergei
Thanks Sergei.
Unfortunately, we need to do comparisons during phases when the Table
object, and therefore the Field object, may not be around, such as during
recovery. So for fixed length fields, we privately encode their length and
metadata that explains how to compare them.
On Tue, Jan 15, 2013 at 2:32 PM, Sergei Golubchik
Hi, Zardosht!
On Jan 15, Zardosht Kasheff wrote:
Hello all,
In MySQL/MariaDB 5.1, when a timestamp field was used as a key, we used an integer compare to compare the fields. We assumed all integer fields (including date, time, and timestamp) could use an integer comparison to compare the fields, and that they were either 1, 2, 3, 4 or 8 bytes long.
We now see in MariaDB 5.3 and beyond that changes have been made to the timestamp field, such that the length of the field may be something other than 1, 2, 3, 4,or 8 bytes.
Given a timestamp field of N bytes, how are we meant to compare them? Are some meant to be integer comparisons? Are others supposed to be memory comparisons?
It's stored as 4-byte bigendian number of the integer part of the timestamp, and then as 0- to 4-byte bigendian number for the fractional part. Because both parts are stored as bigendian numbers, I suppose, you can compare timestamps either as numbers or with memcmp.
See Field_timestamp_hires::store_TIME().
But really the API-correct way is to use Field::key_type() method and compare the values using the specified method. It's more future-proof than to hard-code comparison method mapping based on the field types.
Regards, Sergei
Hi, Zardosht! On Jan 15, Zardosht Kasheff wrote:
Unfortunately, we need to do comparisons during phases when the Table object, and therefore the Field object, may not be around, such as during recovery. So for fixed length fields, we privately encode their length and metadata that explains how to compare them.
Yes, of course. I've seen this in other engines too. I mean, that to decide on how to compare, it's better to look at Field::key_type(), not at Field::type(). You can still privately encode this decision and store it in the table or elsewhere, field's key_type() will never change without an ALTER TABLE.
But really the API-correct way is to use Field::key_type() method and compare the values using the specified method. It's more future-proof than to hard-code comparison method mapping based on the field types.
Regards, Sergei
Another question, are old timestamp fields stored the way is used to be? as
a little endian integer? or are those now big-endian as well?
On Tue, Jan 15, 2013 at 2:32 PM, Sergei Golubchik
Hi, Zardosht!
On Jan 15, Zardosht Kasheff wrote:
Hello all,
In MySQL/MariaDB 5.1, when a timestamp field was used as a key, we used an integer compare to compare the fields. We assumed all integer fields (including date, time, and timestamp) could use an integer comparison to compare the fields, and that they were either 1, 2, 3, 4 or 8 bytes long.
We now see in MariaDB 5.3 and beyond that changes have been made to the timestamp field, such that the length of the field may be something other than 1, 2, 3, 4,or 8 bytes.
Given a timestamp field of N bytes, how are we meant to compare them? Are some meant to be integer comparisons? Are others supposed to be memory comparisons?
It's stored as 4-byte bigendian number of the integer part of the timestamp, and then as 0- to 4-byte bigendian number for the fractional part. Because both parts are stored as bigendian numbers, I suppose, you can compare timestamps either as numbers or with memcmp.
See Field_timestamp_hires::store_TIME().
But really the API-correct way is to use Field::key_type() method and compare the values using the specified method. It's more future-proof than to hard-code comparison method mapping based on the field types.
Regards, Sergei
Hi, Zardosht! On Jan 15, Zardosht Kasheff wrote:
Another question, are old timestamp fields stored the way is used to be? as a little endian integer? or are those now big-endian as well?
Yes, little endian. (otherwise old tables wouldn't be readable in the new mariadb version) Regards, Sergei
participants (2)
-
Sergei Golubchik -
Zardosht Kasheff