Sometimes after adding new columns to a table we face with problem of strange aliases in projection columns: new columns have aliases of old columns names. Columns were added like alter table DMA.test_migrations add column my_column int
Example of DDL:
CREATE TABLE DMA.test_migrations
(
launch_id int NOT NULL,
datamart varchar(128),
actual_date date,
time_sla interval,
kek int,
lol int,
lolkek int,
my_column int,
my_column2 int,
my_column3 int,
my_column4 int,
my_column5 int,
my_column6 int,
my_column7 int
);
CREATE PROJECTION DMA.test_migrations_super /*+basename(test_migrations),createtype(P)*/
(
launch_id,
datamart,
actual_date,
time_sla,
kek,
lol,
lolkek,
my_column,
my_column2,
my_column3,
my_column4,
my_column5,
my_column6,
my_column7
)
AS
SELECT test_migrations.launch_id,
test_migrations.datamart,
test_migrations.actual_date,
test_migrations.time_sla,
test_migrations.kek,
test_migrations.lol,
test_migrations.lolkek,
test_migrations.my_column AS launch_id,
test_migrations.my_column2 AS datamart,
test_migrations.my_column3 AS actual_date,
test_migrations.my_column4 AS time_sla,
test_migrations.my_column5 AS kek,
test_migrations.my_column6 AS lol,
test_migrations.my_column7 AS lolkek
FROM DMA.test_migrations
ORDER BY test_migrations.datamart,
test_migrations.actual_date
UNSEGMENTED ALL NODES;
SELECT MARK_DESIGN_KSAFE(1);
This situation leads to DDL statement interfered with this statement error while select analyze_histogram('DMA.test_migrations')
Does anyone face with such a problem?
We did't succeed in finding hypoteses of such behaviour.
The complete sequence of actions that led to the problem:
create table if not exists dma.test_migrations
(
launch_id int not null,
datamart varchar(128),
actual_date date,
time_sla interval
)
order by datamart, actual_date
unsegmented all nodes;
ALTER TABLE dma.test_migrations ADD COLUMN kek int ;
ALTER TABLE dma.test_migrations ADD COLUMN lol int ;
alter table dma.test_migrations drop column lol;
ALTER TABLE dma.test_migrations ADD COLUMN lol int ;
ALTER TABLE dma.test_migrations ADD COLUMN lolkek int ;
ALTER TABLE dma.test_migrations ADD COLUMN my_column int ;
ALTER TABLE dma.test_migrations ADD COLUMN my_column2 int ;
ALTER TABLE dma.test_migrations ADD COLUMN my_column3 int ;
ALTER TABLE dma.test_migrations ADD COLUMN my_column4 int ;
ALTER TABLE dma.test_migrations ADD COLUMN my_column5 int ;
ALTER TABLE dma.test_migrations ADD COLUMN my_column6 int ;
ALTER TABLE dma.test_migrations ADD COLUMN my_column7 int ;