Repository: matrixorigin/matrixone
Pull request #4695 (2022-08-25 07:06:35)

fix bug:show create table xx when xx is a view
## What type of PR is this?

- [ ] API-change
- [x] BUG
- [ ] Improvement
- [ ] Documentation
- [ ] Feature
- [ ] Test and CI
- [x] Code Refactoring

## Which issue(s) this PR fixes:

issue #4643

## What this PR does / why we need it:
1、fix bug:show create table xx when xx is a view
2、refactor:  move show create table/database from frontend to plan



Patch:
diff --git a/pkg/frontend/mysql_cmd_executor.go b/pkg/frontend/mysql_cmd_executor.go
index 0905dc855..e6e276f42 100644
--- a/pkg/frontend/mysql_cmd_executor.go
+++ b/pkg/frontend/mysql_cmd_executor.go
@@ -265,7 +265,7 @@ func (o *outputQueue) flush() error {
 		}
 	} else {
 		//send group of row
-		if o.showStmtType == ShowCreateTable || o.showStmtType == ShowCreateDatabase || o.showStmtType == ShowColumns {
+		if o.showStmtType == ShowColumns {
 			o.rowIdx = 0
 			return nil
 		}
@@ -303,116 +303,31 @@ func (foq *fakeOutputQueue) flush() error {
 }
 
 const (
-	tableNamePos    = 1
-	tableCommentPos = 6
-
-	attrNamePos    = 17
-	attrTypPos     = 18
-	charWidthPos   = 20
-	nullablePos    = 21
-	primaryKeyPos  = 25
-	attrCommentPos = 28
-
-	showCreateTableAttrCount = 30
+	primaryKeyPos = 25
 )
 
-/*
-handle show create table in plan2 and tae
-*/
-func handleShowCreateTable(ses *Session) error {
-	tableName := string(ses.Data[0][tableNamePos].([]byte))
-	createStr := fmt.Sprintf("CREATE TABLE `%s` (", tableName)
-	rowCount := 0
-	var pkDefs []string
-	for _, d := range ses.Data {
-		colName := string(d[attrNamePos].([]byte))
-		if colName == "PADDR" {
-			continue
-		}
-		nullOrNot := "NOT NULL"
-		if d[nullablePos].(int8) == 1 {
-			nullOrNot = "DEFAULT NULL"
-		}
-
-		var hasAttrComment string
-		attrComment := string(d[attrCommentPos].([]byte))
-		if attrComment != "" {
-			hasAttrComment = " COMMENT '" + attrComment + "'"
-		}
-
-		if rowCount == 0 {
-			createStr += "\n"
-		} else {
-			createStr += ",\n"
-		}
-		typ := types.Type{Oid: types.T(d[attrTypPos].(int32))}
-		typeStr := typ.String()
-		if typ.Oid == types.T_varchar || typ.Oid == types.T_char {
-			typeStr += fmt.Sprintf("(%d)", d[charWidthPos].(int32))
-		}
-		createStr += fmt.Sprintf("`%s` %s %s%s", colName, typeStr, nullOrNot, hasAttrComment)
-		rowCount++
-		if string(d[primaryKeyPos].([]byte)) == "p" {
-			pkDefs = append(pkDefs, colName)
-		}
-	}
-	if len(pkDefs) != 0 {
-		pkStr := "PRIMARY KEY ("
-		for _, def := range pkDefs {
-			pkStr += fmt.Sprintf("`%s`", def)
-		}
-		pkStr += ")"
-		if rowCount != 0 {
-			createStr += ",\n"
-		}
-		createStr += pkStr
-	}
-
-	if rowCount != 0 {
-		createStr += "\n"
-	}
-	createStr += ")"
-
-	tableComment := string(ses.Data[0][tableCommentPos].([]byte))
-	if tableComment != "" {
-		createStr += " COMMENT='" + tableComment + "',"
-	}
-
-	row := make([]interface{}, 2)
-	row[0] = tableName
-	row[1] = createStr
-
-	ses.Mrs.AddRow(row)
-
-	if err := ses.GetMysqlProtocol().SendResultSetTextBatchRowSpeedup(ses.Mrs, 1); err != nil {
-		logutil.Errorf("handleShowCreateTable error %v \n", err)
-		return err
-	}
-	return nil
-}
-
 /*
 handle show create database in plan2 and tae
 */
-func handleShowCreateDatabase(ses *Session) error {
-	dbNameIndex := ses.Mrs.Name2Index["Database"]
-	dbsqlIndex := ses.Mrs.Name2Index["Create Database"]
-	firstRow := ses.Data[0]
-	dbName := firstRow[dbNameIndex]
-	createDBSql := fmt.Sprintf("CREATE DATABASE `%s`", dbName)
-	firstRow[dbsqlIndex] = createDBSql
-
-	row := make([]interface{}, 2)
-	row[0] = dbName
-	row[1] = createDBSql
-
-	ses.Mrs.AddRow(row)
-	if err := ses.GetMysqlProtocol().SendResultSetTextBatchRowSpeedup(ses.Mrs, 1); err != nil {
-		logutil.Errorf("handleShowCreateDatabase error %v \n", err)
-		return err
-	}
-	return nil
-}
+// func handleShowCreateDatabase(ses *Session) error {
+// 	dbNameIndex := ses.Mrs.Name2Index["Database"]
+// 	dbsqlIndex := ses.Mrs.Name2Index["Create Database"]
+// 	firstRow := ses.Data[0]
+// 	dbName := firstRow[dbNameIndex]
+// 	createDBSql := fmt.Sprintf("CREATE DATABASE `%s`", dbName)
+// 	firstRow[dbsqlIndex] = createDBSql
+
+// 	row := make([]interface{}, 2)
+// 	row[0] = dbName
+// 	row[1] = createDBSql
+
+// 	ses.Mrs.AddRow(row)
+// 	if err := ses.GetMysqlProtocol().SendResultSetTextBatchRowSpeedup(ses.Mrs, 1); err != nil {
+// 		logutil.Errorf("handleShowCreateDatabase error %v \n", err)
+// 		return err
+// 	}
+// 	return nil
+// }
 
 /*
 handle show columns from table in plan2 and tae
@@ -524,7 +439,7 @@ func getDataFromPipeline(obj interface{}, bat *batch.Batch) error {
 		if err != nil {
 			return err
 		}
-		if oq.showStmtType == ShowCreateDatabase || oq.showStmtType == ShowCreateTable || oq.showStmtType == ShowColumns {
+		if oq.showStmtType == ShowColumns {
 			row2 := make([]interface{}, len(row))
 			copy(row2, row)
 			ses.Data = append(ses.Data, row2)
@@ -1554,11 +1469,6 @@ func (cwft *TxnComputationWrapper) GetColumns() ([]interface{}, error) {
 	var err error
 	cols := plan2.GetResultColumnsFromPlan(cwft.plan)
 	switch cwft.GetAst().(type) {
-	case *tree.ShowCreateTable:
-		cols = []*plan2.ColDef{
-			{Typ: &plan2.Type{Id: int32(types.T_char)}, Name: "Table"},
-			{Typ: &plan2.Type{Id: int32(types.T_char)}, Name: "Create Table"},
-		}
 	case *tree.ShowColumns:
 		cols = []*plan2.ColDef{
 			{Typ: &plan2.Type{Id: int32(types.T_char)}, Name: "Field"},
@@ -1936,12 +1846,6 @@ func (mce *MysqlCmdExecutor) doComQuery(requestCtx context.Context, sql string)
 		case *tree.ShowColumns:
 			ses.showStmtType = ShowColumns
 			ses.Data = nil
-		case *tree.ShowCreateDatabase:
-			ses.showStmtType = ShowCreateDatabase
-			ses.Data = nil
-		case *tree.ShowCreateTable:
-			ses.showStmtType = ShowCreateTable
-			ses.Data = nil
 		case *tree.Delete:
 			ses.GetTxnCompileCtx().SetQueryType(TXN_DELETE)
 		case *tree.Update:
@@ -2036,15 +1940,7 @@ func (mce *MysqlCmdExecutor) doComQuery(requestCtx context.Context, sql string)
 			if err = runner.Run(0); err != nil {
 				goto handleFailed
 			}
-			if ses.showStmtType == ShowCreateTable {
-				if err = handleShowCreateTable(ses); err != nil {
-					goto handleFailed
-				}
-			} else if ses.showStmtType == ShowCreateDatabase {
-				if err = handleShowCreateDatabase(ses); err != nil {
-					goto handleFailed
-				}
-			} else if ses.showStmtType == ShowColumns {
+			if ses.showStmtType == ShowColumns {
 				if err = handleShowColumns(ses); err != nil {
 					goto handleFailed
 				}
diff --git a/pkg/frontend/session.go b/pkg/frontend/session.go
index f5d0000ec..1debe9117 100644
--- a/pkg/frontend/session.go
+++ b/pkg/frontend/session.go
@@ -42,10 +42,8 @@ const MaxPrepareNumberInOneSession = 64
 type ShowStatementType int
 
 const (
-	NotShowStatement   ShowStatementType = 0
-	ShowCreateDatabase ShowStatementType = 1
-	ShowCreateTable    ShowStatementType = 2
-	ShowColumns        ShowStatementType = 3
+	NotShowStatement ShowStatementType = 0
+	ShowColumns      ShowStatementType = 1
 )
 
 type TxnHandler struct {
@@ -785,6 +783,7 @@ func (tcc *TxnCompilerContext) Resolve(dbName string, tableName string) (*plan2.
 
 	var cols []*plan2.ColDef
 	var defs []*plan2.TableDefType
+	var properties []*plan2.Property
 	var TableType, Createsql string
 	for _, def := range engineDefs {
 		if attr, ok := def.(*engine.AttributeDef); ok {
@@ -798,10 +797,10 @@ func (tcc *TxnCompilerContext) Resolve(dbName string, tableName string) (*plan2.
 				},
 				Primary: attr.Attr.Primary,
 				Default: attr.Attr.Default,
+				Comment: attr.Attr.Comment,
 			})
 		} else if pro, ok := def.(*engine.PropertiesDef); ok {
-			properties := make([]*plan2.Property, len(pro.Properties))
-			for i, p := range pro.Properties {
+			for _, p := range pro.Properties {
 				switch p.Key {
 				case catalog.SystemRelAttr_Kind:
 					TableType = p.Value
@@ -809,18 +808,11 @@ func (tcc *TxnCompilerContext) Resolve(dbName string, tableName string) (*plan2.
 					Createsql = p.Value
 				default:
 				}
-				properties[i] = &plan2.Property{
+				properties = append(properties, &plan2.Property{
 					Key:   p.Key,
 					Value: p.Value,
-				}
+				})
 			}
-			defs = append(defs, &plan2.TableDefType{
-				Def: &plan2.TableDef_DefType_Properties{
-					Properties: &plan2.PropertiesDef{
-						Properties: properties,
-					},
-				},
-			})
 		} else if viewDef, ok := def.(*engine.ViewDef); ok {
 			defs = append(defs, &plan2.TableDefType{
 				Def: &plan2.TableDef_DefType_View{
@@ -829,8 +821,23 @@ func (tcc *TxnCompilerContext) Resolve(dbName string, tableName string) (*plan2.
 					},
 				},
 			})
+		} else if commnetDef, ok := def.(*engine.CommentDef); ok {
+			properties = append(properties, &plan2.Property{
+				Key:   catalog.SystemRelAttr_Comment,
+				Value: commnetDef.Comment,
+			})
 		}
 	}
+	if len(properties) > 0 {
+		defs = append(defs, &plan2.TableDefType{
+			Def: &plan2.TableDef_DefType_Properties{
+				Properties: &plan2.PropertiesDef{
+					Properties: properties,
+				},
+			},
+		})
+	}
+
 	if tcc.QryTyp != TXN_DEFAULT {
 		hideKeys, err := table.GetHideKeys(ctx)
 		if err != nil {
diff --git a/pkg/sql/plan/build_show.go b/pkg/sql/plan/build_show.go
index 1775d2434..90397c019 100644
--- a/pkg/sql/plan/build_show.go
+++ b/pkg/sql/plan/build_show.go
@@ -19,6 +19,7 @@ import (
 	"fmt"
 	"strings"
 
+	"github.com/matrixorigin/matrixone/pkg/container/types"
 	"github.com/matrixorigin/matrixone/pkg/errno"
 	"github.com/matrixorigin/matrixone/pkg/pb/plan"
 	"github.com/matrixorigin/matrixone/pkg/sql/errors"
@@ -35,18 +36,20 @@ func buildShowCreateDatabase(stmt *tree.ShowCreateDatabase, ctx CompilerContext)
 		return nil, errors.New(errno.InvalidDatabaseDefinition, fmt.Sprintf("database '%v' is not exist", stmt.Name))
 	}
 
+	// get data from schema
 	//sql := fmt.Sprintf("SELECT md.datname as `Database` FROM %s.mo_database md WHERE md.datname = '%s'", MO_CATALOG_DB_NAME, stmt.Name)
-	sql := fmt.Sprintf("SELECT md.datname as `Database`,dat_createsql as `Create Database` FROM %s.mo_database md WHERE md.datname = '%s'", MO_CATALOG_DB_NAME, stmt.Name)
-	return returnByRewriteSQL(ctx, sql, plan.DataDefinition_SHOW_CREATEDATABASE)
+	// sql := fmt.Sprintf("SELECT md.datname as `Database`,dat_createsql as `Create Database` FROM %s.mo_database md WHERE md.datname = '%s'", MO_CATALOG_DB_NAME, stmt.Name)
+	// return returnByRewriteSQL(ctx, sql, plan.DataDefinition_SHOW_CREATEDATABASE)
+
+	sqlStr := "select \"%s\" as `Database`, \"%s\" as `Create Database`"
+	createSql := fmt.Sprintf("CREATE DATABASE `%s`", stmt.Name)
+	sqlStr = fmt.Sprintf(sqlStr, stmt.Name, createSql)
+	// log.Println(sqlStr)
+
+	return returnByRewriteSQL(ctx, sqlStr, plan.DataDefinition_SHOW_CREATEDATABASE)
 }
 
 func buildShowCreateTable(stmt *tree.ShowCreateTable, ctx CompilerContext) (*Plan, error) {
-	sql := `
-		SELECT * 
-			FROM %s.mo_tables mt JOIN %s.mo_columns mc 
-				ON mt.relname = mc.att_relname and mt.reldatabase=mc.att_database 
-		WHERE mt.reldatabase = '%s' AND mt.relname = '%s'
-	`
 	tblName := stmt.Name.Parts[0]
 	dbName := ctx.DefaultDatabase()
 	if stmt.Name.NumParts == 2 {
@@ -57,10 +60,86 @@ func buildShowCreateTable(stmt *tree.ShowCreateTable, ctx CompilerContext) (*Pla
 	if tableDef == nil {
 		return nil, errors.New(errno.UndefinedTable, fmt.Sprintf("table '%v' doesn't exist", tblName))
 	}
+	if tableDef.TableType == catalog.SystemViewRel {
+		newStmt := tree.NewShowCreateView(tree.SetUnresolvedObjectName(1, [3]string{tblName, "", ""}))
+		return buildShowCreateView(newStmt, ctx)
+	}
 
-	sql = fmt.Sprintf(sql, MO_CATALOG_DB_NAME, MO_CATALOG_DB_NAME, dbName, tblName)
+	// sql := `
+	// 	SELECT *
+	// 		FROM %s.mo_tables mt JOIN %s.mo_columns mc
+	// 			ON mt.relname = mc.att_relname and mt.reldatabase=mc.att_database
+	// 	WHERE mt.reldatabase = '%s' AND mt.relname = '%s'
+	// `
+	// sql = fmt.Sprintf(sql, MO_CATALOG_DB_NAME, MO_CATALOG_DB_NAME, dbName, tblName)
 	// log.Println(sql)
 
+	createStr := fmt.Sprintf("CREATE TABLE `%s` (", tblName)
+	rowCount := 0
+	var pkDefs []string
+
+	for _, col := range tableDef.Cols {
+
+		colName := col.Name
+		if colName == "PADDR" {
+			continue
+		}
+		nullOrNot := "NOT NULL"
+		if col.Default != nil && col.Default.NullAbility {
+			nullOrNot = "DEFAULT NULL"
+		}
+
+		var hasAttrComment string
+		if col.Comment != "" {
+			hasAttrComment = " COMMENT '" + col.Comment + "'"
+		}
+
+		if rowCount == 0 {
+			createStr += "\n"
+		} else {
+			createStr += ",\n"
+		}
+		typ := types.Type{Oid: types.T(col.Typ.Id)}
+		typeStr := typ.String()
+		if typ.Oid == types.T_varchar || typ.Oid == types.T_char {
+			typeStr += fmt.Sprintf("(%d)", col.Typ.Width)
+		}
+		createStr += fmt.Sprintf("`%s` %s %s%s", colName, typeStr, nullOrNot, hasAttrComment)
+		rowCount++
+		if col.Primary {
+			pkDefs = append(pkDefs, colName)
+		}
+	}
+	if len(pkDefs) != 0 {
+		pkStr := "PRIMARY KEY ("
+		for _, def := range pkDefs {
+			pkStr += fmt.Sprintf("`%s`", def)
+		}
+		pkStr += ")"
+		if rowCount != 0 {
+			createStr += ",\n"
+		}
+		createStr += pkStr
+	}
+
+	if rowCount != 0 {
+		createStr += "\n"
+	}
+	createStr += ")"
+
+	for _, def := range tableDef.Defs {
+		if proDef, ok := def.Def.(*plan.TableDef_DefType_Properties); ok {
+			for _, kv := range proDef.Properties.Properties {
+				if kv.Key == catalog.SystemRelAttr_Comment {
+					createStr += " COMMENT='" + kv.Value + "',"
+				}
+			}
+		}
+	}
+
+	sql := "select \"%s\" as `Table`, \"%s\" as `Create Table`"
+	sql = fmt.Sprintf(sql, tblName, createStr)
+
 	return returnByRewriteSQL(ctx, sql, plan.DataDefinition_SHOW_CREATETABLE)
 }
 
diff --git a/pkg/vm/engine/tae/moengine/helper.go b/pkg/vm/engine/tae/moengine/helper.go
index 27efe3015..0bc71ca0d 100644
--- a/pkg/vm/engine/tae/moengine/helper.go
+++ b/pkg/vm/engine/tae/moengine/helper.go
@@ -75,6 +75,7 @@ func SchemaToDefs(schema *catalog.Schema) (defs []engine.TableDef, err error) {
 				Name:    col.Name,
 				Type:    col.Type,
 				Primary: col.IsPrimary(),
+				Comment: col.Comment,
 				Default: &plan.Default{
 					NullAbility:  col.Default.NullAbility,
 					OriginString: col.Default.OriginString,


Test patch:
diff --git a/pkg/frontend/mysql_cmd_executor_test.go b/pkg/frontend/mysql_cmd_executor_test.go
index f04be840f..b3af8cca6 100644
--- a/pkg/frontend/mysql_cmd_executor_test.go
+++ b/pkg/frontend/mysql_cmd_executor_test.go
@@ -772,78 +772,6 @@ func Test_GetComputationWrapper(t *testing.T) {
 	})
 }
 
-func Test_handleShowCreateTable(t *testing.T) {
-	ctx := context.TODO()
-	convey.Convey("handleShowCreateTable succ", t, func() {
-		ctrl := gomock.NewController(t)
-		defer ctrl.Finish()
-		ioses := mock_frontend.NewMockIOSession(ctrl)
-		ioses.EXPECT().OutBuf().Return(buf.NewByteBuf(1024)).AnyTimes()
-		ioses.EXPECT().Write(gomock.Any(), gomock.Any()).Return(nil).AnyTimes()
-
-		eng := mock_frontend.NewMockEngine(ctrl)
-		txnClient := mock_frontend.NewMockTxnClient(ctrl)
-		pu, err := getParameterUnit("test/system_vars_config.toml", eng, txnClient)
-		if err != nil {
-			t.Error(err)
-		}
-		proto := NewMysqlClientProtocol(0, ioses, 1024, pu.SV)
-		guestMmu := guest.New(pu.SV.GuestMmuLimitation, pu.HostMmu)
-		var gSys GlobalSystemVariables
-		InitGlobalSystemVariables(&gSys)
-		ses := NewSession(proto, guestMmu, pu.Mempool, pu, &gSys)
-		ses.SetRequestContext(ctx)
-		ses.Data = make([][]interface{}, 1)
-		ses.Data[0] = make([]interface{}, showCreateTableAttrCount)
-		ses.Data[0][tableNamePos] = []byte("tableName")
-		ses.Data[0][tableCommentPos] = []byte("table comment")
-		ses.Data[0][attrNamePos] = []byte("col1")
-		ses.Data[0][attrTypPos] = int32(1)
-		ses.Data[0][charWidthPos] = int32(1)
-		ses.Data[0][nullablePos] = int8(1)
-		ses.Data[0][primaryKeyPos] = []byte("p")
-		ses.Data[0][attrCommentPos] = []byte("column comment")
-
-		ses.Mrs = &MysqlResultSet{}
-		err = handleShowCreateTable(ses)
-		convey.So(err, convey.ShouldBeNil)
-	})
-}
-
-func Test_handleShowCreateDatabase(t *testing.T) {
-	ctx := context.TODO()
-	convey.Convey("handleShowCreateDatabase succ", t, func() {
-		ctrl := gomock.NewController(t)
-		defer ctrl.Finish()
-		ioses := mock_frontend.NewMockIOSession(ctrl)
-		ioses.EXPECT().OutBuf().Return(buf.NewByteBuf(1024)).AnyTimes()
-		ioses.EXPECT().Write(gomock.Any(), gomock.Any()).Return(nil).AnyTimes()
-
-		eng := mock_frontend.NewMockEngine(ctrl)
-		txnClient := mock_frontend.NewMockTxnClient(ctrl)
-		pu, err := getParameterUnit("test/system_vars_config.toml", eng, txnClient)
-		if err != nil {
-			t.Error(err)
-		}
-		proto := NewMysqlClientProtocol(0, ioses, 1024, pu.SV)
-		guestMmu := guest.New(pu.SV.GuestMmuLimitation, pu.HostMmu)
-		var gSys GlobalSystemVariables
-		InitGlobalSystemVariables(&gSys)
-		ses := NewSession(proto, guestMmu, pu.Mempool, pu, &gSys)
-		ses.SetRequestContext(ctx)
-
-		ses.Mrs = &MysqlResultSet{}
-		ses.Mrs.Name2Index = make(map[string]uint64)
-		ses.Mrs.Name2Index["Database"] = 1
-		ses.Mrs.Name2Index["Create Database"] = 2
-		ses.Data = make([][]interface{}, 1)
-		ses.Data[0] = make([]interface{}, 3)
-
-		err = handleShowCreateDatabase(ses)
-		convey.So(err, convey.ShouldBeNil)
-	})
-}
-
 func Test_handleShowColumns(t *testing.T) {
 	ctx := context.TODO()
 	convey.Convey("handleShowColumns succ", t, func() {
diff --git a/pkg/sql/plan/build_test.go b/pkg/sql/plan/build_test.go
index bfcb0c10f..3aa0ba3c3 100644
--- a/pkg/sql/plan/build_test.go
+++ b/pkg/sql/plan/build_test.go
@@ -760,6 +760,7 @@ func TestShow(t *testing.T) {
 		"show columns from nation from tpch",
 		"show columns from nation where `Field` like '%ff' or `Type` = 1 or `Null` = 0",
 		"show create view v1",
+		"show create table v1",
 	}
 	runTestShouldPass(mock, t, sqls, false, false)
 
diff --git a/test/cases/view/view.test b/test/cases/view/view.test
index b8fe938a8..a753c7703 100644
--- a/test/cases/view/view.test
+++ b/test/cases/view/view.test
@@ -21,5 +21,6 @@ create view v1 as select "a";
 show create view v1;
 create view v2 as select 'a';
 show create view v2;
+show create table v2;
 drop view v1;
 drop view v2;
\ No newline at end of file
diff --git a/test/result/view/view.result b/test/result/view/view.result
index 9d7d99d53..2b30ef03f 100644
--- a/test/result/view/view.result
+++ b/test/result/view/view.result
@@ -37,5 +37,8 @@ create view v2 as select 'a';
 show create view v2;
 View    Create View
 v2    create view v2 as select 'a';
+show create table v2;
+View    Create View
+v2    create view v2 as select 'a';
 drop view v1;
 drop view v2;
\ No newline at end of file