starrocks icon indicating copy to clipboard operation
starrocks copied to clipboard

Support default value expr when create table for column definition

Open Astralidea opened this issue 3 years ago • 9 comments

Feature request

Is your feature request related to a problem? Please describe.

Describe the solution you'd like

CREATE TABLE `test` (
    k1 int,
    gmt_create date NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=OLAP
DUPLICATE KEY(`k1`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`k1`) BUCKETS 2
PROPERTIES (
    "replication_num" = "1",
    "in_memory" = "false",
    "storage_format" = "DEFAULT"
);

It will be supported like now() , uuid() or and function or simple expression.

Describe alternatives you've considered

Additional context

For each batch of imports, the time of the default column generated by the database is expected to be consistent.

  • [x] #1832
  • [x] #1242
  • [x] #11152
  • [x] #2231

Astralidea avatar Nov 11 '21 02:11 Astralidea

@Astralidea I test MySQL, it doesn't support now() as default value when executing create table statement. So I think we can only support CURRENT_TIMESTAMP.

imay avatar Nov 12 '21 15:11 imay

@Astralidea I test MySQL, it doesn't support now() as default value when executing create table statement. So I think we can only support CURRENT_TIMESTAMP.

mysql 8 support now() as default value,but it will treated as CURRENT_TIMESTAMP. in my local environment mysql version is 8.0.23.

Astralidea avatar Nov 12 '21 15:11 Astralidea

mysql 8 support now() as default value,but it will treated as CURRENT_TIMESTAMP. in my local environment mysql version is 8.0.23.

@Astralidea I have another question. Does the MySQL 8.0 support other functions besides now() such as uuid()?

imay avatar Nov 15 '21 06:11 imay

mysql 8 support now() as default value,but it will treated as CURRENT_TIMESTAMP. in my local environment mysql version is 8.0.23.

@Astralidea I have another question. Does the MySQL 8.0 support other functions besides now() such as uuid()?

Yes.it supported uuid() or some simple expression. e.g.

CREATE TABLE t1 (
  `k1` int(11) NULL COMMENT "",
  uuid_field     VARCHAR(36) DEFAULT (uuid())
);

You can use this statement to test. note:The parentheses around the function cannot be omitted for simple expression

Astralidea avatar Nov 15 '21 08:11 Astralidea

mysql 8 support now() as default value,but it will treated as CURRENT_TIMESTAMP. in my local environment mysql version is 8.0.23.

@Astralidea I have another question. Does the MySQL 8.0 support other functions besides now() such as uuid()?

Yes.it supported uuid() or some simple expression. e.g.

CREATE TABLE t1 (
  `k1` int(11) NULL COMMENT "",
  uuid_field     VARCHAR(36) DEFAULT (uuid())
);

You can use this statement to test. note:The parentheses around the function cannot be omitted for simple expression

Is the parentheses necessary for now()?

And regarding for StarRocks, what do we do?

Do we support both now() and CURRENT_TIMESTAMP? Or do we only support CURRENT_TIMESTAMP?

imay avatar Nov 15 '21 10:11 imay

Is the parentheses necessary for now()? No. Mysql 8 will treated now() as CURRENT_TIMESTAMP. but you can use parentheses (now())

And regarding for StarRocks, what do we do? I think we're just doing what mysql does, and this PR is doing the same thing. If user set default value to now() it will convert to default CURRENT_TIMESTAMP.

Do we support both now() and CURRENT_TIMESTAMP? Or do we only support CURRENT_TIMESTAMP? So I think we should support both now() and CURRENT_TIMESTAMP like mysql.

for uuid() or other expression maybe next PR。

Astralidea avatar Nov 15 '21 10:11 Astralidea

@Astralidea Thanks for your reply. I agree with you.

imay avatar Nov 15 '21 11:11 imay

We have marked this issue as stale because it has been inactive for 6 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to StarRocks!

github-actions[bot] avatar Sep 05 '22 11:09 github-actions[bot]

This will continue to support uuid

Astralidea avatar Sep 13 '22 02:09 Astralidea

uuid & uuid_numric already supported

Astralidea avatar Nov 25 '22 08:11 Astralidea