[Bug]: SHOW xxx failed when running plan2 on TAE

[sukki37]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Environment

- Version or commit-id (e.g. v0.1.0 or 8b23a93): 8c8674e
- Hardware parameters:
- OS type:
- Others:
   special config:
    usePlan2 = true
    storageEngine = "aoe"

Actual Behavior

Expected Behavior

No response

Steps to Reproduce

No response

Additional information

No response

[sukki37]

@nnsgmsone can u give some explanations?

[nnsgmsone]

The implementation of show is strange, it's intended to be converted to query and generates some sql at first, but in the end it is converted to ddl again, but the compile does not add code to handle ddl show. The processing of this show does not seem to be self-consistent. This is the code for show database:

func buildShowDatabases(stmt *tree.ShowDatabases, ctx CompilerContext) (*Plan, error) {
	if stmt.Like != nil && stmt.Where != nil {
		return nil, errors.New(errno.SyntaxError, "like clause and where clause cannot exist at the same time")
	}
	ddlType := plan.DataDefinition_SHOW_DATABASES
	sql := fmt.Sprintf("SELECT datname `Database` FROM %s.mo_database", MO_CATALOG_DB_NAME)

	if stmt.Where != nil {
		return returnByWhereAndBaseSql(ctx, sql, stmt.Where, ddlType)
	}

	if stmt.Like != nil {
		// append filter [AND datname like stmt.Like] to WHERE clause
		likeExpr := stmt.Like
		likeExpr.Left = tree.SetUnresolvedName("datname")
		return returnByLikeAndSql(ctx, sql, likeExpr, ddlType)
	}

	return returnByRewriteSql(ctx, sql, ddlType)
}
func getReturnDdlBySelectStmt(ctx CompilerContext, stmt tree.Statement, ddlType plan.DataDefinition_DdlType) (*Plan, error) {
	queryPlan, err := BuildPlan(ctx, stmt)
	if err != nil {
		return nil, err
	}
	return &Plan{
		Plan: &plan.Plan_Ddl{
			Ddl: &plan.DataDefinition{
				DdlType: ddlType,
				Query:   queryPlan.GetQuery(),
			},
		},
	}, nil
}
func (c *compile) compileScope(pn *plan.Plan) (*Scope, error) {
	switch qry := pn.Plan.(type) {
	case *plan.Plan_Query:
		return c.compileQuery(qry.Query)
	case *plan.Plan_Ddl:
		switch qry.Ddl.DdlType {
		case plan.DataDefinition_CREATE_DATABASE:
			return &Scope{
				Magic: CreateDatabase,
				Plan:  pn,
			}, nil
		case plan.DataDefinition_DROP_DATABASE:
			return &Scope{
				Magic: DropDatabase,
				Plan:  pn,
			}, nil
		case plan.DataDefinition_CREATE_TABLE:
			return &Scope{
				Magic: CreateTable,
				Plan:  pn,
			}, nil
		case plan.DataDefinition_DROP_TABLE:
			return &Scope{
				Magic: DropTable,
				Plan:  pn,
			}, nil
		case plan.DataDefinition_CREATE_INDEX:
			return &Scope{
				Magic: CreateIndex,
				Plan:  pn,
			}, nil
		case plan.DataDefinition_DROP_INDEX:
			return &Scope{
				Magic: DropIndex,
				Plan:  pn,
			}, nil
		}
	}
	return nil, errors.New(errno.SyntaxErrororAccessRuleViolation, fmt.Sprintf("query '%s' not support now", pn))
}

[nnsgmsone]

In addition, both aoe and /tae contain catalog information, but aoe needs to abstract kv's catalog into an engine interface, while tae only needs to use its own catalog.

[ouyuanning]

After discussion with @nnsgmsone.
Suggest change compileScope() method like that:

func (c *compile) compileScope(pn *plan.Plan) (*Scope, error) {
	switch qry := pn.Plan.(type) {
	case *plan.Plan_Query:
		return c.compileQuery(qry.Query)
	case *plan.Plan_Ddl:
		switch qry.Ddl.DdlType {
		case plan.DataDefinition_CREATE_DATABASE:
			return &Scope{
				Magic: CreateDatabase,
				Plan:  pn,
			}, nil
		case plan.DataDefinition_DROP_DATABASE:
			return &Scope{
				Magic: DropDatabase,
				Plan:  pn,
			}, nil
		case plan.DataDefinition_CREATE_TABLE:
			return &Scope{
				Magic: CreateTable,
				Plan:  pn,
			}, nil
		case plan.DataDefinition_DROP_TABLE:
			return &Scope{
				Magic: DropTable,
				Plan:  pn,
			}, nil
		case plan.DataDefinition_CREATE_INDEX:
			return &Scope{
				Magic: CreateIndex,
				Plan:  pn,
			}, nil
		case plan.DataDefinition_DROP_INDEX:
			return &Scope{
				Magic: DropIndex,
				Plan:  pn,
			}, nil
                // add case like this
		case plan.DataDefinition_SHOW_DATABASES, plan.DataDefinition_SHOW_TABLES.......:
			return c.compileQuery(pn.GetDdl().GetQuery())
		}
	}
	return nil, errors.New(errno.SyntaxErrororAccessRuleViolation, fmt.Sprintf("query '%s' not support now", pn))
}

[sukki37]

no error returned, but still wrong result.

[nnsgmsone]

catalog tables' row is always zero，this is a bug.

[sukki37]

Still remain some bugs with handling SHOW COLUMS FROM xxx

[XuPeng-SH]

Checked with @daviszhen. SHOW COLUMNS is still in development for TAE.

[sukki37]

tested with 08db8ed