HASHTYPE: a binary type to store hashes
Details
| Detail name | Value |
|---|---|
| Changelog Number | 7044 |
| Type | New Feature |
| Status | Resolved |
| Fix Versions | Exasol 7.0.0 |
| Resolution Date | 2020-09-11 |
Background
UUIDs and hash values in general are becoming more an more important in relational databases, since they supersede simple integer based identity values. Storing such hashes in CHAR columns is not efficient since it takes more storage space than needed and performs slower than necessary. For these reasons we introduce an new binary type for hashes that can be efficiently stored and computed.
Feature Description
The new HASHTYPE is a fixed binary type, that can store hash values between 2 and 1024 bytes.
CREATE TABLE T(H HASHTYPE); INSERT INTO T VALUES 'a7b38db6-d985-42ca-a3ba-cc78c3d5bc2f'; SELECT H FROM T; --> a7b38db6d98542caa3bacc78c3d5bc2f
As input we accept hex and uuid style hashes, the following input is valid for a 16 byte HASHTYPE:
550e8400-e29b-11d4-a716-446655440000
{550e8400-e29b-11d4-a716-446655440000}
550E8400E29B11D4A716446655440000
550e-8400-e29b-11d4-a716-4466-5544-0000
The output format is hex per default, but can be switched to uuid style for 16 byte hashes:
SELECT H FROM T; --> a7b38db6d98542caa3bacc78c3d5bc2f ALTER SESSION SET HASHTYPE_FORMAT = 'UUID'; SELECT H FROM T; --> a7b38db6-d985-42ca-a3ba-cc78c3d5bc2f
Additionally we add new hash functions that directly return HASHTYPE data:
HASHTYPE_MD5
HASHTYPE_SHA1
HASHTYPE_SHA256
HASHTYPE_SHA512
HASHTYPE_TIGER
CREATE TABLE T(h HASHTYPE(20 BYTE));
INSERT INTO T SELECT HASHTYPE_SHA1('The quick brown fox jumps over the lazy dog');
SELECT H FROM T;
-->
2fd4e1c67a2d28fced849ee1bb76e7391b93eb12
The existing hash functions (HASH_MD5, HASH_SHA1, HASH_SHA256, HASH_SHA512, HASH_TIGER) still return char data.
Benefits
With the new HASHTYPE hashes are stored and computed efficiently.
Furthermore input data is checked to be a valid hash of the given size.
Changed behavior
1. New reserved keywords HASHTYPE and HASHTYPE_FORMAT 2. This feature breaks backward migration. If a customer uses the new type and migrates back to a version that does not support this type, he cannot query columns created with the new type anymore. A workaround is to convert all problem columns to CHAR(32) before the backward migration in performed.
