How do you change a column from NULL to NOT NULL in memSQL

Off-Topic
1

How do you change a column from NULL to NOT NULL in memSQL.

I have created the table as below.

create table t_tinyint1(c TINYINT,c2 VARCHAR(100), key() USING CLUSTERED COLUMNSTORE);

Trying to alter but no luck

ALTER TABLE t_tinyint1 modify c2 VARCHAR(100) not null ;

Facing the below error.

SQL Error [1890] [HY000]: This version of MemSQL cannot perform an offline ALTER TABLE on a COLUMN STORE table. The requested ALTER TABLE cannot be performed online because it modifies column c2 from NULL to NOT NULL.

2

Consider adding a new column with the desired type, updating it with the new data, then dropping the old one. After that, rename the new column to the original column name. Could take a long time for a large table, but it should work.

3

Is there any option using ALTER statement?

4

It’s not supported yet. We have a feature request tracking this. I’ve taken note of your feedback. Thank you.

5

I believe this process is same for both row-store table and column-store tables. Please correct me.

6

ALTER TABLE … MODIFY …
can be used for rowstores to change a column data type, but not the nullability setting.

7

Hey Hanson, was this feature ever added?

Regards,
Nick

8

Hi Nick. No, it’s still on the backlog. -Eric

9

Hi hanson,

Is this feature got added or its still in backlog ?

10

Is this feature added now in recent versions - On Prem?

11

This is still on our backlog. You can’t yet do this in a single step. I expect us to make more ALTERs like this possible at a future date.

Also, you can change the column order as described here. That can be useful if you solve this by making a new column, updating it, and removing the old one, and renaming the new one, so you wind up with the same column order as you originally had.