This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new c4dfad18106 [Enhancement](column) support default value for
int/bigint/decimal type column (#34617)
c4dfad18106 is described below
commit c4dfad18106406f8e33079e35a63cdfef78a798a
Author: yangshijie <[email protected]>
AuthorDate: Sat May 11 14:59:20 2024 +0800
[Enhancement](column) support default value for int/bigint/decimal type
column (#34617)
Support int/bigint/decimal default value to int/bigint/decimal type column.
legacy work: #23594
---
.../antlr4/org/apache/doris/nereids/DorisParser.g4 | 4 +-
fe/fe-core/src/main/cup/sql_parser.cup | 12 +++
.../java/org/apache/doris/analysis/ColumnDef.java | 4 +-
.../doris/nereids/parser/LogicalPlanBuilder.java | 2 +
.../org/apache/doris/catalog/CreateTableTest.java | 12 +++
.../load_p0/insert/test_insert_default_value.out | 8 ++
.../test_json_load_default_number_value.out | 30 ++++++++
.../insert/test_insert_default_value.groovy | 85 ++++++++++++++++++++++
.../test_json_load_default_number_value.groovy | 83 +++++++++++++++++++++
.../test_update_schema_change.groovy | 12 ++-
10 files changed, 244 insertions(+), 8 deletions(-)
diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
index 26618690e86..5f9fbfb1f6e 100644
--- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
+++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
@@ -579,8 +579,8 @@ columnDef
(aggType=aggTypeDef)?
((NOT)? NULL)?
(AUTO_INCREMENT (LEFT_PAREN autoIncInitValue=number RIGHT_PAREN)?)?
- (DEFAULT (nullValue=NULL | INTEGER_VALUE | stringValue=STRING_LITERAL|
CURRENT_DATE
- | defaultTimestamp=CURRENT_TIMESTAMP (LEFT_PAREN
defaultValuePrecision=number RIGHT_PAREN)?))?
+ (DEFAULT (nullValue=NULL | INTEGER_VALUE | DECIMAL_VALUE |
stringValue=STRING_LITERAL
+ | CURRENT_DATE | defaultTimestamp=CURRENT_TIMESTAMP (LEFT_PAREN
defaultValuePrecision=number RIGHT_PAREN)?))?
(ON UPDATE CURRENT_TIMESTAMP (LEFT_PAREN onUpdateValuePrecision=number
RIGHT_PAREN)?)?
(COMMENT comment=STRING_LITERAL)?
;
diff --git a/fe/fe-core/src/main/cup/sql_parser.cup
b/fe/fe-core/src/main/cup/sql_parser.cup
index dbe5edd0a71..e13e2d826c6 100644
--- a/fe/fe-core/src/main/cup/sql_parser.cup
+++ b/fe/fe-core/src/main/cup/sql_parser.cup
@@ -3918,6 +3918,18 @@ opt_default_value ::=
{:
RESULT =
ColumnDef.DefaultValue.currentTimeStampDefaultValueWithPrecision(precision);
:}
+ | KW_DEFAULT INTEGER_LITERAL:value
+ {:
+ RESULT = new ColumnDef.DefaultValue(true, value);
+ :}
+ | KW_DEFAULT LARGE_INTEGER_LITERAL:value
+ {:
+ RESULT = new ColumnDef.DefaultValue(true, value);
+ :}
+ | KW_DEFAULT DECIMAL_LITERAL:value
+ {:
+ RESULT = new ColumnDef.DefaultValue(true, value);
+ :}
;
opt_is_key ::=
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/ColumnDef.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/ColumnDef.java
index 6207a5088af..46a48339d54 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/ColumnDef.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/ColumnDef.java
@@ -70,9 +70,9 @@ public class ColumnDef {
// used for column which defaultValue is an expression.
public DefaultValueExprDef defaultValueExprDef;
- public DefaultValue(boolean isSet, String value) {
+ public DefaultValue(boolean isSet, Object value) {
this.isSet = isSet;
- this.value = value;
+ this.value = value == null ? null : value.toString();
this.defaultValueExprDef = null;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
index ec295724f4f..9c7b3ca229a 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
@@ -2614,6 +2614,8 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
if (ctx.DEFAULT() != null) {
if (ctx.INTEGER_VALUE() != null) {
defaultValue = Optional.of(new
DefaultValue(ctx.INTEGER_VALUE().getText()));
+ } else if (ctx.DECIMAL_VALUE() != null) {
+ defaultValue = Optional.of(new
DefaultValue(ctx.DECIMAL_VALUE().getText()));
} else if (ctx.stringValue != null) {
defaultValue = Optional.of(new
DefaultValue(toStringValue(ctx.stringValue.getText())));
} else if (ctx.nullValue != null) {
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/catalog/CreateTableTest.java
b/fe/fe-core/src/test/java/org/apache/doris/catalog/CreateTableTest.java
index 8a7b5051ec7..c4901ced10d 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/catalog/CreateTableTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/catalog/CreateTableTest.java
@@ -229,6 +229,18 @@ public class CreateTableTest extends TestWithFeService {
Assert.assertTrue(tbl13.getColumn(Column.SEQUENCE_COL).getAggregationType() ==
AggregateType.NONE);
Assert.assertTrue(tbl13.getColumn(Column.SEQUENCE_COL).getType() ==
Type.INT);
Assert.assertEquals(tbl13.getSequenceMapCol(), "v1");
+
+ ExceptionChecker.expectThrowsNoException(
+ () -> createTable("create table test.tbl14\n" + "(k1 int, k2
int default 10)\n" + "duplicate key(k1)\n"
+ + "distributed by hash(k2) buckets 1\n" +
"properties('replication_num' = '1'); "));
+
+ ExceptionChecker.expectThrowsNoException(
+ () -> createTable("create table test.tbl15\n" + "(k1 int, k2
bigint default 11)\n" + "duplicate key(k1)\n"
+ + "distributed by hash(k2) buckets 1\n" +
"properties('replication_num' = '1'); "));
+
+ ExceptionChecker.expectThrowsNoException(
+ () -> createTable("create table test.tbl17\n" + "(k1 int, k2
decimal(10,2) default 10.3)\n" + "duplicate key(k1)\n"
+ + "distributed by hash(k2) buckets 1\n" +
"properties('replication_num' = '1'); "));
}
@Test
diff --git a/regression-test/data/load_p0/insert/test_insert_default_value.out
b/regression-test/data/load_p0/insert/test_insert_default_value.out
new file mode 100644
index 00000000000..7fc34c82fda
--- /dev/null
+++ b/regression-test/data/load_p0/insert/test_insert_default_value.out
@@ -0,0 +1,8 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select1 --
+10 10000 10000000 92233720368547758 19223372036854775807
10.3 10.3
+10 10000 10000000 92233720368547758 19223372036854775807
10.3 10.3
+
+-- !select2 --
+true 10 10000 10000000 92233720368547758
19223372036854775807 3.14159 hello world, today is 15/06/2023
2023-06-15 2023-06-15T16:10:15 10.3
+true 10 10000 10000000 92233720368547758
19223372036854775807 3.14159 hello world, today is 15/06/2023
2023-06-15 2023-06-15T16:10:15 10.3
diff --git
a/regression-test/data/load_p0/stream_load/test_json_load_default_number_value.out
b/regression-test/data/load_p0/stream_load/test_json_load_default_number_value.out
new file mode 100644
index 00000000000..b6775a558b5
--- /dev/null
+++
b/regression-test/data/load_p0/stream_load/test_json_load_default_number_value.out
@@ -0,0 +1,30 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select1 --
+1 default_country beijing 2345671 \N \N \N
+2 default_country shanghai 2345672 \N \N \N
+3 default_country guangzhou 2345673 \N \N \N
+4 default_country shenzhen 2345674 \N \N \N
+5 default_country hangzhou 2345675 \N \N \N
+6 default_country nanjing 2345676 \N \N \N
+7 default_country wuhan 2345677 \N \N \N
+8 default_country chengdu 2345678 \N \N \N
+9 default_country xian 2345679 \N \N \N
+10 default_country hefei 23456710 \N \N \N
+
+-- !select2 --
+10
+
+-- !select3 --
+1 default_country default_city 2345671 \N \N \N
+2 default_country shanghai 2345672 \N \N \N
+3 default_country beijing 2345673 \N \N \N
+4 default_country shenzhen 2345674 \N \N \N
+5 default_country hangzhou 2345675 \N \N \N
+6 default_country nanjing 2345676 \N \N \N
+7 default_country default_city 2345677 \N \N \N
+8 default_country chengdu 2345678 \N \N \N
+9 default_country default_city 2345679 \N \N \N
+10 default_country default_city 23456710 \N \N \N
+
+-- !select4 --
+10
diff --git
a/regression-test/suites/load_p0/insert/test_insert_default_value.groovy
b/regression-test/suites/load_p0/insert/test_insert_default_value.groovy
new file mode 100644
index 00000000000..1e894196e28
--- /dev/null
+++ b/regression-test/suites/load_p0/insert/test_insert_default_value.groovy
@@ -0,0 +1,85 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_insert_default_value") {
+
+ sql """ SET enable_fallback_to_original_planner=false """
+
+ sql """ DROP TABLE IF EXISTS test_insert_dft_tbl"""
+
+ sql """
+ CREATE TABLE test_insert_dft_tbl (
+ `k1` tinyint default 10,
+ `k2` smallint default 10000,
+ `k3` int default 10000000,
+ `k4` bigint default 92233720368547758,
+ `k5` largeint default 19223372036854775807,
+ `k6` decimal(10,2) default 10.3,
+ `k7` double default 10.3
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k1`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 5
+ PROPERTIES (
+ "replication_num"="1"
+ );
+ """
+
+ sql """ set enable_nereids_planner=true """
+ sql """ set enable_nereids_dml=true """
+ sql """ insert into test_insert_dft_tbl values() """
+
+ sql """ set enable_nereids_planner=false """
+ sql """ set enable_nereids_dml=false """
+ sql """ insert into test_insert_dft_tbl values() """
+ qt_select1 """ select k1, k2, k3, k4, k5, k6, k7 from test_insert_dft_tbl
"""
+
+ sql "drop table test_insert_dft_tbl"
+
+ sql """
+ CREATE TABLE test_insert_dft_tbl (
+ `k1` boolean default "true",
+ `k2` tinyint default 10,
+ `k3` smallint default 10000,
+ `k4` int default 10000000,
+ `k5` bigint default 92233720368547758,
+ `k6` largeint default 19223372036854775807,
+ `k7` double default 3.14159,
+ `k8` varchar(64) default "hello world, today is 15/06/2023",
+ `k9` date default "2023-06-15",
+ `k10` datetime default "2023-06-15 16:10:15",
+ `k11` decimal(10,2) default 10.3
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k1`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 5
+ PROPERTIES (
+ "replication_num"="1"
+ );
+ """
+
+ sql """ set enable_nereids_planner=true """
+ sql """ set enable_nereids_dml=true """
+ sql """ insert into test_insert_dft_tbl values() """
+
+ sql """ set enable_nereids_planner=false """
+ sql """ set enable_nereids_dml=false """
+ sql """ insert into test_insert_dft_tbl values() """
+ qt_select2 """ select k1, k2, k3, k4, k5, k6, k7, k8, k9, k10, k11 from
test_insert_dft_tbl """
+
+ sql "drop table test_insert_dft_tbl"
+}
\ No newline at end of file
diff --git
a/regression-test/suites/load_p0/stream_load/test_json_load_default_number_value.groovy
b/regression-test/suites/load_p0/stream_load/test_json_load_default_number_value.groovy
new file mode 100644
index 00000000000..66a4d791663
--- /dev/null
+++
b/regression-test/suites/load_p0/stream_load/test_json_load_default_number_value.groovy
@@ -0,0 +1,83 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_json_load_default_number_value") {
+
+ sql """ SET enable_nereids_planner=true """
+ sql """ SET enable_fallback_to_original_planner=false """
+
+ def testTable = "test_json_load_default_number_value"
+
+ def create_test_table = {testTablex ->
+ // multi-line sql
+ sql """ DROP TABLE IF EXISTS ${testTable} """
+ def result = sql """
+ CREATE TABLE IF NOT EXISTS ${testTable} (
+ id INT NOT NULL DEFAULT 0,
+ country VARCHAR(32) NULL DEFAULT 'default_country',
+ city VARCHAR(32) NULL DEFAULT 'default_city',
+ code BIGINT DEFAULT 1111,
+ date_time DATETIME DEFAULT CURRENT_TIMESTAMP,
+ flag CHAR,
+ name VARCHAR(64),
+ descript STRING)
+ DISTRIBUTED BY RANDOM BUCKETS 10
+ PROPERTIES("replication_allocation" =
"tag.location.default: 1");
+ """
+
+ // DDL/DML return 1 row and 3 column, the only value is update row
count
+ assertTrue(result.size() == 1)
+ assertTrue(result[0].size() == 1)
+ assertTrue(result[0][0] == 0, "Create table should update 0 rows")
+ }
+
+ def load_json_data = {strip_flag, read_flag, format_flag, json_paths,
file_name ->
+ // load the json data
+ streamLoad {
+ table testTable
+
+ // set http request header params
+ set 'strip_outer_array', strip_flag
+ set 'read_json_by_line', read_flag
+ set 'format', format_flag
+ set 'jsonpaths', json_paths
+ file file_name
+ }
+ }
+
+ // case1: import simple json lack one column
+ try {
+ create_test_table.call(testTable)
+ load_json_data.call('true', '', 'json', '', 'simple_json.json')
+ sql "sync"
+ qt_select1 "select id, country, city, code, flag, name, descript from
${testTable} order by id"
+ qt_select2 "select count(1) from ${testTable} where date_time is not
null"
+ } finally {
+ try_sql("DROP TABLE IF EXISTS ${testTable}")
+ }
+
+ // case2: import json lack one column of rows
+ try {
+ create_test_table.call(testTable)
+ load_json_data.call('true', '', 'json', '',
'simple_json2_lack_one_column.json')
+ sql "sync"
+ qt_select3 "select id, country, city, code, flag, name, descript from
${testTable} order by id"
+ qt_select4 "select count(1) from ${testTable} where date_time is not
null"
+ } finally {
+ try_sql("DROP TABLE IF EXISTS ${testTable}")
+ }
+}
\ No newline at end of file
diff --git
a/regression-test/suites/schema_change_p0/test_update_schema_change.groovy
b/regression-test/suites/schema_change_p0/test_update_schema_change.groovy
index 68671ab8245..0452294fff1 100644
--- a/regression-test/suites/schema_change_p0/test_update_schema_change.groovy
+++ b/regression-test/suites/schema_change_p0/test_update_schema_change.groovy
@@ -16,6 +16,10 @@
// under the License.
suite ("test_update_schema_change") {
+
+ sql """ SET enable_nereids_planner=true """
+ sql """ SET enable_fallback_to_original_planner=false """
+
def tableName = "schema_change_update_test"
def getAlterColumnJobState = { tbName ->
@@ -34,9 +38,9 @@ suite ("test_update_schema_change") {
`last_visit_date` DATETIME DEFAULT "1970-01-01 00:00:00" COMMENT
"用户最后一次访问时间",
`last_update_date` DATETIME DEFAULT "1970-01-01 00:00:00" COMMENT
"用户最后一次更新时间",
`last_visit_date_not_null` DATETIME NOT NULL DEFAULT "1970-01-01
00:00:00" COMMENT "用户最后一次访问时间",
- `cost` BIGINT DEFAULT "0" COMMENT "用户总消费",
- `max_dwell_time` INT DEFAULT "0" COMMENT "用户最大停留时间",
- `min_dwell_time` INT DEFAULT "99999" COMMENT "用户最小停留时间")
+ `cost` BIGINT DEFAULT 0 COMMENT "用户总消费",
+ `max_dwell_time` INT DEFAULT 0 COMMENT "用户最大停留时间",
+ `min_dwell_time` INT DEFAULT 99999 COMMENT "用户最小停留时间")
UNIQUE KEY(`user_id`, `date`, `city`, `age`, `sex`) DISTRIBUTED BY
HASH(`user_id`)
BUCKETS 8
PROPERTIES ( "replication_num" = "1" , "light_schema_change" =
"false");
@@ -60,7 +64,7 @@ suite ("test_update_schema_change") {
}
sql """
- ALTER table ${tableName} ADD COLUMN new_column INT default "1";
+ ALTER table ${tableName} ADD COLUMN new_column INT default 1;
"""
def max_try_secs = 60
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
