Update using a hashtype column for comparison sometimes fails
Details
| Detail name | Value |
|---|---|
| Changelog Number | 17517 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 7.1.0, Exasol 8.0.0 |
| Fix Versions | Exasol 7.1.23, Exasol 8.23.0 |
| Resolution Date | 2023-09-11 |
Description
An UPDATE statement throws the error “Feature not supported: Incomparable Types” if the following conditions are met:
- The UPDATE uses a comparison involving the HASHTYPE column c.
- The UPDATE uses a comparison involving a HASHTYPE literal.
- There is an index on the HASHTYPE column c.
Preparation
CREATE OR REPLACE TABLE t (uuid HASHTYPE, c INT);
INSERT INTO t VALUES ('f7a4a14e-c7cc-11ec-8e32-243cb85692d9',1);
ENFORCE LOCAL INDEX ON t(uuid);
Example
-- This leads to an error: UPDATE t SET c = 2 WHERE uuid = 'f7a4a14e-c7cc-11ec-8e32-243cb85692d9';
Workaround
Cast the literal to HASHTYPE:
UPDATE t SET c = 2 WHERE uuid = cast('f7a4a14e-c7cc-11ec-8e32-243cb85692d9' as HASHTYPE);
Fix
In this situation, the UPDATE runs as expected.
