Can Ceresdb support default value for columns?

[MichaelLeeHZ]

Describe This Problem

Can Ceresdb support default value for columns something like timestamp = now()

Proposal

• In Oceanbase, I can record data insert time, like gmt_create = now().

Additional Context

No response

[Rachelint]

It is still not supported now , are you interested to make it?

[ShiKaiWi]

It's a very useful feature, and we will support it in the near future.

[ygf11]

Does it means we need support default value which is an expr?

The grammar may be like: CREATE TABLE test(a int DEFAULT b + 1, b int DEFAULT 0, c timestamp DEFAULT now())...;

[waynexia]

Does it means we need support default value which is an expr?

The grammar may be like: CREATE TABLE test(a int DEFAULT b + 1, b int DEFAULT 0, c timestamp DEFAULT now())...;

Yes, that gramma looks great! (only we don't support now() at present 🤣).

[ygf11]

Yes, that gramma looks great! (only we don't support now() at present 🤣).

Let me have a try :D

[ShiKaiWi]

CREATE TABLE test(a int DEFAULT b + 1, b int DEFAULT 0, c timestamp DEFAULT now())

In this statement, a = b + 1 seems a little bit complex to implement, and I suppose the feature that defines default value as any expression) can be supported in another PR. What we can do here is to define the default value as some simple expression, e.g. literal value or some built-in function like now.

@ygf11 @waynexia What do you think of it?

[ygf11]

In this statement, a = b + 1 seems a little bit complex to implement, and I suppose the feature that defines default value as any expression) can be supported in another PR. What we can do here is to define the default value as some simple expression, e.g. literal value or some built-in function like now.

I almost finish most of it, but submiting a simple implement is also ok to me. I will submit a pr today or tomorrow.

[ShiKaiWi]

In this statement, a = b + 1 seems a little bit complex to implement, and I suppose the feature that defines default value as any expression) can be supported in another PR. What we can do here is to define the default value as some simple expression, e.g. literal value or some built-in function like now.

I almost finish most of it, but submiting a simple implement is also ok to me. I will submit a pr today or tomorrow.

It will be surely great if you have made it.

[jiacai2050]

I thought of one corner case when default value defined in expression

what if two columns's default value reference each other?

MySQL only allow to reference a column defined before it, how will you avoid this?

[ygf11]

I thought of one corner case when default value defined in expression

Yes, it is a problem. So circle reference is not allowed here, we can check and return error when create table.

MySQL only allow to reference a column defined before it, how will you avoid this?

We can reorder the missing columns, the simple columns will run first, and other columns which depends it will run after.

for example:

CREATE TABLE t(c1 string tag not null, 
               ts timestamp not null, 
               c3 uint32 Default c4, 
               c4 uint32 Default c5, 
               c5 uint32 Default 0, timestamp key(ts),primary key(c1, ts)) \
               ENGINE=Analytic WITH (ttl='70d',update_mode='overwrite',arena_block_size='1KB')"

Insert into t(c1, ts) values(xx, xxx);

The original order of the missing column is (c3, c4, c5). After we reorder them, they will be (c5, c4 c3). This running order is ok.

Maybe the behavior of mysql is also an alternative, because the full implement maybe a little complex like @ShiKaiWi says.

[jiacai2050]

Maybe the behavior of mysql is also an alternative, because the full implement maybe a little complex like @ShiKaiWi says.

Yep, I think we can follow what MySQL does, leave circle reference detect/reorder for future work.

A tracking issue can be created to discuss those optimization.

[ygf11]

A tracking issue can be created to discuss those optimization.

I create a tracking issue #252.

[jiacai2050]

For now, this feature is ready for basic use, so closing.

CREATE TABLE `test` (
`ts` timestamp TIMESTAMP KEY NOT NULL, 
`t1` string default "hello",  
 `ts2` timestamp default now()
) with (
 enable_ttl = 'false'
);

insert into `test`  (ts) 
values (123);

select * from `test`

Wii return

{
    "rows": [
        {
            "ts": 123,
            "tsid": 0,
            "t1": "hello",
            "ts2": 1667307913166
        }
    ]
}