[Bug] CREATE TABLE LIKE Exception

[hf200012]

Search before asking

• [X] I had searched in the issues and found no similar issues.

Version

1.0

What's Wrong?

CREATE TABLE `expamle_tbl` (
  `user_id` largeint(40) NOT NULL COMMENT "用户id",
  `username` varchar(50) NOT NULL COMMENT "用户昵称",
  `city` varchar(20) NULL COMMENT "用户所在城市",
  `age` smallint(6) NULL COMMENT "用户年龄",
  `sex` tinyint(4) NULL COMMENT "用户性别",
  `phone` largeint(40) NULL COMMENT "用户电话",
  `address` varchar(500) NULL COMMENT "用户地址",
  `register_time` datetime NULL COMMENT "用户注册时间",
  `__DORIS_DELETE_SIGN__` tinyint(4) NOT NULL DEFAULT "0" COMMENT "doris delete flag hidden column"
) ENGINE=OLAP
UNIQUE KEY(`user_id`, `username`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2"
)

mysql> desc expamle_tbl;
+-----------------------+--------------+------+-------+---------+---------+
| Field                 | Type         | Null | Key   | Default | Extra   |
+-----------------------+--------------+------+-------+---------+---------+
| user_id               | LARGEINT     | No   | true  | NULL    |         |
| username              | VARCHAR(50)  | No   | true  | NULL    |         |
| city                  | VARCHAR(20)  | Yes  | false | NULL    | REPLACE |
| age                   | SMALLINT     | Yes  | false | NULL    | REPLACE |
| sex                   | TINYINT      | Yes  | false | NULL    | REPLACE |
| phone                 | LARGEINT     | Yes  | false | NULL    | REPLACE |
| address               | VARCHAR(500) | Yes  | false | NULL    | REPLACE |
| register_time         | DATETIME     | Yes  | false | NULL    | REPLACE |
| __DORIS_DELETE_SIGN__ | TINYINT      | No   | false | 0       | REPLACE |
+-----------------------+--------------+------+-------+---------+---------+
9 rows in set (0.00 sec)

If SET show_hidden_columns=true is set;

mysql> create table expamle_tbl_1 like expamle_tbl;
ERROR 1105 (HY000): errCode = 2, detailMessage = Failed to execute CREATE TABLE LIKE expamle_tbl. Reason: errCode = 2, detailMessage = Duplicate column name '__DORIS_DELETE_SIGN__'

Change to SET show_hidden_columns=false; it is normal

mysql> show create table expamle_tbl;

create table vehicle_state_record_1 like vehicle_state_record; ERROR 1105 (HY000): errCode = 2, detailMessage = Failed to execute CREATE TABLE LIKE vehicle_state_record. Reason: errCode = 2, detailMessage = Duplicate column name 'DORIS_DELETE_SIGN'

What You Expected?

success

How to Reproduce?

No response

Anything Else?

No response

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[stalary]

Let me have a look.

[hf200012]

Let me have a look.

OK

[stalary]

__DORIS_DELETE_SIGN__ is a built-in field and should not be used when create table. I have tried it and it is normal to not have this field

[nextdreamblue]

i meet some error like this issue.

i reproduce it like this :

MySQL [abc]> ADMIN SET FRONTEND CONFIG ("enable_batch_delete_by_default" = "true"); Query OK, 0 rows affected (0.01 sec)

MySQL [abc]> MySQL [abc]> SET show_hidden_columns=true; Query OK, 0 rows affected (0.00 sec)

MySQL [abc]> use aaa Database changed MySQL [aaa]> CREATE TABLE expamle_1 ( user_id largeint(40) NOT NULL COMMENT "用户id", username varchar(50) NOT NULL COMMENT "用户昵称" ) ENGINE=OLAP UNIQUE KEY(user_id, username) COMMENT "OLAP" DISTRIBUTED BY HASH(user_id) BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "in_memory" = "false", "storage_format" = "V2" ); Query OK, 0 rows affected (4.47 sec)

MySQL [aaa]> show create table expamle_1; +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | expamle_1 | CREATE TABLE expamle_1 ( user_id largeint(40) NOT NULL COMMENT "用户id", username varchar(50) NOT NULL COMMENT "用户昵称", __DORIS_DELETE_SIGN__ tinyint(4) NOT NULL DEFAULT "0" COMMENT "doris delete flag hidden column" ) ENGINE=OLAP UNIQUE KEY(user_id, username) COMMENT "OLAP" DISTRIBUTED BY HASH(user_id) BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "in_memory" = "false", "storage_format" = "V2" ) | +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (13.05 sec)

MySQL [aaa]> create table expamle_2 like expamle_1; ERROR 1105 (HY000): errCode = 2, detailMessage = Failed to execute CREATE TABLE LIKE expamle_1. Reason: errCode = 2, detailMessage = Duplicate column name 'DORIS_DELETE_SIGN'

[nextdreamblue]

i commit a pr to fix this bug . https://github.com/apache/incubator-doris/pull/9694

[nextdreamblue]

@stalary i think it is a bug, please check it