Changing column type of partition key removes partitioning

Detail name	Value
Changelog Number	16553
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 8.11.0
Resolution Date	2023-02-28

Description

The partitioning for a table is silently removed if the following conditions are met:

• A table is partitioned by one or more columns (partition key)
• The table is not empty
• The data type of one of the columns in the the partition key is changed

Preparation:

drop schema if exists TEST cascade;
create schema TEST;
open schema TEST;

create or replace table TESTTABLE(col DECIMAL(16,0), partition by col);
insert into TESTTABLE values (NULL), ('9');

Example:

-- returns true, table is partitioned
select TABLE_HAS_PARTITION_KEY from EXA_ALL_TABLES where TABLE_SCHEMA = 'TEST' and TABLE_NAME = 'TESTTABLE';

alter table TESTTABLE modify col DECIMAL(36,0);

-- *** returns false, table is no longer partitioned ***
select TABLE_HAS_PARTITION_KEY from EXA_ALL_TABLES where TABLE_SCHEMA = 'TEST' and TABLE_NAME = 'TESTTABLE';

Workaround

Restore the partitioning using the ALTER TABLE statement. For example:

alter table TESTTABLE partition by col;

Fix

After changing the data type of a column in the partition key, the table is still partitioned as expected.

Note: Changing the data type of a column in the partition key may be expensive for large tables and take a long time to execute.