Repository: matrixorigin/matrixone
Pull request #5035 (2022-09-15 09:08:03)

fix show create table with auto_incr col
## What type of PR is this?

- [ ] API-change
- [x] BUG
- [ ] Improvement
- [ ] Documentation
- [ ] Feature
- [ ] Test and CI
- [ ] Code Refactoring

## Which issue(s) this PR fixes:

issue #4825 

## What this PR does / why we need it:
fix show create table with auto_incr col


Patch:
diff --git a/pkg/sql/plan/build_show.go b/pkg/sql/plan/build_show.go
index 2c798b703..6b27188c5 100644
--- a/pkg/sql/plan/build_show.go
+++ b/pkg/sql/plan/build_show.go
@@ -86,8 +86,15 @@ func buildShowCreateTable(stmt *tree.ShowCreateTable, ctx CompilerContext) (*Pla
 			continue
 		}
 		nullOrNot := "NOT NULL"
-		if col.Default != nil && col.Default.NullAbility {
-			nullOrNot = "DEFAULT NULL"
+		if col.Default != nil {
+			if col.Default.Expr != nil {
+				nullOrNot = "DEFAULT " + col.Default.OriginString
+			} else if col.Default.NullAbility {
+				nullOrNot = "DEFAULT NULL"
+			}
+		}
+		if col.AutoIncrement {
+			nullOrNot = "NOT NULL AUTO_INCREMENT"
 		}
 
 		var hasAttrComment string


Test patch:
diff --git a/test/cases/auto_increment/auto_increment_columns.result b/test/cases/auto_increment/auto_increment_columns.result
index fe8075814..3cdbd56a7 100644
--- a/test/cases/auto_increment/auto_increment_columns.result
+++ b/test/cases/auto_increment/auto_increment_columns.result
@@ -597,3 +597,8 @@ a    b    c
 5    5    'ccc'
 6    6    '%%'
 drop table t13;
+
+create table t1(a int, b int default(10), c int auto_increment);
+show create table t1;
+Table    Create Table
+t1    CREATE TABLE `t1` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT (10),\n`c` INT NOT NULL AUTO_INCREMENT\n)
\ No newline at end of file
diff --git a/test/cases/auto_increment/auto_increment_columns.sql b/test/cases/auto_increment/auto_increment_columns.sql
index 246963016..db81bbfef 100644
--- a/test/cases/auto_increment/auto_increment_columns.sql
+++ b/test/cases/auto_increment/auto_increment_columns.sql
@@ -397,4 +397,7 @@ c varchar(25)
 load data infile '$resources/auto_increment_columns/auto_increment_3.csv' into table t13;
 select * from t13 order by a;
 
-drop table t13;
\ No newline at end of file
+drop table t13;
+
+create table t1(a int, b int default(10), c int auto_increment);
+show create table t1;
diff --git a/test/result/ddl/table_partition.result b/test/result/ddl/table_partition.result
index b166639ac..e6bbcb2b6 100644
--- a/test/result/ddl/table_partition.result
+++ b/test/result/ddl/table_partition.result
@@ -63,7 +63,7 @@ PARTITION p3 VALUES LESS THAN (21)
 );
 show create table tp13;
 Table    Create Table
-tp13    CREATE TABLE `tp13` (\n`id` INT NOT NULL,\n`fname` VARCHAR(30) DEFAULT NULL,\n`lname` VARCHAR(30) DEFAULT NULL,\n`hired` DATE NOT NULL,\n`separated` DATE NOT NULL,\n`job_code` INT NOT NULL,\n`store_id` INT NOT NULL,\nPRIMARY KEY (`id`)\n) partition by range(id) (partition p0 values less than (6), partition p1 values less than (11), partition p2 values less than (16), partition p3 values less than (21))
+tp13    CREATE TABLE `tp13` (\n`id` INT NOT NULL,\n`fname` VARCHAR(30) DEFAULT NULL,\n`lname` VARCHAR(30) DEFAULT NULL,\n`hired` DATE DEFAULT 1970-01-01,\n`separated` DATE DEFAULT 9999-12-31,\n`job_code` INT NOT NULL,\n`store_id` INT NOT NULL,\nPRIMARY KEY (`id`)\n) partition by range(id) (partition p0 values less than (6), partition p1 values less than (11), partition p2 values less than (16), partition p3 values less than (21))
 CREATE TABLE tp14 (
 id INT NOT NULL,
 fname VARCHAR(30),
@@ -81,7 +81,7 @@ PARTITION p3 VALUES LESS THAN MAXVALUE
 );
 show create table tp14;
 Table    Create Table
-tp14    CREATE TABLE `tp14` (\n`id` INT NOT NULL,\n`fname` VARCHAR(30) DEFAULT NULL,\n`lname` VARCHAR(30) DEFAULT NULL,\n`hired` DATE NOT NULL,\n`separated` DATE NOT NULL,\n`job_code` INT DEFAULT NULL,\n`store_id` INT DEFAULT NULL\n) partition by range(year(separated)) (partition p0 values less than (1991), partition p1 values less than (1996), partition p2 values less than (2001), partition p3 values less than (MAXVALUE))
+tp14    CREATE TABLE `tp14` (\n`id` INT NOT NULL,\n`fname` VARCHAR(30) DEFAULT NULL,\n`lname` VARCHAR(30) DEFAULT NULL,\n`hired` DATE DEFAULT 1970-01-01,\n`separated` DATE DEFAULT 9999-12-31,\n`job_code` INT DEFAULT NULL,\n`store_id` INT DEFAULT NULL\n) partition by range(year(separated)) (partition p0 values less than (1991), partition p1 values less than (1996), partition p2 values less than (2001), partition p3 values less than (MAXVALUE))
 CREATE TABLE tp15 (
 a INT NOT NULL,
 b INT NOT NULL