COLUMNAR indexes and SKIPLIST indexes cannot be used on the same table

Off-Topic
1

I need to do a MERGE using spark-connector (3.0.8) based on some keys for which I am trying to create a Columnstore table with multiple keys as primary key:

CREATE TABLE test (
c_id bigint(20) ,
a_id bigint(20),
b_id bigint(20) ,
cl_id bigint(20),
cy_id bigint(20) ,
rate decimal(38,12) DEFAULT NULL,
capacity decimal(38,0) DEFAULT NULL,
requests decimal(38,0) DEFAULT NULL,
fact1 decimal(38,0) ,
fact2 decimal(38,12) ,
metric1 decimal(38,0) DEFAULT NULL,
month int(11) ,
load_timestamp timestamp ,
load_date date ,
primary key(c_id, a_id, b_id, cl_id, cy_id, fact1, fact2, month, load_timestamp, load_date) ,
SHARD KEY key1 (load_date)
USING CLUSTERED COLUMNSTORE
) AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE=‘STRICT_ALL_TABLES’;

But getting this error:

SQL Error [1851] [HY000]: COLUMNAR indexes and SKIPLIST indexes cannot be used on the same table

Select @@version;
5.5.58

2

Welcome to the SingleStore forums, @atiwari!

This works (on 7.5):

CREATE TABLE test (
c_id bigint(20) ,
a_id bigint(20),
b_id bigint(20) ,
cl_id bigint(20),
cy_id bigint(20) ,
rate decimal(38,12) DEFAULT NULL,
capacity decimal(38,0) DEFAULT NULL,
requests decimal(38,0) DEFAULT NULL,
fact1 decimal(38,0) ,
fact2 decimal(38,12) ,
metric1 decimal(38,0) DEFAULT NULL,
month int(11) ,
load_timestamp timestamp ,
load_date date ,
unique key(c_id, a_id, b_id, cl_id, cy_id, 
  fact1, fact2, month, load_timestamp, load_date) using hash,
SHARD KEY key1 (load_date)
USING CLUSTERED COLUMNSTORE
);

It’s often not a great idea to shard on a date column because data size can be skewed by date. So consider a different shard key (to give uniform size shards) unless you have a good reason for that.

Next time, to tell us your version of SingleStore, use select @@memsql_version because @@version give the MySQL compatibility version, not the SingleStore version.