Not detect SimpleAggregateFunction DataType

[mbtolou]

Bulk insert not detect datatype of column :

create table test.data_test
(
ts DATETIME,
id VARCHAR, 
v0 SimpleAggregateFunction(anyLast,Nullable(Float64)), 
v1 SimpleAggregateFunction(anyLast,Nullable(Float64)), 
v2 SimpleAggregateFunction(anyLast,Nullable(Float64)), 
v3 SimpleAggregateFunction(anyLast,Nullable(Float64)), 
p0 SimpleAggregateFunction(anyLast,Nullable(VARCHAR))
) ENGINE=AggregatingMergeTree 
PARTITION BY toStartOfQuarter(ts) 
ORDER BY (ts,id);

exception like this :

One or more errors occurred. (Unknown type: SimpleAggregateFunction(anyLast, Nullable(Decimal(12, 0))))

[DarkWanderer]

Hi. See #51 and docs - you cannot insert directly to table with aggregate function (using any client, not just this one).

I recommend creating a "virtual" table (with Null engine)

CREATE TABLE test.data_test_insert  
(
   ts DateTime,
  id VARCHAR,
  v0 Float64,
  v0 Float64,
  v0 Float64,
  v0 Float64,
  p0 VARCHAR
)
ENGINE Null;
FROM test.data_test_insert

and a "Material view" (trigger) which will insert data from the "virtual" table to your actual table:

CREATE MATERIALIZED VIEW test.data_test_mv TO test.data_test AS SELECT 
   ts,
  id,
  anyLastState(v0),
  anyLastState(v1),
  anyLastState(v2),
  anyLastState(v3),
  anyLastState(p0)
FROM test.data_test_insert

This way, you can bulk insert into the "virtual" table and the data will automatically flow into regular one

[mbtolou]

but i test with dbeaver.

insert record successfully done in dbeaver .

[DarkWanderer]

Can you share the insert statement you used?

[mbtolou]

drop table if exists  test.data_test;

create table test.data_test
(
ts DATETIME,
id VARCHAR, 
v0 SimpleAggregateFunction(anyLast,Nullable(Float64)), 
v1 SimpleAggregateFunction(anyLast,Nullable(Float64)), 
v2 SimpleAggregateFunction(anyLast,Nullable(Float64)), 
v3 SimpleAggregateFunction(anyLast,Nullable(Float64)), 
p0 SimpleAggregateFunction(anyLast,Nullable(VARCHAR))
) ENGINE=AggregatingMergeTree 
PARTITION BY toStartOfQuarter(ts) 
ORDER BY (ts,id);

insert into test.data_test values('2019-01-01 00:00:00','id1',null,null,null,null,null);
insert into test.data_test values('2019-01-01 00:00:00','id2',1,1,1,1,'str1');
insert into test.data_test values('2019-01-01 00:00:00','id2',3,3,3,3,'str3');
insert into test.data_test(ts,id,v0,v1,v2) values('2019-01-01 00:00:00','id2',4,2,4);
insert into test.data_test(ts,id,v0,v1,v2) values('2019-01-01 00:00:00','id2',4,4,4);

select * from test.data_test;

select * from test.data_test final;

[DarkWanderer]

Hm, I didn't realize SimpleAggregateFunction behaves differently from AggregateFunction for insertion. I can add support in that case

[DarkWanderer]

Will be in next release